Merge Fields - Total number of oustanding invoices

[HR_UK]

Hi All,

 

Is there a merge field for the total number of outstanding invoices the client has?

 

I'm aware of {$client_due_invoices_balance} but looking for number of items, not the balance.

 

On a side note - The online documentation http://docs.whmcs.com/Email_Templates#Merge_Fields is particuarly useless. Surely this would be the perfect page to list all of the merge files that WHMCS posseses along with a list of what type of email template it will work with rather than having to guess / ask on here / look at snippets at bottom of each default template.

 

 

Thanks, Mark

[sentq]

Mark,

 

MergeFields is the fields you can specify by ActionHook:PreEmailSend, the variables available at the bottom of template can be differ based on what type of email template you are editing (General, Admin, Products/Services, etc), also any 3rd party can provide specific mergefields there to specific type or email template.

[HR_UK]

Hi,

 

I use the API but cannot understand how to get started with hooks.

 

I have used the WHMCS example from http://docs.whmcs.com/Hooks:EmailPreSend

and copied it into a file called "email_custom_variables.php" inside includes/hooks.

 

Then I have added {$my_custom_var} to the bottom of my "invoice created" template. But it shows nothing.

 

From the limited documentation, I am assuming that WHMCS just runs all the php files in the include directory so should be immediately 'active'?

 

Thanks, Mark

[brian!]

Mark,

 

take a look at the thread below...

 

https://forum.whmcs.com/showthread.php?98066-Hooks-and-or-email-merge-fields-for-custom-urls

 

looking at the documentation code, it's not specifying the hook (don't know if you remembered to add that bit in to your code?) - but try some of the examples in the thread above as a starting point.

[HR_UK]

Brilliant - Got it working now thank you.

 

How do I read the value of something from the template/email in the hook? I need the hook to know what client ID I am sending an invoice for so that I can goto the API to request the relevant information from the API.

 

Thanks, Mark

[sentq]

How do I read the value of something from the template/email in the hook? I need the hook to know what client ID I am sending an invoice for so that I can goto the API to request the relevant information from the API.

based on what type of email template you are sending, you will need to query database to get clientid

Invoices => tblinvoices.userid

products => tblhosting.userid,

etc

[HR_UK]

But I have nothing to query it with?

 

I understand how to add to the template with the hook, but how can I get /anything/ from the email template?

 

I need to get something, so I have a basis to query for the rest. i.e. I need it to have the invoice number or client ID. Then obviously with either of these I can get everything from the DB.

[HR_UK]

I got it

 

$vars['relid']

 

gives me the invoice id... I can work it all backwards now with the api Thanks for your help.

[sentq]

I got it

 

$vars['relid']

 

gives me the invoice id... I can work it all backwards now with the api Thanks for your help.

yes the "relid" is what I mentioned, read this:

http://docs.whmcs.com/API:Send_Email#Required_Attributes

[HR_UK]

Thanks guys.

 

For anyone wanting the complete result to show the number of outstanding invoices for a client, here it is (also useful example code of a basic hook + sql look up)

 

 

 

<?php
if (!defined("WHMCS"))	{
   die("This file cannot be accessed directly");	}

function hook_emailvars($vars) {

$root_path = substr ($_SERVER["DOCUMENT_ROOT"], 0, strpos ($_SERVER["DOCUMENT_ROOT"], "/public_html") + 12);

require ($root_path."/client/configuration.php");

$dblink = mysqli_connect($db_host, $db_username, $db_password);
mysqli_select_db ($dblink, $db_name);

$temp = mysqli_fetch_row (mysqli_query ($dblink, "SELECT userid FROM `tblinvoices` WHERE id=".$vars['relid']." LIMIT 1;") );

$invoice_qty = mysqli_num_rows (mysqli_query ($dblink, "SELECT id FROM `tblinvoices` WHERE userid='$temp[0]' AND status LIKE 'Unpaid';") );
if (!is_numeric ($invoice_qty) )	{	$invoice_qty = 0;	}
mysqli_close ($dblink);


$merge_fields = array();
$merge_fields['client_due_invoices_qty'] = $invoice_qty;


return $merge_fields;
}


add_hook("EmailPreSend",1,"hook_emailvars");				// Make the Custom Variables		
add_hook("EmailTplMergeFields",1,"hook_emailvars");			// Merge them onto the tpl so shown on edit template page

?>

[brian!]

you could also use Capsule to access the database, which would remove the need to use the configuration file in your code.

 

<?php

use Illuminate\Database\Capsule\Manager as Capsule;

function hook_emailvars($vars) {

   $userid = Capsule::table('tblinvoices')
               ->where('id',$vars['relid'])
               ->pluck('userid');

   $invoice_qty = Capsule::table('tblinvoices')
               ->where('userid',$userid)
               ->where('status','Unpaid')
               ->count();            

   $merge_fields = array();
   $merge_fields['client_due_invoices_qty'] = $invoice_qty;

   return $merge_fields;
}
add_hook("EmailPreSend",1,"hook_emailvars");                // Make the Custom Variables        
add_hook("EmailTplMergeFields",1,"hook_emailvars");            // Merge them onto the tpl so shown on edit template page
?>

note - when using on v7, you might need to change pluck to value.