List invoices by month

[rjcuser]

How can I print out a list of invoices raised on a month by month basis, and if possible if they are paid and when?

[dutchnet]

Hi,

I have created an report which does just that. It does not show the date paid but it does show the status of the invoice.
Current fields on the report:
Invoice ID Inoice No., Invoice date, Due Date, Client Name, Invoice Amount, Status, Description and Invoice Amount

The report lists all invoices generated in the current month with option to go back and forth between the lists. The Invoice Id links to the correct invoice so you are able to open the invoice directly from the report.
PM me if you want the report source code

[othellotech]

PM me if you want the report source code
Why not paste it into the user-contributions section of the forum, then everyone could benefit or use it as a starting point ... :D

[dutchnet]

No problem :)

I will post it tomorrow when I have access to the source

[Peak-Host]

Thanks, this has helped me sort of too.

[dutchnet]

Below is the source code as promissed, have a look and it and do'nt flame :)

<?php


$months = array('Januari','Februari','March','April','May',' June','July','August','September','October','Novem ber','December');

if ($month=="") {
$month=date("m");
$year=date("Y");
}

$pmonth = str_pad($month, 2, "0", STR_PAD_LEFT);

$reportdata["title"] = "Invoice details for ".$months[$month-1]." ".$year;
$reportdata["description"] = "Invoice per customer per month";

// $query = "SELECT tblinvoices.*,tblclients.firstname,tblclients.last name FROM tblinvoices INNER JOIN tblclients ON tblclients.id=tblinvoices.userid WHERE date like '$year-$pmonth%' AND tblinvoices.status = 'Unpaid' ORDER BY date, userid ASC";
$query = "SELECT tblinvoices.*,tblinvoiceitems.description,tblinvoi ceitems.amount,tblclients.firstname,tblclients.las tname FROM tblinvoices INNER JOIN tblinvoiceitems ON tblinvoices.id = tblinvoiceitems.invoiceid INNER JOIN tblclients ON tblclients.id=tblinvoices.userid WHERE date like '$year-$pmonth%' ORDER BY tblinvoices.id ASC";
$result = mysql_query($query);
$num_rows = mysql_num_rows($result);

$reportdata["headertext"] = "Total number of Invoices: $num_rows";

// Added Prodyct to headers -yabdabo
$reportdata["tableheadings"] = array("Invoice ID","Invoice #","Invoice Date","Invoice Due Date","Client","Amount","Status","Description","Amount");

while ($data = mysql_fetch_array($result)) {
$id = "<a href=\"invoices.php?action=edit&id=".$data["id"]."\">".$data["id"] ."</a>";
$invoicenum = $data["invoicenum"];
$date = $data["date"];
$duedate = $data["duedate"];
$amount = $CONFIG["CurrencySymbol"].$data["total"];
$paymentmethod = $data["value"];
$status = $data["status"];
$date = fromMySQLDate($date);
$duedate = fromMySQLDate($duedate);
$clientname = $data["firstname"]." ".$data["lastname"];
$description = $data["description"];
$itemamount = $data["amount"];

$grandtotal += $data['total'];

// Added $product to results -yabdao
$reportdata["tablevalues"][] = array("$id","$invoicenum","$date","$duedate","$clientname","$amount","$status","$description","$itemamount");
}

$data['footertext'] = "<p style=\"text-align:center\"><span style=\"font-size:2em; margin:10px auto; font-weight:bold;\">Totaal: ".$CONFIG["CurrencySymbol"]."$grandtotal</span><br />";

$data["footertext"].="</td><td align=right>";
if ($month=="12") {
$data["footertext"].="<a href=\"$PHP_SELF?report=$report&month=1&year=".($year+1)."\">January ".($year+1)." >></a>";
} else {
$data["footertext"].="<a href=\"$PHP_SELF?report=$report&month=".($month+1)."&year=".$year."\">".$months[(($month+1)-1)]." $year >></a>";
}
$data["footertext"].="</td></tr></table>";

?>

[Iceman]

hmmm... not working here...running 3.5.1. Can't see anything obviously wrong with the code either.

Cheers,
Paul

[Nick]

There's a couple of errant spaces in the query, probably replicated throughout the code if it's been caused by copying and pasting.

[isdoo]

Thanks dutchnet :)

[Iceman]

Yes I found them ALL eventually. :)

FYI. Try pasting code in the CODE tags, it should avoid any whitespace errors. i.e

php goes here

Cheers,
Paul

[Iceman]

Thanks Dutchnet, it works really well....once the spaces are removed. :)
Thanks heaps for sharing the code.

Cheers,
Paul


For those having problems with the original pasted data...try this...



<?php
$months = array('January','February','March','April','May',' June','July','August','September','October','Novem ber','December');

if ($month=="") {
$month=date("m");
$year=date("Y");
}

$pmonth = str_pad($month, 2, "0", STR_PAD_LEFT);

$reportdata["title"] = "Invoice details for ".$months[$month-1]." ".$year;
$reportdata["description"] = "Invoice per customer per month";

//$query = "SELECT tblinvoices.*,tblclients.firstname,tblclients.last name FROM tblinvoices INNER JOIN tblclients ON tblclients.id=tblinvoices.userid WHERE date like '$year-$pmonth%' AND tblinvoices.status = 'Unpaid' ORDER BY date, userid ASC";
$query = "SELECT tblinvoices.*,tblinvoiceitems.description,tblinvoi ceitems.amount,tblclients.firstname,tblclients.las tname FROM tblinvoices INNER JOIN tblinvoiceitems ON tblinvoices.id = tblinvoiceitems.invoiceid INNER JOIN tblclients ON tblclients.id=tblinvoices.userid WHERE date like '$year-$pmonth%' ORDER BY tblinvoices.id ASC";
$result = mysql_query($query);
$num_rows = mysql_num_rows($result);

$reportdata["headertext"] = "Total number of Invoices: $num_rows";

// Added Prodyct to headers -yabdabo
$reportdata["tableheadings"] = array("Invoice ID","Invoice #","Invoice Date","Invoice Due Date","Client","Amount","Status","Description","Amount");

while ($data = mysql_fetch_array($result)) {
$id = "<a href=\"invoices.php?action=edit&id=".$data["id"]."\">".$data["id"] ."</a>";
$invoicenum = $data["invoicenum"];
$date = $data["date"];
$duedate = $data["duedate"];
$amount = $CONFIG["CurrencySymbol"].$data["total"];
$paymentmethod = $data["value"];
$status = $data["status"];
$date = fromMySQLDate($date);
$duedate = fromMySQLDate($duedate);
$clientname = $data["firstname"]." ".$data["lastname"];
$description = $data["description"];
$itemamount = $data["amount"];

$grandtotal += $data['total'];

// Added $product to results -yabdao
$reportdata["tablevalues"][] = array("$id","$invoicenum","$date","$duedate","$clientname","$amount","$status","$description","$itemamount");
}

$data['footertext'] = "<p style=\"text-align:center\"><span style=\"font-size:2em; margin:10px auto; font-weight:bold;\">Total: ".$CONFIG["CurrencySymbol"]."$grandtotal</span><br />";

$data["footertext"].="</td><td align=right>";
if ($month=="12") {
$data["footertext"].="<a href=\"$PHP_SELF?report=$report&month=1&year=".($year+1)."\">January ".($year+1)." >></a>";
} else {
$data["footertext"].="<a href=\"$PHP_SELF?report=$report&month=".($month+1) ."&year=".$year."\">".$months[(($month+1)-1)]." $year >></a>";
}
$data["footertext"].="</td></tr></table>";

?>

[Nick]

I made some improvements to the original code:

<?php
if ($month=="") {
$month=date("m");
$year=date("Y");
}

$pmonth = str_pad($month, 2, "0", STR_PAD_LEFT);

$reportdata["title"] = "Invoice details for ".date("F", mktime(1,1,1,$month))." ".$year;
$reportdata["description"] = "Invoice per customer per month";

$query = "SELECT `tblinvoices`.*, CONCAT(`tblclients`.`firstname`, ' ', `tblclients`.`lastname`) AS 'fullname' FROM `tblinvoices`, `tblclients` WHERE `tblinvoices`.`date` LIKE '" . $year . "-" . $pmonth . "%' AND `tblclients`.`id` = `tblinvoices`.`userid` ORDER BY `tblinvoices`.`id` ASC";
$result = mysql_query($query);
$num_rows = mysql_num_rows($result);

$reportdata["headertext"] = "Total number of Invoices: " . $num_rows;

$reportdata["tableheadings"] = array("Invoice ID","Invoice #","Invoice Date","Invoice Due Date","Client","Status","Amount");

while ($data = mysql_fetch_array($result)) {
$id = "<a href=\"invoices.php?action=edit&id=".$data["id"]."\">".$data["id"] ."</a>";
$client = "<a href='clientssummary.php?userid=" . $data['userid'] . "'>" . $data['fullname'] . "</a>";
$amount = $CONFIG["CurrencySymbol"].$data["total"];
$date = fromMySQLDate($data['date']);
$duedate = fromMySQLDate($data['duedate']);
$status = $data['status'];
$colour = "<span style='color:";
switch($status)
{
case "Paid":
$colour .= "green";
$grandtotal += $data['total'];
break;
case "Unpaid":
$colour .= "darkred";
$grandtotal += $data['total'];
break;
case "Cancelled":
$colour .= "lightgrey";
break;
}
$colour .= ";'>";

$reportdata["tablevalues"][] = array($id,$data["invoicenum"],$date,$duedate,$client,$colour . $status . "</span>",$colour . $amount . "</span>");
}

$data['footertext'] = "<p style=\"text-align:center\"><span style=\"font-size:2em; margin:10px auto; font-weight:bold;\">Total: ".$CONFIG["CurrencySymbol"] . $grandtotal . "</span><br />";
$next = mktime(1,1,1,$month + 1,1,$year);
$prev = mktime(1,1,1,$month - 1,1,$year);
$data["footertext"].="</td></tr><tr><td align=center>";
$data["footertext"].="<a href='?report=".$report."&month=".date("n",$prev)."&year=".date("Y",$prev)."'>&lt;&lt; ".date("F",$prev)." " . date("Y",$prev) . "</a>";
$data["footertext"].=" | <a href=\"?report=".$report."&month=".date("n",$next)."&year=".date("Y",$next)."\">".date("F",$next)." " . date("Y",$next) . " &gt;&gt;</a>";
$data["footertext"].="</td></tr></table>";

?>

Now shows colour by status, alignment is sorted out and I removed the 'description' column, which was causing problems with duplicate items.

'Cancelled' items are also not added to the total at the bottom.

[minadreapta]

is this an add-on module?

any instructions like where to upload this file?

i tried to upload this into the modules/admin/ directory but it doesn't seem to be working.

thanks.

[dutchnet]

it's should go into the modules/reports directory. It's a custom report

[2bsafe]

This is great. Thanks for the hard work. As a thought, could this be modified to display invoices for an individual client and in effect be used to produce a statement? Wouldn't know how to do that myself but know it would benefit lots of us if someone could do it.

Thanks.

[Amcom]

I made no improvements to the original code: I take no credit for this code. I just added the (paid date) next to the amount, and change around line 49 from this
$data['footertext'] = "<p style=\"text-align:center\"><span style=\"font-size:2em; margin:10px auto; font-weight:bold;\">Total: ".$CONFIG["CurrencySymbol"] . $grandtotal . "</span><br />";
To this
$data['footertext'] = "<p style=\"text-align:center\"><span style=\"font-size:2em; margin:10px auto; font-weight:bold;\">Total: ".$CONFIG["CurrencySymbol"] . $grandtotal . "</span><table width=90% align=center><tr><td>";



<?php

if ($month=="") {
$month=date("m");
$year=date("Y");
}

$pmonth = str_pad($month, 2, "0", STR_PAD_LEFT);

$reportdata["title"] = "Invoice details for ".date("F", mktime(1,1,1,$month))." ".$year;
$reportdata["description"] = "Invoice per customer per month";

$query = "SELECT `tblinvoices`.*, CONCAT(`tblclients`.`firstname`, ' ', `tblclients`.`lastname`) AS 'fullname' FROM `tblinvoices`, `tblclients` WHERE `tblinvoices`.`date` LIKE '" . $year . "-" . $pmonth . "%' AND `tblclients`.`id` = `tblinvoices`.`userid` ORDER BY `tblinvoices`.`id` ASC";
$result = mysql_query($query);
$num_rows = mysql_num_rows($result);

$reportdata["headertext"] = "Total number of Invoices: " . $num_rows;

$reportdata["tableheadings"] = array("Invoice ID","Invoice #","Invoice Date","Invoice Due Date","Client","Status","Amount","Date Paid");

while ($data = mysql_fetch_array($result)) {
$id = "<a href=\"invoices.php?action=edit&id=". $data["id"] ."\" target=\"_blank\"\">". $data["id"] ."</a>";
$client = "<a href=\"clientssummary.php?userid=". $data['userid'] ."\" target=\"_blank\"\">" . $data['fullname'] . "</a>";
$amount = $CONFIG["CurrencySymbol"].$data["total"];
$date = fromMySQLDate($data['date']);
$duedate = fromMySQLDate($data['duedate']);
$datepaid = fromMySQLDate($data['datepaid']);
$status = $data['status'];
$colour = "<span style='color:";
switch($status)
{
case "Paid":
$colour .= "green";
$grandtotal += $data['total'];
break;
case "Unpaid":
$colour .= "darkred";
$grandtotal += $data['total'];
break;
case "Cancelled":
$colour .= "lightgrey";
break;
}
$colour .= ";'>";

$reportdata["tablevalues"][] = array($id,$data["invoicenum"],$date,$duedate,$client,$colour . $status . "</span>",$colour . $amount . "</span>",$colour . $datepaid . "</span>");
}

$data['footertext'] = "<p style=\"text-align:center\"><span style=\"font-size:2em; margin:10px auto; font-weight:bold;\">Total: ".$CONFIG["CurrencySymbol"] . $grandtotal . "</span><table width=90% align=center><tr><td>";
$next = mktime(1,1,1,$month + 1,1,$year);
$prev = mktime(1,1,1,$month - 1,1,$year);
$data["footertext"].="</td></tr><tr><td align=center>";
$data["footertext"].="<a href='?report=".$report."&month=".date("n",$prev)."&year=".date("Y",$prev)."'>&lt;&lt; ".date("F",$prev)." " . date("Y",$prev) . "</a>";
$data["footertext"].=" | <a href=\"?report=".$report."&month=".date("n",$next)."&year=".date("Y",$next)."\">".date("F",$next)." " . date("Y",$next) . " &gt;&gt;</a>";
$data["footertext"].="</td></tr></table>";

?>

[handsonwebhosting]

Nice addition folks!

[netmotiv8]

Hi,

This is great report :-)

We could do with being able to output this report as a .csv file and include the Client ID as well.


Anyone do this?

[icecoolcontracts]

GREAT Stuff, works a treat!

[yosoychema69]

Thank you very much.

[ffeingol]

Not to be too picky (because this is great work) but shouldn't the query be:



`duedate` LIKE '" . $year . "-" . $pmonth . "%'


not



`date` LIKE '" . $year . "-" . $pmonth . "%'


If you generate invoices before they are due (we generate them 7 days before they are due) you're going to have invoices in the wrong month.

[Sandor]

Hi everyone,

I changed/added to this a bit so that it shows invoices per a specified period.. Start and end dates can be selected via controls at the top..

I also modified it to display the dates in MM/DD/YYYY format.

I would like to put an 'export as csv' button at the bottom.. Where can I find the code that powers the existing csv files.. It should be easy to repurpose it.. ?

Thanks!
-Sandor



<?php

if ($startday=="") {
$startday=date("d");
$startmonth=date("m");
$startyear=date("Y");
$endday=date("d");
$endmonth=date("m");
$endyear=date("Y");
}

$pmonth = str_pad($month, 2, "0", STR_PAD_LEFT);

$reportdata["title"] = "Invoice details for $startmonth/$startday/$startyear - $endmonth/$endday/$endyear";
$reportdata["description"] = "Invoices Per Period";

$reportdata["headertext"] = "<form method=\"post\" action=\"$PHP_SELF?report=$report&calculate=true\"><center>Start Date: ";

$reportdata["headertext"] .= "</select> <select name=\"startmonth\">";
for ( $counter = 1; $counter <= 12; $counter += 1) {
$reportdata["headertext"] .= "<option";
if ($counter==$startmonth) { $reportdata["headertext"] .= " selected"; }
$reportdata["headertext"] .= ">$counter";
}
$reportdata["headertext"] .= "<select name=\"startday\">";
for ( $counter = 1; $counter <= 31; $counter += 1) {
$reportdata["headertext"] .= "<option";
if ($counter==$startday) { $reportdata["headertext"] .= " selected"; }
$reportdata["headertext"] .= ">$counter";
}
$reportdata["headertext"] .= "</select> <select name=\"startyear\">";
for ( $counter = 2006; $counter <= 2010; $counter += 1) {
$reportdata["headertext"] .= "<option";
if ($counter==$startyear) { $reportdata["headertext"] .= " selected"; }
$reportdata["headertext"] .= ">$counter";
}

$reportdata["headertext"] .= "</select> End Date: ";

$reportdata["headertext"] .= "</select> <select name=\"endmonth\">";
for ( $counter = 1; $counter <= 12; $counter += 1) {
$reportdata["headertext"] .= "<option";
if ($counter==$endmonth) { $reportdata["headertext"] .= " selected"; }
$reportdata["headertext"] .= ">$counter";
}
$reportdata["headertext"] .= "<select name=\"endday\">";
for ( $counter = 1; $counter <= 31; $counter += 1) {
$reportdata["headertext"] .= "<option";
if ($counter==$endday) { $reportdata["headertext"] .= " selected"; }
$reportdata["headertext"] .= ">$counter";
}
$reportdata["headertext"] .= "</select> <select name=\"endyear\">";
for ( $counter = 2006; $counter <= 2010; $counter += 1) {
$reportdata["headertext"] .= "<option";
if ($counter==$endyear) { $reportdata["headertext"] .= " selected"; }
$reportdata["headertext"] .= ">$counter";
}
$reportdata["headertext"] .= "</select> <input type=\"submit\" value=\"Generate Report\"></form>";

$startday = str_pad($startday,2,"0",STR_PAD_LEFT);
$startmonth = str_pad($startmonth,2,"0",STR_PAD_LEFT);
$endday = str_pad($endday,2,"0",STR_PAD_LEFT);
$endmonth = str_pad($endmonth,2,"0",STR_PAD_LEFT);

$startdate = $startyear.$startmonth.$startday;
$enddate = $endyear.$endmonth.$endday;

$enddate = $enddate."235959";

$query = "SELECT `tblinvoices`.*, CONCAT(`tblclients`.`firstname`, ' ', `tblclients`.`lastname`) AS 'fullname' FROM `tblinvoices`, `tblclients` WHERE `tblinvoices`.`date` >='$startdate' AND `tblinvoices`.`date` <='$enddate' AND `tblclients`.`id` = `tblinvoices`.`userid` ORDER BY `tblinvoices`.`id` ASC";
$result = mysql_query($query);
$num_rows = mysql_num_rows($result);



while ($data = mysql_fetch_array($result)) {
$id = "<a href=\"invoices.php?action=edit&id=". $data["id"] ."\" target=\"_blank\"\">". $data["id"] ."</a>";
$client = "<a href=\"clientssummary.php?userid=". $data['userid'] ."\" target=\"_blank\"\">" . $data['fullname'] . "</a>";
$amount = $CONFIG["CurrencySymbol"].$data["total"];
$date = fromMySQLDate($data['date']);
$duedate = fromMySQLDate($data['duedate']);
$datepaid = fromMySQLDate($data['datepaid']);
$status = $data['status'];
$colour = "<span style='color:";
switch($status)
{
case "Paid":
$colour .= "green";
$grandtotal += $data['total'];
break;
case "Unpaid":
$colour .= "darkred";
$grandtotal += $data['total'];
break;
case "Cancelled":
$colour .= "lightgrey";
break;
}
$colour .= ";'>";

$reportdata["tableheadings"] = array("Invoice ID","Invoice #","Invoice Date","Invoice Due Date","Client","Status","Amount","Date Paid");

$reportdata["tablevalues"][] = array($id,$data["invoicenum"],$date,$duedate,$client,$colour . $status . "</span>",$colour . $amount . "</span>",$colour . $datepaid . "</span>");
}
$reportdata["headertext"] .= "<p>Total number of Invoices: $num_rows</p>";

$reportdata["headertext"] .= "<p>Total: ".$CONFIG["CurrencySymbol"]." $grandtotal</p>";
$data['footertext'] = "<p style=\"text-align:center\"><span style=\"font-size:2em; margin:10px auto; font-weight:bold;\">Total: ".$CONFIG["CurrencySymbol"] . $grandtotal . "</span><table width=90% align=center><tr><td>";
$data["footertext"].="</td></tr></table>";

?>

[webworld]

Hi could someone add Product/Services to this report, I tried myself but I'm not clever enough.

Cheers

[ckung]

Great report! Thank you very much for all the hard work. I have been waiting for this report for a while. Can someone help me to include the "Payment Methods" option?
I use pdf batch for producing paper invoices monthly. By having the "Payment Methods" option will help me greatly for reviewing the total for each method since pdf batch can't provide the total.

Thank you!
CK

[Tim H]

Great work everyone. I need this and appreciate all your efforts to get it out. If someone could make it into a CSV export for all of us, that would really help me a lot.

Thanks. Tim

[BobC]

Why is there a blank Invoice Number column to the right of the Invoice ID?

Any quick way to eliminate that?

Bob Connors

[fepixie]

very nice - just what i was after - thanks all :)

I've just made a couple of small tweaks...
- the date back in d/m/y format
- the date selectors wont conk out after 2010 now (was hard coded as max 2010)


<?php

if ($startday=="") {
$startday=date("d");
$startmonth=date("m");
$startyear=date("Y");
$endday=date("d");
$endmonth=date("m");
$endyear=date("Y");
}

$pmonth = str_pad($month, 2, "0", STR_PAD_LEFT);

$reportdata["title"] = "Invoice details for $startday/$startmonth/$startyear - $endday/$endmonth/$endyear";
$reportdata["description"] = "Invoices Per Period";

$reportdata["headertext"] = "<form method=\"post\" action=\"$PHP_SELF?report=$report&calculate=true\"><center>Start Date: ";

$reportdata["headertext"] .= "<select name=\"startday\">";
for ( $counter = 1; $counter <= 31; $counter += 1) {
$reportdata["headertext"] .= "<option";
if ($counter==$startday) { $reportdata["headertext"] .= " selected"; }
$reportdata["headertext"] .= ">$counter";
}
$reportdata["headertext"] .= "</select> <select name=\"startmonth\">";
for ( $counter = 1; $counter <= 12; $counter += 1) {
$reportdata["headertext"] .= "<option";
if ($counter==$startmonth) { $reportdata["headertext"] .= " selected"; }
$reportdata["headertext"] .= ">$counter";
}
$currentyear=date("Y");
$reportdata["headertext"] .= "</select> <select name=\"startyear\">";
for ( $counter = 2006; $counter <= $currentyear+1; $counter++) {
$reportdata["headertext"] .= "<option";
if ($counter==$startyear) { $reportdata["headertext"] .= " selected"; }
$reportdata["headertext"] .= ">$counter";
}

$reportdata["headertext"] .= "</select> End Date: ";

$reportdata["headertext"] .= "<select name=\"endday\">";
for ( $counter = 1; $counter <= 31; $counter += 1) {
$reportdata["headertext"] .= "<option";
if ($counter==$endday) { $reportdata["headertext"] .= " selected"; }
$reportdata["headertext"] .= ">$counter";
}
$reportdata["headertext"] .= "</select> <select name=\"endmonth\">";
for ( $counter = 1; $counter <= 12; $counter += 1) {
$reportdata["headertext"] .= "<option";
if ($counter==$endmonth) { $reportdata["headertext"] .= " selected"; }
$reportdata["headertext"] .= ">$counter";
}

$reportdata["headertext"] .= "</select> <select name=\"endyear\">";
for ( $counter = 2006; $counter <= $currentyear+1; $counter++) {
$reportdata["headertext"] .= "<option";
if ($counter==$endyear) { $reportdata["headertext"] .= " selected"; }
$reportdata["headertext"] .= ">$counter";
}
$reportdata["headertext"] .= "</select> <input type=\"submit\" value=\"Generate Report\"></form>";

$startday = str_pad($startday,2,"0",STR_PAD_LEFT);
$startmonth = str_pad($startmonth,2,"0",STR_PAD_LEFT);
$endday = str_pad($endday,2,"0",STR_PAD_LEFT);
$endmonth = str_pad($endmonth,2,"0",STR_PAD_LEFT);

$startdate = $startyear.$startmonth.$startday;
$enddate = $endyear.$endmonth.$endday;

$enddate = $enddate."235959";

$query = "SELECT `tblinvoices`.*, CONCAT(`tblclients`.`firstname`, ' ', `tblclients`.`lastname`) AS 'fullname' FROM `tblinvoices`, `tblclients` WHERE `tblinvoices`.`date` >='$startdate' AND `tblinvoices`.`date` <='$enddate' AND `tblclients`.`id` = `tblinvoices`.`userid` ORDER BY `tblinvoices`.`id` ASC";
$result = mysql_query($query);
$num_rows = mysql_num_rows($result);



while ($data = mysql_fetch_array($result)) {
$id = "<a href=\"invoices.php?action=edit&id=". $data["id"] ."\" target=\"_blank\"\">". $data["id"] ."</a>";
$client = "<a href=\"clientssummary.php?userid=". $data['userid'] ."\" target=\"_blank\"\">" . $data['fullname'] . "</a>";
$amount = $CONFIG["CurrencySymbol"].$data["total"];
$date = fromMySQLDate($data['date']);
$duedate = fromMySQLDate($data['duedate']);
$datepaid = fromMySQLDate($data['datepaid']);
$status = $data['status'];
$colour = "<span style='color:";
switch($status)
{
case "Paid":
$colour .= "green";
$grandtotal += $data['total'];
break;
case "Unpaid":
$colour .= "darkred";
$grandtotal += $data['total'];
break;
case "Cancelled":
$colour .= "lightgrey";
break;
}
$colour .= ";'>";

$reportdata["tableheadings"] = array("Invoice ID","Invoice Date","Invoice Due Date","Client","Status","Amount","Date Paid");

$reportdata["tablevalues"][] = array($id,$date,$duedate,$client,$colour . $status . "</span>",$colour . $amount . "</span>",$colour . $datepaid . "</span>");
}
$reportdata["headertext"] .= "<p>Total number of Invoices: $num_rows</p>";

$reportdata["headertext"] .= "<p>Total: ".$CONFIG["CurrencySymbol"]." $grandtotal</p>";
$data['footertext'] = "<p style=\"text-align:center\"><span style=\"font-size:2em; margin:10px auto; font-weight:bold;\">Total: ".$CONFIG["CurrencySymbol"] . $grandtotal . "</span><table width=90% align=center><tr><td>";
$data["footertext"].="</td></tr></table>";

?>

[Nitrodesign]

Congratulations!
The report is perfect!