Can data capsule code be added with local api requests?

[TBroMEM]

We have some custom PHP deployed to our environment so we can make requests from an external app. The custom PHP includes various functions that uses localAPI.

We would like to add data capsule code to update some additional values in tblinvoiceitems, to get around issue we had encountered in updating duedate (not propagating duedate on tblinvoiceitems).

Is it suitable to add the additional data capsule update code just after this but before function termination?

This is pseudo code. There is additional code initializing and setting environment variables such as the LocalAPI request

    public function updateinfo($invoiceId, $dueDate, ...)
    {
        {
	//define data 
        }
        $data = [
            'invoiceid' => (int) $invoiceId,
            'duedate' => date('Y-m-d', strtotime($dueDate)),
            'itemdescription' => $itemDescriptions,
            'itemamount' => $itemAmounts,
            'itemtaxed' => $itemTaxeds
        ];
        $this->api('UpdateInvoice', $data);

	// can data capsule code suitable be added here to update tblinvoiceitems?

    }

 

[DennisHermannsen]

The UpdateInvoice command allows you to change invoice items as well: https://developers.whmcs.com/api-reference/updateinvoice/

[TBroMEM]

Our existing updateinvoice internal API request updats the duedate and the invoice items (description, etc.) but the duedate is not propagating down to the tblinvoiceitems, which causes duplicate invoice to be generate when the cron runs on the following day. We think there is an issue in the updateinvoice internal api method so splitting out the update of duedate and invoice item description. If this does not prove fruitful we would want to simply update the tblinvoiceitems.duedate directly through capsule just after initial updateinvoice request. I am trying to get ahead of it.

[TBroMEM]

We confirmed that using local API that the using UpdateInvoice to update duedate is NOT propagating down the updated duedate to tblinvoiceitems. It only touches tblinvoice.duedate.

Does anyone have any thoughts on whether we can add database capsule code to our custom PHP (which initiates local API function/calls) to the same function called to update the invoice due date and invoice items (description, etc.)?

If so, please provide some guidance.

[DennisHermannsen]

Why do you want to update the "duedate" column in tblinvoiceitems, though? I don't believe it's used for anything.

If you want to, you can use Capsule to update the rows directly. I just don't think there's any reason to do so.

[pRieStaKos]

@TBroMEM  You need to check Query Manager (Laravel's Query Builder), but I think the whole approach is wrong, in the first place. Anyway.... 

 

 

[TBroMEM]

@pRieStaKos @DennisHermannsen In our testing, the cron references the duedate on the tblinvoiceitems to determine whether an invoice is due, compared to the client product next due date (reference via tblinvoiceitems.relid) or continuous invoice generation, along with automation settings.

This was confirmed by support as well who related the following:

"The way the invoicing (cron) logic in WHMCS works, is that it goes through each service looking at the next due date. It then checks the tblinvoiceitems table to see if an existing invoice already exists for that date (looking at the tblinvoiceitems.duedate) value. If an invoice with that due date is present, another invoice will not be generated."

We have had some trial code which was updating the duedate for the invoice created by order, to set out one month using UpdateInvoice api. Yet, the daily cron kept creating a duplicate invoice due with same duedate as the invoice created by order and updated with new due date (same date set with the client product next due date). We found that the duedate in tblinvoiceitems was NOT being updated via the UpdateInvoice API.

Consequently when the next cron ran, it sees the original due date (from trial signup - day of signup) associated to the invoice line (rather than due date set out one month via API) and generates a duplicate invoice due. Now, the UpdateInvoice api updates the duedate on tblinvoice and reflects that way in the application, but the cron is not using that to determine if an invoice is due for a related client product.

We tested with new code (adding capsule code after the localapi submit - within the function called) and set the correct duedate for the related tblinvoiceitems line. When we ran the cron, it did not generate a duplicate invoice. Problem solved.

On the flip side, I am not sure why the Cron would be coded to look at the duedate on an invoice line, rather than the duedate on the invoice itself. I did not think you could have one invoice with multiple due dates (even if multiple services). Where in the app can you see one invoice, with multiple duedates per invoice line? Essentially, it would appear that the cron should look at the duedate on tblinvoice rather than tblinvoiceitems when assessing whether an invoice is due.

[pRieStaKos]

One combination I can think of is hosting (or other annual due date) along with a domain with min 2y renewal. Due dates are different and it will generate separate invoices next time.

[TBroMEM]

@pRieStaKos Yes, but that is the very point I am driving at. Each one would have a distinct invoice with duedate set on tblinvoice. 

What was in my thinking is if you have one (1) invoice generated that only reflects one due date (visible to client and app), what does it serve the cron to look for duedates on tblinvoiceitems? It only needs to get the duedate from tblinvoice to then compare to the relid from the tblinvoiceitems. 

Nonetheless we found the fix and will be applying in our trial signup api code.