List invoices by month

rjcuser · April 14, 2008

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 · April 17, 2008

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 · April 17, 2008

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

dutchnet · April 17, 2008

No problem

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

Peak-Host · April 18, 2008

Thanks, this has helped me sort of too.

dutchnet · April 20, 2008

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','November','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.lastname 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,tblinvoiceitems.amount,tblclients.firstname,tblclients.lastname 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'] = "Totaal: ".$CONFIG["CurrencySymbol"]."$grandtotal ";

$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 · April 20, 2008

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

Cheers,

Paul

Nick · April 20, 2008

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 · April 20, 2008

Thanks dutchnet

Iceman · April 20, 2008

Yes I found them ALL eventually.

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

[code]php goes here[/code]

Cheers,

Paul

Iceman · April 20, 2008

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','November','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,tblinvoiceitems.amount,tblclients.firstname,tblclients.lastname 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 · April 20, 2008

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)."'><< ".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) . " >></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 · April 20, 2008

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 · April 20, 2008

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

2bsafe · September 5, 2008

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 · September 6, 2008

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'] = "Total: ".$CONFIG["CurrencySymbol"] . $grandtotal . " ";

To this

$data['footertext'] = "Total: ".$CONFIG["CurrencySymbol"] . $grandtotal . "<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)."'><< ".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) . " >></a>";
$data["footertext"].="</td></tr></table>";

?>

handsonwebhosting · September 6, 2008

Nice addition folks!

netmotiv8 · January 30, 2009

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 · March 2, 2009

GREAT Stuff, works a treat!

yosoychema69 · July 28, 2009

Thank you very much.

ffeingol · July 29, 2009

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 · August 30, 2009

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>";

?>

Edited August 30, 2009 by Sandor

webworld · September 23, 2010

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

Cheers

ckung · September 26, 2010

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 · October 1, 2010

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

List invoices by month

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Recently Browsing 0 members

Important Information