Custom Client Fields in PDF

[majorwebhosting]

Hi,

 

I have emplemented WHMCS on my system, but according to Belgian law I have to add the clients VAT number on his invoices. I have added the VAT field as a "Custom Client Field", but cannot find a way to get this on the PDF invoice. What variable can I call (and with what parameters) in pdfconfig.php to get this information on the document?

 

Thanx

Miguel

[Brett]

You will have to query the database directly. I am no expert so I will let someone else answer this question.

[quel]

You can use this.

 

function myVATNumber($myclientemail){
//$myclientemail can contain the user id or the user email;
global $db_name,$db_host, $db_username,$db_password;
$myVAT = "";

if(!$myclientemail) return "";

$link = mysql_connect($db_host, $db_username,$db_password) or  die('Could not connect: ' . mysql_error());
mysql_select_db($db_name, $link) or die('Could not select database.');

$sql="SELECT B.value from tblclients A, tblcustomfieldsvalues B WHERE B.fieldid=14 and B.relid=A.id and (A.email='$myclientemail' or A.id='$myclientemail')";
$result = mysql_query($sql) or   die('Invalid query: ' . mysql_error());
if(mysql_num_rows($result)>0)
{
	$row = mysql_fetch_array($result);
	if(strlen($row['value'])>0)	$myVAT = $row['value'];
}

mysql_close($link);

return $myVAT;
}

 

NOTE: the field 14 is my custom one for VAT. in your whmcs that may change, so look at the id where VAT is stored using phpmyadmin and change the number to whatever is ok (B.fieldid=14 <-- this number)

 

Inside the file pdfconfig.php you can just put this function on the top, then add:

 

      if($clientsdetails['email']=='')
               $m=$clientsdetails['id'];
       else
               $m=$clientsdetails['email'];

         //get the VAT from this customer
       $myVATNumber=myVATNumber($m);

 

 

Finally, do substitute the clients details for the code:

 

# Clients Details
$pdf->Cell(0,4,$_LANG["invoicesinvoicedto"],0,1);
$pdf->SetFont('Arial','',;
if ($clientsdetails["companyname"]) {
       $pdf->Cell(0,4,$clientsdetails["companyname"],0,1,'L');
       $pdf->Cell(0,4,$_LANG["invoicesattn"].": ".$clientsdetails["firstname"]." ".$clientsdetails["lastname"],0,1,'L'); } e$
       $pdf->Cell(0,4,$clientsdetails["firstname"]." ".$clientsdetails["lastname"],0,1,'L');
}
$pdf->SetFont('Arial','',;
$pdf->Cell(0,4,$clientsdetails["address1"],0,1,'L');
if ($clientsdetails["address2"]) {
       $pdf->Cell(0,4,$clientsdetails["address2"],0,1,'L');
}
$pdf->Cell(0,4,$clientsdetails["city"].", ".$clientsdetails["state"].", ".$clientsdetails["postcode"],0,1,'L');
$pdf->Cell(0,4,$clientsdetails["country"],0,1,'L');
$pdf->Cell(0,4,"VAT/CIF:".$myVATNumber,0,1,'L');
$pdf->Ln(11);

 

Enjoy.

 

Matt, now that you have done the proforma invoice for EU companies (chapeau on that. TX A LOT. You're the 1rst US guy that cares to understand the difficulties we have at the other side of the lake), maybe you could add the VAT number as a default entry, since this is as well required. With VAT number on it, the pdf file could be used directly as invoice to the customer.

 

Q

[awardle]

This would be great if VAT numbers could be added to Invoices if setup, for the time being I will try this mod though.

 

Thanks

[majorwebhosting]

I was wondering already if I was the first user having this problem. ;-)

Would indeed be a great feature for future releases.

Quel, thanx for your solution, I am going to give it a try right away.

Happy new year!

[awardle]

I misread this post last night, I just need to display my VAT number on all invoices it would be good if this feature can be added in, plus the one you need.

 

Thanks

Aaron

[JofleyUK]

Maybe this is too simple for you guys, I added the VAT number after the Address (in the same box) in the company details. No complex editing. So long as the company info doesn't go over 4 lines it appears on the standard and pdf invoice.

[awardle]

Yep I have done the same, but it would be nice for it to auto insert if enabled in WHCMS then you don't have to worry in the future when you perform an upgrade.

[itboss]

You can use this.

 

function myVATNumber($myclientemail){
//$myclientemail can contain the user id or the user email;
global $db_name,$db_host, $db_username,$db_password;
$myVAT = "";

if(!$myclientemail) return "";

$link = mysql_connect($db_host, $db_username,$db_password) or  die('Could not connect: ' . mysql_error());
mysql_select_db($db_name, $link) or die('Could not select database.');

$sql="SELECT B.value from tblclients A, tblcustomfieldsvalues B WHERE B.fieldid=14 and B.relid=A.id and (A.email='$myclientemail' or A.id='$myclientemail')";
$result = mysql_query($sql) or   die('Invalid query: ' . mysql_error());
if(mysql_num_rows($result)>0)
{
	$row = mysql_fetch_array($result);
	if(strlen($row['value'])>0)	$myVAT = $row['value'];
}

mysql_close($link);

return $myVAT;
}

 

NOTE: the field 14 is my custom one for VAT. in your whmcs that may change, so look at the id where VAT is stored using phpmyadmin and change the number to whatever is ok (B.fieldid=14 <-- this number)

 

Inside the file pdfconfig.php you can just put this function on the top, then add:

 

      if($clientsdetails['email']=='')
               $m=$clientsdetails['id'];
       else
               $m=$clientsdetails['email'];

         //get the VAT from this customer
       $myVATNumber=myVATNumber($m);

 

 

Finally, do substitute the clients details for the code:

 

# Clients Details
$pdf->Cell(0,4,$_LANG["invoicesinvoicedto"],0,1);
$pdf->SetFont('Arial','',;
if ($clientsdetails["companyname"]) {
       $pdf->Cell(0,4,$clientsdetails["companyname"],0,1,'L');
       $pdf->Cell(0,4,$_LANG["invoicesattn"].": ".$clientsdetails["firstname"]." ".$clientsdetails["lastname"],0,1,'L'); } e$
       $pdf->Cell(0,4,$clientsdetails["firstname"]." ".$clientsdetails["lastname"],0,1,'L');
}
$pdf->SetFont('Arial','',;
$pdf->Cell(0,4,$clientsdetails["address1"],0,1,'L');
if ($clientsdetails["address2"]) {
       $pdf->Cell(0,4,$clientsdetails["address2"],0,1,'L');
}
$pdf->Cell(0,4,$clientsdetails["city"].", ".$clientsdetails["state"].", ".$clientsdetails["postcode"],0,1,'L');
$pdf->Cell(0,4,$clientsdetails["country"],0,1,'L');
$pdf->Cell(0,4,"VAT/CIF:".$myVATNumber,0,1,'L');
$pdf->Ln(11);

 

Enjoy.

 

Matt, now that you have done the proforma invoice for EU companies (chapeau on that. TX A LOT. You're the 1rst US guy that cares to understand the difficulties we have at the other side of the lake), maybe you could add the VAT number as a default entry, since this is as well required. With VAT number on it, the pdf file could be used directly as invoice to the customer.

 

Q

 

Very nice script!! But I don't know where to put the second part of the script?

 

"

 

      if($clientsdetails['email']=='')
               $m=$clientsdetails['id'];
       else
               $m=$clientsdetails['email'];

         //get the VAT from this customer
       $myVATNumber=myVATNumber($m);

"

 

Do I put this also at the top? Outside the bracket of the first part?

 

Thanks

[dutchnet]

To print all your company legal details in the footer on an invoice you can change the footer code to be something like this

 

# Footer
$pdf->SetMargins(15, 27, 15); // left, top, right
$pdf->SetAutoPageBreak(TRUE, 5); // TRUE, margin down
$pdf->SetFooterMargin(5);
$pdf->setPrintHeader(false);
$pdf->setPrintFooter(false);

// Factuur footer
$pdf->SetXY(15,-15); # Set Location
//Set font
$pdf->SetFont('Arial', '', 6.5); # Use 6.5pt Text
$pdf->SetTextColor(128);

//Centered text in a framed 80*5 mm cell and line break
$pdf->MultiCell(180, 3, "DutchNet - Bilderdijklaan 112 A - 3723 DG Bilthoven\nKvK xxxxxxxx - VAT nr: NL.xxxxxxxxx.x.xx - Bank Account xxxxxxxxxxxxx - SWIFT / BIC code PSTBNL21 - IBAN xx xx xxxx xxxx xxxx xx\nPut your disclaimer here. maybe with a link to your home page", 0, 'C');

?>