How to add quantity and unit price as separate columns on invoicing

[Ma1kavian]

Hello,

 

I am having trouble setting up my invoice properly. Not sure how to set up that Quantity and Single unit price show at separate columns when invoicing. I think I covered almost all of the general/product options in WHMCS and I couldn't get this needed output. I wouldn't bother with this normally, but in my country we have to have an invoice in this format.

 

I tried tempering with invoicepdf.tpl, and I managed to modify it mostly so that it could output the pdf invoice toward user the way we need it. I just can't manage for quantity and unit price to show in separate columns. I looked up in database and I could't find any record for quantity in $invoiceditems. If I could manage to get quantity from the DB I could easily get unit price by dividing the total amount with quantity.

 

I included a picture as an sample for you to understand better what I need.

 

Is there nay way that you can help me?

 

Thank you!

Dusan

[Vivek Nair]

Hello,

           As you mentioned, there are no direct ways to include the quantity. One way you can do is that for each product even if it's the same one qty should be 1 and it can be show in a different row which you might already know. Another option you have is to add a configurable option from Setup -> Product/Service -> Configurable option. There you can set option for quantity for products. You might have to use hooks to override  the carttotal but you will have this quantity value in the db in tblhostingconfigoptions. 

Hope it helps.

Regards

[brian!]

as i've done this commercially for a number of clients, I can't (won't!) really describe the detailed method publicly here, but I will say that @Ma1kavian is closer to the solution that he knows... the database is always your friend!

1 hour ago, Vivek Nair said:

You might have to use hooks to override  the carttotal but you will have this quantity value in the db in tblhostingconfigoptions. 

you can't use hooks in in the invoicepdf.tpl template - it's effectively a PHP file anyway, so anything you want to do, including interacting with the database, you can do directly in the template file.

as for the rest of your suggestion e.g using configurable options, or assuming quantities etc, they're unnecessary and overcomplicated steps.

[Ma1kavian]

Thanks for the for of encouragement @brian!.!

I managed to solve this. Didn't use any additional info from the database (I am not experienced in php in order to do so confidently), but I managed to extract quantity number and unit price from the items description. I was using various php functions in order to do that. Now my pdf output is mostly satisfied for the customers.

One thing that is troubling me now is to add exchange rate of currency on the invoice. Coincidentally I found the hook on the forums made by you @brian!, but reading through your post now I see that I can't apply hooks on theinvoicepdf.tpl. Good to know, I will try something else then.

 

Either way, thx for the help guys.

Sincerely,

Dusan

[brian!]

38 minutes ago, Ma1kavian said:

I managed to solve this. Didn't use any additional info from the database (I am not experienced in php in order to do so confidently), but I managed to extract quantity number and unit price from the items description. I was using various php functions in order to do that. Now my pdf output is mostly satisfied for the customers.

aaah so you have Group Similar Line Items enabled in your general settings!

39 minutes ago, Ma1kavian said:

One thing that is troubling me now is to add exchange rate of currency on the invoice. Coincidentally I found the hook on the forums made by you @brian!, but reading through your post now I see that I can't apply hooks on theinvoicepdf.tpl. Good to know, I will try something else then.

just to clarify, the invoicepdf.tpl file doesn't use external hooks, but the code within hooks (with slight tweaks) can be used directly in the template file itself...

so without looking up the hook you're talking about, it was probably just a basic query to the database using capsule - if so, you can certainly use that code directly in the invoicepdf.tpl template... if you come unstuck trying to do it, reply back here and i'll tweak the above hook code for you.

[Ma1kavian]

1 hour ago, brian! said:

aaah so you have Group Similar Line Items enabled in your general settings!

Yeah, and I basically used the 'if' function in order to separate items those that have quantity and ones that are single, and from that I managed to make strings to fit my table. I attached the example:

I understood the hook situation. You make hook in php so that you could call it in the tpl files written in html. I do have a problem doing the same for the invoicepdf.tpl. Not sure what needs to be tweaked in order for code to display $exchangerate on the invoice.

Here is the code from hook I used:

<?php

use Illuminate\Database\Capsule\Manager as Capsule;
function hook_currency_exchange_rates($vars) 
{
   $exchangerate = Capsule::table('tblcurrencies')
               ->where('code', 'RSD')
               ->get();

   return array("exchangerate" => $exchangerate[0]->rate);
}
add_hook("ClientAreaPage", 1, "hook_currency_exchange_rates");
?>

I just changed "where" in order to get the exchange rate from currency I need because I have multiple of them defined.

This is what I have tried to place in invoicepdf.tpl:

use Illuminate\Database\Capsule\Manager as Capsule;
  
function currency_exchange_rates($vars) 
{
   $exchangerate = Capsule::table('tblcurrencies')
               ->where('code', 'RSD')
               ->get();

   return array("exchangerate" => $exchangerate[0]->rate);
}

 

As I understood Capsule is an API to call info from database, not sure if I should use Capsule or PDO in order to do the same in invoicepdf.tpl. If you can help it would be greatly appreciated!

 

[Ma1kavian]

Ok, I figured this out. I managed to pull exchange rate value from the database. Thanks for the help and thanks for leading me into the right direction! On the other note I learned how to fetch values from the database

[brian!]

23 hours ago, Ma1kavian said:

This is what I have tried to place in invoicepdf.tpl:

that's close, but you don't need to use a function or return the array, so it would basically be the following inside your invoicepdf.tpl file (i'm assuming you're using WHMCS v7 or later)...

if ($status == 'Paid') {
    $exchangerate = Capsule::table('tblaccounts')->where('invoiceid', $id)->value('rate');
    }
 else {
    $exchangerate = Capsule::table('tblcurrencies')->where('code', 'RSD')->value('rate');
 }

that would give you an $exchangerate variable to output wherever you want...

or to use in calculations to convert the total to your default currency...

at this point, it's probably important why we're querying one of 2 database tables and the difference between them.

• if an invoice has been paid, the exchange rate used at the time of payment is stored in the tblaccounts table. (image1)
• at any other time (unpaid, cancelled etc), you can only obtain TODAY'S current exchange rate from the tblcurrencies table (image2) - so when the order/invoice is generated, it will be correct... but by the time they pay, it might be a different rate used in tblaccounts (e.g if you have automatic currency updating enabled in your settings) - but if you're using Serbian Dinar, then that probably doesn't apply to you as I don't think the automatic currency update includes RSD as an option - and I assume that you are updating the rate manually.
• also, it's worth knowing that PDF invoices are generated on the fly using the current database values, so if a client logs into their client area and views an unpaid PDF invoice, it would use the exchange rate from today and not the day the invoice was generated - old exchange rates are not stored within WHMCS by default... however, if a client is sent an unpaid invoice by email, then the exchange rate shown in the PDF will remain the same.

hopefully that makes some sense to you.

[Ma1kavian]

This the code that I used:

$exchangerate = Capsule::table('tblcurrencies')->where('code', 'RSD')->value('rate');

As you said, I am updating currency manually for now, so I don't need the 'if' function just yet. But it is good to have it here, thanks!

Also thanks for the clarification on how the exchange rate is stored in the database. Didn't know that.

[brian!]

9 hours ago, Ma1kavian said:

As you said, I am updating currency manually for now, so I don't need the 'if' function just yet. But it is good to have it here, thanks!

the IF statement was more to ensure when a client is looking at a paid invoice, they're seeing the exchange rate value that was used, and not todays (which could be 3 years from now) rate.

[Didaka]

On 10/6/2017 at 5:23 PM, brian! said:

that's close, but you don't need to use a function or return the array, so it would basically be the following inside your invoicepdf.tpl file (i'm assuming you're using WHMCS v7 or later)...

 

if ($status == 'Paid') {
    $exchangerate = Capsule::table('tblaccounts')->where('invoiceid', $id)->value('rate');
    }
 else {
    $exchangerate = Capsule::table('tblcurrencies')->where('code', 'RSD')->value('rate');
 }

 

that would give you an $exchangerate variable to output wherever you want...

or to use in calculations to convert the total to your default currency...

at this point, it's probably important why we're querying one of 2 database tables and the difference between them.

• if an invoice has been paid, the exchange rate used at the time of payment is stored in the tblaccounts table. (image1)
• at any other time (unpaid, cancelled etc), you can only obtain TODAY'S current exchange rate from the tblcurrencies table (image2) - so when the order/invoice is generated, it will be correct... but by the time they pay, it might be a different rate used in tblaccounts (e.g if you have automatic currency updating enabled in your settings) - but if you're using Serbian Dinar, then that probably doesn't apply to you as I don't think the automatic currency update includes RSD as an option - and I assume that you are updating the rate manually.
• also, it's worth knowing that PDF invoices are generated on the fly using the current database values, so if a client logs into their client area and views an unpaid PDF invoice, it would use the exchange rate from today and not the day the invoice was generated - old exchange rates are not stored within WHMCS by default... however, if a client is sent an unpaid invoice by email, then the exchange rate shown in the PDF will remain the same.

hopefully that makes some sense to you.

Thank you for this guide the exchange rate part is working fine, but I am struggling to add "GBP Total field". Can you please guide me on how to add Exchange rate total?

Do I have to add something like the below statement in invoicepdf.tpl;

$exchangeratetotal = Capsule::table('tblcurrencies')->where('code', 'GBP')->value('total');

Your help will be appreciated.

[VITEX]

Hi there,

I am looking for a smiliar solution and would appreciate help.

We are also selling one-offs like Hardware and need to create custom invoices with the quantity shown (e.g. Monitor - Qty 3 - Price per item - total.

Is this possible or do you know a plugin ?

Best regards