Profit and Loss Report

[Chance]

Hello all.

WHMCS was definitely not designed with pre-built reports to report detailed income statistics.

Having the need to view details about what income came in from what product, domain, or service was a must for us.

 

So I wrote a custom report that does report ALL income in a very detailed layout, grouped by product type and/or service type, similar to how a true P&L report would do. Our report is titled Profit & Loss. We also track our expenses related to our business so our P&L actually contains both income and expense, with a complete summary for total income, total expenses, gross profit, net income, etc. And it is a report that is grouped by product type can be filtered by date range. Each income total in the P&L report is a link in the report that loads a Sales by Product report with line by line item detail for all the invoice items used in the P&L report for a specific product and/or service.

 

For this post, I am only posting the queries needed to get the summary income from the WHMCS database tables.

 

WHMCS does keep track of every product/service sold--it just does so in more than one database table. Not a great method but it does work. You just have to know which database table the different products, services, and domain information is stored in. This is easily accomplished when you know the product type.

 

All products of type 'Other' are stored in tblinvoiceitems, and the relid field in tblinvoiceitems points directly to the product in tblproducts.

 

All products of type 'Domain' are stored in tbldomains, and the relid field in tblinvoiceitems points directly to the domain in tbldomains.

 

All products of type 'Hosting' are stored in tblhosting, and the relid field in tblinvoiceitems points directly to the product in tblhosting, and the packageid field in tblhosting points directly to the product in tblproducts.

 

1) Report Product Income

Create a query to pull back all products from your WHMCS install, like so, for example:

$query = "SELECT pg.id AS gid, pg.name AS groupname, p.id, p.name, p.type FROM tblproductgroups pg INNER join tblproducts p ON p.gid=pg.id ORDER BY pg.name, p.order";

$products = mysql_query($query);

 

2) Set your date range and running total

$totalincome = 0;

$startdate = '2011-06-01';

$enddate = '2011-06-30';

 

3) Then, loop through each product, like so:

while ( $product = mysql_fetch_array( $products ) )

{

$pid = $product["id"];

 

# if product type = other get data from tblinvoiceitems

if($product['type']=="other")

{

# get data from invoices

$query = "SELECT SUM(amount) FROM tblinvoiceitems ii WHERE relid=$pid AND invoiceid IN ";

$query .="(SELECT id FROM tblinvoices WHERE (datepaid >= '$startdate' AND datepaid <= '$enddate')) ";

$query .= "AND type='Other'";

$pincome = scalarQuery($query);

$totalincome += $pincome;

}

 

# all other products go through tblhosting

else

{

$query = "SELECT SUM(amount) FROM tblinvoiceitems ii WHERE relid IN ";

$query .= "(SELECT id FROM tblhosting WHERE packageid=$pid) AND invoiceid IN ";

$query .="(SELECT id FROM tblinvoices WHERE (datepaid >= '$startdate' AND datepaid <= '$enddate')) ";

$query .= "AND type!='Other' AND type !='Domain'";

$pincome = scalarQuery($query);

$totalincome += $pincome;

}

 

4) Report Domain Income

Getting domain-related income is a three-step process because you need to know how much you earned in registrations, transfers, and renewals.

 

 

4a) For registrations, like so:

$query = "SELECT SUM(amount) FROM tblinvoiceitems WHERE type='Domain' AND description LIKE 'Domain Registration%' ";

$query .= "AND invoiceid IN ";

$query .= "(SELECT id FROM tblinvoices WHERE datepaid >= '$startdate' AND datepaid <= '$enddate') ";

$domainreg = scalarQuery($query);

 

4b) For transfers, like so:

$query = "SELECT SUM(amount) FROM tblinvoiceitems WHERE type='Domain' AND description LIKE 'Domain Transfer%' ";

$query .= "AND invoiceid IN ";

$query .= "(SELECT id FROM tblinvoices WHERE datepaid >= '$startdate' AND datepaid <= '$enddate') ";

$domaintfr = scalarQuery($query);

 

4c) For renewals, like so:

$query = "SELECT SUM(amount) FROM tblinvoiceitems WHERE type='Domain' AND description LIKE 'Domain Renewal%' ";

$query .= "AND invoiceid IN ";

$query .= "(SELECT id FROM tblinvoices WHERE datepaid >= '$startdate' AND datepaid <= '$enddate') ";

$domainrnw = scalarQuery($query);

 

$totalincome += ($domainreg + $domaintfr + $domainrnw);

 

5) Late Fees is also income:

$query = "SELECT SUM(amount) FROM tblinvoiceitems ii WHERE invoiceid IN ";

$query .="(SELECT id FROM tblinvoices WHERE id=ii.invoiceid ";

$query .= "AND (datepaid >= '$startdate' AND datepaid <= '$enddate')) ";

$query .= "AND type='LateFee'";

$latefees = scalarQuery($query);

$totalincome += $latefees;

 

Our custom P&L report, coupled with our own custom Sales by Product report, and a few helper scripts containing useful database parsing functions, report header date selectors, etc., works perfectly and we have been using them for almost two years now with 100% success.

 

If you would like to see this in action, PM me.

[mtk]

Very nice post, and even better that you moved it to a new thread...

[Rezaee]

Thanks for

Would you please attach report file ?

Thanks

[bullfrog3459]

hi,

 

Would you be able to share your file(s) for us to use to get the best our of our WHMCS's?

 

Thanks,

 

Jeremiah

[Chance]

Hello,

 

This post is in response to both the repliers of this post here and those that have PMed us regarding uploading our custom reports so that members could preview them.

 

I have edited these reports extensively to remove all of our additional customizations that do not pertain to the original topic, which was custom reports on income from products. If you are interested, we will also share our customized Sales Tax Liability report, which includes much more detailed information for tax-reporting purposes in the US.

 

There are three files for Product Sales:

1) reportfunctions_2.php, containing a mixture of functions and code used by all reports.

2) profit_and_loss_2.php, containing summary info of income from all products, domains, etc.

3) sales_by_product_2.php, containing the detail lines used to build the profit_and_loss report.

 

I will not explain how the reports work as it should be pretty straightforward once you upload the scripts to your WHMCS install.

 

Again, if you have trouble getting them to work, I would be willing to share a desktop session so that you can see our reports in live action.

 

Anyway, thank you to every one that has shown interest and happy customizing!!!

custom_reports.zip

[ethix]

Thank you, I just Subscribed to this thread about 20 minutes ago lol

[Chance]

Hi ethix,

 

Glad to see you here. Hope this information helps you....

[ethix]

I just uploaded and the reports look real good

 

Thanks you for sharing this it is appreciated.

[webio]

This is a quick fix which needs to be added to functions PHP file which will allow to run this reports on Windows system where money_format function is not working:

 

function money_format($format, $number)

{

return number_format($number, 2, $locale['decimal_point'], $locale['thousands_sep']);

}

 

P.S. Solution from http://www.smert.net/2008/05/23/inconsistencies-between-php-on-windows-show-me-the-money_format/

[Snowman]

ok ive installed these modules into our system and im afraid there are some calculation issues...

 

the profit and loss report is about 800k short of what our revenue is... i will go thru the filses calculations and see if i can pinpoint the issue when i get some time

Edited July 10, 2011 by Snowman

[Snowman]

actually found the problem... none of our VPS, Cloud or dedicated packages are in either report....

[Chance]

Thanks to Snowman for pointing out that missing data.

 

We have corrected the Sales By Product 2 report to include income from virtual and dedicated server accounts, as well as income from reseller accounts. The lines that have been changed begin at 198 and end at 223.

 

We were not able to validate Snowman's mention of the P & L report missing that income though so we did not need to change that report at all to include that data.

 

We tested these new mods by creating new orders for each type of product mentioned and then ran the reports. Both reports do include this data properly.

 

Thanks to all who have taken the time to upload these reports and use them. It is nice to know they are useful to others.

custom_reports.zip

[merlinpa1969]

I notice that it does not take into accout product type Other....

It doesnt pick up and of the SSLs we sell

 

not complaint just information

[openmind]

I must be being a little thick here but...

 

I've uploaded the reports to /modules/reports/ and they appear in my list of reports. Selecting any of the reports just displays this:

 

> # -------------------------------------------------------------------------------------- $dateselector=""; $dateselector.=""; $dateselector.=""; $dateselector.=""; $dateselector.=""; if($year-1 >= $minyear) 	$dateselector.=""; $dateselector.= "<< ".($year-1); if($year-1 >= $minyear)  	$dateselector.="";	 $dateselector.=""; $dateselector.="Jan  "; $dateselector.="Feb  "; $dateselector.="Mar  "; $dateselector.="Apr  "; $dateselector.="May  "; $dateselector.="Jun  "; $dateselector.="Jul  "; $dateselector.="Aug  "; $dateselector.="Sep  "; $dateselector.="Oct  "; $dateselector.="Nov  "; $dateselector.="Dec  "; $dateselector.="[ All ]"; $dateselector.=""; if($year+1 <= $maxyear) 	$dateselector.=""; $dateselector.= ($year+1)." >>"; if($year+1 <= $maxyear)  	$dateselector.="";	 $dateselector.="";  # table for selecting previous and next years in sequence excluding month options $dateselectoryear=""; $dateselectoryear.=""; $dateselectoryear.=""; $dateselectoryear.=""; $dateselectoryear.=""; $dateselectoryear.="<< ".($year-1).""; $dateselectoryear.=""; $dateselectoryear.="";  $dateselectoryear.="".($year+1)." >>"; $dateselectoryear.="";  # ##################################################################### # # safe calculation of days in month # # ##################################################################### function daysinmonth($month, $year) { 	if(checkdate($month, 31, $year)) return 31;     if(checkdate($month, 30, $year)) return 30;     if(checkdate($month, 29, $year)) return 29;     if(checkdate($month, 28, $year)) return 28;     return 0; // error }  # ##################################################################### # detailLine(array(">Test", ">This", ">Out"); function detailLine($columns, $hoverrow = 0) { 	# each report line is a new table row 	# this table row has the option of adding hover state 	$tr = "":	$align = "text-align: right;"; 	$column = substr($column, 1); 	break; 			default:	$align = ""; 													break; 		} 		 		// font weight 		$font = substr($column, 0, 3); 		switch($font) 		{ 			case "(b)":	$font = "font-weight: bold;"; $column = substr($column, 3); break; 			default:	$font = ""; 												break; 		}  		// font color 		$color = substr($column, 0, 3); 		switch($color) 		{ 			case "(c)":	$color = "color: #0000FF !important;"; $column = substr($column, 3); break; 			default:	$color = "";   							                    break; 		} 		 		// column spanning 		$colspan = substr($column, 0, 1); 		if($colspan==".") 		{ 			$colspan = 1; 			$column = substr($column, 1); 			while(substr($column, 0, 1)==".") { $column = substr($column, 1); $colspan++; } 		} 		else 			$colspan = 1;  		// border of cell 		$border = substr($column, 0, 4); 		switch($border) 		{ 			case "(BT)":	$border = "border-top: 1px solid #B6B6B6;"; 	$column = substr($column, 4); 	break; 			case "(BB)":	$border = "border-bottom: 1px solid #B6B6B6;"; 	$column = substr($column, 4); 	break; 			case "(BL)":	$border = "border-left: 1px solid #B6B6B6;"; 	$column = substr($column, 4); 	break; 			case "(BR)":	$border = "border-right: 1px solid #B6B6B6;"; 	$column = substr($column, 4); 	break; 			case "(BA)":	$border = "border: 1px solid #B6B6B6;"; 		$column = substr($column, 4); 	break; 			default:		$border = ""; 																	break; 		} 		 		// padding of cell 		$padding = substr($column, 0, 4); 		switch($padding) 		{ 			case "(PT)":	$padding = "padding-top: 10px;"; 		$column = substr($column, 4); 	break; 			case "(PB)":	$padding = "padding-bottom: 10px;"; 	$column = substr($column, 4); 	break; 			default:		$padding = ""; 															break; 		}  		// add all styles together 		$style = "style=\"$highlight$align$font$color$border$padding\""; 		 		// null columns are still to be shown 		if($column == "") $column = " "; 		 		$tr .= "$column\n"; 	} 	$tr .= "\n"; 	return $tr; } ?>    No Data for the ReportReport Generated on 14/07/2011 10:24

[openmind]

I've applied the Windows fix btw...

[openmind]

OK I fixed the error by adding php to the opening <? in the reportfunctions.php file but all the reports are not producing any data?

[openmind]

Just noticed that if I click any of the report links they go to /portal/admin/reports.php?report=reportfunctions_2

[ethix]

Just noticed that if I click any of the report links they go to /portal/admin/reports.php?report=reportfunctions_2

 

It all appears to be working correctly for me.

[openmind]

Wish I could say the same

[ethix]

lol

 

Any errors in your logs?

Tried different web browsers and or a hard refresh?

[openmind]

I've moved the rportfunctions file to the includes folder and now on both links, the screen just refreshes and goes back to reports.

 

This us server side data so it won't be cached...

[openmind]

Got there in the end by enabling short tags in my php.ini and re-uploading everything

[ethix]

Good to hear you got it working

 

With the cacheing, I once had an ISP that stuck a transparent proxy between the users and net and it would even cache php pages. So I had hard refresh all the time when visiting forums.

[openmind]

Nah I use Virgin here in the UK, they wouldn't be that clever

[jesscoburn]

Got there in the end by enabling short tags in my php.ini and re-uploading everything

 

Thanks for sharing Phil. This helped me as well.