What is the table schema for tblorders

[TBroMEM]

I am trying to workaround a challenge with the following hook based on invoicecreation. I am considering transitioning to the AcceptOrder hook, but need to know if the tblorders includes invoiceid (created with order). That may be a better design.

Essentially, within the external app API flow, once order is created, it updates the client product (tblhosting) with a due date 30 days out, and then updates the invoice with new date. Initially the invoice sets due dates on invoice line same as invoice date (on initial create). BUT, when it updates due date again (30 days out), the system is not updating duedate on tblinvoiceitems. There is no bandwidth for our dev to workout the backend coding, so I am trying to resolve with a hook.

Below is my latest code, which works, but it is still not setting the right due date. I thought by pulling due date from the tblhosting record tied to the tblinvoiceitems lines would resolve. I cannot really increase the sleep (was hoping it gave time for nextduedate to be set) because it delays the action in the external app, and that is client facing.
 

<?php

if (!defined("WHMCS"))
    die("This file cannot be accessed directly");

//alternate use WHMCS\Database\Capsule;
use Illuminate\Database\Capsule\Manager as Capsule;

/*
 * Hook to update invoice items due dates from Invoice create
 * Invoice created by an order/invoice created from external app via API
 */

add_hook('InvoiceCreation', 1, function($vars) {
    $invoiceId = $vars['invoiceid'];
    $source = $vars['source']; // 'autogen' or 'adminarea'
    $adminuser = $vars['user'];
    logActivity('Invoice ' . $invoiceId . ' created by ' . $adminuser . ' from ' . $source . ' selected for eval by hook updateInvoiceItemsDueDate');

    // Update the due date in tblinvoiceitems
    foreach (Capsule::table('tblinvoiceitems')->where('invoiceid', $invoiceId)->get() as $line) {
	//Vars for invoice item
	$id = $line->id;
	$relid = $line->relid;

	if ($adminuser === 9){
    	    sleep(10); // Pauses script execution for 30 seconds. Allow time for edit of invoice created with new due date

	    // Fetch nextduedate from client product  
	    $hostingProductId = Capsule::table('tblhosting')->where('id', $relid)->first(); //pluck('packageid');
	    $nextDueDate = $hostingProductId->nextduedate;

	    // Update due date on invoice line
	    $queryResult = Capsule::table('tblinvoiceitems')->where('id', $id)->update(['duedate' => $nextduedate]);

	    // Fetch description from invoice line
	    // $invLine = Capsule::table('tblinvoiceitems')->where('id', $id)->first();
	    // $description = $invLine->description;

	    // Log activity
	    logActivity('Invoice ' . $invoiceId . ' line ' . $id . ' updated with due date ' . $nextDueDate . ' by hook updateInvoiceItemsDueDate');
	}
	
	elseif ($source === "adminarea"){
	    logActivity('Invoice ' . $invoiceId . ' unqualified for update by hook updateInvoiceItemsDueDate');
	}
		
    }
});

?>

This is the result from Activity Log.

[TBroMEM]

Incidentally, I had crafted a new hook based on AcceptOrder. It is listed below. I will be testing tonight and will document results. 

<?php

if (!defined("WHMCS"))
    die("This file cannot be accessed directly");

use Illuminate\Database\Capsule\Manager as Capsule;

/*
 * Hook to update invoice items due dates for invoice linked to new Order
 * But only if the tblinvoiceitems duedate does not equal the tblinvoice duedate.
 */

add_hook('AcceptOrder', 1, function($vars) {
    $orderId = $vars['orderid'];
    // fetch invoice id from order
    $order = Capsule::table('tblorders')->where('id', $orderId)->first();
    $orderInvoiceId = $order->invoiceid;
    // Fetch duedate from order invoice
    $invoice = Capsule::table('tblinvoices')->where('invoiceid', $orderInvoiceId)->first();
    $invoiceDueDate = $invoice->duedate;

	// Log activity
    logActivity('Invoice ' . $orderInvoiceId . ' from order ' . $order . ' selected for eval by hook acceptOrderUpdInvItemsDueDate');

    // Update the due date in tblinvoiceitems
    foreach (Capsule::table('tblinvoiceitems')->where('invoiceid', $orderInvoiceId)->get() as $line) {

 	//Vars for conditions and logging activity
	$id = $line->id;
        $description = $line->description;
	$invItemDueDate = $line->duedate;
	
	if($invoiceDueDate != $invItemDueDate) {
	    // Update due date on invoice line
	    $queryResult = Capsule::table('tblinvoiceitems')->where('id', $id)->update(['duedate' => $invoiceDueDate]);
        // Log activity
	    logActivity('Invoice ' . $orderInvoiceId . ' line with description ' . $description . ' updated with due date ' . $invoiceDueDate . ' by hook acceptOrderUpdInvItemsDueDate: due date mismatch');
	}
	
	else {logActivity('Invoice ' . $invoiceId . ' line with description ' . $description . ' not qualified by hook acceptOrderUpdInvItemsDueDate: due dates match');
	}
    }
});

?>

 

Edited July 30 by TBroMEM

[TBroMEM]

The code that at referenced in previous reply did not pass muster in testing. I did not reference the correct field identifier within the tblinvoices capsule. Should have been id not 'invoiceid'. Will be testing again. I am still not sure invoiceid is the correct column to reference in tblorders. We will see.

Below is excerpt with change highlighted.

add_hook('AcceptOrder', 1, function($vars) {
    $orderId = $vars['orderid'];
    // fetch invoice id from order
    $order = Capsule::table('tblorders')->where('id', $orderId)->first();
    $orderInvoiceId = $order->invoiceid;
    // Fetch duedate from order invoice
    $invoice = Capsule::table('tblinvoices')->where('id', $orderInvoiceId)->first();
    $invoiceDueDate = $invoice->duedate;

Edited July 31 by TBroMEM
correction

[DennisHermannsen]

tblorders has an invoiceid column. The only place you're using that column is inside tblinvoices - and that just has the "id" column.

[TBroMEM]

I had updated the php code. Once I am able to get it deployed and tested I will provide a status update.

On the flip side, is anyone awae of an addon to allow admins to deploy hooks via the Admin app?

[DennisHermannsen]

What do you mean by deploying hooks from the admin app?

[TBroMEM]

@DennisHermannsen It would be easier for an Admin to deploy a new hook (or even a new theme) via the Admin app. At the present, I have to coordinate with our IT resource to moving in changes on our WHMCS server. Maybe this is more of a direct question for WHMCS to advise if this is even feasible. 

[DennisHermannsen]

It could be done (but you'd have to develop a module to allow for that). You would need to consider the security aspect of it, though.

[TBroMEM]

Supposedly there is no module addon to aid a system admin in deploying new hooks (or even configured themes) within the admin app. This would be nice to have.