Mysql report in client area?

[Remitur]

Hello.

I need to make available to the user, in client area, the results of a certain mysql query...

In admin area there's a comprehensive "report engine", and so the job would be easy... but what about client area?

Before going on reinventing the wheel: does exist any bootstrap tool, or any customizable ready template to do this?

 

[brian!]

how much data is there and how/where do you want to show it?

if there's enough data for a table (and that's an applicable method to show it), then you could just copy one of the existing dataTables templates and adjust the template code accordingly (and write the hook to return the required data);

if there isn't that much data, then where to display it? custom page ?? in an existing page ??? in a sidebar/panel ????

[Remitur]

@brian!

Sorry: as result of a query mysql, I mean a bunch of records (even hundredsof them) to show in a custom page, but did not explained it...

My desiderd goal is something like the /clientarea.php?action=services , where the user may have hundreds of record, and an interface to navigate into them (filtres, search, pagination for 10, 35 or 50 records, and so on...)

In https://developers.whmcs.com/themes/customising/ I found the reference to "tablelist.tpl - Controls the output of all filterable data list tables throughout the client area", but found no other documentation about, and neither any example code... so I guess the only way is going to analyze the Smarty variables that are passed to the existing pages in client area that use tablelist.tpl (such as /clientarea.php?action=services or /clientarea.php?action=invoices ), and try to discover how to use it... 

 

 

Edited November 29, 2020 by Remitur

[brian!]

19 hours ago, Remitur said:

My desired goal is something like the /clientarea.php?action=services , where the user may have hundreds of record, and an interface to navigate into them (filters, search, pagination for 10, 35 or 50 records, and so on...)

if this is going to be on a custom page, then you're probably looking at creating 3 parts to the solution.

1. a custom .php page that will include your SQL query and return it to the template as a Smarty array - https://developers.whmcs.com/advanced/creating-pages/
2. a custom .tpl template based on clientareainvoices or clientareaproducts - frankly, I don't think it will matter which dt template you choose as a starting point, they're all very similar... you should just need to change the tablename references; specifiy if certain columns can't be sorted or filtered; adjust the table headings & variable names to your return SQL query array (or vice versa if you're feeling lazy!).
3. a custom sidebar hook to generate the filter - i'm not aware of any such hook code ever being published here or elsewhere - it's an absolute pain to do, so worry about 1 & 2 first and then cross the filter bridge when you have your output and know what you want to filter on... in any event, it's likely going to need to use a similar query as (1) to generate the statuses or whatever it's going to be filtering on.

search and pagination features will be automatically available unless disabled, so I wouldn't worry about that either.

19 hours ago, Remitur said:

In https://developers.whmcs.com/themes/customising/ I found the reference to "tablelist.tpl - Controls the output of all filterable data list tables throughout the client area", but found no other documentation about, and neither any example code...

I wouldn't worry about tablelist, it's highly unlikely that you would ever need to edit it - it's just a sub-template used by clientareainvoices etc to output an array in a sortable, filterable table.

19 hours ago, Remitur said:

so I guess the only way is going to analyze the Smarty variables that are passed to the existing pages in client area that use tablelist.tpl (such as /clientarea.php?action=services or /clientarea.php?action=invoices ), and try to discover how to use it... 

really, all you should need to do is get the array generated by the query into an order that the custom template can output - e.g fields of the array are referenced correctly by the template code.

if it helps to start off with, put a {debug} in clientareainvoices and then examine how the $invoices array is being outputted by the template.

technically, these are DataTables that WHMCS is using and their documentation can be found at https://datatables.net/examples/index - however, WHMCS seems to call it in a slightly kinky way and therefore a word of advice - don't go to their docs, think that you can use x, y or z exciting feature and then try to incorporate them into WHMCS - you'll likely end up with errors!

certainly to start off with, you shouldn't even need to view the docs as you're not reinventing the wheel, just duplicating what WHMCS already does but with new content.