pos/is4c-nf/lib/models/trans/TaxViewModel.php
<?php
/*******************************************************************************
Copyright 2013 Whole Foods Co-op
This file is part of Fannie.
IT CORE is free software; you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
the Free Software Foundation; either version 2 of the License, or
(at your option) any later version.
IT CORE 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
GNU General Public License for more details.
You should have received a copy of the GNU General Public License
in the file license.txt along with IT CORE; if not, write to the Free Software
Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
*********************************************************************************/
namespace COREPOS\pos\lib\models\trans;
use COREPOS\pos\lib\models\ViewModel;
/**
@class TaxViewModel
*/
class TaxViewModel extends ViewModel
{
protected $name = "taxView";
protected $preferred_db = 'trans';
protected $columns = array(
'id' => array('type'=>'INT'),
'description' => array('type'=>'VARCHAR(255)'),
'taxTotal' => array('type'=>'MONEY'),
'fsTaxable' => array('type'=>'MONEY'),
'fsTaxTotal' => array('type'=>'MONEY'),
'foodstampTender' => array('type'=>'MONEY'),
'taxrate' => array('type'=>'FLOAT'),
);
public function definition()
{
return "
SELECT
r.id AS id,
r.description AS description,
CAST(SUM(CASE
WHEN l.trans_type IN ('I','D') AND discountable=0 THEN total
WHEN l.trans_type IN ('I','D') AND discountable<>0 THEN total * ((100-s.percentDiscount)/100)
ELSE 0 END
) * MAX(r.rate) AS DECIMAL(10,2)) as taxTotal,
CAST(SUM(CASE
WHEN l.trans_type IN ('I','D') AND discountable=0 AND foodstamp=1 THEN total
WHEN l.trans_type IN ('I','D') AND discountable<>0 AND foodstamp=1 THEN total * ((100-s.percentDiscount)/100)
ELSE 0 END
) AS DECIMAL(10,2)) as fsTaxable,
CAST(SUM(CASE
WHEN l.trans_type IN ('I','D') AND discountable=0 AND foodstamp=1 THEN total
WHEN l.trans_type IN ('I','D') AND discountable<>0 AND foodstamp=1 THEN total * ((100-s.percentDiscount)/100)
ELSE 0 END
) * MAX(r.rate) AS DECIMAL(10,2)) as fsTaxTotal,
-1*MAX(fsTendered) as foodstampTender,
MAX(r.rate) as taxrate
FROM
taxrates AS r
LEFT JOIN localtemptrans AS l
ON r.id=l.tax
CROSS JOIN lttsummary AS s
WHERE trans_type <> 'L'
GROUP BY r.id,r.description";
}
public function doc()
{
return '
Use:
This view is a revised, BETA way of dealing
with taxes. Rather than generate the tax total
(including foodstamp exemptions) with a series of
cascading views, this single view provides a
record for each available tax rate. Exemption
calculations then occur on the code side in a
far-easier-to-read imperative style.
id is the tax rate\'s identifier and description
is its description.
taxTotal is the total tax due for this particular
rate. SUM(taxTotal) over the view would be the total
tax due with all rates.
fsTaxable is the *retail* cost of goods taxed at this rate.
fsTaxTotal is tax due on those items at this rate.
foodstampTender is the total amount tendered in foodstamps
for the transaction. This will be the same for all records
in this view and is provided as a convenience to avoid a
second look-up query.
rate is this tax rate as a decimal - i.e., 1% is 0.01.
----------------------------------------------
In calculating exemptions, foodstampTender and fsTaxable
are important. If foodstampTender is >= fsTaxable then
all foodstampable, taxable items were purchased with foodstamps
and you can subtract fsTaxTotal from taxTotal. On the other
hand if foodstampTender is < fsTaxable then you should reduce
taxTotal by a proportional pro-rated amount.
When dealing with multiple tax rates, it is important to
reduce foodstampTender each time it is used. The value in the
view is the same for all records and POS has to decide where
to apply that tender more than once.
';
}
}