details: https://code.openbravo.com/erp/devel/pi/rev/f68ed69a17c5 changeset: 25897:f68ed69a17c5 user: Unai Martirena <unai.martirena <at> openbravo.com> date: Wed Feb 11 10:22:22 2015 +0100 summary: Fixes Bug 28854: Performance problems improved in Add Payment window.
AddPaymentOrderInvoicesTransformer has been changed to improve the performance of the query details: https://code.openbravo.com/erp/devel/pi/rev/3eb4cc225283 changeset: 25898:3eb4cc225283 user: Unai Martirena <unai.martirena <at> openbravo.com> date: Wed Feb 11 10:24:41 2015 +0100 summary: Fixes bug 28890: Valued Stock Report shows correct qty with cost adjustments. diffstat: modules/org.openbravo.advpaymentmngt/src-db/database/sourcedata/AD_TABLE.xml | 4 +- modules/org.openbravo.advpaymentmngt/src/org/openbravo/advpaymentmngt/hqlinjections/AddPaymentOrderInvoicesTransformer.java | 46 +++++-- src/org/openbravo/erpCommon/ad_reports/ReportValuationStock_data.xsql | 61 +++++---- 3 files changed, 67 insertions(+), 44 deletions(-) diffs (202 lines): diff -r 890102397a42 -r 3eb4cc225283 modules/org.openbravo.advpaymentmngt/src-db/database/sourcedata/AD_TABLE.xml --- a/modules/org.openbravo.advpaymentmngt/src-db/database/sourcedata/AD_TABLE.xml Tue Feb 10 18:34:33 2015 +0000 +++ b/modules/org.openbravo.advpaymentmngt/src-db/database/sourcedata/AD_TABLE.xml Wed Feb 11 10:24:41 2015 +0100 @@ -70,12 +70,12 @@ <!--58AF4D3E594B421A9A7307480736F03E--> <HQLQUERY><![CDATA[select @selectClause@ from FIN_Payment_ScheduleDetail as psd left outer join psd.orderPaymentSchedule as ops -left outer join ops.order as ord +left outer join ops.order as ord @joinClauseOrder@ left outer join ord.businessPartner as ordbp left outer join ops.fINPaymentPriority as opriority left outer join ops.finPaymentmethod opsfp left outer join psd.invoicePaymentSchedule as ips -left outer join ips.invoice as inv +left outer join ips.invoice as inv @joinClauseInvoice@ left outer join inv.businessPartner as invbp left outer join ips.fINPaymentPriority as ipriority left outer join ips.finPaymentmethod as ipsfp diff -r 890102397a42 -r 3eb4cc225283 modules/org.openbravo.advpaymentmngt/src/org/openbravo/advpaymentmngt/hqlinjections/AddPaymentOrderInvoicesTransformer.java --- a/modules/org.openbravo.advpaymentmngt/src/org/openbravo/advpaymentmngt/hqlinjections/AddPaymentOrderInvoicesTransformer.java Tue Feb 10 18:34:33 2015 +0000 +++ b/modules/org.openbravo.advpaymentmngt/src/org/openbravo/advpaymentmngt/hqlinjections/AddPaymentOrderInvoicesTransformer.java Wed Feb 11 10:24:41 2015 +0100 @@ -68,6 +68,8 @@ boolean justCount = strJustCount.equalsIgnoreCase("true"); StringBuffer selectClause = getSelectClause(transactionType, hasSelectedIds); + StringBuffer joinClauseOrder = getJoinClauseOrder(); + StringBuffer joinClauseInvoice = getJoinClauseInvoice(); StringBuffer whereClause = getWhereClause(transactionType, requestParameters, selectedPSDs); StringBuffer groupByClause = getGroupByClause(transactionType); StringBuffer orderByClause = new StringBuffer(); @@ -95,6 +97,8 @@ String transformedHql = hqlQuery.replace("@selectClause@", selectClause.toString()); transformedHql = transformedHql.replace("@joinClause@", " "); + transformedHql = transformedHql.replace("@joinClauseOrder@", joinClauseOrder.toString()); + transformedHql = transformedHql.replace("@joinClauseInvoice@", joinClauseInvoice.toString()); transformedHql = transformedHql.replace("@whereClause@", whereClause.toString()); transformedHql = transformedHql.replace("@groupByClause@", groupByClause.toString()); transformedHql = appendOrderByClause(transformedHql, orderByClause, justCount); @@ -161,6 +165,20 @@ return selectClause; } + protected StringBuffer getJoinClauseOrder() { + StringBuffer joinClauseOrder = new StringBuffer(); + joinClauseOrder + .append(" with ord.businessPartner.id = :businessPartnerId and ord.salesTransaction = :isSalesTransaction and ord.currency.id = :currencyId"); + return joinClauseOrder; + } + + protected StringBuffer getJoinClauseInvoice() { + StringBuffer joinClauseInvoice = new StringBuffer(); + joinClauseInvoice + .append(" with inv.businessPartner.id = :businessPartnerId and inv.salesTransaction = :isSalesTransaction and inv.currency.id = :currencyId"); + return joinClauseInvoice; + } + protected StringBuffer getWhereClause(String transactionType, Map<String, String> requestParameters, List<String> selectedPSDs) { String strBusinessPartnerId = requestParameters.get("received_from"); @@ -169,12 +187,13 @@ StringBuffer whereClause = new StringBuffer(); // Create WhereClause - whereClause.append(" (psd.paymentDetails is null"); - // If opened from Payment Window, add payment details lines if (strFinPaymentId != null) { - whereClause.append(" or (fp.id = :paymentId and (ips is not null or ops is not null)) "); + whereClause.append(" (psd.paymentDetails is null or fp.id = :paymentId)"); + } else { + whereClause.append(" psd.paymentDetails is null"); } - whereClause.append(") "); + whereClause.append(" and coalesce(ips,ops) is not null "); + if (strOrganizationId != null) { whereClause.append(" and psd.organization.id in :orgIds "); } @@ -201,40 +220,37 @@ } } whereClause.append(")"); - } else { - whereClause.append(" fp.id = :paymentId "); } - whereClause.append(" or "); if ("I".equals(transactionType)) { - whereClause.append(" (inv is not null "); + whereClause.append(" ( "); + whereClause.append(" inv.salesTransaction = :isSalesTransaction"); if (strBusinessPartnerId != null && !"null".equals(strBusinessPartnerId)) { whereClause.append(" and invbp.id = :businessPartnerId "); } - whereClause.append(" and inv.salesTransaction = :isSalesTransaction"); whereClause.append(" and inv.currency.id = :currencyId ) "); } else if ("O".equals(transactionType)) { - whereClause.append(" (ord is not null "); + whereClause.append(" ( "); + whereClause.append(" ord.salesTransaction = :isSalesTransaction"); if (strBusinessPartnerId != null && !"null".equals(strBusinessPartnerId)) { whereClause.append(" and ordbp.id = :businessPartnerId "); } - whereClause.append(" and ord.salesTransaction = :isSalesTransaction"); whereClause.append(" and ord.currency.id = :currencyId ) "); } else { - whereClause.append(" (inv is not null "); + whereClause.append(" ( "); + whereClause.append(" inv.salesTransaction = :isSalesTransaction"); if (strBusinessPartnerId != null && !"null".equals(strBusinessPartnerId)) { whereClause.append(" and invbp.id = :businessPartnerId "); } - whereClause.append(" and inv.salesTransaction = :isSalesTransaction"); whereClause.append(" and inv.currency.id = :currencyId ) "); - whereClause.append(" or (ord is not null "); + whereClause.append(" or ( "); + whereClause.append(" ord.salesTransaction = :isSalesTransaction"); if (strBusinessPartnerId != null && !"null".equals(strBusinessPartnerId)) { whereClause.append(" and ordbp.id = :businessPartnerId"); } - whereClause.append(" and ord.salesTransaction = :isSalesTransaction"); whereClause.append(" and ord.currency.id = :currencyId ) "); } diff -r 890102397a42 -r 3eb4cc225283 src/org/openbravo/erpCommon/ad_reports/ReportValuationStock_data.xsql --- a/src/org/openbravo/erpCommon/ad_reports/ReportValuationStock_data.xsql Tue Feb 10 18:34:33 2015 +0000 +++ b/src/org/openbravo/erpCommon/ad_reports/ReportValuationStock_data.xsql Wed Feb 11 10:24:41 2015 +0100 @@ -28,37 +28,44 @@ <SqlMethodComment></SqlMethodComment> <Sql> <![CDATA[ - SELECT CATEGORY_NAME, M_PRODUCT_ID, PRODUCT_NAME, SUM(QTY) AS QTY, UOM_NAME, CASE ISCOSTCALCULATED - WHEN 'Y' THEN SUM(TOTAL_COST) / SUM(QTY) + SELECT CATEGORY_NAME, ZZ.M_PRODUCT_ID, PRODUCT_NAME, SUM(M.MOVEMENTQTY) AS QTY, UOM_NAME, + CASE ZZ.ISCOSTCALCULATED + WHEN 'Y' THEN SUM(TOTAL_COST) / SUM(M.MOVEMENTQTY) ELSE NULL END AS AVERAGE_COST, SUM(TOTAL_COST) AS 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, - CASE a.iscostcalculated - WHEN 'Y' THEN C_CURRENCY_CONVERT_PRECISION (Suma,A.c_currency_id,?,A.movementdate,NULL,A.AD_CLIENT_ID,?,'C') + FROM M_TRANSACTION M, + (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, + C_UOM.NAME AS UOM_NAME, SUM(A.SUMA) AS TOTAL_COST, A.ISCOSTCALCULATED, A.AD_CLIENT_ID, A.C_CURRENCY_ID, A.M_TRANSACTION_ID + FROM M_PRODUCT_CATEGORY, + M_TRANSACTION TR + LEFT JOIN (SELECT TRX.M_TRANSACTION_ID, TRX.M_PRODUCT_ID, + CASE TRX.ISCOSTCALCULATED + WHEN 'Y' THEN C_CURRENCY_CONVERT_PRECISION (SUM(CASE WHEN TRX.MOVEMENTQTY < 0 THEN - TC.TRXCOST ELSE TC.TRXCOST END),TC.C_CURRENCY_ID,?,TC.MOVEMENTDATE,NULL,TRX.AD_CLIENT_ID,?,'C') ELSE NULL - END AS TOTAL_COST, A.ISCOSTCALCULATED, A.AD_CLIENT_ID, A.MOVEMENTDATE, A.C_CURRENCY_ID - FROM M_PRODUCT_CATEGORY, - (SELECT trx.m_transaction_id, trx.M_PRODUCT_ID, trx.MOVEMENTQTY AS QTY, sum(CASE WHEN trx.MOVEMENTQTY < 0 THEN- tc.cost ELSE tc.cost END) AS Suma, - trx.C_UOM_ID, trx.AD_CLIENT_ID, trx.iscostcalculated, tc.c_currency_id, coalesce(tc.dateacct, tc.COSTDATE) as movementdate - FROM M_TRANSACTION trx - JOIN M_LOCATOR l ON trx.M_LOCATOR_ID = l.M_LOCATOR_ID - LEFT JOIN M_TRANSACTION_COST tc ON trx.m_transaction_id = tc.m_transaction_id - WHERE trx.MOVEMENTDATE < to_date(?) - AND COALESCE(tc.DATEACCT, tc.COSTDATE) < to_date(?) - AND l.M_WAREHOUSE_ID = ? - GROUP BY trx.m_transaction_id, trx.M_PRODUCT_ID, trx.MOVEMENTQTY, trx.C_UOM_ID, trx.AD_CLIENT_ID, trx.iscostcalculated, tc.c_currency_id, coalesce(tc.dateacct, tc.COSTDATE)) 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, UOM_NAME, ISCOSTCALCULATED - HAVING SUM(QTY) <>0 - ORDER BY CATEGORY_NAME, PRODUCT_NAME + END AS SUMA, + TRX.C_UOM_ID, TRX.AD_CLIENT_ID, TRX.ISCOSTCALCULATED, TC.C_CURRENCY_ID + 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, C_CURRENCY_ID, COALESCE(DATEACCT, COSTDATE) as MOVEMENTDATE + FROM M_TRANSACTION_COST + WHERE COALESCE(DATEACCT, COSTDATE) < to_date(?) + GROUP BY m_transaction_id, C_CURRENCY_ID, COALESCE(DATEACCT, COSTDATE)) TC ON TRX.M_TRANSACTION_ID = TC.M_TRANSACTION_ID + WHERE TRX.MOVEMENTDATE < to_date(?) + AND L.M_WAREHOUSE_ID = ? + GROUP BY TRX.M_TRANSACTION_ID, TRX.M_PRODUCT_ID, TRX.C_UOM_ID, TRX.AD_CLIENT_ID, TRX.ISCOSTCALCULATED, TC.C_CURRENCY_ID, TC.MOVEMENTDATE) A ON TR.M_TRANSACTION_ID = A.M_TRANSACTION_ID, + 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.SUMA <> 0 OR TR.MOVEMENTQTY <> 0) + GROUP BY CATEGORY_NAME, A.M_PRODUCT_ID, PRODUCT_NAME, UOM_NAME, A.ISCOSTCALCULATED, A.AD_CLIENT_ID, A.C_CURRENCY_ID, A.M_TRANSACTION_ID) ZZ + WHERE M.M_TRANSACTION_ID = ZZ.M_TRANSACTION_ID + GROUP BY ZZ.M_PRODUCT_ID, CATEGORY_NAME, PRODUCT_NAME, UOM_NAME, ZZ.ISCOSTCALCULATED + HAVING SUM(M.MOVEMENTQTY) <>0 + ORDER BY CATEGORY_NAME, PRODUCT_NAME ]]></Sql> <Field name="rownum" value="count"/> <Parameter name="adLanguage"/> ------------------------------------------------------------------------------ Dive into the World of Parallel Programming. The Go Parallel Website, sponsored by Intel and developed in partnership with Slashdot Media, is your hub for all things parallel software development, from weekly thought leadership blogs to news, videos, case studies, tutorials and more. Take a look and join the conversation now. http://goparallel.sourceforge.net/ _______________________________________________ Openbravo-commits mailing list [email protected] https://lists.sourceforge.net/lists/listinfo/openbravo-commits
