Fetch data from the table and insert in the email template.

[fabianbur]

I suppose this is simple, but it is making my life difficult.

I need in "Invoice Payment Confirmation" Include a value from authdata field of tblclients table.

Here I have searched and maybe something similar to this hook is the way

<?php

use WHMCS\Database\Capsule;

add_hook('EmailPreSend', 1, function($vars)
{
    if (in_array($vars['messagename'], array('Invoice Payment Confirmation')))
    {
        $codigopay = Capsule::select(Capsule::raw('SELECT authdata FROM tblclients WHERE id = "' . $vars['relid'] . '"));

        foreach ($codigopay as $v)
        {
            $merge_fields['code_payme'][] = $v->authdata;
        }

        return $merge_fields;
    }
});

Thank you very much for the help.

 

[fabianbur]

This seems easier, but it still doesn't work

<?php
use WHMCS\Database\Capsule;

add_hook("EmailPreSend", 1, function($vars){

   return array("auth_code" => Capsule::select(Capsule::raw('SELECT authdata FROM tblclients WHERE id = "' . $vars['relid'] . '"));

});

I have inserted {$auth_code} in email template

[brian!]

On 16/07/2021 at 04:25, fabianbur said:

This seems easier, but it still doesn't work

you still need to specify that it only works for the Invoice Payment Confirmation email, but where you're going wrong is that you are assuming that relid = client ID, but in an invoice email, relid = invoice ID.

you can use the invoice ID to get the client ID, or almost certainly there will be an existing mergefield that stores the clientid (I think it's literally called clientid, but i'm going from memory on that)... if you need to query the db for it...

you can ignore the $overdueinvoices line from the code and change it to get your auth code value.

[sylviareidvq]

On 7/16/2021 at 8:55 AM, fabianbur said:

This seems easier, but it still doesn't work

<?php
use WHMCS\Database\Capsule;

add_hook("EmailPreSend", 1, function($vars){ tellpopeyes

   return array("auth_code" => Capsule::select(Capsule::raw('SELECT authdata FROM tblclients WHERE id = "' . $vars['relid'] . '"));

});

I have inserted {$auth_code} in email template

You can insert relational table data into your email template by ... This combined with the Max rows means you will retrieve the most recent.