Domain Name Report

[Hosting NSB]

Good Morning Everyone,

 

I am writing to ask if anyone has or could help me make a simple domain report. I have found ok reports for every other aspect of WHMCS but have yet to find a suitable domain report.

 

I need something that, at bare minimum, shows the client name and what they paid for their domain (renewal, transfer, or new registration). Preferably I could include what we paid to our registrar (eNom) and include anything that has to do with domains (even if they purchase it within a package).

 

Anyone have anything like this already made up, or know how I could set one up easily.

 

I asked the WHMCS staff and they just pointed me to the custom reports, but even using this I cant find a way to do what I want, so even if someone could guide me in the right direction that way, that would be fine.

 

Thank you so much!

[brian!]

Good Morning Everyone,

I am writing to ask if anyone has or could help me make a simple domain report. I have found ok reports for every other aspect of WHMCS but have yet to find a suitable domain report.

I need something that, at bare minimum, shows the client name and what they paid for their domain (renewal, transfer, or new registration). Preferably I could include what we paid to our registrar (eNom) and include anything that has to do with domains (even if they purchase it within a package).

including what you paid eNom could be awkward - unless you can think of a simple way to get that amount for each domain?

 

Anyone have anything like this already made up, or know how I could set one up easily.

I asked the WHMCS staff and they just pointed me to the custom reports, but even using this I cant find a way to do what I want, so even if someone could guide me in the right direction that way, that would be fine.

unless i'm missing something, couldn't you just use the default Domains Report ?

 

https://docs.whmcs.com/Reports#Reports

 

Domains

Generate a custom export of domain name information by applying up to 5 filters. CSV Export is available in the Tools menu in the top-right corner of the page.

that will give you a list of domains, with the client name and what they paid for it (registration/transfer and/or renewal)...

 

 

it doesn't include currencies by default, but if you're only using USD, that won't be an issue.

 

basically the SQL query is just tbldomains joined with tblclients - so theoretically you could add additional field terms to output in that report (basically anything from tbldomains) - it's just a case of working out what you want to know.

 

btw - your domain search form on your main site doesn't work correctly, e.g it doesn't pass the search term to the cart... code looks to be from v6 and the site is now using v7... just take a look at the code from utilities -> integration code -> domain ordering and that should work.

 

https://forum.whmcs.com/showthread.php?131125-Domain-form-validating&p=523629#post523629

[Hosting NSB]

Thank you so much I will look through this now, thank you thank you!

[Hosting NSB]

Ok im running into the same sort of issue, I can output a list of domain names, but how could I do a monthly report. It's perfectly fine not to show what we pay to eNom, but I need to be able to run a report of all domains that went through the system for a certain month and how much they paid, can I do that with the domain tab? I feel like im missing something here.

[brian!]

Ok im running into the same sort of issue, I can output a list of domain names, but how could I do a monthly report. It's perfectly fine not to show what we pay to eNom, but I need to be able to run a report of all domains that went through the system for a certain month and how much they paid, can I do that with the domain tab? I feel like im missing something here.

oh ok, this is awkward because you're falling between two reports.... Domains will give you a list of domains, but you're really wanting to query Invoices (but the report doesn't have descriptions)...

 

if it were me, and you had access to phpMyAdmin or similar (to allow you to query the database tables directly), i'd do this...

SELECT tblclients.firstname,tblclients.lastname,tblclients.companyname,tblinvoiceitems.*,tblinvoices.date,tblinvoices.datepaid
FROM tblinvoiceitems
INNER JOIN tblinvoices ON tblinvoiceitems.invoiceid = tblinvoices.id
INNER JOIN tblclients ON tblinvoiceitems.userid = tblclients.id
WHERE tblinvoiceitems.type LIKE '%Domain%' AND tblinvoices.datepaid BETWEEN '2017-05-01' AND '2017-09-01'

that would give you a list of domain related invoices that were paid this month; if you wanted to see a list of domain invoices generated, then change tblinvoices.datepaid to tblinvoices.date in the WHERE statement.

 

 

you'll be able to export the results from phpMyAdmin to a number of different formats... you can al edit the select query if there are fields you don't want to see.

 

once you know the query, it's often easier/quicker to use phpMyAdmin than editing an existing WHMCS report... if I get the chance, I might look at turning the above into a report, but it won't be until the weekend as i'm busy with paid work.

[Hosting NSB]

Thank you so much, I will try this now!

[Hosting NSB]

That seemed to do it, I have to run through all the paperwork to see if it is exactly what I need but this solution is a good work around, great thinking. If you do end up making a report that would be awesome, but this should get me what I need for now, thank you so much!

[brian!]

I had a spare 5 mins to hack the domains report before packing up for the day, so here's an Invoice Items Report for you... just create a .php file in /modules/reports and paste the following into it...

<?php

# Invoice Items Report
# written by !brian

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

$reportdata["title"] = "Invoice Items";

$filterfields = array("id"=>"ID","userid"=>"User ID","date"=>"Invoice Date","duedate"=>"Due Date","datepaid"=>"Date Paid","clientname"=>"Client Name","type"=>"Type","description"=>"Description","amount"=>"Amount","taxed"=>"Taxed","paymentmethod"=>"Payment Method");

$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 invoice items by applying up to 5 filters. CSV Export is available via the Tools menu to the right.";

   $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=tblinvoiceitems.userid)";
           }
           if ($val == 'date') {
               $val = "(SELECT date FROM tblinvoices WHERE id=tblinvoiceitems.invoiceid)";
           }
           if ($val == 'datepaid') {
               $val = "(SELECT date(datepaid) FROM tblinvoices WHERE id=tblinvoiceitems.invoiceid)";
           }            
           $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=tblinvoiceitems.userid)";
           if ($fieldname=="date") $fieldname = "(SELECT date FROM tblinvoices WHERE id=tblinvoiceitems.invoiceid)";
           if ($fieldname=="datepaid") $fieldname = "(SELECT date(datepaid) FROM tblinvoices WHERE id=tblinvoiceitems.invoiceid)";
           $fieldlist[] = $fieldname;
       }
   }

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

}

and then to get a list of Domain invoices generated in August, filter like below (select whichever checkboxes you want to output)...

 

you could add a third filter of Date Paid / Containing 2017-08 to find those invoice that were both generated and paid in August 2017.

[Hosting NSB]

Wow thank you so much, that looks to be what I need, only thing I noticed is I saw a domain in there that got canceled but no indication, but that doesn't happen often and this hack has gotten me almost all the way there, thank you so much, you are awesome! This will be plenty fine I can bridge the small gaps myself. I cant believe they don't have this report standard. Thank you thank you thank you!

[Hosting NSB]

fixed my problem by running the report as Date Paid, very nice!

[brian!]

fixed my problem by running the report as Date Paid, very nice!

cool - I was going to suggest you could add the domain status value from tbldomains, but if you can effectively get the required info via date paid, then that's fine.