Cleaning out old accounts (based on services and last login), possible?

[-M-]

Hi,

Running on WHMCS v7.x (not planning to update to WHMCS v8.x for the time being), but I am cleaning out old accounts. However this really a time consuming job.
I am trying to remove (old) accounts with the following requirements;

• Accounts created before 2019
• Accounts which have no logins after (january) 2019
• Accounts which only have 1 or 2 services

Is there an easier way to do this, than rather doing this by hand?!

Currently I am looking at my clents list and sorting them from creation date. After that I open all clients one by one which have 1 or 2 services active. Then I check their last login and remove the client if needed.

Surely there must be a better / easier way for a sophisticated program like WHMCS? If not; is there a MySQL query I can run in phpMyAdmin or something?

Thanks in advance.

[bear]

I'm curious as to why you're removing clients that have active services. 
Also, are you not concerned about the security issue with that version? They made it sound rather serious.

[-M-]

Because we only use the ordering system? So there are no active services. We process everything by hand. That's why I want to clean out old accounts based on the previous post.
Not much used else with WHMCS. We recently upgraded from WHCS v6.x to v7.x.

No clue what security issue you are talking about? This one: https://blog.whmcs.com/133679/security-update-2021-02-26

Already patched that. So dunno what you mean.

Edited March 31, 2021 by MvdL1979

[brian!]

3 hours ago, MvdL1979 said:

Surely there must be a better / easier way for a sophisticated program like WHMCS?

Tom posted a report 5 years ago that could be adapted for your purpose...

for your purpose, you'd probably want to tweak the initial db query to include to wheredates (lastlogin and datecreated), and you might need to tweak the product count if it needs to check for all services rather than just active ones... and bear in mind, that it's checking services - so a client could have no services, but 10 active domains and this report wouldn't show that.

[bear]

3 hours ago, MvdL1979 said:

Not much used else with WHMCS. We recently upgraded from WHCS v6.x to v7.x.

No clue what security issue you are talking about? This one: https://blog.whmcs.com/133679/security-update-2021-02-26

Already patched that. So dunno what you mean.

Not all of v7 was patched (it only went from 7.7 and up), so with you saying you were using 7 (and no prior mention of just having upgraded and patched or what specific version you were using), I assumed you might not want to do any updating at at all and were on *some* version of 7 that was not yet patched. 

[-M-]

Okay, thank you...

Nevermind; managed to get it correctly sorted by using: ->orderBy('lastlogin', 'asc')

Edited April 1, 2021 by MvdL1979

[-M-]

Okay fooled around with it a bit more. But still a lot of work.
So I am looking for a different solution; how can I delete all clients (in bulk) based on;

1. Signed up before december 2017
2. And have not logged in after january 2018

I don't care about the fact they have products or whatver. We only use WHMCS for odering anyways.

Thanks.

[brian!]

5 minutes ago, MvdL1979 said:

I don't care about the fact they have products or whatever. We only use WHMCS for ordering anyways.

you would have to run a SQL query for that - something along the lines of....

DELETE FROM tblclients WHERE datecreated < '2017-12-01' AND lastlogin < '2018-01-01'

that lastlogin date could be 2018-01-31 depending on what you mean by after jan 2018.... backup the table before you run such queries.

[-M-]

Okay, thank you. Will give it a go.