Exporting report for clients that have not ordered a service

[Ragonz]

As per the title I'm looking for a way to export a report that shows the first/lastname and email address of any client who has registered with us but has not purchased anything. Is that possible?

[brian!]

if you wanted to do this outside of WHMCS, e.g directly in phpmyadmin, then you could run a basic SQL query to find which clients did not have hosting - basically, you're just listing the clients in tblclients who are not in tblhosting...

 

SELECT tblclients.firstname, tblclients.lastname, tblclients.email
FROM tblclients
LEFT  JOIN tblhosting ON tblclients.id = tblhosting.userid
WHERE tblhosting.userid IS NULL 

if you wanted to expand that and get a list of clients who hadn't bought domains and hosting...

 

SELECT tblclients.firstname, tblclients.lastname, tblclients.email
FROM tblclients
LEFT  JOIN tblhosting ON tblclients.id = tblhosting.userid
LEFT  JOIN tbldomains ON tblclients.id = tbldomains.userid
WHERE tblhosting.userid IS NULL 
AND tbldomains.userid IS NULL

from inside phpmyadmin, you'd be able to export the results to a number of formats including pdf and csv.

[userofservice]

On 29/04/2017 at 5:11 PM, brian! said:

if you wanted to do this outside of WHMCS, e.g directly in phpmyadmin, then you could run a basic SQL query to find which clients did not have hosting - basically, you're just listing the clients in tblclients who are not in tblhosting...

 

 

SELECT tblclients.firstname, tblclients.lastname, tblclients.email
FROM tblclients
LEFT  JOIN tblhosting ON tblclients.id = tblhosting.userid
WHERE tblhosting.userid IS NULL 

 

 

if you wanted to expand that and get a list of clients who hadn't bought domains and hosting...

 

 

SELECT tblclients.firstname, tblclients.lastname, tblclients.email
FROM tblclients
LEFT  JOIN tblhosting ON tblclients.id = tblhosting.userid
LEFT  JOIN tbldomains ON tblclients.id = tbldomains.userid
WHERE tblhosting.userid IS NULL 
AND tbldomains.userid IS NULL

 

 

from inside phpmyadmin, you'd be able to export the results to a number of formats including pdf and csv.

#Active Clients with no services example - 0 (0)
SELECT tc.id,tc.firstname,tc.lastname,tc.status
FROM tblclients tc
LEFT JOIN tblhosting th ON tc.id = th.userid
LEFT JOIN tblhostingaddons tha ON tc.id = tha.userid
LEFT JOIN tbldomains td ON tc.id = td.userid
WHERE tc.status = 'Active'
AND th.userid IS NULL
AND tha.userid IS NULL
AND td.userid IS NULL
ORDER BY tc.id DESC;