Anonymous | Login
Project:
RSS
  
News | My View | View Issues | Roadmap | Summary

View Issue Details[ Jump to Notes ] [ Issue History ] [ Print ]
ID
0054144
TypeCategorySeverityReproducibilityDate SubmittedLast Update
defect[POS2] POSmajoralways2023-12-15 14:492024-01-08 17:36
ReporterLcazauxView Statuspublic 
Assigned ToSABARINATH P 
PriorityhighResolutionfixedFixed in Version
StatusclosedFix in branchFixed in SCM revision
ProjectionnoneETAnoneTarget Version
OSAnyDatabaseAnyJava version
OS VersionDatabase versionAnt version
Product VersionSCM revision 
Review Assigned To
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary

0054144: Orders menu is not loading and the time out error appears

DescriptionIn the POS2, when trying to go into the Orders Menu, the lines are not loaded, nothing is displayed, and when opening the dev tool we can see after some time the time-out error message appears which makes the load of the lines fail.
Steps To ReproducePlease contact me to have access to the client environment.

Once you have the access:
- Go to the POS
- Go to the Order menu
- Open your dev tool
--> lines are not loading after some time a time-out error message appears in the dev tool
TagsNo tags attached.
Attached Files

- Relationships [ Relation Graph ] [ Dependency Graph ]

-  Notes
(0158272)
avicente (developer)
2023-12-15 14:55

 In other customers we have avoided this issue increasing the timeout for
org.openbravo.retail.posterminal.PaidReceiptsFilter
org.openbravo.retail.posterminal.PaidReceipts
(0158895)
SABARINATH P (developer)
2024-01-05 13:03

 I think the problem was in the query that fetches the orders in the paidreceiptfilters. I have taken the whole query that fetches order from the log in my local and omitted organisation details and client details to test this in CDS-SSH db, as it was not same as my local.

The actual query was,

select
  case when order0_.EM_Cfis_Fiscalization_Result is not null then true else false end as col_0_0_,
  case when documentty1_.IsReturn = 'Y' then 'RET' when documentty1_.DocSubTypeSO = 'OB' then 'QT' when order0_.EM_Obpos_Islayaway = 'Y' then 'LAY' else 'ORD' end as col_1_0_,
  order0_.C_Order_ID as col_2_0_,
  documentty1_.C_DocType_ID as col_3_0_,
  order0_.DocStatus as col_4_0_,
  order0_.DocumentNo as col_5_0_,
  order0_.Created as col_6_0_,
  order0_.DateOrdered as col_7_0_,
  order0_.DateOrdered as col_8_0_,
  order0_.DateOrdered as col_9_0_,
  businesspa2_.C_BPartner_ID as col_10_0_,
  businesspa2_.Name as col_11_0_,
  order0_.GrandTotal as col_12_0_,
  order0_.GrandTotal as col_13_0_,
  order0_.GrandTotal as col_14_0_,
  order0_.Iscancelled as col_15_0_,
  organizati3_.AD_Org_ID as col_16_0_,
  organizati3_.Value as col_17_0_,
  organizati3_.Name as col_18_0_,
  organizati5_.AD_Org_ID as col_19_0_,
  organizati5_.Name as col_20_0_,
  order0_.IsDelivered as col_21_0_,
  order0_.BPartner_ExtRef as col_22_0_,
  (
    select
      coalesce(
        max(
          orderline8_.EM_Obrdm_Delivery_Mode
        ),
        'PickAndCarry'
      )
    from
      C_OrderLine orderline8_
    where
      order0_.C_Order_ID = orderline8_.C_Order_ID
      and orderline8_.EM_Obpos_Isdeleted = 'N'
  ) as col_23_0_,
  (
    select
      min(
        case when orderline9_.EM_Obrdm_Delivery_Date is null
        or orderline9_.EM_Obrdm_Delivery_Time is null then null else to_timestamp(
          (
            to_char(
              orderline9_.EM_Obrdm_Delivery_Date,
              'YYYY'
            )|| '-' || to_char(
              orderline9_.EM_Obrdm_Delivery_Date,
              'MM'
            )|| '-' || to_char(
              orderline9_.EM_Obrdm_Delivery_Date,
              'DD'
            )|| ' ' || to_char(
              orderline9_.EM_Obrdm_Delivery_Time,
              'HH24'
            )|| ':' || to_char(
              orderline9_.EM_Obrdm_Delivery_Time,
              'MI'
            )
          ),
          'YYYY-MM-DD HH24:MI'
        ) end
      )
    from
      C_OrderLine orderline9_
    where
      order0_.C_Order_ID = orderline9_.C_Order_ID
  ) as col_24_0_,
  order0_.InvoiceRule as col_25_0_,
  (
    select
      case when max(invoice10_.C_Invoice_ID) is null then false else true end
    from
      C_Invoice invoice10_
    where
      order0_.C_Order_ID = invoice10_.C_Order_ID
  ) as col_26_0_,
  order0_.EM_Obpos_Islayaway as col_27_0_,
  (
    select
      coalesce(
        sum(fin_paymen11_.Paidamt),
        0
      )
    from
      FIN_Payment_Schedule fin_paymen11_
    where
      fin_paymen11_.C_Order_ID = order0_.C_Order_ID
  ) as col_28_0_,
  (
    select
      case when count(orderline12_.C_OrderLine_ID)> 0 then true else false end
    from
      C_OrderLine orderline12_
      inner join M_InOutLine materialmg13_ on orderline12_.M_Inoutline_ID = materialmg13_.M_InOutLine_ID
      inner join C_OrderLine orderline14_ on materialmg13_.C_OrderLine_ID = orderline14_.C_OrderLine_ID
    where
      orderline14_.C_Order_ID = order0_.C_Order_ID
  ) as col_29_0_,
  case when documentty1_.IsReturn = 'Y' then to_char('Refunded') when documentty1_.DocSubTypeSO = 'OB' then to_char('UnderEvaluation') when order0_.Iscancelled = 'Y' then to_char('Cancelled') when order0_.GrandTotal > 0
  and (
    select
      coalesce(
        sum(fin_paymen17_.Paidamt),
        0
      )
    from
      FIN_Payment_Schedule fin_paymen17_
    where
      fin_paymen17_.C_Order_ID = order0_.C_Order_ID
  )= 0 then to_char('UnPaid') when order0_.GrandTotal > 0
  and (
    select
      coalesce(
        sum(fin_paymen18_.Paidamt),
        0
      )
    from
      FIN_Payment_Schedule fin_paymen18_
    where
      fin_paymen18_.C_Order_ID = order0_.C_Order_ID
  )< order0_.GrandTotal then to_char('PartiallyPaid') else to_char('Paid') end as col_30_0_
from
  C_Order order0_
  inner join C_DocType documentty1_ on order0_.C_DocType_ID = documentty1_.C_DocType_ID
  inner join C_BPartner businesspa2_ on order0_.C_BPartner_ID = businesspa2_.C_BPartner_ID
  inner join AD_Org organizati3_ on order0_.AD_Org_ID = organizati3_.AD_Org_ID
  inner join OBPOS_APPLICATIONS obpos_appl4_ on order0_.EM_Obpos_Applications_ID = obpos_appl4_.Obpos_Applications_ID
  left outer join AD_Org organizati5_ on order0_.AD_OrgTrx_ID = organizati5_.AD_Org_ID
where
  1 = 1
  and 1 = 1
  and order0_.DateOrdered >= to_date('2023-12-28', 'yyyy-MM-dd')
  and order0_.AD_Client_ID = '3AFE04DCE6EE4C5A9912EDFF5517C3A7'
  and order0_.EM_Obpos_Isdeleted = 'N'
  and (
    order0_.DocStatus not in ('CJ', 'CA', 'NC', 'AE', 'ME')
  )
  and (
    order0_.DocStatus <> 'CL'
    or order0_.Iscancelled = 'Y'
  )
order by
  col_6_0_ desc,
  col_5_0_ desc
limit
  '301';


Here, the select subquery -> (
    select
      case when max(invoice10_.C_Invoice_ID) is null then false else true end
    from
      C_Invoice invoice10_
    where
      order0_.C_Order_ID = invoice10_.C_Order_ID
  ) as col_26_0_,
  order0_.EM_Obpos_Islayaway as col_27_0_,

and

(
    select
      case when count(orderline12_.C_OrderLine_ID)> 0 then true else false end
    from
      C_OrderLine orderline12_
      inner join M_InOutLine materialmg13_ on orderline12_.M_Inoutline_ID = materialmg13_.M_InOutLine_ID
      inner join C_OrderLine orderline14_ on materialmg13_.C_OrderLine_ID = orderline14_.C_OrderLine_ID
    where
      orderline14_.C_Order_ID = order0_.C_Order_ID
  ) as col_29_0_,

makes slow perfomance. When I removed these select subqueries and executed in CDS SSH DB it executed within 2 secs. By optimizing this we can solve this issue.
(0158925)
hgbot (developer)
2024-01-08 10:48

 Merge Request created: https://gitlab.com/openbravo/product/pmods/org.openbravo.pos2/-/merge_requests/2330 [^]
(0158926)
hgbot (developer)
2024-01-08 10:49

 Merge Request created: https://gitlab.com/openbravo/product/pmods/org.openbravo.retail.posterminal/-/merge_requests/1445 [^]
(0158959)
hgbot (developer)
2024-01-08 17:36

 Directly closing issue as related merge request is already approved.

Repository: https://gitlab.com/openbravo/product/pmods/org.openbravo.pos2 [^]
Changeset: 6675c1c3cccb102c8d46d2ce94d49df1e2b5ac58
Author: sabarinath palanisamy <sabarinath.palanisamy.ext@openbravo.com>
Date: 08-01-2024 16:36:43
URL: https://gitlab.com/openbravo/product/pmods/org.openbravo.pos2/-/commit/6675c1c3cccb102c8d46d2ce94d49df1e2b5ac58 [^]

Fixed ISSUE-54144: Removed Ver Ret subquery in PRFilterProperties to improve performance

---
M src/org/openbravo/pos2/master/PaidReceiptsFilterProperties.java
M web-jspack/org.openbravo.pos2/src/components/TicketList/TicketListRemoteGrid/ProofOfPaymentPopoverButton.jsx
---
(0158960)
hgbot (developer)
2024-01-08 17:36

 Merge request merged: https://gitlab.com/openbravo/product/pmods/org.openbravo.pos2/-/merge_requests/2330 [^]
(0158961)
hgbot (developer)
2024-01-08 17:36

 Merge request merged: https://gitlab.com/openbravo/product/pmods/org.openbravo.retail.posterminal/-/merge_requests/1445 [^]
(0158962)
hgbot (developer)
2024-01-08 17:36

 Directly closing issue as related merge request is already approved.

Repository: https://gitlab.com/openbravo/product/pmods/org.openbravo.retail.posterminal [^]
Changeset: b4528d8dcb6d02688974c4a811ce2b0f5b240cc3
Author: sabarinath palanisamy <sabarinath.palanisamy.ext@openbravo.com>
Date: 08-01-2024 16:36:47
URL: https://gitlab.com/openbravo/product/pmods/org.openbravo.retail.posterminal/-/commit/b4528d8dcb6d02688974c4a811ce2b0f5b240cc3 [^]

Fixed ISSUE-54144: Improved performance in Paidreceiptfilterproperties by removing unneeded subquery

---
M src/org/openbravo/retail/posterminal/PaidReceiptsFilterProperties.java
---

- Issue History
Date Modified Username Field Change
2023-12-15 14:49 Lcazaux New Issue
2023-12-15 14:49 Lcazaux Assigned To => Retail
2023-12-15 14:49 Lcazaux Triggers an Emergency Pack => No
2023-12-15 14:55 avicente Note Added: 0158272
2023-12-16 08:12 SABARINATH P Assigned To Retail => SABARINATH P
2023-12-18 12:16 SABARINATH P Status new => scheduled
2024-01-05 13:03 SABARINATH P Note Added: 0158895
2024-01-08 10:48 hgbot Note Added: 0158925
2024-01-08 10:49 hgbot Note Added: 0158926
2024-01-08 17:36 hgbot Resolution open => fixed
2024-01-08 17:36 hgbot Status scheduled => closed
2024-01-08 17:36 hgbot Fixed in Version => 24Q1
2024-01-08 17:36 hgbot Note Added: 0158959
2024-01-08 17:36 hgbot Note Added: 0158960
2024-01-08 17:36 hgbot Note Added: 0158961
2024-01-08 17:36 hgbot Fixed in Version 24Q1 => RR24Q1
2024-01-08 17:36 hgbot Note Added: 0158962

Copyright © 2000 - 2009 MantisBT Group
Powered by Mantis Bugtracker