details: https://code.openbravo.com/erp/devel/pi/rev/1171fec5325a changeset: 17400:1171fec5325a user: Gorka Ion Damián <gorkaion.damian <at> openbravo.com> date: Mon Jul 02 17:52:26 2012 +0200 summary: [Costing]Fix issues in valued stock report.
details: https://code.openbravo.com/erp/devel/pi/rev/6b96be838bc8 changeset: 17401:6b96be838bc8 user: Gorka Ion Damián <gorkaion.damian <at> openbravo.com> date: Tue Jul 03 10:52:18 2012 +0200 summary: [Costing]Add javadoc to public method. diffstat: src/org/openbravo/costing/CostingUtils.java | 3 + src/org/openbravo/erpCommon/ad_reports/ReportValuationStock.java | 45 ++++++++++ src/org/openbravo/erpCommon/ad_reports/ReportValuationStock_data.xsql | 29 ++++- 3 files changed, 69 insertions(+), 8 deletions(-) diffs (140 lines): diff -r f313b3335867 -r 6b96be838bc8 src/org/openbravo/costing/CostingUtils.java --- a/src/org/openbravo/costing/CostingUtils.java Mon Jul 02 16:27:37 2012 +0200 +++ b/src/org/openbravo/costing/CostingUtils.java Tue Jul 03 10:52:18 2012 +0200 @@ -278,6 +278,9 @@ } } + /** + * Returns the newer order line for the given product, business partner and organization. + */ public static OrderLine getOrderLine(Product product, BusinessPartner bp, Organization org) { OrganizationStructureProvider osp = OBContext.getOBContext().getOrganizationStructureProvider(); diff -r f313b3335867 -r 6b96be838bc8 src/org/openbravo/erpCommon/ad_reports/ReportValuationStock.java --- a/src/org/openbravo/erpCommon/ad_reports/ReportValuationStock.java Mon Jul 02 16:27:37 2012 +0200 +++ b/src/org/openbravo/erpCommon/ad_reports/ReportValuationStock.java Tue Jul 03 10:52:18 2012 +0200 @@ -31,17 +31,23 @@ import org.openbravo.dal.core.DalUtil; import org.openbravo.dal.core.OBContext; import org.openbravo.dal.service.OBDal; +import org.openbravo.dal.service.OBQuery; import org.openbravo.erpCommon.businessUtility.WindowTabs; import org.openbravo.erpCommon.utility.ComboTableData; import org.openbravo.erpCommon.utility.DateTimeData; 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.ToolBar; import org.openbravo.erpCommon.utility.Utility; import org.openbravo.financial.FinancialUtils; +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.transaction.MaterialTransaction; import org.openbravo.xmlEngine.XmlDocument; public class ReportValuationStock extends HttpSecureAppServlet { @@ -111,6 +117,14 @@ data = ReportValuationStockData.select(this, vars.getLanguage(), strBaseCurrencyId, strCurrencyId, strDate, legalEntity.getId(), DateTimeData.nDaysAfter(this, strDate, "1"), strWarehouse, strCategoryProduct); + boolean hasTrxWithNoCost = hasTrxWithNoCost(strDate, strWarehouse, strCategoryProduct); + if (hasTrxWithNoCost) { + OBError warning = new OBError(); + warning.setType("Warning"); + warning.setTitle(OBMessageUtils.messageBD("Warning")); + warning.setMessage(OBMessageUtils.messageBD("TrxWithNoCost")); + vars.setMessage("ReportValuationStock", warning); + } } catch (ServletException ex) { myMessage = Utility.translateError(this, vars, vars.getLanguage(), ex.getMessage()); } @@ -218,6 +232,37 @@ } } + private boolean hasTrxWithNoCost(String strDate, String strWarehouse, String strCategoryProduct) { + StringBuffer where = new StringBuffer(); + where.append(" as trx"); + where.append(" join trx." + MaterialTransaction.PROPERTY_STORAGEBIN + " as loc"); + 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(strCategoryProduct)) { + where.append(" and p." + Product.PROPERTY_PRODUCTCATEGORY + ".id = :prodCategory"); + } + + OBQuery<MaterialTransaction> whereQry = OBDal.getInstance().createQuery( + MaterialTransaction.class, where.toString()); + whereQry.setFilterOnReadableClients(false); + whereQry.setFilterOnReadableOrganization(false); + try { + whereQry.setNamedParameter("maxDate", + OBDateUtils.getDate(DateTimeData.nDaysAfter(this, strDate, "1"))); + } catch (Exception e) { + // DoNothing parse exception not expected. + log4j.error("error parsing date: " + strDate, e); + } + whereQry.setNamedParameter("wh", strWarehouse); + if (!"".equals(strCategoryProduct)) { + whereQry.setNamedParameter("prodCategory", strCategoryProduct); + } + whereQry.setMaxResult(1); + return whereQry.uniqueResult() != null; + } + public String getServletInfo() { return "Servlet ReportValuationStock. This Servlet was made by Pablo Sarobe"; } // end of getServletInfo() method diff -r f313b3335867 -r 6b96be838bc8 src/org/openbravo/erpCommon/ad_reports/ReportValuationStock_data.xsql --- a/src/org/openbravo/erpCommon/ad_reports/ReportValuationStock_data.xsql Mon Jul 02 16:27:37 2012 +0200 +++ b/src/org/openbravo/erpCommon/ad_reports/ReportValuationStock_data.xsql Tue Jul 03 10:52:18 2012 +0200 @@ -31,15 +31,26 @@ SELECT CATEGORY_NAME, M_PRODUCT_ID, PRODUCT_NAME, QTY, UOM_NAME, AVERAGE_COST, TOTAL_COST 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, - C_CURRENCY_ROUND(C_CURRENCY_CONVERT(Suma, ?, ?, TO_DATE(?), NULL, A.AD_CLIENT_ID, ?)/A.QTY, ?, NULL) AS AVERAGE_COST, - C_CURRENCY_CONVERT(Suma, ?, ?, TO_DATE(?), NULL, A.AD_CLIENT_ID) AS TOTAL_COST + CASE a.iscostcalculated + WHEN 'Y' THEN C_CURRENCY_ROUND(C_CURRENCY_CONVERT(Suma, ?, ?, TO_DATE(?), NULL, A.AD_CLIENT_ID, ?)/A.QTY, ?, 'Y') + ELSE null + END AS AVERAGE_COST, + CASE a.iscostcalculated + WHEN 'Y' THEN C_CURRENCY_CONVERT(Suma, ?, ?, TO_DATE(?), NULL, A.AD_CLIENT_ID, ?) + ELSE null + END AS TOTAL_COST FROM M_PRODUCT_CATEGORY, - (SELECT M_PRODUCT_ID, SUM(MOVEMENTQTY) AS QTY, SUM(CASE WHEN MOVEMENTQTY<0 THEN transactioncost *-1 ELSE transactioncost END) as Suma, C_UOM_ID, m_transaction.AD_CLIENT_ID - FROM M_TRANSACTION, M_LOCATOR - WHERE M_TRANSACTION.M_LOCATOR_ID = M_LOCATOR.M_LOCATOR_ID - AND MOVEMENTDATE < to_date(?) - AND M_LOCATOR.M_WAREHOUSE_ID = ? - GROUP BY M_PRODUCT_ID, C_UOM_ID, m_transaction.AD_CLIENT_ID) A, C_UOM, + (SELECT trx.M_PRODUCT_ID, SUM(trx.MOVEMENTQTY) AS QTY, SUM(CASE WHEN MOVEMENTQTY<0 THEN -tc.trxcost ELSE tc.trxcost END) as Suma, + trx.C_UOM_ID, trx.AD_CLIENT_ID, trx.iscostcalculated + FROM M_TRANSACTION trx + JOIN M_LOCATOR l ON trx.M_LOCATOR_ID = l.M_LOCATOR_ID + LEFT JOIN (SELECT SUM(cost) as trxcost, m_transaction_id + FROM M_TRANSACTION_COST + WHERE costdate < to_date(?) + GROUP BY m_transaction_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) 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 @@ -59,6 +70,8 @@ <Parameter name="cCurrencyBase"/> <Parameter name="cCurrencyConv"/> <Parameter name="date"/> + <Parameter name="legalEntity"/> + <Parameter name="datePlus"/> <Parameter name="datePlus"/> <Parameter name="warehouse"/> <Parameter name="categoryProduct" optional="true" after="AND 1=1"><![CDATA[ AND M_PRODUCT.M_PRODUCT_CATEGORY_ID= ? ]]></Parameter> ------------------------------------------------------------------------------ Live Security Virtual Conference Exclusive live event will cover all the ways today's security and threat landscape has changed and how IT managers can respond. Discussions will include endpoint security, mobile security and the latest in malware threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/ _______________________________________________ Openbravo-commits mailing list [email protected] https://lists.sourceforge.net/lists/listinfo/openbravo-commits
