• From: Italy
  • Registered: 11/18/2013
  • Posts: 19

Topic: need retrieving tax rate per item during invoice generation

Hi all!
I have been browsing forum and (with my little knowledge) the source code to understand how I could retrieve, during the generation of rep107, the tax rate per each item to include it in the invoice (that is a requirement locally).
I cannot "guess" the rate by reverse calculation due to rounding of numbers.
e.g. an item with VAT 4% priced 1.58 EUR with a discount of 25% will generate values that, applied to the reverse calculation of VAT, will return 3.92% (which is obviously wrong).
While that is correct in GL, because the needed rounding of numbers, that is of no value to print the rate used originally.
How can I retrieve that value?
Any SQL you might suggest?
Any help will be highly appreciated because I am totally stuck without such info.
Thanks a lot to all for the great job and support.
Carlo

  • dls
  • Senior Member
  • Offline
  • From: UK
  • Registered: 02/23/2010
  • Posts: 63

Re: need retrieving tax rate per item during invoice generation

Hi Carlo

Try the one below. From what you say I think it should fit your requirements. - It is available elsewhere on this forum.


<?php
/**********************************************************************
    Copyright (C) FrontAccounting, LLC.
    Released under the terms of the GNU General Public License, GPL,
    as published by the Free Software Foundation, either version 3
    of the License, or (at your option) any later version.
    This program is distributed in the hope that it will be useful,
    but WITHOUT ANY WARRANTY; without even the implied warranty of
    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. 
    See the License here <http://www.gnu.org/licenses/gpl-3.0.html>.
***********************************************************************/
$page_security = $_POST['PARAM_0'] == $_POST['PARAM_1'] ?
    'SA_SALESTRANSVIEW' : 'SA_SALESBULKREP';
// ----------------------------------------------------------------
// $ Revision:    2.0 $
// Creator:    Joe Hunt
// date_:    2005-05-19
// Title:    Print Invoices
// ----------------------------------------------------------------
$path_to_root="..";

include_once($path_to_root . "/includes/session.inc");
include_once($path_to_root . "/includes/date_functions.inc");
include_once($path_to_root . "/includes/data_checks.inc");
include_once($path_to_root . "/sales/includes/sales_db.inc");

//----------------------------------------------------------------------------------------------------

print_invoices();

//----------------------------------------------------------------------------------------------------

function print_invoices()
{
    global $path_to_root, $alternative_tax_include_on_docs, $suppress_tax_rates, $no_zero_lines_amount;
   
    include_once($path_to_root . "/reporting/includes/pdf_report.inc");

    $from = $_POST['PARAM_0'];
    $to = $_POST['PARAM_1'];
    $currency = $_POST['PARAM_2'];
    $email = $_POST['PARAM_3'];
    $pay_service = $_POST['PARAM_4'];
    $comments = $_POST['PARAM_5'];
    $customer = $_POST['PARAM_6'];
    $orientation = $_POST['PARAM_7'];

    if (!$from || !$to) return;

    $orientation = ($orientation ? 'L' : 'P');
    $dec = user_price_dec();

    $fno = explode("-", $from);
    $tno = explode("-", $to);
    $from = min($fno[0], $tno[0]);
    $to = max($fno[0], $tno[0]);

    $cols = array(4, 60, 225, 300, 325, 375, 425, 465, 515);

    // $headers in doctext.inc
    $aligns = array('left',    'left',    'right', 'left', 'right', 'right', 'right', 'right');

    $params = array('comments' => $comments);

    $cur = get_company_Pref('curr_default');

    if ($email == 0)
        $rep = new FrontReport(_('INVOICE'), "InvoiceBulk", user_pagesize(), 9, $orientation);
    if ($orientation == 'L')
        recalculate_cols($cols);
    for ($i = $from; $i <= $to; $i++)
    {
            if (!exists_customer_trans(ST_SALESINVOICE, $i))
                continue;
            $sign = 1;
            $myrow = get_customer_trans($i, ST_SALESINVOICE);

            if($customer && $myrow['debtor_no'] != $customer) {
                continue;
            }
            $baccount = get_default_bank_account($myrow['curr_code']);
            $params['bankaccount'] = $baccount['id'];

            $branch = get_branch($myrow["branch_code"]);
            $sales_order = get_sales_order_header($myrow["order_"], ST_SALESORDER);
            if ($email == 1)
            {
                $rep = new FrontReport("", "", user_pagesize(), 9, $orientation);
                $rep->title = _('INVOICE');
                $rep->filename = "Invoice" . $myrow['reference'] . ".pdf";
            }   
            $rep->SetHeaderType('Header2');
            $rep->currency = $cur;
            $rep->Font();
            $rep->Info($params, $cols, null, $aligns);

            $contacts = get_branch_contacts($branch['branch_code'], 'invoice', $branch['debtor_no'], true);
            $baccount['payment_service'] = $pay_service;
            $rep->SetCommonData($myrow, $branch, $sales_order, $baccount, ST_SALESINVOICE, $contacts);
            $rep->NewPage();
               $result = get_customer_trans_details(ST_SALESINVOICE, $i);
            $SubTotal = 0;
            while ($myrow2=db_fetch($result))
            {
                if ($myrow2["quantity"] == 0)
                    continue;

                $Net = round2($sign * ((1 - $myrow2["discount_percent"]) * $myrow2["unit_price"] * $myrow2["quantity"]),
                   user_price_dec());
                $SubTotal += $Net;
                $DisplayPrice = number_format2($myrow2["unit_price"],$dec);
                $DisplayQty = number_format2($sign*$myrow2["quantity"],get_qty_dec($myrow2['stock_id']));
            $DisplayTaxRate = number_format2(abs($myrow2["unit_tax"]) * 100 / abs($myrow2["unit_price"]),user_percent_dec()) . "%";
                $DisplayNet = number_format2($Net,$dec);
                if ($myrow2["discount_percent"]==0)
                      $DisplayDiscount ="";
                else
                      $DisplayDiscount = number_format2($myrow2["discount_percent"]*100,user_percent_dec()) . "%";
                $rep->TextCol(0, 1,    $myrow2['stock_id'], -2);
                $oldrow = $rep->row;
                $rep->TextColLines(1, 2, $myrow2['StockDescription'], -2);
                $newrow = $rep->row;
                $rep->row = $oldrow;
                if ($Net != 0.0 || !is_service($myrow2['mb_flag']) || !isset($no_zero_lines_amount) || $no_zero_lines_amount == 0)
                {
                    $rep->TextCol(2, 3,    $DisplayQty, -2);
                    $rep->TextCol(3, 4,    $myrow2['units'], -2);
                    $rep->TextCol(4, 5,    $DisplayPrice, -2);
                    $rep->TextCol(5, 6,    $DisplayDiscount, -2);
                    $rep->TextCol(6, 7,    $DisplayTaxRate, -2);
                    $rep->TextCol(7, 8,    $DisplayNet, -2);
                }   
                $rep->row = $newrow;
                //$rep->NewLine(1);
                if ($rep->row < $rep->bottomMargin + (15 * $rep->lineHeight))
                    $rep->NewPage();
            }

            $memo = get_comments_string(ST_SALESINVOICE, $i);
            if ($memo != "")
            {
                $rep->NewLine();
                $rep->TextColLines(1, 5, $memo, -2);
            }

               $DisplaySubTot = number_format2($SubTotal,$dec);
               $DisplayFreight = number_format2($sign*$myrow["ov_freight"],$dec);

            $rep->row = $rep->bottomMargin + (15 * $rep->lineHeight);
            $doctype = ST_SALESINVOICE;

            $rep->TextCol(3, 6, _("Sub-total"), -2);
            $rep->TextCol(7, 8,    $DisplaySubTot, -2);
            $rep->NewLine();
            $rep->TextCol(3, 6, _("Shipping"), -2);
            $rep->TextCol(7, 8,    $DisplayFreight, -2);
            $rep->NewLine();
            $tax_items = get_trans_tax_details(ST_SALESINVOICE, $i);
            $first = true;
            while ($tax_item = db_fetch($tax_items))
            {
                if ($tax_item['amount'] == 0)
                    continue;
                $DisplayTax = number_format2($sign*$tax_item['amount'], $dec);
               
                if (isset($suppress_tax_rates) && $suppress_tax_rates == 1)
                    $tax_type_name = $tax_item['tax_type_name'];
                else
                    $tax_type_name = $tax_item['tax_type_name']." (".$tax_item['rate']."%) ";

                if ($tax_item['included_in_price'])
                {
                    if (isset($alternative_tax_include_on_docs) && $alternative_tax_include_on_docs == 1)
                    {
                        if ($first)
                        {
                            $rep->TextCol(3, 6, _("Total Tax Excluded"), -2);
                            $rep->TextCol(7, 8,    number_format2($sign*$tax_item['net_amount'], $dec), -2);
                            $rep->NewLine();
                        }
                        $rep->TextCol(3, 6, $tax_type_name, -2);
                        $rep->TextCol(7, 8,    $DisplayTax, -2);
                        $first = false;
                    }
                    else
                        $rep->TextCol(3, 7, _("Included") . " " . $tax_type_name . _("Amount") . ": " . $DisplayTax, -2);
                }
                else
                {
                    $rep->TextCol(3, 6, $tax_type_name, -2);
                    $rep->TextCol(7, 8,    $DisplayTax, -2);
                }
                $rep->NewLine();
            }

            $rep->NewLine();
            $DisplayTotal = number_format2($sign*($myrow["ov_freight"] + $myrow["ov_gst"] +
                $myrow["ov_amount"]+$myrow["ov_freight_tax"]),$dec);
            $rep->Font('bold');
            $rep->TextCol(3, 6, _("TOTAL INVOICE"), - 2);
            $rep->TextCol(7, 8, $DisplayTotal, -2);
            $words = price_in_words($myrow['Total'], ST_SALESINVOICE);
            if ($words != "")
            {
                $rep->NewLine(1);
                $rep->TextCol(1, 7, $myrow['curr_code'] . ": " . $words, - 2);
            }
            $rep->Font();
            if ($email == 1)
            {
                $rep->End($email);
            }
    }
    if ($email == 0)
        $rep->End();
}

?>

  • From: Italy
  • Registered: 11/18/2013
  • Posts: 19

Re: need retrieving tax rate per item during invoice generation

Thank you dls.
Unfortunately this is not sufficient.
Let me better explain:

Let's start with the result.
See here a new template for invoice
(I am sorry it's in dual language at the moment so I provide only the landscape version and I apologise using my company details on that)

http://printdemo.gelatiflor.net/wrong_invoice.pdf

Some background:
We have 5 VAT rates used in this invoice, specifically 22%, 10%, 4%, 0% for a reason, 0% for another reason.

The 2 zero rate items are fictionary and they refer to a sale outside EU (E1) and to a sale within EU (E2).

The items are self explanatory and, for the example, are named A22, A10, A4, E1 and E2 (incidentally they are all services so I didn't have to bother with inventory for each of the numerous tests I did)

My goal was to show the VAT rate per each item in the invoice and then, at the end of the invoice, show a summary of all VAT rates involved, with a sum of taxable amount and a total of taxes amounts.

I already managed to have the summary adjust its size according to the number of lines it contains. No big deal but I hope that the layout of the invoice is more readable and more attractive this way.

The solution you provide (which you correctly state is taken from elsewhere in the Forum) shows the VAT rate (or better say the tax rate) with a reverse calculation based on taxable amount and tax amount.

That is what I used too.

Now look at the last line of page 2.

I intentionally used a very low value as the price for a given item.
Specifically 1.58
Then I applied a discount of 25%
on the net price I calculated 4% of VAT.
These are the non rounded results:
           
Net price   
1.185
Tax   
0.0474
Total   
1.2324
Naturally, if I reverse calculate the tax, it shows 4.00%
If I round values to cents (as it is for the far majority of cases) these are the results:
           
Net price   
1.19
Tax
0.05   
Total   
1.24
These values are more than fine for the GL as rounding is a necessity.
If I now try and calculate the tax percentage with the formula that is e.g. in rep107 (untouched) which shows:
$DisplayTaxRate = number_format2($myrow2["unit_tax"] * 100 / $myrow2["unit_price"], user_percent_dec()) . "%";

The result is shown there and it's obviously wrong.

At first I thought to create or use an already created function to retrieve the tax rate for each item in the invoice and use that to print the associated tax rate.

Bear in mind that this is just to show the rate on the invoice, not to change the posts on GL because they are correct and rounded as they should.

If I increase the number of decimals in my preferences I get the right result but I cannot show amounts wiht less than cents (or pence, if you like) because that would be obviously wrong for accounting and VAT reports (of whichever nature they were).
Using a function could be a solution if tax rates were not subjected to changes from time to time.
(imagine UK VAT 17.5% increased to 19%: each re-print of the invoice would show the wrong tax rates)

That means that the tax rate must be filed with the value AT THE TIME OF ORIGINAL TRANSACTION.

The simplest solution, which would NOT bother the DB but in a very minimal way, would be –to my humble opinion- to add a field, say taxrate, in debtor_trans_details.

No big deal, I guess.

Nevertheless that would be a sort of problem in future updates or versions of FA.

As you can see, my invoice template is compliant with EU vat rules and, presumably, also with the UK ones.

I am relatively new to the forum and do not even know where or to whom I should ask for these relatively small changes.

Any hint on how to solve the problem?

Sorry for the lengthy replay but I needed to fully explain the context.

Best and thanks again,

Carlo

4 Reply by dls (edited by dls 03/11/2015 11:29:38 pm)

  • dls
  • Senior Member
  • Offline
  • From: UK
  • Registered: 02/23/2010
  • Posts: 63

Re: need retrieving tax rate per item during invoice generation

Carlo
I see the problem. The required data is available in the trans_tax_details table which is accessed lower down in the report -get_trans_tax_details(ST_SALESINVOICE, $I)   and the data is used -(".$tax_item['rate']."%)

One approach is therefore to get that data and incorporate appropriately. I am working on other things at the moment, but I would look further into that. Sorry I can't be more help at this time.

  • From: Italy
  • Registered: 11/18/2013
  • Posts: 19

Re: need retrieving tax rate per item during invoice generation

Hi dls.
The data you mention are those already used in the bottom summary.
get_trans_tax_details(ST_SALESINVOICE, $I) is already a summary and is not item related.
It is a summary of all items using each single $tax_item['rate'].
That is of little use when trying to single out each item's rate and it is calculated AFTER the call for each item in a separate array.
I'll see if I can create a function with my little PHP knowledge.
In the meantime, whenever you or others have time, any hint will be more than appreciated.
Best,
Carlo

  • joe
  • Administrator
  • Offline
  • Registered: 03/15/2007
  • Posts: 5,107

Re: need retrieving tax rate per item during invoice generation

Hello,
janusz is planning to have this work in release 2.4. This is already implementid in a local Polish project.

Joe

7 Reply by apmuthu (edited by apmuthu 03/19/2015 03:37:11 pm)

  • From: Singapore, India, Chennai
  • Registered: 05/02/2009
  • Posts: 6,160

Re: need retrieving tax rate per item during invoice generation

At the moment, just increase the number of decimal places in the Setup => Display Setup and round off the field in the report to 2 decimal places.

FA v2.4 has been in the making for quite a few years now....a few more months to wait is okay perhaps....

apmuthu's Website

  • From: Italy
  • Registered: 11/18/2013
  • Posts: 19

Re: need retrieving tax rate per item during invoice generation

Hi all.
I actually solved the whole problem by adding a few functions (most of the credit goes to apmuthu, to be frank), although I needed to alter a few tables in the DB in order to accomodate historically the bit of info so obtained.
I'd like to share it with the community if there is any interest.
I have no idea how to do that, though.
Any help, if interested, will be appreciated.
Carlo

  • From: Singapore, India, Chennai
  • Registered: 05/02/2009
  • Posts: 6,160

Re: need retrieving tax rate per item during invoice generation

Create a page in the wiki detailing your solution - it will be a nice resource for the community. Alternatively, prepare a PDF document about what changes need to be done, complete with code files and zip them and upload them somewhere and then post your link here.

apmuthu's Website

  • From: Italy
  • Registered: 11/18/2013
  • Posts: 19

Re: need retrieving tax rate per item during invoice generation

working on a PDF to explain what I did so far.
I just need to download a "virgin" FA and compare files with mine since I was so stupid not to keep an efficient track of changes.
I may take a few days to complete the doc and then I'll post it.

I have already uploaded the code, together with a backup of the DB (under company/0/backup) named

antemovimenti.sql

(which is a today's backup).

You may download the zip file from

http://FAdev.gelatiflor.net/erp20150322.zip

Needless to say, since I am just a mere newbie with PHP, I am certain that there will be many naiveties in my code.

I'm here to learn, please.

Best and thanks,

Carlo

  • From: Singapore, India, Chennai
  • Registered: 05/02/2009
  • Posts: 6,160

Re: need retrieving tax rate per item during invoice generation

Take the latest FA snapshot from the official Git Repo.

apmuthu's Website

  • From: Italy
  • Registered: 11/18/2013
  • Posts: 19

Re: need retrieving tax rate per item during invoice generation

I am running ver. 2.3.22 (which I believed to be the last one).

I noticed some differences between the version I'm running and the one you suggested to use as a comparison version.

Which of the two is the latest?

  • From: Singapore, India, Chennai
  • Registered: 05/02/2009
  • Posts: 6,160

Re: need retrieving tax rate per item during invoice generation

The latest is in the Official Git Repo.

http://devel.frontaccounting.com/git/?p … ;a=summary

FA v2.3.23 has been released today.

apmuthu's Website

  • Registered: 09/09/2015
  • Posts: 1

Re: need retrieving tax rate per item during invoice generation

Just wondering if there is an update on this. I would like to implement this as well. I am using 2.4 at this point.

  • From: Singapore, India, Chennai
  • Registered: 05/02/2009
  • Posts: 6,160

Re: need retrieving tax rate per item during invoice generation

v2.4 is in beta. The stable one is v2.3.24+ snapshot?

apmuthu's Website