List of clients sorted by total income

[nitaish]

I want to export list of clients in whmcs sorted by income in descending order. WHMCS allows to fetch the top 10 clients list based on income. I want to export the list of all clients with the total income of each client sorted income wise. Can anyone help me with the query?

[nitaish]

I finally could write a MySQL query to fetch the results. Sharing the query here for the benefit of the WHMCS community.

 

select c.id,c.firstname,c.lastname,c.companyname,c.email,c.address1,c.address2,c.city,c.state,c.postcode,c.country,c.phonenumber,c.credit,c.status,sum(i.total) as invoice_total from tblclients c, tblinvoices i where c.id=i.userid group by c.id order by invoice_total desc

[brian!]

thanks for sharing the SQL query.

 

I was going to suggest that you could have just edited the "Top 10 Clients" report and removed the LIMIT 0,10 from the query in there.

[nitaish]

thanks for sharing the SQL query.

 

I was going to suggest that you could have just edited the "Top 10 Clients" report and removed the LIMIT 0,10 from the query in there.

 

I wanted to try that, but could not find the exact mysql query to get the Top 10 Clients Report.

[brian!]

this is the query from the report file...

 

$query = "SELECT tblclients.id,tblclients.firstname, tblclients.lastname, SUM(tblaccounts.amountin/tblaccounts.rate), SUM(tblaccounts.fees/tblaccounts.rate), SUM(tblaccounts.amountout/tblaccounts.rate), SUM((tblaccounts.amountin/tblaccounts.rate)-(tblaccounts.fees/tblaccounts.rate)-(tblaccounts.amountout/tblaccounts.rate)) AS balance, tblaccounts.rate FROM tblaccounts INNER JOIN tblclients ON tblclients.id = tblaccounts.userid GROUP BY userid ORDER BY balance DESC LIMIT 0,10";