Report Generator 3.X For WHMCS

About Report Generator For WHMCS

Report Generator For WHMCS allows you to generate and manage any kind of reports using inbuilt drag & drop WYSIWYG editor. Module lets you to adjust reports to your needs through using various filters, conditions and even your own PHP code. You can display ready reports in tables, graphs or charts. You can also generate reports in XML, CSV and PDF, as well as schedule their creating and send them over an email to specified staff members.

• Admin Area Features:

✔ Drag & Drop WYSIWYG Report Builder

✔ Dynamic Reports Editing

✔ Report Types (Data Table, Pie Chart, Area Chart, Column Chart)

✔ Predefined Reports

✔ User Created Reports

✔ Multi-Sections Of Dynamically Generated Reports

✔ Dynamic Filters

✔ Predefined SQL Expressions At The Report Builder

✔ Multiple Joins At The Report Builder

✔ Two View Types Of Report Pages (Simple And Widget Alike)

✔ Print Reports With A Print Preview

✔ Schedule Automatic Reports Sending To Chosen Staff Members With Specified Conditions

✔ Export To CSV, XML and XML Structure

✔ Export To CSV From Report View - Allows You To Set Up Undefault Value Of Filter

✔ Import From The XML Structure

✔ Generate PDF - Both Manually And By Cron Job

✔ Translation Of Database Columns And Tables From The Settings Tab

✔ Enable Unicode Font For Generated PDF

• General Info:

✔ Multi-Language Support

✔ Supports WHMCS V5 and Later

✔ User-Friendly Interface

✔ Migration Tool

• User Created Reports:

✔ 1. Section 1: Orders Of Services In Selected Time Period (Column Chart)

✔ 2. Section 1: Orders Of Services In Time (Column Chart)

✔ 3. Section 1: Orders Over The Weekdays (Pie Chart)

✔ 4. Section 1: Most Valuable Clients (Data Table)

✔ 5. Section 1: User Accounts Created In Selected Time Period (Data Table)

Section 2: Amount Of User Accounts Created In Selected Time Period (Column Chart)

✔ 6. Section 1: Tickets Replied By Admin (Data Table)

Section 2: Tickets Replied By Admin (Data Table)

Section 3: Tickets Replied By Admin (Pie Chart)

✔ 7. Section 1: Customers Per Country (Pie Chart)

• Predefined Reports:

✔ 1. Section 1: Open Tickets For The Time Period (Data Table)

Section 2: New Invoices For The Time Period (Data Table)

Section 3: Invoice Reminders For The Time Period (Data Table)

Section 4: Hosting Renewals (Data Table)

Section 5: Suspended Services For The Time Period (Data Table)

Section 6: Domain Renewals For The Time Period (Data Table)

✔ 2. Section 1: Closed/Answered Status Tickets By The Staff Member (Data Table)

Section 2: Tickets Stats For The Staff Over Time (Area Chart)

Section 3: Closed To-Do List Tasks Owned By The Staff Member (Data Table)

Section 4: To-Do List Tasks Owned By The Staff Member Grouped By Status (Pie Chart)

Section 5: Orders Summary (Area Chart)

✔ 3. Section 1: Opened Tickets Stats For This Staff Over Time (Area Chart)

Section 2: To-Do List Tasks Owned By The Staff Member Grouped By Status (Pie Chart)

Section 3: Closed To-Do List Tasks Owned By The Staff Member (Data Table)

Installation

This tutorial will show you how to successfully install and configure Report Generator For WHMCS. We will guide you step by step through the whole installation and configuration process.

1. Log in to your client area and download Report Generator For WHMCS.

2. Upload and extract the module into the main WHMCS directory. Files in your WHMCS directory should look like this.

3. Remove all files from 'templates_c' folder.

4. When you install Report Generator for the first time you have to rename 'license_RENAME.php' file. File is located in 'modules/addons/reportgenerator/license_RENAME.php' . Rename it from 'license_RENAME.php' to 'license.php' .

5. In order to configure your license key, you have to edit a previously renamed 'license.php' file.' Enter your license key between quotation marks as presented on the following screen. You can find your license key in your client area → 'My Products' .

6. Now you have to create 3 folders in a 'storage' folder and set chmod '755' or '777' to them. 'storage' folder is located in 'your_whmcs/modules/addons/reportgenerator/' . Note: If you set up chmods incorrectly, export report and generate PDF report functionalities will not work.

7. Now you have to activate the module in your WHMCS system. Log in to your WHMCS admin area. Go to 'Setup' → 'Addon Modules' . Afterwards, find 'Report Generator' and press 'Activate' button.

8. In the next step you need to permit access to this module. To do so, click on 'Configure' button, tick 'Full Administrator' and press 'Save Changes' .

9. The last step is setting up cron jobs. You can find them at your module 'Addons' → 'Report Generator' → 'Cron Jobs' → 'Cron Setup' .

10. You have just successfully installed Report Generator! You can access the module at 'Addons' → 'Report Generator' .

Configuration and Management

Report Generator For WHMCS allows your to create custom reports using drag & drop WYSIWYG builder. Module also offers additional useful features like predefined reports, import/export of reports, generating reports in PDF, ability to send then to admins and much more.

Reports List

At the 'Reports Lists' tab you can view all predefined reports as well as those created by you. Here you can manage your reports by viewing (1), editing (2) and removing them (8). Additionally, you can generate PDF version of reports (3) as well as export reports to CSV (4 & 5), XML (6) and XML structure (7).

For predefined reports you can view report, generate PDF file and export report to CSV and XML.

To start creation of report press 'Create New Report' . More information about creating new reports will be presented in the Creating Report section.

Report View

In the report view you can see all tables and charts generated by the report. You can display report in a simple or widget format. To switch between them press one of the buttons marked on the screen below.

PDF Report View

You can also view a PDF version of report which can be sent to your staff memebers or saved to PDF file on your disk.

Creating Report

In this section we will show you how to prepare a few examplary reports. If you wish to create your own report, we strongly recommend creating reports shown below firstly in order to get some practice. The first report will be quite simple and described step by step while the next ones will be more and more complex. We will show you how specific options affect a final report.

First Report - User Accounts Created

In the beginning there is only one section in your report. Each section generates one chart on the final report. In order to create additional charts on your report you need to add a new section. Below we will show you how to set up a single section. This examplary report will show all new clients whose accounts were created during the last month. Start by entering report name and description.

Now, select desired chart type and table you want to start with. As you can see, default columns have been loaded. For purposes of this report we will remove them through pressing 'Remove All The Columns' .

Adding columns to report is quite easy owing to an autocomplete box. Type table name and list of all available columns will show up. Now, select desired column.

As you may have noticed earlier, each change made affects report preview displayed below. In some situations preview may be replaced with error, which will contain information about lacking or miss-configured settings of report. On the following screen error message is caused by using incorrect function 'COUNT()' .

To set up time period for report, go to 'Conditions' tab and mark 'Filter results by date' .

Afterwards, select 'Clients Table : datecreated' and 'Monthly' from marked dropdown menus. Now, report will be generated using only accounts created during the last 30 days. Note: Time period for report can be changed later at the report view or email configuration.

You can set up ordering records by specific column. To do that, go to 'Groups & Limits' tab. We will order records by client last name. Choose 'Clients Table : lastname' and 'Ascending' as shown on the following screen. Your report displaying all new accounts created during the last month is ready, press 'Generate Report' to save it.

Let's view that report through pressing 'View' button. As you can see, column names are names of table columns. Our module allows you to easily change column names under which table columns will be displayed. You will find more information about setting up custom column names in Tables And Columns Definitions section. While viewing report with enabled filtering by date you can easily change time periods as shown on the following screen.

Second Report - Orders Per User

This report will be counting orders made by clients this year. Due to that, we will need to use group by command to count those products. As previously, start by defining report name and description. Additionally, choose report type and starting table.

Join orders table to clients table. We will need it to display columns from both tables here. Press 'Join Another Table' to proceed.

It is time to think about how we want to connect these two tables. We want to display a client even if he/she has no orders, therefore from 'Please Specify A Join Type' we will choose: 'Return all rows from the left table, even if there are no matches at the right table' .

Now select two tables to join. For purposes of this report, these will be tables 'Clients' and 'Orders' . It is time to decide which columns will be used to join chosen columns. In the table 'Orders' we can find column 'userid' in which there is stored ID of client who made an order. Therefore we will join them using 'Clients Table : ID' and 'Orders Table : userid' . In the end press 'Confirm!' .

We want to display clients details in one column and quantity of orders in another one. The first column will be displayed by: concat(tblclients.id, '# ', tblclients.firstname, ' ', tblclients.lastname) as Client Concat will join provided columns while 'Client' is alias for this column. Alias will be used for generated report. COUNT(*) as Orders Count along with grouping by allow us to count records for each client. Orders Table : ID as Any_Order_Exist This column was added to handle clients who do not have any orders. Due to join type, 'COUNT(*) as Orders' will be filled with 1 for them. Proceed to 'Groups & Limits' tab to set up grouping by (currently our generator does not recognize what should be counted).

We want to group records by clients, therefore fill 'Group This Section By' with 'Clients Table : ID' which is a unique key at the client table. Additionally, let's set up sorting clients by quantity of orders, we can use column alias for this. As we predicted, clients with 0 orders have assigned 1 order. Proceed to 'Your Own Code' to fix that.

At 'Your Own Code' tab you can modify received output. Code has to be placed in delimiters as shown below. <?php // your custom code here ?> The '$result' variable is passed by reference and contains just iterated element of the result array. In this way you can create new columns in your table or modify existing ones through modifying this variable. You can access it as typical array where the key is a column name and value is a current row value. Important 1: Use this field with caution, because every mistake made here can result with error in your production environment. Note: While working on $result you can use aliases! What we need to do is assign 0 to 'Orders' column if 'Any_Order_Exist' column is empty. Additionally, we are unable to remove previously added column, therefore we will display 'Yes/No' in 'Any_Order_Exist' depending on existence of any order assigned to client. Code placed below will do it for you: <?php foreach($result as $element) // iterate over all records storing current row in $element variable { if($element->Any_Order_Exist=="") // check if Any_Order_Exist is empty {  $element->Orders=0; //assign 0 to Orders if Any_Order_Exist is empty  $element->Any_Order_Exist='No'; //assign 'No' to Any_Order_Exist if it is empty } else // if previous if was false, commands stored here will be ordered {  $element->Any_Order_Exist='Yes'; //assign 'Yes' to Any_Order_Exist } } ?>

Your code is ready, press 'Generate Report' to save it.

Third Report - Unpaid Invoices

In the third report we will show you how to prepare report displaying clients with the most unpaid invoices. We will also add second section displaying unpaid invoices which can be filtered by client first and last name. Start by defining report name and description, choose report type and starting table. Press 'Join Another Table' to proceed.

Now, join invoice table using 'Clients Table : ID' and 'Invoices Table : userid' and set it up to return tables only if there is a match in the invoice table.

Add 'COUNT(*) as Sum' column and proceed to the 'Conditions' tab.

Press 'Create New Filter' button to define new condition for your report.

Select column you want to define condition for, select filter operand, define default value of filter and filter friendly name. Conditions can be changed at the report view, they will be displayed under friendly name defined here.

Fill 'concat(tblclients.id, tblclients.lastname, tblclients.firstname)' into 'Group This Section By' field. Owing to previously introduced concat you can both make sure you will be counting invoices properly and display string generated by concat under summary column in table. This concat returns client ID, lastname and firstname in one string. Order records using 'Sum' alias and define limit of column for this report. Now, let's create the second section. To do so, press 'Create New Section' .

In this section we want to display list of all unpaid invoices of selected client. Select date table and start of client table. Additionally, join client table with invoice table as in previous section.

Now, add desired columns. As you can see on the following screen, we have used concat to display clients details in the first column. Proceed to condition tab and press 'Add New Condition' button.

There are two conditions to add, their default values are left blank so default report will omit these conditions. Nonetheless, you will be able to easily filter for desired client in report view. Now, create conditions shown below.

Proceed to 'Groups & Limits' tab and set up ordering by ID. You can also increase limits of record displayed on the report.

Let's proceed to 'Your Own Code' section to replace 'No Company Assigned' with blank field. Copy and paste code placed below: <?php foreach($result as $element) if($element->companyname=='No Company Assigned') {  $element->companyname=''; } ?> Your report is ready, press 'Generate Report' to finish.

Modifying Report

All reports can be easily altered. Press 'Edit' next to report that you wish to modify.

Edit screen is the same as generate screen. Therefore you are allowed to modify and even entirely change report according to your needs. We will add filtering results by clients' nationality to this report. Join hosting table with client table as shown on the following screen.

Proceed to 'Conditions' tab and press 'Add New Filter' . Filter set up shown on the following screen will filter report output depending on client's country.

Email Configuration

At 'Email Configuration' you can set up emails with PDF reports sent to administrators. Start by selecting admin or admins to which email with report should be sent. Afterwards, choose which of your reports should be sent and decide how often reports should be sent using 'Time Period' dropdown menu. In addition to that, you can define filter values for generated report if report uses any. Confirm through pressing 'Add Relation' button.

As you can see on the following screen, all relations are listed at the bottom of 'Email Configuration' page. Each of those relations can be removed at any time through pressing 'Delete' button.

Import

To import report from the XML file into your Report Generator, go to 'Import' tab. Press 'Browse' button and select your report XML file. Afterwards, press 'Upload' button and your report will be uploaded into the module.

Imported report should be renamed afterwards. Press 'Update Report' as soon as you finish.

Now you can go to 'Reports List' to see that your report has been successfully imported and it is ready to use.

Default Reports Settings

At 'Settings' → 'Default Reports Settings' tab you are allowed to define a few behaviors of our module. 1. Protects generating reports from data lost during refreshing. 2. Pressing 'To PDF' adds a cron job which will generate a PDF report for you. Reports generated by cron are available at 'Cron Jobs' → 'Cron Generated Reports' . 3. Font used to generate PDF reports is switched to font, which supports unicode. During this option being enabled, generated PDF reports are much larger than earlier.

Tables And Columns Definitions

Our module allows you to set up custom table and column names. It is very simple. Go to 'Report Generator' → 'Settings' → 'Advanced Builder Settings' . As you can see, there are three text fields. The first one, 'Table Definitions' enables you to easily change name of the tables visible on the reports created by module. The second one, 'Column Definitions' does the same thing for columns. 'Additional Group Expressions' field, lets you set up predefined MySQL expressions which afterwards may be added through one click. Note 1: 'Column Definitions' will set up custom name for all columns with that name.

Migration

Due to changes in the module and its structure, upgrading it from version 1.0 requires migration tool. It can be accessed at the 'Migration' tab of our module and allows you to migrate reports from the previous version of Report Generator For WHMCS. As you can see, all reports which existed in version 1.0 are listed here. In order to import report from the previous version of the module press 'Migrate' button next to it. Important: Email settings will be lost during upgrade.

Afterwards, press 'Update Report' button. Your report is now available at the 'Reports List' → 'User Generated Reports' .

Tips

1. In order to work properly, module requires PHP with a PDO support.

2. There is workaround which enables you to copy report. Simply export report to XML structure and import report from a received file.

Common Problems

1. When you have problems with connection, check whether your SELinux or firewall does not block ports.

2. If you encounter any troubles with our module it is possible that you have not installed PDO.