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
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
On 2/14/2020 at 7:24 PM, Kian said:

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").

Please send me full tutorials or guide lines how i change products in into products name and how company name in custom field front i have design but not working
and giving me error blow check please

 

Screenshot_2020-05-14 WHMCS - Reports.png

Screenshot_2020-05-14 Oops .png

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

 

Share this post


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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


  • Recently Browsing   0 members

    No registered users viewing this page.

×
×
  • Create New...

Important Information

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