Jump to content
ScottN

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

Recommended Posts

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

Share this post


Link to post
Share on other sites

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;
   }

}

Share this post


Link to post
Share on other sites

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

 

Thank you, again!!!

 

- Scott

Share this post


Link to post
Share on other sites

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

Edited by baymax

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

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:

sample-227.thumb.png.1beb8981f06f3978d220536043ac13eb.png

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 by Kian

Share this post


Link to post
Share on other sites
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. 🙂

3JQpfI1.png

Share this post


Link to post
Share on other sites

Your cross-reading skills are impressive 😛

Share this post


Link to post
Share on other sites
5 minutes ago, Kian said:

Your cross-reading skills are impressive 😛

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

 

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Recently Browsing   0 members

    No registered users viewing this page.

×

Important Information

By using this site, you agree to our Terms of Use & Guidelines and understand your posts will initially be pre-moderated