I have a requirement to locate any successfully paid invoices that relate to a subscription - something I thought would be quite easy but I’m going round in circles. Getting the subscriptions themselves, Im fine with - I can pick up whether its active, when it started etc. My problem is then linking the invoices to the subscription so that I can check there is a paid invoice in the list. The customer has situations where people sign up for a subscription, so an invoice is raised but then the card payment fails so whilst there is a subscription and an invoice, its not been a subscription that’s worth counting in the figures so i need to identify if the invoice has been paid before counting the subscription in some stats.
I’m initially selecting all subscriptions for a specific contact (this will be extended later to filter for a specific product id) as below
$subscriptions = $is->getConnection() ->data() ->findByField("RecurringOrder",1000,0,"ContactId",$user->contact_id, ["Id", "ContactId", "OriginatingOrderId", "AutoCharge", "BillingAmt", "BillingCycle", "CC1", "CC2", "EndDate", "Frequency", "NextBillDate", "StartDate", "Status", "SubscriptionPlanId", "ProductId", "ReasonStopped"]);
To get any subscriptions that relate to a contact and then looking at the Job table to get any jobs relating to that subscription.
$jobs = $is->getConnection() ->data()->query("Job",1000,0, ["JobRecurringId"=>$subscription_id], ["Id"],"Id",false);
Once I’ve got the job id, I believe thats the invoice number and so I then look at the Invoice payments based on the invoiceId to see if there is a successful payment against it.
My problem is that for some subscriptions, I can find jobs in the Job table and for others I can’t, even though in Infusionsoft I can see an invoice having been raised and a payment having been processed
Any one got any hints or suggestions on how I can do this?
Thanks in advance