Jump to content
Chance

Profit and Loss Report

Recommended Posts

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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

Hi ethix,

 

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

Share this post


Link to post
Share on other sites

I just uploaded and the reports look real good :)

 

Thanks you for sharing this it is appreciated.

Share this post


Link to post
Share on other sites

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/

Share this post


Link to post
Share on other sites

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 by Snowman

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

lol

 

Any errors in your logs?

Tried different web browsers and or a hard refresh?

Share this post


Link to post
Share on other sites

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...

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Recently Browsing   0 members

    No registered users viewing this page.

×

Important Information

By using this site, you agree to our Terms of Use & Guidelines and understand your posts will initially be pre-moderated