Suitecrm: 7.8.21 Date condition problems in AOW Workflows

[gunnicom]

AOW_Workflow condition seems not working for "date less than today".
Steps to Reproduce:

1. Create Workflow
2. Module Meetings
3. Only Scheduler
4. Conditions
   4a. Start date - less than - Date - Today or Now
5. Look at error log when scheduler runs.

Error:

SELECT meetings.id AS id FROM meetings WHERE meetings.date_start < DATE_ADD(meetings., INTERVAL ) AND meetings.status = 'Planned' AND NOT EXISTS (SELECT * FROM aow_processed WHERE aow_processed.aow_workflow_id='xxxxxxxxxxxxxxxxxxxxx' AND aow_processed.parent_id=meetings.id AND aow_processed.status = 'Complete' AND aow_processed.deleted = 0) AND meetings.deleted = 0 : MySQL error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ' INTERVAL ) AND meetings.status = 'Planned' AND NOT EXISTS (SELECT * FROM aow_' at line 1

[gunnicom]

I am not alone:
https://suitecrm.com/suitecrm/forum/suitecrm-7-0-discussion/20431-problem-bug

[pgorod]

This seems to affect other versions.

Here is a case with 7.10.9:

https://suitecrm.com/suitecrm/forum/suitecrm-7-0-discusion-general/20455-problema-con-las-campanas/

[maarisk]

Did not have this problem in 7.10.7. Started seeing this after upgrade to 7.10.9 in logs. Workflow reminder e-mails due to this bug are not being sent.

Logs:
Fri Sep 28 21:30:00 2018 [99533][1][FATAL] Query Failed: SELECT aos_invoices.id AS id FROM aos_invoices WHERE aos_invoices.status = 'Unpaid' AND aos_invoices.due_date = DATE_ADD(aos_invoices., INTERVAL ) AND aos_invoices.billing_address_country = 'Latvija' AND NOT EXISTS (SELECT * FROM aow_processed WHERE aow_processed.aow_workflow_id='d053b088-07e2-a3d5-dfc0-5b0029a8df0b' AND aow_processed.parent_id=aos_invoices.id AND aow_processed.status = 'Complete' AND aow_processed.deleted = 0) AND aos_invoices.deleted = 0 : MySQL error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' INTERVAL ) AND aos_invoices.billing_address_country = 'Latvija' AND NOT EXIST' at line 1
Fri Sep 28 21:30:00 2018 [99533][1][FATAL] Mysqli_query failed.

[pgorod]

Do any of you have this warning in your logs (make sure log level includes warnings):

Date operator is not set in app_list_string

[gunnicom]

@pgorod Yes it is in there:

Mon Oct  1 11:47:02 2018 [112013][1][WARN] Date operator is not set in app_list_string[]
Mon Oct  1 11:47:02 2018 [112013][1][FATAL]  Query Failed: SELECT meetings.id AS id FROM meetings  WHERE ...

[pgorod]

This seems to be the guilty part of code:

https://github.com/salesagility/SuiteCRM/blob/hotfix/modules/AOW_WorkFlow/AOW_WorkFlow.php#L497-L503

Please check if you have this in place:
https://github.com/salesagility/SuiteCRM/blob/master/include/language/en_us.lang.php#L3161-L3163

And if you don't have a file in custom overriding that one...

[gunnicom]

Do you think it is language dependant?

include/language/en_us.lang.php

$app_list_strings['aow_date_options']['now'] = 'Now';
$app_list_strings['aow_date_options']['today'] = 'Today';
$app_list_strings['aow_date_options']['field'] = 'This Field';
$app_list_strings['aow_date_operator']['now'] = '';
$app_list_strings['aow_date_operator']['plus'] = '+';
$app_list_strings['aow_date_operator']['minus'] = '-';

include/language/de_DE.lang.php

$app_list_strings['aow_date_options']['now'] = 'Jetzt';
$app_list_strings['aow_date_options']['today'] = 'Heute';
$app_list_strings['aow_date_options']['field'] = 'Dieses Feld';
$app_list_strings['aow_date_operator']['now'] = '';
$app_list_strings['aow_date_operator']['plus'] = 'Plus';
$app_list_strings['aow_date_operator']['minus'] = 'Minus';

[gunnicom]

Oh, and from the error
Mon Oct 1 11:47:02 2018 [112013][1][WARN] Date operator is not set in app_list_string[]
It seems that $params1 is not set.
And when looking at the SQL i think $params is not set at all:

DATE_ADD(meetings., INTERVAL )

$value = "DATE_ADD($value, INTERVAL ".$dateOp." $params2 ".$params3.")";

[pgorod]

I agree, it seems the app_list_strings are there, but the params1 used to index the array isn't.

That is coming from here: https://github.com/salesagility/SuiteCRM/blob/hotfix/modules/AOW_WorkFlow/AOW_WorkFlow.php#L423

Can you go through this with a debugger and try to see why the condition is not correctly filled?

[gunnicom]

I think that this makes totally no sense:

                    if ($params === false) {
                        LoggerManager::getLogger()->error('Unserializable data given');
                    } else {
                        $params = [null];
                    }

If it is not serializable log an error, and if it is, set it to "null".
Params before that is:

Array
(
    [0] => today
    [1] => now
    [2] =>
    [3] =>
)

And after:

Array
(
    [0] =>
)

[gunnicom]

Ok commenting out "$params = [null];" makes it work again, but i am really curious what were the thoughts behind that code?
That is the commit by @gymad https://github.com/salesagility/SuiteCRM/commit/b8d6ad742c20fd958d552e6e4a95d024ed99f91a

[gunnicom]

My guess is that the "} else {" has to be removed.

[ghost]

Just updated o 7.10.10 and found a whole bunch of errors in logs about workflows with Date condition. Debugging lead to the same conclusion (wish I saw this thread earlier). @pgorod reference to this issue and the propose fix for #6467 does not answer this. Sending a pull request with the correct fix. @gunnicom my guess is as good as yours, the else condition would create an empty array without creating more errors while logging the error. Commenting out $params = [null]; however can be problematic in the failed condition.