0042385: Performance improvement in PaidReceipts query

changeset:   12764:f6b0176ba626
tag:         tip
user:        Guillermo Alvarez de Eulate <guillermo.alvarez@openbravo.com>
date:        Mon Nov 25 10:41:13 2019 +0100
summary:     Improve performance in paid Receipt

diff -r 64dc1f28d90a -r f6b0176ba626 src/org/openbravo/retail/posterminal/PaidReceipts.java
--- a/src/org/openbravo/retail/posterminal/PaidReceipts.java	Mon Nov 25 12:04:40 2019 +0530
+++ b/src/org/openbravo/retail/posterminal/PaidReceipts.java	Mon Nov 25 10:41:13 2019 +0100
@@ -383,13 +383,15 @@
             + "obpos_currency_rate(p.financialAccount.currency, p.obposApplications.organization.currency, null, null, p.obposApplications.client.id, p.obposApplications.organization.id) as rate, "
             + "obpos_currency_rate(p.obposApplications.organization.currency, p.financialAccount.currency, null, null, p.obposApplications.client.id, p.obposApplications.organization.id) as mulrate, "
             + "p.financialAccount.currency.iSOCode as isocode, "
-            + "p.paymentMethod.openDrawer as openDrawer "
-            + " from OBPOS_App_Payment as p where p.financialAccount.id in (select scheduleDetail.paymentDetails.finPayment.account.id from FIN_Payment_ScheduleDetail as scheduleDetail where scheduleDetail.orderPaymentSchedule.order.id=:orderId)"
-            + "group by  p.financialAccount.id, p.commercialName ,p.searchKey,"
-            + "obpos_currency_rate(p.financialAccount.currency, p.obposApplications.organization.currency, null, null, p.obposApplications.client.id, p.obposApplications.organization.id),"
-            + "obpos_currency_rate(p.obposApplications.organization.currency, p.financialAccount.currency, null, null, p.obposApplications.client.id, p.obposApplications.organization.id),"
-            + "p.financialAccount.currency.iSOCode, p.paymentMethod.openDrawer, p.active "
-            + "order by p.active desc";
+            + "p.paymentMethod.openDrawer as openDrawer " + "from FIN_Payment_Schedule as ps "
+            + "join ps.fINPaymentScheduleDetailOrderPaymentScheduleList psd "
+            + "join psd.paymentDetails pd " + "join pd.finPayment fp "
+            + "join OBPOS_App_Payment p with fp.account = p.financialAccount "
+            + "where ps.order.id=:orderId "
+            + "group by  p.financialAccount.id, p.commercialName ,p.searchKey, "
+            + "obpos_currency_rate(p.financialAccount.currency, p.obposApplications.organization.currency, null, null, p.obposApplications.client.id, p.obposApplications.organization.id), "
+            + "obpos_currency_rate(p.obposApplications.organization.currency, p.financialAccount.currency, null, null, p.obposApplications.client.id, p.obposApplications.organization.id), "
+            + "p.financialAccount.currency.iSOCode, p.paymentMethod.openDrawer";
         Query<Object[]> paymentsTypeQuery = OBDal.getInstance()
             .getSession()
             .createQuery(hqlPaymentsType, Object[].class);