Generate a report of clients with no active services?

[ichilver]

Hi

 

I would like to generate a report that gave me a list of all 'active' clients who may or may not have any products but where none are active.

 

Basically I want to generate a list of clients who either have no services at all, or have had services in the past but are terminated, cancelled, etc (anything but active)

 

I'm happy to do a MySQL report direct on the database but I'm not sure of the relationships between the tables.

 

Any ideas on this?

 

Ultimately the list this generates I then want to close those accounts down if they are old enough, to a) keep the system clean and b) conform to the new license cost based on number of clients.

 

Many thanks in advance

[brian!]

what about using the "Client Status Update" option within the Automation settings ?

 

http://docs.whmcs.com/Automation_Settings#Client_Status_Update

 

WHMCS automatically sets clients with no active products/services to Inactive status, this helps you to distinguish and filter clients effectively. However you can stop WHMCS from doing this by changing this setting. More information on this feature is available in Client Management.

 

Disabled - The status of a client will not be changed automatically

Change client status based on active/inactive products - If a client has no active or suspended products/services then their account will automatically be set to Inactive status when the cron job next runs.

Change client status based on active/inactive products and not logged in for longer than 3 months - In addition to the above option, a client's account will only be set to inactive when their last login date was over 3 months ago.

[ichilver]

Hi

 

I'm running version 5.3.14 at the moment and I can't see that option.

 

- - - Updated - - -

 

Found it...

 

Many thanks..

[twhiting9275]

This isn't precisely what you're looking for, but it'll help you determine which clients are active, and which aren't. I'm sure it'll come in useful not only to you but to others shortly as well

1: Copy the following code to your clipboard

2: Save as a file in whmcs/modules/reports (I used active_clients.php)

3: Go into whmcs -> admin -> reports -> more .

4: At the bottom, look for 'Active Clients' in the 'other' field

5: Click on the client name to go directly to their profile and change the status, if they have no active services

 

I left clients with active services in. If you want to take them out, look for

$showactive = TRUE;

 

and change that to

$showactive = FALSE;

 

<?php
/*
Active client report for WHMCS
Click on the client name to get to their account profile directly.
You can change the 'Status' dropdown if you feel you need to, just make sure they have no active or suspended services, or WHMCS will change it back 
By default, this shows all active clients (with active services or not).
To change this, change 
$showactive  = TRUE; 
to 
$showactive = FALSE;
*/

use Illuminate\Database\Capsule\Manager as Capsule;
$print = $_GET['print'];
if (empty($print))
{
$print = FALSE;
}
if ($print !='true')
{
$print = FALSE;
}
//do we want to show clients with active services?
$showactive = TRUE;
# The title of your report
$reportdata["title"] = "Active Clients";

# The description of your report
$reportdata["description"] = "Active Clients List";

# Header text - this gets displayed above the report table of data
$reportdata["headertext"] = "This is a list of your active clients.<br /> You can click on the client's name o go directly to their account.<br />";
//let's get the clients



# Report Table of Data Column Headings - should be an array of values
$reportdata["tableheadings"] = array("Client","Active Services?","Signed Up");

$uquery = Capsule::table('tblclients')-> select('*') -> WHERE ('status', '=', 'Active') ->get();
foreach($uquery as $cdata)
{
$clientid = $cdata->id;
$clientfirst = $cdata->firstname;
$clientlast = $cdata->lastname;
$datecreated = $cdata->datecreated;
$cdate = \Carbon\Carbon::createFromTimeStamp(strtotime($datecreated))->diffForHumans();

$numactive = Capsule::table('tblhosting')-> select('id') -> WHERE ('userid', '=', $clientid) -> WHERE ('domainstatus', '=', 'Active') ->count();
if ($showactive)
{
	if (!$print)
	{
		$clientlink = "<a href=\"./clientsprofile.php?userid=$clientid\">$clientfirst $clientlast</a>";
	}
	if ($print)
	{
		$clientlink = "$clientfirst $clientlast";
	}
	$reportdata["tablevalues"][] = array("$clientlink","$numactive","$cdate");
}
if ((!$showactive) && (empty($numactive)))
{
	if (!$print)
	{
		$clientlink = "<a href=\"./clientsprofile.php?userid=$clientid\">$clientfirst $clientlast</a>";
	}
	if ($print)
	{
		$clientlink = "$clientfirst $clientlast";
	}
	$reportdata["tablevalues"][] = array("$clientlink","$numactive","$cdate");
}
}

# Report Footer Text - this gets displayed below the report table of data
$data["footertext"] = "Active clients report courtesy of <a href=\"https://www.whmcsguru.com\">WHMCS Guru</a>";

[POFEquality]

What if I want to add email, domain name and contact number on the list... also on the listing if a client has signed up for a certain product in the past but is cancelled or inactive i want to exclude that. 

Can someone ammend the code