Hi Team ,
We are facing issue while filtering Dates in Datagrids (currently working in FR locale , but this issue exists even for EN locale also) .
The issue is if we choose Dates Between : then filter will select previous Dates in its queries from that of chosen Dates.
Tested for : Opportunities Entity index page
Start : 19 oct. 2017 End: 21 oct. 2017
result will be fetched from 2017-10-18 22:00:00 to 2017-10-20 22:00:00 . i think there is some timezone issue during filtering. The below details i got from logs while triggered the Date filter btn .12345678[2017-10-31 14:01:39] event.DEBUG: Notified event "oro.entity.count_query.optimize" to listener "Oro\Bundle\FormBundle\EventListener\CountQueryOptimizationListener::onOptimize".  [2017-10-31 14:01:39] security.DEBUG: ACL found, permission granted. Voting to grant access.  [2017-10-31 14:01:39] doctrine.DEBUG: SELECT count(o0_.id) AS sclr_0 FROM orocrm_sales_opportunity o0_ WHERE (o0_.close_date >= ? AND o0_.close_date <= ?) AND o0_.status_id NOT IN (?) AND o0_.organization_id = 1 ["2017-10-19 00:00:00","2017-10-21 00:00:00",["lost","won"]] [2017-10-31 14:01:39] security.DEBUG: ACL found, permission granted. Voting to grant access.  [2017-10-31 14:01:39] security.DEBUG: ACL found, permission granted. Voting to grant access.  [2017-10-31 14:01:39] security.DEBUG: ACL found, permission granted. Voting to grant access.  [2017-10-31 14:01:39] event.DEBUG: Notified event "oro_datagrid.orm_datasource.result.before" to listener "Oro\Bundle\DataGridBundle\EventListener\OrmDatasourceAclListener::onResultBefore".  [2017-10-31 14:01:39] doctrine.DEBUG: SELECT o0_.id AS id_0, o0_.name AS name_1, o0_.created_at AS created_at_2, o0_.updated_at AS updated_at_3, o0_.close_date AS close_date_4, o0_.probability AS probability_5, o0_.budget_amount_value AS budget_amount_value_6, o0_.close_revenue_value AS close_revenue_value_7, o0_.budget_amount_currency AS budget_amount_currency_8, o0_.close_revenue_currency AS close_revenue_currency_9, o0_.status_id AS sclr_10, CONCAT_WS(' ', o1_.first_name, o1_.last_name) AS sclr_11, o1_.id AS id_12, o2_.label AS label_13, o3_.email AS email_14, o4_.id AS id_15, CONCAT_WS(' ', o4_.first_name, o4_.last_name) AS sclr_16, o5_.name AS name_17, o0_.ac_contact_count AS ac_contact_count_18, o0_.ac_contact_count_in AS ac_contact_count_in_19, o0_.ac_contact_count_out AS ac_contact_count_out_20, o0_.ac_last_contact_date AS ac_last_contact_date_21, o0_.ac_last_contact_date_in AS ac_last_contact_date_in_22, o0_.ac_last_contact_date_out AS ac_last_contact_date_out_23, DATEDIFF(CURRENT_DATE, o0_.ac_last_contact_date) AS sclr_24, o0_.organization_id AS sclr_25, o0_.user_owner_id AS sclr_26 FROM orocrm_sales_opportunity o0_ LEFT JOIN orocrm_contact o1_ ON o0_.contact_id = o1_.id AND (o1_.organization_id = 1) LEFT JOIN orocrm_sales_opport_close_rsn o2_ ON o0_.close_reason_name = o2_.name LEFT JOIN orocrm_contact_email o3_ ON o1_.id = o3_.owner_id AND (o3_.is_primary = 1) LEFT JOIN oro_user o4_ ON o0_.user_owner_id = o4_.id LEFT JOIN orocrm_sales_customer o6_ ON o0_.customer_association_id = o6_.id LEFT JOIN orocrm_account o5_ ON o6_.account_id = o5_.id AND (o5_.organization_id = 1) WHERE (o0_.close_date >= ? AND o0_.close_date <= ?) AND o0_.status_id NOT IN (?) AND o0_.organization_id = 1 ORDER BY o0_.created_at DESC, o0_.id DESC LIMIT 25 OFFSET 0 ["2017-10-18 22:00:00","2017-10-20 22:00:00",["lost","won"]]
If you notice above , the 1st query which is to get count will take parameters correctly (i.e start as 2017-10-19 00:00:00 & end as 2017-10-21 00:00:00), where in the second Qry used to fetch the result – parameters used for start & end dates are different (i.e start as 2017-10-18 22:00:00 & end as 2017-10-20 22:00:00).
i tried looking at the files CountQueryOptimizationListener.php & OrmDatasourceAclListener.php but dint find much from them.
i even tried typecasting the date field used in filters option of Datagrid with DATE but it too dint work .
This is the issue for if select Dates between ,Dates not between option & similar kind of date changes issue occurs for rest of Date filter options also.
I tried looking at both FilterBundle & DataGrid Bundle for any modifications that i can do ( like: FilterBundle/Filter/AbstractDateFilter.php & FilterBundle\Utils\DateFilterModifier.php & some JS files under Filter Directory) . but could not get much idea on how to go with it.
If we check for any of DateTime Filter also,the similar issue occurs there as well.
Application Details for reference:
Application Version: 2.3
Application Edition: Community
2. Environment related information:
OS, name and version: CentOS 7.3
Web server, name and version: Apache 2.4
Database, name and version: MySQL 5.7
PHP, version: 7.1
The same issue is seen even in demo.orocrm.com
Please need help in how to solve this issue.Is there any link that shows how the filters work by dynamically building the queries so that i can do some modification to those dates to fix issue.
Thanks & Regards,
The forum ‘OroPlatform – Installation/Technical Issues or Problems’ is closed to new topics and replies.