Best way to export list of customer's hosting packages?

[ScottN]

I have a customer that has quite a few hosting packages and they'd like to get an export of them.

 

When I use the Reports feature in WHMCS > Exports > Services, it's missing a checkbox for the actual Product/Service. I can export all of their products and services... but it doesn't say WHAT the product/service is. I can list domain name, order date, dollar amount and many other attributes... but not the package (Bronze, Gold, etc.)

 

Any tips/tricks would be appreciated!

 

- Scott

[sentq]

you can edit this report to include the field/column you need, after 2 minutes I was able to make it export product name

 

<?php

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

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

$filterfields = array("id"=>"ID","userid"=>"User ID","clientname"=>"Client Name","orderid"=>"Order ID","packageid"=>"Product ID","packagename"=>"Product Name","server"=>"Server ID","domain"=>"Domain Name","dedicatedip"=>"Dedicated IP","assignedips"=>"Assigned IPs","firstpaymentamount"=>"First Payment Amount","amount"=>"Recurring Amount","billingcycle"=>"Billing Cycle","nextduedate"=>"Next Due Date","paymentmethod"=>"Payment Method","domainstatus"=>"Status","username"=>"Username","password"=>"Password","notes"=>"Notes","subscriptionid"=>"Subscription ID","suspendreason"=>"Suspend Reason");

$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 services by applying up to 5 filters. CSV Export is available via the download link at the bottom of the page.";

   $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=tblhosting.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=tblhosting.userid)";
           if ($fieldname=="packagename") $fieldname = "(SELECT `name` FROM `tblproducts` WHERE `tblproducts`.`id`=`tblhosting`.`packageid`)";
           $fieldlist[] = $fieldname;
       }
   }

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

}

[ScottN]

Woot! Thank you!!! This worked great AND I received an education on custom reports!

 

Thank you, again!!!

 

- Scott

[baymax]

sentq I need this as well. However, the code above didn't work for me. Please and thank you!

Edited February 13, 2020 by baymax

[brian!]

8 hours ago, baymax said:

However, the code above didn't work for me. Please and thank you! 

it would still work for me in v7.9.1 and might be slightly easier for you as a starting point.

[baymax]

17 hours ago, brian! said:

it would still work for me in v7.9.1 and might be slightly easier for you as a starting point.

Brian!  Here's what I've done.

Tested the query from the above code:

SELECT `name` FROM `tblproducts` WHERE `tblproducts`.`id`=`tblhosting`.`packageid`

This did not work so I made some adjustments and came up with the following:

SELECT `name` FROM `tblproducts` inner join `tblhosting` on `tblproducts`.`id`=`tblhosting`.`packageid`

This worked but the Services page still produced errors about 'packagename'.

I'm stumped.

[Kian]

Try with this SQL query:

SELECT CONCAT(t4.firstname, " ", t4.lastname) AS clientname, t4.companyname, t3.name AS product_group, t2.name AS product_name, CONCAT(t3.name, " > ", t2.name) AS product_name_and_group, t1.firstpaymentamount, t1.amount, t1.billingcycle, t1.nextduedate, t1.domainstatus AS status FROM tblhosting AS t1 LEFT JOIN tblproducts AS t2 ON t1.packageid = t2.id LEFT JOIN tblproductgroups AS t3 ON t2.gid = t3.id LEFT JOIN tblclients AS t4 ON t1.userid = t4.id

Here's the outcome:

If you want to filter by user id simply add WHERE t4.id = '{USER_ID}' at the very end of the query. To filter also by product status (eg. Active and Terminated) add WHERE t4.id = '{USER_ID}' AND t1.domainstatus IN ("Active", "Terminated").

Edited February 14, 2020 by Kian

[brian!]

2 minutes ago, Kian said:

Try with this SQL query

except in another thread, he said that he didn't want to use a SQL query .

On 13/02/2020 at 03:14, baymax said:

Aside from doing it from the db, I'd really like to be able to generate reports from WHMCS.

in any event, he now has it as a custom WHMCS report. 🙂

[Kian]

Your cross-reading skills are impressive 😛

[brian!]

5 minutes ago, Kian said:

Your cross-reading skills are impressive 😛

or just plain sad - the jury is still out on that one. 😛

 

[Kian]

There's no Companyname column in tblhosting table that's why you have an error.

[zoannon]

this works great !!!

wondering if anyone would be so kind as to advise how to split the client name into first and last.  I would like to import into a crm .

thank you.  This is prob the most useful report in the system !

 

Glenn

[Kian]

Get rid of CONCAT() as follows:

SELECT t4.firstname, t4.lastname, t4.companyname, t3.name AS product_group, t2.name AS product_name, CONCAT(t3.name, " > ", t2.name) AS product_name_and_group, t1.firstpaymentamount, t1.amount, t1.billingcycle, t1.nextduedate, t1.domainstatus AS status FROM tblhosting AS t1 LEFT JOIN tblproducts AS t2 ON t1.packageid = t2.id LEFT JOIN tblproductgroups AS t3 ON t2.gid = t3.id LEFT JOIN tblclients AS t4 ON t1.userid = t4.id

 

[zoannon]

7 hours ago, Kian said:

Get rid of CONCAT() as follows:

SELECT t4.firstname, t4.lastname, t4.companyname, t3.name AS product_group, t2.name AS product_name, CONCAT(t3.name, " > ", t2.name) AS product_name_and_group, t1.firstpaymentamount, t1.amount, t1.billingcycle, t1.nextduedate, t1.domainstatus AS status FROM tblhosting AS t1 LEFT JOIN tblproducts AS t2 ON t1.packageid = t2.id LEFT JOIN tblproductgroups AS t3 ON t2.gid = t3.id LEFT JOIN tblclients AS t4 ON t1.userid = t4.id

 

Perfect, that works for me.

As a matter of interest, I can sort of work out the structure but would you mind advising why you use the t.4 and t.3 etc ...

Thanks for the query it works and I can export as needed .  🙂

[Kian]

t1, t2 etc. are SQL aliases. I use them to avoid writing verbose queries. Without aliases 569 chars:

SELECT tblclients.firstname, tblclients.lastname, tblclients.companyname, tblproductgroups.name AS product_group, tblhosting.name AS product_name, CONCAT(tblproductgroups.name, " > ", tblhosting.name) AS product_name_and_group, tblhosting.firstpaymentamount, tblhosting.amount, tblhosting.billingcycle, tblhosting.nextduedate, tblhosting.domainstatus AS status FROM tblhosting LEFT JOIN tblproducts ON tblhosting.packageid = tblhosting.id LEFT JOIN tblproductgroups ON tblhosting.gid = tblproductgroups.id LEFT JOIN tblclients AS t4 ON tblhosting.userid = tblclients.id

With aliases 425 chars -25%:

SELECT t4.firstname, t4.lastname, t4.companyname, t3.name AS product_group, t2.name AS product_name, CONCAT(t3.name, " > ", t2.name) AS product_name_and_group, t1.firstpaymentamount, t1.amount, t1.billingcycle, t1.nextduedate, t1.domainstatus AS status FROM tblhosting AS t1 LEFT JOIN tblproducts AS t2 ON t1.packageid = t2.id LEFT JOIN tblproductgroups AS t3 ON t2.gid = t3.id LEFT JOIN tblclients AS t4 ON t1.userid = t4.id

This way I can keep JOINs short.

Edited September 10, 2020 by Kian

[rugg]

Hi,

where to put the codes ?

because i add a new file or replace existed reports.php,

all shows "This file cannot be accessed directly".

thanks