pos/is4c-nf/lib/Scanning/SpecialUPCs/CouponCode.php
<?php
/*******************************************************************************
Copyright 2010 Whole Foods Co-op
This file is part of IT CORE.
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\Scanning\SpecialUPCs;
use COREPOS\pos\lib\Scanning\SpecialUPC;
use COREPOS\pos\lib\Database;
use COREPOS\pos\lib\DisplayLib;
use COREPOS\pos\lib\MiscLib;
use COREPOS\pos\lib\TransRecord;
/**
@class CouponCode
Handle standard manufacturer coupons
This module looks for UPCs with prefix
- 005 UPC-12 coupon
- 099 EAN-13 coupon
It extracts the manufacturer prefix from
the UPC and validates that a matching item
is in the transaction
It looks up the coupon code to calculate
a discount value and adds the coupon to
the transaction
*/
class CouponCode extends SpecialUPC
{
private $ean;
private $alts = array();
public function isSpecial($upc)
{
$upcPrefix = '005';
if ($this->session->get('UpcIncludeCheckDigits') == 1) {
$upcPrefix = '05';
}
$eanPrefix = '099';
if ($this->session->get('EanIncludeCheckDigits') == 1) {
$eanPrefix = '99';
}
$this->ean = false;
if (substr($upc,0,strlen($upcPrefix)) == $upcPrefix) {
$this->getAlts($upc);
return true;
} elseif (substr($upc,0,strlen($eanPrefix)) == $eanPrefix) {
$this->getAlts($upc);
$this->ean = true;
return true;
}
return false;
}
private function getAlts($upc)
{
$dbc = Database::pDataConnect();
$prep = $dbc->prepare("SELECT altUPC FROM CouponAlts WHERE upc=? AND expires >= " . $dbc->curdate());
$res = $dbc->execute($prep, array($upc));
while ($row = $dbc->fetchRow($res)) {
$this->alts[] = $row['altUPC'];
}
}
private function upcToParts($upc)
{
/**
Adjust string index of pieces
based on whether check digits
have been included
*/
$manIdStart = 3;
$famStart = 8;
$valStart = 11;
if ( ($this->ean && $this->session->get('EanIncludeCheckDigits') == 1) ||
(!$this->ean && $this->session->get('UpcIncludeCheckDigits') == 1)
) {
$manIdStart = 2;
$famStart = 9;
$valStart = 10;
}
$manId = substr($upc, $manIdStart, 5);
$fam = substr($upc, $famStart, 3);
$val = substr($upc, $valStart, 2);
return array($manId, $fam, $val, $manIdStart);
}
private function getValue($val)
{
$dbc = Database::pDataConnect();
$query = "select Value,Qty from couponcodes where Code = '".$val."'";
$result = $dbc->query($query);
return $dbc->fetchRow($result);
}
private function checkLimits($upc, $json)
{
$dbc = Database::pDataConnect();
$query2 = "SELECT reason, threshold FROM disableCoupon WHERE upc='$upc'";
$result2 = $dbc->query($query2);
if ($result2 && $dbc->numRows($result2) > 0) {
$row = $dbc->fetchRow($result2);
if ($row['threshold'] <= 0) {
$json['output'] = DisplayLib::boxMsg(
$row['reason'],
_("coupon disabled"),
false,
DisplayLib::standardClearButton()
);
return $json;
}
$transDB = Database::tDataConnect();
$qtyQ = "SELECT SUM(quantity) FROM localtemptrans WHERE upc='$upc'";
$qtyR = $transDB->query($qtyQ);
if ($transDB->numRows($qtyR) > 0) {
$qtyW = $transDB->fetchRow($qtyR);
$qty = $qtyW[0];
if ($qty >= $row['threshold']) {
$json['output'] = DisplayLib::boxMsg(
_('coupon already applied'),
'',
false,
DisplayLib::standardClearButton()
);
return $json;
}
}
}
return true;
}
private function ncgCouponItems($upc)
{
$dbc = Database::pDataConnect();
if (!$dbc->tableExists('NcgCouponItems')) {
return array();
}
$prep = $dbc->prepare("SELECT itemUPC FROM NcgCouponItems WHERE couponUPC=?");
$items = $dbc->getAllValues($prep, array($upc));
if (!is_array($items)) {
return array();
}
return $items;
}
public function handle($upc,$json)
{
list($manId, $fam, $val, $manIdStart) = $this->upcToParts($upc);
$valueInfo = $this->getValue($val);
if (!$valueInfo) {
$json['output'] = DisplayLib::boxMsg(
_("coupon type unknown")."<br />"._("enter coupon manually"),
'',
false,
DisplayLib::standardClearButton()
);
return $json;
}
$value = $valueInfo["Value"];
$qty = $valueInfo["Qty"];
$chk = $this->checkLimits($upc, $json);
if ($chk !== true) {
return $chk;
}
$ncgItems = $this->ncgCouponItems($upc);
if ($fam == "992") {
// 992 basically means blanket accept
// Old method of asking cashier to assign a department
// just creates confusion
// Instead I just try to guess, otherwise use zero
// (since that's what would happen anyway when the
// confused cashier does a generic coupon tender)
$value = MiscLib::truncate2($value);
$this->session->set("couponupc",$upc);
$this->session->set("couponamt",$value);
$dept = 0;
$dbc = Database::tDataConnect();
// SQL strings are indexed starting w/ one instead of zero
// hence $manIdStart+1
$query = "select department from localtemptrans
WHERE substring(upc," . ($manIdStart+1) . ",5)='$manId'
GROUP BY department
ORDER BY count(*) desc";
$result = $dbc->query($query);
if ($dbc->numRows($result) > 0) {
$row = $dbc->fetchRow($result);
$dept = $row['department'];
}
TransRecord::addCoupon($upc, $dept, $value);
$json['output'] = DisplayLib::lastpage();
return $json;
}
// validate coupon
$dbc = Database::tDataConnect();
$matchLength = 5;
$matchOn = $manId;
if ($this->session->get('EnforceFamilyCode')) {
$matchLength = 8;
$matchOn = $manId . $fam;
}
/* the idea here is to track exactly which
items in the transaction a coupon was
previously applied to
SQL strings are indexed starting w/ one instead of zero
hence $manIdStart+1
*/
$query = "select max(t.unitPrice) as unitPrice,
max(t.department) as department,
max(t.ItemQtty) as itemQtty,
sum(case when c.quantity is null then 0 else c.quantity end) as couponQtty,
max(case when c.quantity is not null then 0 else t.foodstamp end) as foodstamp,
max(case when c.quantity is not null then 0 else t.tax end) as tax,
max(case when c.quantity is not null then 0 else t.discountable end) as discountable,
max(t.emp_no) as emp_no,
max(t.trans_no) as trans_no,
t.trans_id from
localtemptrans as t left join couponApplied as c
on t.emp_no=c.emp_no and t.trans_no=c.trans_no
and t.trans_id=c.trans_id
where (";
if (count($ncgItems) > 0) {
$query .= " t.upc IN (";
foreach ($ncgItems as $ni) {
$query .= "'{$ni}',";
}
$query = substr($query, 0, strlen($query) - 1) . ') ';
} else {
$query .= " substring(t.upc," . ($manIdStart+1) . ", {$matchLength})='{$matchOn}'";
}
/* not right per the standard, but organic valley doesn't
* provide consistent manufacturer ids in the same goddamn
* coupon book */
if ($this->ean) {
$query .= " or substring(t.upc," . $manIdStart . ", {$matchLength})='{$matchOn}'";
}
$query .= ") and t.trans_status <> 'C'
group by t.trans_id
order by MAX(t.unitPrice) desc";
$result = $dbc->query($query);
$numRows = $dbc->numRows($result);
/* no item w/ matching manufacturer */
if ($numRows == 0) {
if (count($this->alts) > 0) {
$next = array_pop($this->alts);
return $this->handle($next, $json);
}
$json['output'] = DisplayLib::boxMsg(
_("product not found")."<br />"._("in transaction"),
'',
false,
DisplayLib::standardClearButton()
);
return $json;
}
/* count up per-item quantites that have not
yet had a coupon applied to them */
$available = array();
$empno=$transno=$dept=$foodstamp=$tax=$discountable=-1;
$actQty = 0;
while($row = $dbc->fetchRow($result)) {
if ($row["itemQtty"] - $row["couponQtty"] > 0) {
$transId = $row["trans_id"];
$available[$transId] = array(0,0);
$available[$transId][0] = $row["unitPrice"];
$available[$transId][1] += $row["itemQtty"];
$available[$transId][1] -= $row["couponQtty"];
$actQty += $available[$transId][1];
}
if ($empno == -1) {
$empno = $row["emp_no"];
$transno = $row["trans_no"];
$dept = $row["department"];
$foodstamp = $row["foodstamp"];
$tax = $row['tax'];
$discountable = $row['discountable'];
}
}
/* every line has maximum coupons applied */
if (count($available) == 0) {
$json['output'] = DisplayLib::boxMsg(
_("Coupon already applied")."<br />"._("for this item"),
'',
false,
DisplayLib::standardClearButton()
);
return $json;
}
/* insufficient number of matching items */
if ($qty > $actQty) {
$msg = sprintf(_("coupon requires %d items"),$qty) . "<br />"
. sprintf(_("there are only %d item(s)"),$actQty) . "<br />"
. _("in this transaction");
$json['output'] = DisplayLib::boxMsg($msg, '', false, DisplayLib::standardClearButton());
return $json;
}
/* free item, multiple choices
needs work, obviously */
if ($value == 0 && count($available) > 1) {
// decide which item(s)
// manually by cashier maybe?
}
/* log the item(s) this coupon is
being applied to */
$applied = 0;
foreach(array_keys($available) as $transId) {
if ($value == 0) {
$value = -1 * $available[$transId][0];
}
if ($qty <= $available[$transId][1]) {
$query = "INSERT INTO couponApplied
(emp_no,trans_no,quantity,trans_id)
VALUES (
$empno,$transno,$qty,$transId)";
$dbc->query($query);
$applied += $qty;
} else {
$query = "INSERT INTO couponApplied
(emp_no,trans_no,quantity,trans_id)
VALUES (
$empno,$transno,".
$available[$transId][1].",$transId)";
$dbc->query($query);
$applied += $available[$transId][1];
}
if ($applied >= $qty) {
break;
}
}
$value = MiscLib::truncate2($value);
$json['udpmsg'] = 'goodBeep';
$status = array('tax'=>$tax, 'foodstamp'=>$foodstamp, 'discountable'=>$discountable);
TransRecord::addCoupon($upc, $dept, $value, $status);
$json['output'] = DisplayLib::lastpage();
$json['redraw_footer'] = True;
return $json;
}
}