Suitecrm: 7.8.20LTS AOR_Report fails using a date parameter

[nelem]

Issue

The report fails with a MySql syntax error when using a non-simple Date value and hitting the Update button. The report also fails when paginating, exporting CSV and exporting PDF.

Expected Behavior

The report should work.

Actual Behavior

MySql Syntax Error, empty page (or export). If the date is not a parameter, the report works correctly.

Possible Fix

There are many references to this problem and the fixes may not have been backported to the 7.8.20 code.

Steps to Reproduce

1. Create a report with a date condition such as today-36 months as a parameter.
2. Paginate (or export) the report.
   3.
   4.

Context

Medium because the report works without the date parameter. The users can edit the (fixed) date in the report.

Your Environment

• SuiteCRM Version used: 7.8.20 (upgraded 7.8.x->7.8.20) Also in 7.8.6
• Browser name and version (e.g. Chrome Version 51.0.2704.63 (64-bit)): 67.0.3396.99 (Official Build) (64-bit)
• Environment name and version (e.g. MySQL, PHP 7): MySql: 5.7.23-0ubuntu0.16.04.1 PHP:7.0.30-0ubuntu0.16.04.1
• Operating System and version (e.g Ubuntu 16.04): Ubuntu16.04

[gunnicom]

Although it is closed maybe it is related to https://github.com/salesagility/SuiteCRM/issues/5663

[Dillon-Brown]

@gunnicom Potentially, although this should have been packported to 7.8.x here: #5895.

[nelem]

The commits for #5895 do seem to match the code for 7.8.20 by eye. However, I could only see find the function updateHiddenReportFields in Chrome loaded Javascript after doing a Repair, Rebuild JS and Rebuild JS Grouping. I assumed the upgrade would have done all that.

The Report value being tested is Field=datefield Operator=Less Than Type=Date Value=Today-36 Months

The function appendHiddenFields doesn't recognize that values can be in a two-dimensional array.
See Comment // values can be #aor_conditions_value3 or #aor_conditions_value[3]
but the values have ids such as "aor_conditions_value[0][0]"

I am now at the limit of my JS knowledge.

[ChangezKhan]

@nelem yeah, you are right . Actually aor_conditions_value should be two-dimensional so when one have multiple conditions in report then it will work with no issues. I think we need to change it to two-dimensional array.

[ChangezKhan]

This issue also reproduce-able in 7.10.7

[nelem]

@ChangezKhan This fixes the Update using a parameter and also the PDF and CSV export. :-)
However, pagination is still broken.

[ChangezKhan]

@nelam I have fixed pagination issue also. Can you test that once again?
Edit: Kindly make sure that you merged those changes perfectly.

[nelem]

@ChangezKhan Fixed. :-) Not sure how I got half the fix.
On another point, is there a quick, foolproof way to update the JS into the group files? Even command line?

[ChangezKhan]

@nelem , I don't think the code available in this AOR_Report.js file gets merged into group files.