Expenses & Tax Year Reports Addons

[fwebs]

I made this a while ago and while it cold do with some improvement it works ok for now. Some things are coded to suit my own needs but it should act as a good basis for anyone wanting to keep some sort of record of your expenses.

 

It tracks 2 currencies British Pounds (GBP) and US Dollars (USD) as I have clients from both. It doesnt deal with Eroros but it would be simple to add

 

It basically allows you to record and list all your day to day expenses and then give you an idea of how your business is doing. Its not in anyway automated etc

 

First up create your new database table

 

DROP TABLE IF EXISTS `mod_expenses`;
CREATE TABLE `mod_expenses` (
 `exp_id` int(10) NOT NULL auto_increment,
 `exp_date` date default '0000-00-00',
 `exp_supplier` varchar(75) default NULL,
 `exp_desc` varchar(255) default NULL,
 `exp_ref` varchar(25) default NULL,
 `exp_pay_meth` varchar(75) default NULL,
 `exp_resale` char(3) default 'No',
 `exp_total_usd` decimal(10,2) default '0.00',
 `exp_total_gbp` decimal(10,2) default '0.00',
 PRIMARY KEY  (`exp_id`),
 KEY `exp_resale` (`exp_resale`)
) TYPE=MyISAM ;

expenses.php

drop this file into - /modules/admin/expenses

 

This creates a new addon module in your admin area so follow the utilities link in the header and then choose addon modules where youll then find the expenses link

 

tax_year_report.php

Drop this file in - /modules/reports

 

This next file is what I uses to get an idea of how things are looking for the tax year. Again this could do with quite a bit of work but its ok for now. One problem I had was getting it to work from april to april (Uk tax year) so hacked it to fit

WHMCS - Expenses & Tax Year Reports Addons.zip

[NRT/Redline]

I did exactly as said...the tax year report woks,,,,expenses...just opens to a white page

did i miss something here?

 

Dave

[othellotech]

The .zip doesnt appear to be valid, so cant offer nay advice on wats wrong with the code

[JasonO]

The .zip doesnt appear to be valid, so cant offer nay advice on wats wrong with the code

 

Yep, Invalid or corrupt here too.

[mrprez]

The zip file is valid, but the module gives a blank page.

[amnesia]

can we get a fix? this would be nice to have!

[fwebs]

heres a new copy of the files.

 

let me know if you have any problems

Expenses & Tax Year Reports Addons.zip

[mrprez]

Much better! Thanks...

 

John

[mrprez]

I made a change to the query that pulls the unpaid invoices. The original query doesn't look at the due date of the invoice and incorrectly shows next months invoices as being due in the current month. Here's my change:

 

$query	= "SELECT SUM(total) AS Paid, (SELECT SUM(total) FROM tblinvoices WHERE  DATE_FORMAT(`date`, '%Y-%m') = '$this_year-$pmonth' [color=red]AND duedate LIKE '%$this_year-$pmonth%'[/color] AND status = 'Unpaid') AS NotPaid  FROM tblinvoices WHERE DATE_FORMAT(`date`, '%Y-%m') = '$this_year-$pmonth' AND status = 'Paid'" ;

[fwebs]

There are no doubt many ways to improve on this as I had to knock it together quickly to replace my old system. I use it as a guide / snapshot. When it comes time to send my accounts in again Ill have to work on it again.

 

I know it would be also be useful to be able to add suppliers and then make reports on how much youve spent with each supplier / type of purchase etc.

 

Youll also notice that the USD to GBP conversion is hardcoded as £1 = $2 so again just a rough guide

 

 

Glad to see others find it useful so far though

[mrprez]

Yep, I have lots of plans for this! Thanks for the initial work. I have removed all the GBP as I don't use that. Will work on adding vendors and maybe recurring payments so I don't have to enter them each month.

 

John

[fwebs]

great, well keep me posted and ill do what i can too.

 

One thing I wanted to use was the calendar that is used throughout whmcs. I did not want to have to include an extra one but like most other things the code is locked.

 

It would be great to have access to stuff like this.

 

Im sure functions for calendar, column sorting etc already exists

[fwebs]

$query    = "SELECT SUM(total) AS Paid, (SELECT SUM(total) FROM tblinvoices WHERE  DATE_FORMAT(`duedate`, '%Y-%m') = '$this_year-$pmonth'  AND status = 'Unpaid') AS NotPaid  FROM tblinvoices WHERE DATE_FORMAT(`duedate`, '%Y-%m') = '$this_year-$pmonth' AND status = 'Paid'" ;

 

the query should be like this then i suppose to work it from due date rather then invoiced date.

[mrprez]

Yes, that works even better as it shows the invoices that are outstanding for Feb on that line.

 

John

[magga]

This is great, thanks !

 

I'm going to attempt to make a few changes to improve it (like adding a calendar to select the date of an expense).

 

I will let you know how I get on.

[NRT/Redline]

Would some-one be able to edit this to remove the GB pound out of it and change it to

show Cnd dollars instead along with US dollars please, I have tried...and a coder I am not.

 

Great program addon thank-you for it

 

Dave

 

NRT/Redline Servers

[fwebs]

you should just be able to change the text where it says gbp to cnd in the code with find and replace and then rename the database field.

 

an even simpler way would be to just change the titles so that the database part of it is not effected.

 

The only other bit to change would be the currency conversion in the report. at the moment gbp is set to be 1 gbp = 2 usd

[NRT/Redline]

Thank-you...

 

I will give it a try...

 

Dave

[magga]

I made a little change which I'm pretty sure works (won't be able to tell 100% until April though !) which basically displays the current tax year as you open the page e.g. if you visited the page during March 2008 would show April 2007 - March 2008 and if you visited the page in April 2008 it will show April 2008 - March 2009 and so on. To do this just change the code at the top of modules/reports/00_tax_year_report.php

 

replace

 

if ($year=="") {
$year=date("Y");
$nextyear=$year+1;
}

 

with

 

if ($month=="") {
$month=date("m");
}
if ($year=="") {
if ($month <='3')
{
$year=(date("Y")-1);
$nextyear=$year+1;
}
else
{
$year=date("Y");
$nextyear=$year+1;
}
}

 

There's also another addition to print the month's/year's out in the standard WHMCS format (February 2008 for example):

 

add at the top:

 

$months = array('January','February','March','April','May','June','July','August','September','October','November','December');

 

and then replace:

 

$this_month 		= $this_year." - ".$pmonth ;

 

with

 

$this_month = $months[$counter-1]." ".$this_year;

 

And nearly forgot to mention, there's a little bug that stops it showing the year properly when you go back a year.

 

You can fix it by changing this:

 

$reportdata["title"] = "Yearly Income Report for tax year - April ".$year." - ".$nextyear."";

 

to this:

 

$reportdata["title"] = "Yearly Income Report for Tax Year - April ".$year." - March".($year + 1)."";

 

I added the "March" bit in but you can take it out if you like.

 

Hope this helps some of you and once again - thanks to the OP for this great report script !

[NRT/Redline]

Nice addition...Thank-you

 

Dave

[railto]

would anyone be willing to modify this slightly to account for Euro, GBP and USD? i would like to use this feature but deal with 3 currencies

[fwebs]

have a look at the code and the database dump and duplicate the gbp part for euros.

[Methinks]

Could someone tell me what piece of code I would have to put in to pull the monthly total from say my server cost to add it to this as a recurring bill?

I have also set up whmcs as a server so I could add the monthly cost of this program to my reports.

So if I could figure out how to do it in this script to add the cost each month of the servers I would not necessarily need a recurring feature. Each month it would pull my servers cost in and expense them out.

But I think I just learned how to turn the computer on last week or it feels that way when I am looking at codes haha.

[Zorro67]

very nice add-on.

 

thanks for the code, works very well.

 

It was simple to change to AUD & our tax year.

[magga]

One thing I've noticed is that in the "Total Invoiced" and "Total Paid" section, it get's this for each month from the due date rather than the invoiced date, for example:

 

Invoice Date: 20/1/08

Due Date: 20/2/08

Paid Date: 25/1/08

 

This would show up in the February 2008 month (I think because of the due date), I would think it should show up in January as this was when the invoice was raised?

 

If you compare this report to the standard WHMCS yearly report, you will see the figures are different.