Expired Subscriptions

[Bat21]

Is there a way of searching for users who previously paid a subscription that has now expired?

I want find users who have let their subscription lapse, I know I could do this manually but as I have 20,000+ users, some of which have never paid a subscription, it would take forever to search through the users to check which ones previously took out a subscription and when their subscription expired.

[Dave]

I don't know if this is available in the ACP, but you can execute a query like this in something like PHPMyAdmin:

PHP Code:

SELECT a.userid, a.username, FROM_UNIXTIME(b.regdate) AS BuyDate, FROM_UNIXTIME(b.expirydate) AS ExpiryDate FROM user a INNER JOIN subscriptionlog b ON a.userid = b.userid WHERE b.expirydate > UNIX_TIMESTAMP() order by b.expirydate desc 


[Bat21]

Thanks Dave.

Unfortunately running that query just shows all active subscriptions. What I am looking for is a list of accounts that have previously had a subscription that has now expired....

[Dave]

Made a little mistake, change > to <.

PHP Code:

SELECT a.userid, a.username, FROM_UNIXTIME(b.regdate) AS BuyDate, FROM_UNIXTIME(b.expirydate) AS ExpiryDate FROM user a INNER JOIN subscriptionlog b ON a.userid = b.userid WHERE b.expirydate < UNIX_TIMESTAMP() order by b.expirydate desc 


[Bat21]

Perfect, thank you Dave :up:

--------------- Added [DATE]1416599799[/DATE] at [TIME]1416599799[/TIME] ---------------

Would it be possible to have the query sort by 'Last Visited' order?

[Dave]

Replace

PHP Code:

order by b.expirydate desc 


by

PHP Code:

order by a.lastactivity desc 


[Bat21]

Many thanks Dave, perfect :up: