Display Custom Field in Invoice

[eliteral]

Hello everyone:

 

Here is our Action Hook which will allow you to display custom field values on the invoice.

Limitation: Only one Custom Field Value is displayed.

 

Expectations: You are expected to know the custom field id's to show

 

This is our first contribution to the community. Comments / criticisms welcome.

 

Thank you.

Best Regards,

Team eLiteral

http://www.eliteral.com

(A Web Hosting Company)

 

<?php
/*

*******************************
* Last Updated: 12-Apr-2010   *
*******************************

Type:		Action Hook

File:		UpdateInvoiceWithCustomField.php

Purpose:	Currently, WHMCS cannot display custom fields on the Invoices. 
			This script will allow you to show one custom field element on the invoice

Version:	1.00

Creator:	eLiteral - A Web Hosting Company
			[http://www.eliteral.com]

Comments:	development@eliteral.com

Install:	Upload this file in your includes/hook folder
			Edit Lines 32 and 38
*/

function UpdateInvoiceWithCustomField($vars) {

/*	
Put the fieldid which you want to display in the invoice from tblcustomfields
eg. $FieldIDs = "1,5,9,13,17,21";
*/	
$FieldIDs = "";

/*	
Put the Custom Field Name. This will be shown on the invoice
eg: $CustomFieldLabel = "Domain Name";
*/
$CustomFieldLabel = "";

/*

*******************************
No editing required below
*******************************

*/

/*	
This is the variable passed to the Action Hook
*/
$InvoiceID = $vars["invoiceid"];

/*	
Associate the type of order: (Hosting = product)
*/	
$Type = "Hosting";

/*	
Locate individual Invoice Items for the said InvoiceID
*/	
$sql_inv_items = "select relid, description from tblinvoiceitems where invoiceid=$InvoiceID and type=\"$Type\"";
$result_inv_items = mysql_query($sql_inv_items) or die("Mysql Error in getting invoice items :".mysql_error());

/* 
For each invoice item, fetch the CustomField value
*/	
while($row_inv_items = mysql_fetch_array($result_inv_items))
{
	$relid = $row_inv_items["relid"];
	$Description = $row_inv_items["description"];

	/* 
		This is looking for the CustomField from each and every product which you have setup
	*/
	$sql_CustomField = "select value from tblcustomfieldsvalues where fieldid IN ($FieldIDs) and relid=$relid";
	$result_CustomField = mysql_query($sql_CustomField) or die ("Mysql Error in getting CustomField :".mysql_error());

	$num = mysql_numrows($result_CustomField);

	if($num > 0)
	{
		$row_CustomField = mysql_fetch_array($result_CustomField);

		$CustomField = $row_CustomField["value"];
		$Description .= "\n".$CustomFieldLabel.": ".$CustomField;

		$sql_update_description = "update tblinvoiceitems set description= \"$Description\" where invoiceid=$InvoiceID and relid=$relid";
		$result_update_description = mysql_query($sql_update_description) or die("Mysql Error in updating description: ".mysql_error());
	}
} /* End while */

} /* End hook */

add_hook("InvoiceCreationPreEmail",0,"UpdateInvoiceWithCustomField");

?>

[griffe]

Do you know how this will be affected during upgrades?

[eliteral]

i am not sure how it will work. But, I am pretty positive that this being a new hook file, it will be separate from the existing files. may be someone from the forum who has worked with HOOK upgrades comment.

[4Cloud.ca]

Here is a slight modification that will also print the field name from tblcustomfields, this allowing you to add different custom fields to invoices.

 

    while($row_inv_items = mysql_fetch_array($result_inv_items)) 
   { 
       $relid = $row_inv_items["relid"]; 
       $Description = $row_inv_items["description"]; 

       /*  
           This is looking for the CustomField from each and every product which you have setup 
       */ 
       $sql_CustomField = "select tblcustomfieldsvalues.value, tblcustomfields.fieldname
		from tblcustomfieldsvalues, tblcustomfields
		where tblcustomfieldsvalues.fieldid IN ($FieldIDs) and tblcustomfieldsvalues.relid=$relid and tblcustomfieldsvalues.fieldid = tblcustomfields.id"; 
       $result_CustomField = mysql_query($sql_CustomField) or die ("Mysql Error in getting CustomField :".mysql_error()); 

       $num = mysql_numrows($result_CustomField); 

       if($num > 0) 
       { 
           $row_CustomField = mysql_fetch_array($result_CustomField); 

           $CustomField = $row_CustomField["value"]; 
		$CustomFieldLabel = $row_CustomField["fieldname"];
           $Description .= "\n".$CustomFieldLabel.": ".$CustomField; 

           $sql_update_description = "update tblinvoiceitems set description= \"$Description\" where invoiceid=$InvoiceID and relid=$relid"; 
           $result_update_description = mysql_query($sql_update_description) or die("Mysql Error in updating description: ".mysql_error()); 
       } 
   } /* End while */ 

 

Terry D

NETAGO

[agermose]

I added the file, but see nothing on the invoice - do I need to change the invoice template also or what to do? Or is it simply because it will only work on new created invovices, not the old once already in the system?

[jeroman]

Great hook but now it is a blank page when ordering inside admin and choose create invoice.

The field is added however.

[ckung]

It doesn't work on mine. I asked Matt and confirmed as followed,

 

you would have to query for if you need those. First querying tblinvoiceitems, then getting the relid, then querying tblcustomfieldsvalues with the relid from tblinvoiceitems (aka tblhosting.id) and the custom field ID you've selected from a seperate query.

 

Please help and this is nice to have so I don't have to create too many product with same custom field.

 

Thanks!