FullCalendar Visualizer Malformed SQL

[Ninpo]

I am using Joomla 3.7.5 and as of this posting, I pulled the very latest github version of fabrik.

I am testing out the FullCalendar visualizer. I have a form with 2 groups in it. One of the groups is set to repeating. In the repeating group, I have 2 date elements, a start and end date.

I set the calendar visualizer start date and end date elements to the respective start and end elements in the table's repeating group.

When the calendar tries to display data back to me in a menu item, there is no data in the calendar. When I opened up the browsers debug console I see the following error.

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 '[] >= '2017-07-30' AND gpf_gaps_credential_manager_91_repeat.date_expiring[] <= ' at line 40

I opened up the visualizer plugin into PHPStorm and found out that in the FullCalendar's model code on line 449 when you pull the $endField = $endElement->getFullName(false) to build the where clause, it returns the array name of the field and passes that to the SQL statement. If the square brackets are checked for and removed prior to assigning it to the where clause the SQL will work properly.

Thanks!

 

[Ninpo]

I was curious if anyone has had a chance to look into this? It would be great to test out the full calendar visualizer with data fed from a repeating group.

 

[troester]

Set the full calendar viz to use the repeat data table directly.

Gesendet von meinem GT-I9300 mit Tapatalk

 

[Ninpo]

Thank you for your response.

If you are talking about setting the table property to use the child table that is generated from the repeated group, there is no way to do that within the table property. The table property only lists the master tables created by the "lists". Much less, even if there was that capability, I need to use some elements from different groups for different properties. I.e. The label property comes from a non-reapeating group but the date elements come from the repeating group.

The problem is that the value that is returned from the getFullName function returns back square brackets which is then pushed into the generated where clause. The square brackets "[]" should be checked for and removed prior to pushing it to the SQL statement, as this is not valid SQL.

This problem only exists in the fullcalendar visualizer, not the calendar visualizer. I read on these forums that the fullcalendar visualizer is replacing the calendar visualizer.

I can set the date elements in the calendar visualizer to a non-repeating group and it works properly. However, once I use date elements within a repeating group, that's where the problem is. In the attached image above, you can see that the value returned into the $endField variable has square brackets which leads to a SQL error. In the 2 attached images, to this post, I show how I have setup the visualizer.

 

[Ninpo]

I have not heard anything in regards to this issue for a while. I went ahead and made the following quick code changes in my local system in order to fix the bracket issue with the start/end date fields.

$startField   = str_replace('[]','', $startElement->getFullName(false));

$endField     = str_replace('[]','', $endElement->getFullName(false));

Is it possible to make this change to the official repository for this plugin so that I do not need to apply this fix every time I do a pull to do an update?

 

[cheesegrits]

Should be fixed as of this commit:

https://github.com/Fabrik/fabrik/commit/ab579415558098b3f8648235d83f85fa9ee284df

That was some code I'd added recently, to constrain the query to only the dates being displayed in the selected month, which didn't exist in the deprecated plugin. Just FYI, you don't need to do that str_replace, just add a second arg of 'false' to the getFullName, which tells it not to add the [] on repeats.

I also added some belt and braces code to properly quote those fields in the query.
-- hugh

 

[Ninpo]

Thank you Hugh!

I look forward to testing it!

I also noticed one more small anomaly with the full calendar visualizer time format option. According to the tooltip if you do not want the time to appear prepended to the label, you use "()". Using () as the time format does not seem to work, instead it displayed the full datetime. I was able to find a workaround for this without any code changes by using a single space character instead of any format for this option. Please see attached screen shots below showing the problem.

 

[cheesegrits]

Ah, OK, thanks. Looks like they changed how the 'moment' time format works in 2.x. I've added a little tweak to keep that tooltip valid, and just replace '()' with ' ' internally, so it'll work as advertized.

https://github.com/Fabrik/fabrik/commit/899390f29cf2446e06beaa8a6ed36a80993835b7

-- hugh

 

[Ninpo]

I just tested both of your changes and they worked perfectly. Thank you!