Custom WHMCS Reports

[HogWildMark]

How do I create a custom WHMCS report that will allow me to pull information and show me the following:

* Number of sales per staff member (We have a custom field created for this)

* Which product they sold.

 

This would be beneficial in allowing us to compensate our staff efficently.

[HogWildMark]

I have the report created now

I just need help getting the required info into it

 

<?php

 

if (!defined("WHMCS"))

die("This file cannot be accessed directly");

 

$reportdata["title"] = "Staff Reports";

$reportdata["description"] = "A summary staff sales";

 

$reportdata["tableheadings"][] = "Period";

 

foreach ($currencies AS $currencyid=>$currencyname) {

$reportdata["tableheadings"][] = "$currencyname Amount";

}

 

$totals = array();

 

for ( $day = 0; $day < 120; $day += 30) {

$startdate = date("Y-m-d",mktime(0,0,0,date("m"),date("d")-$day,date("Y")));

$enddate = date("Y-m-d",mktime(0,0,0,date("m"),date("d")-($day+30),date("Y")));

$rowdata = array();

$rowdata[] = "$day - ".($day+30);

$currencytotals = array();

$query = "SELECT tblcustomfields,SUM(tblinvoices.total),(SELECT SUM(amountin-amountout) FROM tblaccounts INNER JOIN tblinvoices ON tblinvoices.id=tblaccounts.invoiceid INNER JOIN tblclients t2 ON t2.id=tblinvoices.userid WHERE tblinvoices.duedate<='$startdate' AND tblinvoices.duedate>='$enddate' AND tblinvoices.status='Unpaid' AND t2.currency=tblclients.currency) FROM tblinvoices INNER JOIN tblclients ON tblclients.id=tblinvoices.userid WHERE tblinvoices.duedate<='$startdate' AND tblinvoices.duedate>='$enddate' AND tblinvoices.status='Unpaid' GROUP BY tblclients.currency";

$result = mysql_query($query);

while ($data = mysql_fetch_array($result)) {

$currencytotals[$data[0]] = $data[1]-$data[2];

}

foreach ($currencies AS $currencyid=>$currencyname) {

$currencyamount = $currencytotals[$currencyid];

if (!$currencyamount) $currencyamount=0;

$totals[$currencyid] += $currencyamount;

$currency = getCurrency('',$currencyid);

$rowdata[] = formatCurrency($currencyamount);

}

$reportdata["tablevalues"][] = $rowdata;

}

 

$startdate = date("Y-m-d",mktime(0,0,0,date("m"),date("d")-120,date("Y")));

$rowdata = array();

$rowdata[] = "120 +";

$currencytotals = array();

$query = "SELECT tblcustomfields,SUM(tblinvoices.total) FROM tblinvoices INNER JOIN tblclients ON tblclients.id=tblinvoices.userid WHERE tblinvoices.duedate<='$startdate' AND tblinvoices.status='Unpaid' GROUP BY tblclients.currency";

$result = mysql_query($query);

while ($data = mysql_fetch_array($result)) {

$currencytotals[$data[0]] = $data[1];

}

foreach ($currencies AS $currencyid=>$currencyname) {

$currencyamount = $currencytotals[$currencyid];

if (!$currencyamount) $currencyamount=0;

$totals[$currencyid] += $currencyamount;

$currency = getCurrency('',$currencyid);

$rowdata[] = formatCurrency($currencyamount);

}

$reportdata["tablevalues"][] = $rowdata;

 

$rowdata = array();

$rowdata[] = "<b>Total</b>";

foreach ($currencies AS $currencyid=>$currencyname) {

$currencytotal = $totals[$currencyid];

if (!$currencytotal) $currencytotal=0;

$currency = getCurrency('',$currencyid);

$rowdata[] = "<b>".formatCurrency($currencytotal)."</b>";

}

 

$reportdata["tablevalues"][] = $rowdata;

 

?>

[HogWildMark]

Does anyone have any idea on how to do this.

[HogWildMark]

I'm still seeking assistance. Does anyone from WHMCS read these forums. Help would be greatly appreciated?

[Chance]

Hello,

 

Yes, I have actually written several custom reports in WHMCS and incorporated them into WHMCS.

From first glance, it appears that your report file looks valid as far as coding goes with assigning elements of the report to WHMCS's array variables used by the report engine, such as $reportdata['tablevalues'] = ... and so on. I cannot speak for your database retrieval stuff since your database is specific to you, but if your SQL query code is accurate, and you use only the report variables that the reporting engine looks for, you should have no problem.

 

Then, to incorporate your report into WHMCS, you need to copy the report file to the modules/reports/ folder of your WHMCS install.

 

Then, to load the report, you simply invoke it by accessing WHMCS's reports.php script and passing it the report name:

https: // whmcsdomain/admin/reports.php?report=your_report_name

 

In the URL above, the report name should not include the .php extension as WHCMS will automatically add that when loading the report.

 

While you can create custom client-side pages, you cannot create custom admin-side pages. This means that you must use WHMCS's reporting engine to successfully view your reports.

 

Hope this helps.....

[HogWildMark]

Hello,

 

Yes, I have actually written several custom reports in WHMCS and incorporated them into WHMCS.

From first glance, it appears that your report file looks valid as far as coding goes with assigning elements of the report to WHMCS's array variables used by the report engine, such as $reportdata['tablevalues'] = ... and so on. I cannot speak for your database retrieval stuff since your database is specific to you, but if your SQL query code is accurate, and you use only the report variables that the reporting engine looks for, you should have no problem.

 

Then, to incorporate your report into WHMCS, you need to copy the report file to the modules/reports/ folder of your WHMCS install.

 

Then, to load the report, you simply invoke it by accessing WHMCS's reports.php script and passing it the report name:

https: // whmcsdomain/admin/reports.php?report=your_report_name

 

In the URL above, the report name should not include the .php extension as WHCMS will automatically add that when loading the report.

 

While you can create custom client-side pages, you cannot create custom admin-side pages. This means that you must use WHMCS's reporting engine to successfully view your reports.

 

Hope this helps.....

 

I have the reported created in billing/modules/reports called staff_report.php and yes I can run the report from within whmcs admin. That much I know how to do.

 

The above code is from another report I copy/pasted into the staff_report.php

 

What I need to know is what fields I need to generate the report I want or if I can get someone to do it for me?

[Chance]

Hi,

 

Since you posted a sample report that involves currencies, this implies you are looking for a report such as:

 

Employee | USD | GBP | EUR ... etc.

------------------------------------------------------------------------------------

Tom | 4.00 USD | 6.50 GBP | 7.43 EUR ... etc.

Dick | 7.25 USD | 8.50 GBP | 9.99 EUR ... etc.

Harry | 3.00 USD | 5.70 GBP | 6.73 EUR ... etc.

 

That is the most logical way of looking at what sales a specific employee brought in with a specific currency -- one column per currency with one row for each employee.

 

***************************************************************************************************

 

But, take currency out of the equation, this implies you are looking for a report such as:

 

Employee | Product #1 | Product #2 | Product #3 ... etc.

--------------------------------------------------------------------------------------

Tom | 15 | 35 | 6 ... etc.

Dick | 25 | 15 | 9 ... etc.

Harry | 10 | 18 | 2 ... etc.

 

That is the most logical way of looking at how many of each product a specific employee

sold -- one column per product with one row for each employee.

 

***************************************************************************************************

 

Which one are you looking for? Report on currency sales, or report on product sales count?

[HogWildMark]

Hi,

 

Since you posted a sample report that involves currencies, this implies you are looking for a report such as:

 

Employee | USD | GBP | EUR ... etc.

------------------------------------------------------------------------------------

Tom | 4.00 USD | 6.50 GBP | 7.43 EUR ... etc.

Dick | 7.25 USD | 8.50 GBP | 9.99 EUR ... etc.

Harry | 3.00 USD | 5.70 GBP | 6.73 EUR ... etc.

 

That is the most logical way of looking at what sales a specific employee brought in with a specific currency -- one column per currency with one row for each employee.

 

***************************************************************************************************

 

But, take currency out of the equation, this implies you are looking for a report such as:

 

Employee | Product #1 | Product #2 | Product #3 ... etc.

--------------------------------------------------------------------------------------

Tom | 15 | 35 | 6 ... etc.

Dick | 25 | 15 | 9 ... etc.

Harry | 10 | 18 | 2 ... etc.

 

That is the most logical way of looking at how many of each product a specific employee

sold -- one column per product with one row for each employee.

 

***************************************************************************************************

 

Which one are you looking for? Report on currency sales, or report on product sales count?

 

Both actually

 

We pay our staff $2 per sales plus 15% commission on a monthly basis. So I would need total over dollar amount of sales and the total number of sales. It does not need to be broken down per sales

 

Example

Employee | Products Sold | Product Sales

--------------------------------------------------------------------------------------

Tom | 15 | 35

Edited May 11, 2011 by HogWildMark

[HogWildMark]

This is what I want and how I want it layed out

 

<?php

 

# The title of your report

$reportdata["title"] = "Staff Sales Report";

 

# The description of your report

$reportdata["description"] = "Staff Sales Report";

 

# Header text - this gets displayed above the report table of data

$reportdata["headertext"] = "";

 

# Report Table of Data Column Headings - should be an array of values

$reportdata["tableheadings"] = array("Staff Member","Total Products Sold","Total Monthly Sales");

 

# Report Table Values - one of these lines for each row you want in the table

# should be an array of values to match the column headings

$reportdata["tablevalues"][] = array("Staff Name 1",Number of Product Sales","$Grand Total Sales");

$reportdata["tablevalues"][] = array("Staff Name 1",Number of Product Sales","$Grand Total Sales");$reportdata["tablevalues"][] = array("Staff Name 1",Number of Product Sales","$Grand Total Sales");

# Report Footer Text - this gets displayed below the report table of data

$data["footertext"] = "Staff Sales Report";

 

?>

 

I am just not sure what fields I need to enter from our sql tables to pull that information and to go about entering that info into the above code. Plus I would like to generate this report based on the the month I want.