Report Request

[webresellers]

I was wondering if someone could write a report that will show the following information...

 

CUSTOMER | BILLING CYCLE | LAST BILL DATE | NEXT BILL DATE | PAYMENT METHOD | $ DUE PER CYCLE

 

JOHN DOE | MONTHLY | 01/02/2008 | 02/02/2008 | CREDIT CARD | $29.99

 

I am sure there are some other fields that might make a report handy. We see its use helping with billing audits to make sure everyone is getting billed when they should. Might be nice to list the # of outstanding invoices too.

[Nick]

Customers don't have 'one' billing date, each account under the customer has a billing date.

 

Would you want it Listing a customer name, then all their accounts underneath? Eg:

 

John Doe

Acct 1 | MONTHLY | 01/02/2008 | 02/02/2008 | CREDIT CARD | $29.99

Acct 2 | QUARTERLYLY | 08/02/2008 | 08/05/2008 | PAYPAL | $21.99

[webresellers]

...maybe I should have said last invoice date, next invoice date.

[dutchnet]

Hi,

not a terrific looking report, but it's a start:

I would prefer to have the customer details on a separate line and it would be great if it would show the product add-ons, but here is version 0.01

 

<?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"] = "Products per customer ".$months[$month-1]." ".$year;

$reportdata["description"] = "This reports shows per customer all their products, excl. domains";

 

$query = "SELECT tblhosting.domain, tblhosting.domainstatus, tblhosting.paymentmethod, tblhosting.billingcycle, tblhosting.nextduedate, tblhosting.nextinvoicedate, tblhosting.amount, tblhosting.packageid, tblclients.id, tblclients.companyname, tblclients.firstname, tblclients.lastname, tblproducts.id, tblproducts.name FROM tblclients

LEFT JOIN tblhosting ON tblclients.id = tblhosting.userid

JOIN tblproducts ON tblproducts.id = tblhosting.packageid

ORDER BY tblclients.id ASC ";

$result = mysql_query($query);

$num_rows = mysql_num_rows($result);

 

$reportdata["headertext"] = "Products per customer: $num_rows";

 

$reportdata["tableheadings"] = array("Customer","Domainname","Status","Paymenttype","Billing Cycle","Next DueDate","Next Invoice","Amount","Pakket");

 

while ($data = mysql_fetch_array($result)) {

$domain = $data["domain"];

$paymentmethod = $data["paymentmethod"];

$billingcycle = $data["billingcycle"];

$nextduedate = $data["nextduedate"];

$nextinvoice =$data["nextinvoicedate"];

$status = $data["domainstatus"];

$clientname = $data["companyname"]." ".$data["firstname"]." ".$data["lastname"];

$invamount = $data["amount"];

$pakket = $data["name"];

$reportdata["tablevalues"][] = array("$clientname","$domain","$status","$paymentmethod","$billingcycle","$nextduedate","$nextinvoice","$invamount","$pakket");

}

 

$data["footertext"]="<table width=90% align=center><tr><td>";

 

if ($month=="1") {

$data["footertext"].="<a href=\"$PHP_SELF?report=$report&month=12&year=".($year-1)."\"><< December ".($year-1)."</a>";

} else {

$data["footertext"].="<a href=\"$PHP_SELF?report=$report&month=".($month-1)."&year=".$year."\"><< ".$months[($month-2)]." $year</a>";

}

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

 

?>

[webresellers]

Very nice.... thank you... I am finding the report pretty handy as-is. THANK YOU! Maybe for your next update, just having the headings sortable would be very helpful. But, beggers can't be choosers.

[LemonBarley]

i am wondering where can i put the php file that dutchnet has said.

[DataHosts]

In the reports folder in WHMCS

 

/modules/reports

[LemonBarley]

did not seems to work.. showing me a blank page

[DataHosts]

Just tried and works fine for me.

 

I copied the report, fixed a few things, and named the report. Uploaded to my whmcs/modules/reports folder. Logged into WHMCS, went to reports and clicked on the new report. Worked fine for me.

 

<?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"] = "Products per customer ".$months[$month-1]." ".$year;
$reportdata["description"] = "This reports shows per customer all their products, excl. domains";

$query = "SELECT tblhosting.domain, tblhosting.domainstatus, tblhosting.paymentmethod, tblhosting.billingcycle, tblhosting.nextduedate, tblhosting.nextinvoicedate, tblhosting.amount, tblhosting.packageid, tblclients.id, tblclients.companyname, tblclients.firstname, tblclients.lastname, tblproducts.id, tblproducts.name FROM tblclients
LEFT JOIN tblhosting ON tblclients.id = tblhosting.userid
JOIN tblproducts ON tblproducts.id = tblhosting.packageid
ORDER BY tblclients.id ASC ";
$result = mysql_query($query);
$num_rows = mysql_num_rows($result);

$reportdata["headertext"] = "Products per customer: $num_rows";

$reportdata["tableheadings"] = array("Customer","Domain","Status","Payment Type","Billing Cycle","Due Date","Next Invoice","Amount","Product");

while ($data = mysql_fetch_array($result)) {
$domain = $data["domain"];
$paymentmethod = $data["paymentmethod"];
$billingcycle = $data["billingcycle"];
$nextduedate = $data["nextduedate"];
$nextinvoice =$data["nextinvoicedate"];
$status = $data["domainstatus"];
$clientname = $data["companyname"]." ".$data["firstname"]." ".$data["lastname"];
$invamount = $data["amount"];
$pakket = $data["name"];
$reportdata["tablevalues"][] = array("$clientname","$domain","$status","$paymentmethod","$billingcycle","$nextduedate","$nextinvoice","$invamount","$pakket");
}

$data["footertext"]="<table width=90% align=center><tr><td>";

if ($month=="1") {
$data["footertext"].="<a href=\"$PHP_SELF?report=$report&month=12&year=".($year-1)."\"><< December ".($year-1)."</a>";
} else {
$data["footertext"].="<a href=\"$PHP_SELF?report=$report&month=".($month-1)."&year=".$year."\"><< ".$months[($month-2)]." $year</a>";
}
$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>";

?>