List Module pre-filter

[keianrad]

There is a task list and I need to show tasks based on current day and week then I used list module and I used the codes listed below in pre-filter but they don't work correct and it shows wrong tasks.

Daily Tasks:

Element: Date
Condition: GREATER THAN
Value: NOW()- INTERVAL 1 DAY
Type: No quotes
Access: Public

Week Tasks:

Element: Date
Condition: GREATER THAN
Value: NOW()- INTERVAL 1 WEEK
Type: No quotes
Access: Public

Thank you for your help.

 

[cheesegrits]

I'm trying to login to the backend of the site, but there seems to be an additional password access control that wasn't there before.

-- hugh

 

[keianrad]

Fixed.

 

[cheesegrits]

Looks like the date part is probably working, but you have a prefilter on those modules which is checking for $q->office_id, which winds up with this:

WHERE ( ( app_tasks.office_id = '{$q->office_id}' OR app_tasks.assign_to = '799')

... so the placeholder isn't getting set up, because this list doesn't have the prequery on it that sets it up.

You'll need to add a prequery to this list (List settings, Data tab, Filters, at the bottom) so it does the same as whichever list you copied those pre-filters from.

Note that the prequery has to be done on the list itself, it can't be overridden in the module settings.

-- hugh

 

[keianrad]

I deleted all the pre-filters in the list and module and used only a prefilter listed below, but it doesn't work. When I use only "NOW()" as value in prefilter, it works and shows all the task that have date after the current date but when I use "NOW()- INTERVAL 1 DAY" it shows some past tasks and all future tasks. It seems that "- INTERVAL 1 DAY" doesn't work properly.

Element: Date
Condition: GREATER THAN
Value: NOW()- INTERVAL 1 DAY
Type: No quotes
Access: Public

 

[cheesegrits]

I'm looking at the /en/home page, with the "Today" module on it, and I'm not sure what the issue is?

I see 11 events, from 2017-08-16 (yesterday) onwards. If I display the list by itself, using ?option=com_fabrik&view=list&listid=22, so with no filters, I see 35 events, with the same 11 from the 16th onwards. So it looks like the module is correctly filtering for "> 1 day old".

Are you trying to show ONLY tasks for "today"? So you aren't expecting to the the dates in the future? If so, that pre-filter won't do that, you are asking it to show you all dates more recent than one day ago, which includes dates in the future.

If you want to pre-filter for a range, you'll have to use two pre-filters, with the second one "grouped to previous", joined by AND, something like ...

Element: Date
Condition: GREATER THAN
Value: NOW()- INTERVAL 1 DAY
Type: No quotes
Access: Public

AND
Element: Date
Condition: LESS THAN
Value: NOW() + INTERVAL 1 DAY
Type: No quotes
Access: Public

Oh, and there is a specific "TODAY" condition you can use as a prefilter (no need to provide a 'value'), which will generate this as the WHERE clause:

(date_test.dash_date >= CURDATE() AND date_test.dash_date < CURDATE() + INTERVAL 1 DAY)

So you could try using that instead of creating your own logic for "today".

-- hugh

 

[keianrad]

Thank you Hugh, I used "Today" as condition in module pre-filter for Today's tasks and the "Earlier this year" for delayed Tasks but they don't work properly.
There is a task for 08/19/2017 (today) that it doesn't show in the Today's Tasks and it shows in the Delayed Tasks!

 

[keianrad]

Friendly Bump.

 

[cheesegrits]

So does it look like a TZ issue? I other words is the time on the one showing in the wrong place within the TZ difference?

What are you storing the dates as - local or UTC?

-- hugh

 

[cheesegrits]

OK, yes, looking at our code, we always do this:

            case 'today':
                $query = ' (' . $key . ' >= CURDATE() AND ' . $key . ' < CURDATE() + INTERVAL 1 DAY) ';
                break;

... but to properly do "today" we'd have to TZ offset the dates if the date element is set to "Store as UTC".

I'm looking at that now.

-- hugh

 

[cheesegrits]

OK, I think I've fixed it:

https://github.com/Fabrik/fabrik/commit/9dcecb1f2d5fe5e913d6fdc5e76edac53e59ac98

With that commit, all canned date pre-filters should take into account whether the date is stored as UTC or local. If UTC, we apply the TZ offset in the WHERE clause.

-- hugh