SQL query to pull out non-paying subscribers

[nogi]

I have various subscriptions on my site. The main one is called Premium, users that take this sub move from Regular to Premium user group and back when the sub expires. However, with the recent change of site ownership and paypal addresses, subs that are autorenewing/cancelling via PayPal aren't updating back on VB and the users are not reverting back to regular.

I've been able to proactively manage upcoming renewals by using the following query:
Membership Due Dates

SELECT user.username,user.email,subscriptionlog.expiry,subscriptionlog.status
FROM subscriptionlog AS subscriptionlog
LEFT JOIN user AS user ON (user.userid = subscriptionlog.userid)

But, how can I pull a list of users that are still in the Premium group but without any current subscription?

[Zachery]

The old owner needs to cancel the subs in paypal on their end. Because they are renewing, or they'd get dropped.

[nogi]

Originally Posted by Zachery

The old owner needs to cancel the subs in paypal on their end. Because they are renewing, or they'd get dropped.

He has already done this. This has/is causing me another issue with subs, I've got a ton of these messages in the logs now:

vb_error_code authentication_failure
txn_type subscr_cancel

I've taken over the email/domain as well. All the previous subs were associated with his PayPal account, I couldn't see anyway to migrate them over to my PayPal account. Upon cancelling them all on his PayPal - VB tried to cancel the subs on my forum and fails, which is kind of right as peoples subs shouldn't be cut short just because of ownership change.

So far, I've disabled the old sub and created a new one to stop the renewal failures. Problem with this is people can't seem to be bothered to go through the process again resulting in loss of subscribers. For some subscribers that don't renew, the group membership isn't reverting to the old ones, hence the SQL query request.

[kh99]

I don't have subscriptions set up so I can't test this, but I think you should be able to add this to the end of the query you posted above:

WHERE user.usergroupid = P AND subscriptionlog.expirydate < UNIX_TIMESTAMP()

Where you'd replace the P with the usergroupid of the Premium group. You might also want to add "AND subscriptionlog.subscriptionid = S" if it's possible for users to have more than one subscription (to make sure you're matching the subscriptionid S of the Premium subscription).

I have to admit that I don't understand a lot about paid subscriptions. Do the records get removed from the subscription log when the subscription is done? If so, you might want to change your query to "...FROM user LEFT JOIN subscriptionlog", that way you'll find users even if they have no record in the subscriptionlog (and I think it still would work since in that case expirydate should be NULL).