Database query for getting pending cancel information?

[Trevor Gryffyn]

Trying to add some information to a report and need the pending cancels.  I see tblcancelrequests but I can't see what id the relid matches to or how to trace from a user to a cancel or vice version and how to get the future cancellation date.  I see the "Auto-Terminate End of Cycle" checkbox under clientservices.php but that leads to the question of are there multiple pending cancellation indicators, maybe one is just "cancel on this date" and one is "don't auto-renew, effectively cancelling on next billing".

Anyway, just want to add pending cancellations to a homebrew report via database query and could use some help.  Thanks!

-Trevor

[Trevor Gryffyn]

Additional information.  Under tblhosting connected to tblcancelrequests, the recording I'm looking at (who appears on the Pending Cancels page in WHMCS), it shows a cancellation date of 11/8/2024 (US format, m/d/Y) but in tblhosting it says 8/8/2024 on quarterly billing.  If I was going to be doing a pending cancels report, do I need to just do the math of 8/8 + quarterly = 11/8?  Seems really odd.  Most systems would store the 11/8 explicitly.

[Trevor Gryffyn]

I'd still love to hear what people have to say about this so pardon the bump.  Thanks!

[Trevor Gryffyn]

In case someone else is looking, we got a response back from WHMCS support and here's the MySQL query I'm using:

SELECT IF(cr.type = 'End of Billing Period', h.nextduedate, cr.date) effectivedate, cr.type canceltype, cr.reason
FROM whmcs.tblhosting h
LEFT JOIN whmcs.tblcancelrequests cr ON h.id = cr.relid
WHERE cr.id IS NOT NULL
AND h.userid = <whmcsuserid>
;

Obviously substituting the whmcs user id for <whmcsuserid>