Incoming Credit Report

[jvriends]

I would like to share a report that lists the incoming credit, for those hosts that track this data in their accounting system.

 

If anyone can help, I would like to modify this to include a Download CSV button, and limit the width of the description header.

 

<?php

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

$reportdata["title"] = "Incoming Credit";
$reportdata["description"] = "This report shows incoming credit for the selected period";

$reportdata["currencyselections"] = false;

$query = "select year(min(date)) as minimum, year(max(date)) as maximum from tblaccounts;";
$result = mysql_query($query);
$data = mysql_fetch_array($result);
$minyear = $data['minimum'];
$maxyear = $data['maximum'];

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

$reportdata["headertext"] .= "<select name=\"startyear\">";
for ( $counter = $minyear; $counter <= $maxyear; $counter += 1) {
$reportdata["headertext"] .= "<option";
if ($counter==$startyear) { $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";
}

$reportdata["headertext"] .= "</select> <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> End Date: <select name=\"endyear\">";
for ( $counter = $minyear; $counter <= $maxyear; $counter += 1) {
$reportdata["headertext"] .= "<option";
if ($counter==$endyear) { $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=\"endday\">";
for ( $counter = 1; $counter <= 31; $counter += 1) {
$reportdata["headertext"] .= "<option";
if ($counter==$endday) { $reportdata["headertext"] .= " selected"; }
$reportdata["headertext"] .= ">$counter";
}

$reportdata["headertext"] .= "</select> <input type=\"submit\" value=\"Generate Report\"></form>"; 

if ($calculate) {

$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."235959";

$query = "SELECT COUNT(*),SUM(amount) FROM tblcredit WHERE date>='$startdate' AND date<='$enddate'";
$result = mysql_query($query);
$data = mysql_fetch_array($result);
$total = $data[1];

if (!$total) { $total="0.00"; }

$reportdata["headertext"] .= "<br><br><B>Total Credit:</B> ".formatCurrency($total);
}

$reportdata["headertext"] .= "</center>";

$reportdata["tableheadings"] = array("ID","Client Name","Date","Description","Amount","Invoice ID");

$query = "SELECT tblcredit.*,tblclients.firstname,tblclients.lastname FROM tblcredit INNER JOIN tblclients ON tblclients.id=tblcredit.clientid WHERE date>='$startdate' AND date<='$enddate' ORDER BY date ASC";
$result = mysql_query($query);
while ($data = mysql_fetch_array($result)) {
$id = $data["id"];
$client = $data["firstname"]." ".$data["lastname"];
$date = fromMySQLDate($data["date"]);
$description = $data["description"];
$amount = $data["amount"];
$relid = $data["relid"];
$reportdata["tablevalues"][] = array("$id","$client","$date","$description","$amount","$relid");
}

$data["footertext"]="";
?>

[jcorbett]

Thank you. I added this report and ran it but I'm not sure what the results are supposed to mean.

[jvriends]

In my accounting system, I need to track the date a client pays me money that is extra (credit). This report allows me to see the date, amount, and from who gave me this money. I can then take this an add it to my accounting system as a liability.

[tornadohost]

Very Good.

 

Thanks!