Custom Report Issue

[Sonu2007]

I am trying to create custom report which show Paid invoices with clients details in selected month. I am not sure what wrong with my code it is not generating any report.

 

Any one point me in right direction?

 

<?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`.`invoicenum` AS 'Invoice Number', `tblinvoices`.`date` AS 'Date', CONCAT(`tblclients`.`firstname`, ' ', `tblclients`.`lastname`) AS 'fullname', CONCAT (`tblclients`.`address1`,', ',`tblclients.city`,', ',`tblclients.state`,' - ',`tblclients.postcode`,', ',`tblclients.country`) AS 'Address' 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>";
echo $address = $data['address'];
$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["tableheadings"] = array("Invoice ID","Invoice Date","Invoice Due Date","Client","Status","Amount","Date Paid");

$reportdata["tablevalues"][] = array($id,$date,$duedate,$client,$address,$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>";

?>

[brian!]

I haven't looked through the whole code to fix it, but often if you're going to write a WHMCS report, it's easier to work with an existing report and tweak it - e.g the standard "Invoices" report does what you want, apart perhaps from the additional address details and your colour coding of the status value (the latter of which could easily be added to the output).

 

actually, for most situations, it's easier to just write the SQL query and use it in phpMyAdmin or similar.

 

i've added the address field as an option to invoices.php and the code is below (rename it to invoices2.php to ensure it isn't overwritten by WHMCS during an update)...

 

<?php

if (!defined("WHMCS"))
   die("This file cannot be accessed directly");

$reportdata["title"] = "Invoices";

$filterfields = array("id"=>"ID","userid"=>"User ID","clientname"=>"Client Name","address"=>"Address","invoicenum"=>"Invoice Number","date"=>"Creation Date","duedate"=>"Due Date","datepaid"=>"Date Paid","subtotal"=>"Subtotal","credit"=>"Credit","tax"=>"Tax","tax2"=>"Tax2","total"=>"Total","taxrate"=>"Tax Rate","taxrate2"=>"Tax Rate 2","status"=>"Status","paymentmethod"=>"Payment Method","notes"=>"Notes");

$reportdata["description"] = $reportdata["headertext"] = '';

$incfields = $whmcs->get_req_var('incfields');
$filterfield = $whmcs->get_req_var('filterfield');
$filtertype = $whmcs->get_req_var('filtertype');
$filterq = $whmcs->get_req_var('filterq');
if (!is_array($incfields)) $incfields = array();
if (!is_array($filterfield)) $filterfield = array();
if (!is_array($filtertype)) $filtertype = array();
if (!is_array($filterq)) $filterq = array();

if (!$print) {

   $reportdata["description"] = "This report can be used to generate a custom export of invoices by applying up to 5 filters. CSV Export is available via the Tools menu to the right.";

   $reportdata["headertext"] = '<form method="post" action="reports.php?report='.$report.'">
<table class="form" width="100%" border="0" cellspacing="2" cellpadding="3">
<tr><td width="20%" class="fieldlabel">Fields to Include</td><td class="fieldarea"><table width="100%"><tr>';
   $i=0;
   foreach ($filterfields AS $k=>$v) {
       $reportdata["headertext"] .= '<td width="20%"><input type="checkbox" name="incfields[]" value="'.$k.'" id="fd'.$k.'"';
       if (in_array($k,$incfields)) $reportdata["headertext"] .= ' checked';
       $reportdata["headertext"] .= ' /> <label for="fd'.$k.'">'.$v.'</label></td>'; $i++;
       if (($i%5)==0) $reportdata["headertext"] .= '</tr><tr>';
   }
   $reportdata["headertext"] .= '</tr></table></td></tr>';

   for ( $i = 1; $i <= 5; $i ++ ) {
       $reportdata["headertext"] .= '<tr><td width="20%" class="fieldlabel">Filter '.$i.'</td><td class="fieldarea"><select name="filterfield['.$i.']"><option value="">None</option>';
       foreach ($filterfields AS $k=>$v) {
           $reportdata["headertext"] .= '<option value="'.$k.'"';
           if (isset($filterfield[$i]) && $filterfield[$i]==$k) $reportdata["headertext"] .= ' selected';
           $reportdata["headertext"] .= '>'.$v.'</option>';
       }
       $reportdata["headertext"] .= '</select> <select name="filtertype['.$i.']"><option>Exact Match</option><option value="like"';
       if (isset($filtertype[$i]) && $filtertype[$i]=="like") $reportdata["headertext"] .= ' selected';
       $reportdata["headertext"] .= '>Containing</option></select> <input type="text" name="filterq['.$i.']" size="30" value="'.(isset($filterq[$i])?$filterq[$i]:'').'" /></td></tr>';
   }
   $reportdata["headertext"] .= '</table>
<p align="center"><input type="submit" value="Filter" /></p>
</form>';

}

if (count($incfields)) {

   $filters = array();
   foreach ($filterfield as $i => $val) {
       if ($val && array_key_exists($val, $filterfields)) {
           if ($val == 'clientname') {
               $val = "(SELECT CONCAT(firstname,' ',lastname) FROM tblclients WHERE id=tblinvoices.userid)";
           }
           if ($val == 'address') {
               $val = "(SELECT CONCAT(address1,', ',city,', ',state,' - ',postcode,', ',country) FROM tblclients WHERE id=tblinvoices.userid)";
           }
           $filters[] = ($filtertype[$i]=="like")
               ? $val . " LIKE '%" . db_escape_string($filterq[$i]) . "%'"
               : $val . "='" . db_escape_string($filterq[$i]) . "'";
       }
   }

   $fieldlist = array();
   foreach ($incfields AS $fieldname) {
       if (array_key_exists($fieldname,$filterfields)) {
           $reportdata["tableheadings"][] = $filterfields[$fieldname];
           if ($fieldname=="clientname") $fieldname = "(SELECT CONCAT(firstname,' ',lastname) FROM tblclients WHERE id=tblinvoices.userid)";
           if ($fieldname=="address") $fieldname = "(SELECT CONCAT(address1,', ',city,', ',state,' - ',postcode,', ',country) FROM tblclients WHERE id=tblinvoices.userid)";
           $fieldlist[] = $fieldname;
       }
   }

   $result = select_query("tblinvoices", implode(',', $fieldlist), implode(' AND ', $filters));
   while ($data = mysql_fetch_assoc($result)) {
       if (isset($data['paymentmethod'])) $data['paymentmethod'] = $gateways->getDisplayName($data['paymentmethod']);
       $reportdata["tablevalues"][] = $data;
   }

}

note how i've added the address as an option in the filterfields array, and then defined it's query in the filter and fields arrays at the end.

 

also, if you just want to find paid invoices for a particular month, then use the filters... e.g to show Paid invoices for September 2017...

 

 

that also removes the necessity for all your month start/end coding!