List invoices by month

[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!