sc0ttkclark/exports-and-reports

Define custom exports / reports for users by creating each export / report and defining the fields as well as custom MySQL queries to run.

Hello, it’s nice to meet you. My name is Scott. And I like pizza.

exports-and-reports's Issues

Defined reports are being deleted

We experienced a situation where reports were not longer listed within the group they were assigned. While tracking that down I noticed that I simply had to reassign the report to its group and save. That seemed easy enough so I went about "editing" each report and assigning it to its group. About 20 reports into this process I got a message that a report was no longer available. This seemed odd. So I went back to the manage reports screen and saw that all of my reports were gone. I looked at the database and in fact they were gone there too. I restored the reports from a backup. All was fine - FOR AWILE. After about 90 minutes the reports vanished again.

Steps taken to try and debug:
Removed the function to delete a report. This did not work.

Remove function to reset data. This is the current step and am waiting to see if this works.

Export to excel

Chrome Browser, i can export to all files except excel. File save on FTP, but when I clik button chrome tab open and close immediately.

Support for CTE, variables, EXECUTE IMMEDIATE

Trying to run queries like this (also tried w/ CTE) fails without a really specific error. The SQL runs perfectly against the raw database, but not within this plugin.

I think part of the problem is the use of SQL_CALC_FOUND_ROWS which I've seen inserted during my testing, but would be grateful for any advice on how to get these kinds of queries to run.

SET @cols = (SELECT GROUP_CONCAT(CONCAT('`', COLUMN_NAME, '`')) FROM information_schema.COLUMNS WHERE TABLE_NAME='test_table_1' AND (COLUMN_NAME NOT LIKE '%Revenue%'));
EXECUTE IMMEDIATE CONCAT('SELECT ', @cols, ' FROM `test_table_1`');

passing variables with url

Hi

Is there an option to pass variables through the URL so it will be replaced in my predefined SQL query?
for instance:

Select * from TABLE
where field == %%VARIABLE_NUM_1%%

and then sending in the url "http://.........VARIABLE_NUM_1='blabla'....."

thanks ahead
elad

No CSS for datepicker

Hi,
the datepicker UI widget is shown, but without CSS (not really usable).

Thanks,
Gregorio

Permissions issue when mixing group permissions with individual reports

Some of the reports have been disappearing from user accounts, and as far as I can tell, it's caused by something like this:

• Create a reports group. Grant permission to a single/multiple roles.
• Create multiple reports and assign to above group.
• Grant permission to a group (e.g., same group from the first step) to just one of the reports
• Login as that user, this group will not be visible.

Perhaps I'm somewhat unclear on how the reporting permissions are working since they could be specified at the group level and/or the individual level, but would appreciate any guidance on solving this one. Thanks @sc0ttkclark !

Locales

Hi,
I think your plugin is great. One really useful thing would be manage locales, for example in Italy we use commas as decimal separators and the date format is dd/MM/yyyy.

Thanks,
Gregorio

stored procedure support?

For in-depth reports I often use mysql views, and just select from the view. This lets me keep reports in version control also, rather than copying and pasting them every time into the wordpress admin.
However, mysql views don't support subselects and more complicated queries, so stored procedures seem to be the way to go to abstract the code out of the wordpress admin.
Looked into it briefly by just adding "call procedure(foo)" in the sql, seemed like the hardcoded limit and count lookups in the UI class output were the first issues. I imagine a separate entry for stored procedure would be preferable, rather than just the sql text for "call". Thoughts?

Export issue with WP 5.1.1

Seems like none of the exports work in WP 5.1.1

Checked the SQL query shown with &debug=1 in phpmyadmin and it works fine.

Seems like there is an issue in wp-admin-ui/Admin.class.php Line 1519 and 1521: The
$export_file variable is defined only in the child scopes leaving it undefined at those lines.

After fixing that in my local copy I tried exporting again to get the following error page on Chrome, shown in a new tab
The webpage at http://wpdevelopment/wp-admin/admin-ajax.php?action=wp_admin_ui_export&exports_and_reports_download=1&_wpnonce=8f98e93c5b&exports_and_reports_export=test_table_05-01-2019_02-09-35_VuGJW.csv might be temporarily down or it may have moved permanently to a new web address. ERR_UNSAFE_REDIRECT

XLSX exporting as text instead of numbers

For columns formatted as decimal(14,4) NULL the XLSX export is sending these to text. I think this is the case for other numerical fields as well (int, float, etc.). Would be great if these were natively put into Number format for excel.

Undefined index: page_orientation

Hi @sc0ttkclark

I'm seeing this PHP warning in logs, though not sure which report is triggering it:

PHP Notice:  Undefined index: page_orientation in /var/www/html/wp-content/plugins/exports-and-reports/exports-and-reports.php on line 1258

Also, any chance of bumping the new release to include the changes from March?

The SEARCH button does not work !

Whenever I try to put something in the SEARCH field and click on "Search", the same page is displayed. This happens even though the data to search is on the second page.

SQL error on plugin activation (new install)

[06-Apr-2020 23:19:20 UTC] WordPress database error Column count doesn't match value count at row 1 for query INSERT INTO `wp_exportsreports_reports` VALUES ('1', 'Example Posts Report', '1', '0', '0', '0', '', 'SELECT ID,post_title,post_name,post_date,post_content FROM wp_posts WHERE post_type=\'post\'', '', '[{\"name\":\"ID\",\"label\":\"Post ID\",\"hide_report\":\"0\",\"hide_export\":\"0\",\"custom_display\":\"\",\"type\":\"text\"},{\"name\":\"post_title\",\"label\":\"Post Title\",\"hide_report\":\"0\",\"hide_export\":\"0\",\"custom_display\":\"\",\"type\":\"text\"},{\"name\":\"post_name\",\"label\":\"Post\'s Slug\",\"hide_report\":\"0\",\"hide_export\":\"0\",\"custom_display\":\"\",\"type\":\"text\"},{\"name\":\"post_date\",\"label\":\"Post Date\",\"hide_report\":\"0\",\"hide_export\":\"0\",\"custom_display\":\"\",\"type\":\"date\"},{\"name\":\"post_content\",\"label\":\"Post Content\",\"hide_report\":\"1\",\"hide_export\":\"0\",\"custom_display\":\"\",\"type\":\"text\"}]', '0', NOW(), NOW()) made by activate_plugin, do_action('activate_exports-and-reports/exports-and-reports.php'), WP_Hook->do_action, WP_Hook->apply_filters, exports_reports_install_upgrade, exports_reports_sql_install
[06-Apr-2020 23:19:20 UTC] WordPress database error Column count doesn't match value count at row 1 for query INSERT INTO `wp_exportsreports_reports` VALUES ('2', 'Example Pages Report', '1', '0', '0', '1', '', 'SELECT ID,post_title,post_name,post_date,post_content FROM wp_posts WHERE post_type=\'page\'', '', '[{\"name\":\"ID\",\"label\":\"Page ID\",\"hide_report\":\"0\",\"hide_export\":\"0\",\"custom_display\":\"\",\"type\":\"text\"},{\"name\":\"post_title\",\"label\":\"Page Title\",\"hide_report\":\"0\",\"hide_export\":\"0\",\"custom_display\":\"\",\"type\":\"text\"},{\"name\":\"post_name\",\"label\":\"Post\'s Slug\",\"hide_report\":\"0\",\"hide_export\":\"0\",\"custom_display\":\"\",\"type\":\"text\"},{\"name\":\"post_date\",\"label\":\"Page Date\",\"hide_report\":\"0\",\"hide_export\":\"0\",\"custom_display\":\"\",\"type\":\"date\"},{\"name\":\"post_content\",\"label\":\"Page Content\",\"hide_report\":\"1\",\"hide_export\":\"0\",\"custom_display\":\"\",\"type\":\"text\"}]', '1', NOW(), NOW()); made by activate_plugin, do_action('activate_exports-and-reports/exports-and-reports.php'), WP_Hook->do_action, WP_Hook->apply_filters, exports_reports_install_upgrade, exports_reports_sql_install

Multiple errors and warnings with WP_DEBUG = true

Hey Scott,

Thanks for the plugin, I think this will save me a lot of time.

I have installed the plugin on a fresh WP install 4.9.1 with WP_DEBUG = true and I'm seeing a lot of warnings and errors that make the plugin unusable.

There are quite a few but they are mostly array to string errors and undefined offset errors around the listing of the group name.

Also the 'reset now' (and prob 'clear now') features try to use $wp_filesystem which isn't available.

Is this something you could take a look at? I think it would be easy to recreate, I'm running Nginx and PHP 7.1 if that makes any difference.

Best regards,
Rob

Pagination button not shown !

Hello

My SQL query is returning many rows as shown below

Note that "200" is selected and the number of rows is 85.

If I click on "10", no pagination control is shown and it is impossible to go to the next page !

Is this a bug or am I missing something ?

Thank you

Noob: How to get the post's thumbnail?

I´m almost using the default query.

But i want to get the post's thumbnail, how can i do it?

This is the query:

SELECT ID,post_title,post_name,post_date,post_content,the_post_thumbnail FROM wp_posts WHERE post_type='post' AND post_status='publish'

I've added "the_post_thumbnail" but i get nothing more than the message "No items found"

What is the correct query?

Thanks in advance.

This plugin doesn't seem to work on windows due to some \ instead of /

When I try to export a report I get ERR_UNSAFE_REDIRECT error. To fix this I had to go to the Admin.class.php file and replace line 23:

wp_redirect( str_replace( WP_ADMIN_UI_EXPORT_DIR, WP_ADMIN_UI_EXPORT_URL, $file ) );

With this:

wp_redirect( str_replace("\", "/", str_replace( str_replace("/", "\", WP_ADMIN_UI_EXPORT_DIR), WP_ADMIN_UI_EXPORT_URL, $file ) ));

To make sure that all slashes are forward slashes and then everything worked just fine. I have this plugin on a linux server as well and I didn't have to change anything on the linux server so it seems to be a windows issue.

Code requires vendor/autoload.php

After downloading the 0.9.3 .zip file and installing it in our WP install (WP 6.1.1, PHP 7.4), both by uploading the .zip using Plugins >> Add New >> Upload Plugin, and via FTP, then when activating the plugin, an error was raised.
Error message:
Plugin could not be activated because it triggered a fatal error.
Warning: require_once(/httpdocs/wordpress/wp-content/plugins/exports-and-reports-main/vendor/autoload.php): failed to open stream: No such file or directory in /httpdocs/wordpress/wp-content/plugins/exports-and-reports-main/exports-and-reports.php on line 36
This folder/file is not included in the .zip or the repository. As one new to Git and WP plugins, am I missing something obvious?

Can't get Groups to work

When I create a 2nd group it is added to the Reports menu as it should, but clicking on this 2nd group it "links" back to the first group created (I have assigned reports to the group). Is this is known issue? Currently I am forced to keep all reports in one group = one long list :-(

Excel export of numeric fields

Fields defined as numeric seem to be included in the Excel export as text values