details: https://code.openbravo.com/erp/devel/pi/rev/89faeb629846 changeset: 27020:89faeb629846 user: Alvaro Ferraz <alvaro.ferraz <at> openbravo.com> date: Mon Jun 22 18:29:20 2015 +0200 summary: Fixes issue 30009: Performance problems in UpdatePaymentPlan modulscript
UpdatePaymentPlan modulscript has been changed in order to improve the performance. Now a select will be done to get wrong records, and then they will be updated inside a loop. details: https://code.openbravo.com/erp/devel/pi/rev/1cc71ac627a7 changeset: 27021:1cc71ac627a7 user: Alvaro Ferraz <alvaro.ferraz <at> openbravo.com> date: Mon Jun 22 18:36:18 2015 +0200 summary: Fixes issue 30170: Generic UpdatePaymentPlan module script Select query in UpdatePaymentPlan has been changed to make it generic. Now it will fix all fin_payment_schedule related to an order or an invoice which paidamt or outstandingamt does not match with the sum of its fin_payment_scheduledetail amount. diffstat: src-util/modulescript/build/classes/org/openbravo/modulescript/UpdatePaymentPlan.class | 0 src-util/modulescript/build/classes/org/openbravo/modulescript/UpdatePaymentPlanData.class | 0 src-util/modulescript/src/org/openbravo/modulescript/UpdatePaymentPlan.java | 4 +- src-util/modulescript/src/org/openbravo/modulescript/UpdatePaymentPlan_data.xsql | 40 ++++++--- 4 files changed, 29 insertions(+), 15 deletions(-) diffs (77 lines): diff -r c85e2fa57fa1 -r 1cc71ac627a7 src-util/modulescript/build/classes/org/openbravo/modulescript/UpdatePaymentPlan.class Binary file src-util/modulescript/build/classes/org/openbravo/modulescript/UpdatePaymentPlan.class has changed diff -r c85e2fa57fa1 -r 1cc71ac627a7 src-util/modulescript/build/classes/org/openbravo/modulescript/UpdatePaymentPlanData.class Binary file src-util/modulescript/build/classes/org/openbravo/modulescript/UpdatePaymentPlanData.class has changed diff -r c85e2fa57fa1 -r 1cc71ac627a7 src-util/modulescript/src/org/openbravo/modulescript/UpdatePaymentPlan.java --- a/src-util/modulescript/src/org/openbravo/modulescript/UpdatePaymentPlan.java Wed Jun 17 19:17:55 2015 +0200 +++ b/src-util/modulescript/src/org/openbravo/modulescript/UpdatePaymentPlan.java Mon Jun 22 18:36:18 2015 +0200 @@ -31,7 +31,9 @@ ConnectionProvider cp = getConnectionProvider(); boolean isExecuted = UpdatePaymentPlanData.isExecuted(cp); if (!isExecuted) { - UpdatePaymentPlanData.update(cp); + for (UpdatePaymentPlanData record : UpdatePaymentPlanData.getWrongRecords(cp)) { + UpdatePaymentPlanData.update(cp, record.amount, record.amount, record.id); + } UpdatePaymentPlanData.createPreference(cp); } } catch (Exception e) { diff -r c85e2fa57fa1 -r 1cc71ac627a7 src-util/modulescript/src/org/openbravo/modulescript/UpdatePaymentPlan_data.xsql --- a/src-util/modulescript/src/org/openbravo/modulescript/UpdatePaymentPlan_data.xsql Wed Jun 17 19:17:55 2015 +0200 +++ b/src-util/modulescript/src/org/openbravo/modulescript/UpdatePaymentPlan_data.xsql Mon Jun 22 18:36:18 2015 +0200 @@ -23,29 +23,41 @@ <SqlMethodComment></SqlMethodComment> <Sql> <![CDATA[ - SELECT '' AS finpaymentscheduleid, '' AS paidamt FROM DUAL + SELECT '' AS id, '' AS amount FROM DUAL ]]> </Sql> <Field name="rownum" value="count"/> </SqlMethod> + <SqlMethod name="getWrongRecords" type="preparedStatement" return="multiple"> + <SqlMethodComment></SqlMethodComment> + <Sql> + <![CDATA[ + SELECT ps.fin_payment_schedule_id as id, sum(psd.amount + COALESCE(psd.writeoffamt, 0)) as amount + FROM fin_payment_scheduledetail psd + INNER JOIN fin_payment_schedule ps + ON (ps.fin_payment_schedule_id = psd.fin_payment_schedule_order + OR ps.fin_payment_schedule_id = psd.fin_payment_schedule_invoice) + WHERE psd.isinvoicepaid = 'Y' + AND psd.iscanceled = 'N' + AND psd.fin_payment_detail_id IS NOT NULL + GROUP BY ps.fin_payment_schedule_id, ps.paidamt, ps.outstandingamt, ps.amount + HAVING (ps.paidamt <> sum(psd.amount + COALESCE(psd.writeoffamt, 0)) + OR (ps.outstandingamt <> ps.amount - sum(psd.amount + COALESCE(psd.writeoffamt, 0)))) + ]]> + </Sql> + </SqlMethod> <SqlMethod name="update" type="preparedStatement" return="rowCount"> <SqlMethodComment></SqlMethodComment> <Sql> <![CDATA[ - update fin_payment_schedule set outstandingamt = amount - (select sum(psd.amount) - from fin_payment_scheduledetail psd - where isinvoicepaid='Y' - and psd.fin_payment_schedule_order = fin_payment_schedule.fin_payment_schedule_id), - paidamt = (select sum(psd.amount) - from fin_payment_scheduledetail psd - where isinvoicepaid='Y' - and psd.fin_payment_schedule_order = fin_payment_schedule.fin_payment_schedule_id) - WHERE fin_payment_schedule.paidamt/2 = (select sum(psd.amount) - from fin_payment_scheduledetail psd - where isinvoicepaid='Y' - and psd.fin_payment_schedule_order = fin_payment_schedule.fin_payment_schedule_id) + UPDATE fin_payment_schedule + SET paidamt = to_number(?), outstandingamt = amount - to_number(?) + WHERE fin_payment_schedule_id = ? ]]> - </Sql> + </Sql> + <Parameter name="amount1"/> + <Parameter name="amount2"/> + <Parameter name="id"/> </SqlMethod> <SqlMethod name="isExecuted" type="preparedStatement" return="boolean"> <SqlMethodComment></SqlMethodComment> ------------------------------------------------------------------------------ Monitor 25 network devices or servers for free with OpManager! OpManager is web-based network management software that monitors network devices and physical & virtual servers, alerts via email & sms for fault. Monitor 25 devices for free with no restriction. Download now http://ad.doubleclick.net/ddm/clk/292181274;119417398;o _______________________________________________ Openbravo-commits mailing list Openbravo-commits@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/openbravo-commits
