pos/is4c-nf/lib/Scanning/SpecialUPCs/HouseCoupon.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\DiscountModule;
use COREPOS\pos\lib\DisplayLib;
use COREPOS\pos\lib\MiscLib;
use COREPOS\pos\lib\TransRecord;
/**
@class HouseCoupon
WFC style custom store coupons
This class looks for UPC prefix 00499999
The remainder of the UPC is an ID value
to look up requirement(s) and discount
via the houseCoupons and houseCouponItems
tables
*/
class HouseCoupon extends SpecialUPC
{
public function isSpecial($upc)
{
$prefix = $this->session->get('houseCouponPrefix');
if ($prefix == '') {
$prefix = '00499999';
}
if (substr($upc,0,strlen($prefix)) == $prefix) {
return true;
}
return false;
}
public function handle($upc, $json)
{
$coupID = ltrim(substr($upc, -5), "0");
$qualified = $this->checkQualifications($coupID);
if ($qualified !== true) {
$json['output'] = $qualified;
return $json;
}
$available = $this->checkLimits($coupID);
if ($available !== true) {
$json['output'] = $available;
return $json;
}
if ($coupID == 321) {
$json['main_frame'] = MiscLib::baseURL() . 'gui-modules/EmailPage.php';
return $json;
}
$add = $this->getValue($coupID);
TransRecord::addhousecoupon($upc, $add['department'], -1 * $add['value'], $add['description'], $add['discountable'], $add['tax']);
$json['output'] = DisplayLib::lastpage();
$json['udpmsg'] = 'goodBeep';
$json['redraw_footer'] = true;
return $json;
}
/**
helper - lookup coupon record
*/
private function lookupCoupon($coupID)
{
$dbc = Database::pDataConnect();
$infoQ = "SELECT endDate,"
. $dbc->identifierEscape('limit') . ",
discountType,
department,
discountValue,
minType,
minValue,
memberOnly,
endDate,
CASE
WHEN endDate IS NULL THEN 0
ELSE ". $dbc->datediff('endDate', $dbc->now()) . "
END AS expired";
if ($this->session->get('NoCompat') == 1) {
$infoQ .= ", description,
startDate,
CASE
WHEN startDate IS NULL THEN 0
ELSE ". $dbc->datediff('startDate', $dbc->now()) . "
END as preStart,
virtualOnly,
" . $dbc->escape("maxValue");
} else {
// new(ish) columns 16apr14
$hctable = $dbc->tableDefinition('houseCoupons');
if (isset($hctable['description'])) {
$infoQ .= ', description';
} else {
$infoQ .= ', \'\' AS description';
}
if (isset($hctable['startDate'])) {
$infoQ .= ", startDate,
CASE
WHEN startDate IS NULL THEN 0
ELSE ". $dbc->datediff('startDate', $dbc->now()) . "
END as preStart";
} else {
$infoQ .= ', \'1900-01-01\' AS startDate, 0 AS preStart';
}
$infoQ .= isset($hctable['virtualOnly']) ? ', virtualOnly ' : ', 0 AS virtualOnly ';
$mval = $dbc->identifierEscape('maxValue');
$infoQ .= isset($hctable['maxValue']) ? ", {$mval} " : ", 0 AS {$mval} ";
}
$infoQ .= " FROM houseCoupons
WHERE coupID=" . ((int)$coupID);
$infoR = $dbc->query($infoQ);
if ($dbc->num_rows($infoR) == 0) {
return false;
}
return $dbc->fetch_row($infoR);
}
private function errorOrQuiet($msg, $quiet)
{
if ($quiet) {
return false;
}
return DisplayLib::boxMsg(
$msg,
'',
false,
DisplayLib::standardClearButton()
);
}
/**
* Determine if the customer is a member
* @param $couponFlag [int] value of houseCoupons.memberOnly
* @return [boolean]
*/
private function isMember($couponFlag)
{
$isMem = false;
if ($this->session->get('isMember') == 1) {
$isMem = true;
} elseif ($this->session->get('memberID') == $this->session->get('visitingMem') && $couponFlag == 2) {
$isMem = true;
} elseif ($this->session->get('memberID') == '0') {
$isMem = false;
} elseif ($this->session->get('memberID') == 5608) {
$isMem = true;
}
return $isMem;
}
/**
Validate coupon exists, is not expired, and
transaction meets required qualifications
@param $coupID [int] coupon ID
@param $quiet [boolean] just return false rather than
an error message on failure
@return [boolean] true or [string] error message
*/
public function checkQualifications($coupID, $quiet=false)
{
$infoW = $this->lookupCoupon($coupID);
if ($infoW === false) {
return $this->errorOrQuiet(_("coupon not found"), $quiet);
}
if ($infoW["expired"] < 0) {
$expired = substr($infoW["endDate"], 0, strrpos($infoW["endDate"], " "));
return $this->errorOrQuiet(_("coupon expired "). $expired, $quiet);
} elseif ($infoW['preStart'] > 0) {
return $this->errorOrQuiet(_("coupon not available yet "), $quiet);
}
/* check for member-only, longer use tracking
available with member coupons */
if ($infoW["memberOnly"] >= 1 && !$this->isMember($infoW['memberOnly'])) {
if ($quiet) {
return false;
}
return DisplayLib::boxMsg(
_("Apply member number first"),
_('Member only coupon'),
false,
array_merge(array(_('Member Search [ID]') => 'parseWrapper(\'ID\');'), DisplayLib::standardClearButton())
);
}
/* verify the minimum purchase has been made */
$transDB = Database::tDataConnect();
switch ($infoW["minType"]) {
case "Q": // must purchase at least X
case "Q+": // must purchase more than X
$minQ = "select case when sum(ItemQtty) is null
then 0 else sum(ItemQtty) end
" . $this->baseSQL($transDB, $coupID, 'upc');
$minR = $transDB->query($minQ);
$minW = $transDB->fetch_row($minR);
$validQtty = $minW[0];
if ($infoW['minType'] == 'Q+' && $validQtty <= $infoW["minValue"]) {
return $this->errorOrQuiet(_('coupon requirements not met'), $quiet);
} elseif ($infoW['minType'] == 'Q' && $validQtty < $infoW['minValue']) {
return $this->errorOrQuiet(_('coupon requirements not met'), $quiet);
}
break;
case "Q-": // must purchase at least one, no more than X
$minQ = "select case when sum(ItemQtty) is null
then 0 else sum(ItemQtty) end
" . $this->baseSQL($transDB, $coupID, 'upc');
$minR = $transDB->query($minQ);
$minW = $transDB->fetch_row($minR);
$validQtty = $minW[0];
if ($infoW['minType'] == 'Q+' && $validQtty <= $infoW["minValue"]) {
return $this->errorOrQuiet(_('coupon requirements not met'), $quiet);
} elseif ($infoW['minType'] == 'Q' && $validQtty < $infoW['minValue']) {
return $this->errorOrQuiet(_('coupon requirements not met'), $quiet);
} elseif ($infoW['minType'] == 'Q-' && $validQtty < 1) {
return $this->errorOrQuiet(_('coupon requirements not met'), $quiet);
}
break;
case 'D': // must purchase at least amount in $ from department
case 'D+': // must purchase more than amount in $ from department
$minQ = "select case when sum(total) is null
then 0 else sum(total) end
" . $this->baseSQL($transDB, $coupID, 'department');
$minR = $transDB->query($minQ);
$minW = $transDB->fetch_row($minR);
$validQtty = $minW[0];
if ($infoW['minType'] == 'D+' && $validQtty <= $infoW["minValue"]) {
return $this->errorOrQuiet(_('coupon requirements not met'), $quiet);
} elseif ($infoW['minType'] == 'D' && $validQtty < $infoW["minValue"]) {
return $this->errorOrQuiet(_('coupon requirements not met'), $quiet);
}
break;
case 'N': // Inversion of D/D+.
case 'N+': // Must purchase amount outside listed departments
$deptQ = $transDB->prepare('SELECT upc FROM ' . $this->session->get('pDatabase') . $dbc->sep()
. 'houseCouponItems WHERE coupID=?');
$deptR = $transDB->execute($deptQ, array($coupID));
$depts = array();
while ($deptW = $transDB->fetchRow($deptR)) {
$depts[] = $deptW['upc'];
}
if (count($depts) == 0) {
return $this->errorOrQuiet(_('coupon requirements not met'), $quiet);
}
list($nStr, $nArgs) = $transDB->safeInClause($depts);
$minQ = $transDB->prepare("SELECT sum(total) FROM localtemptrans
WHERE trans_type IN ('I', 'D', 'M')
AND department NOT IN ({$nStr})");
$minR = $transDB->execute($minQ, $nArgs);
$minW = $transDB->fetch_row($minR);
$validQtty = $minW[0];
if ($infoW['minType'] == 'D+' && $validQtty <= $infoW["minValue"]) {
return $this->errorOrQuiet(_('coupon requirements not met'), $quiet);
} elseif ($infoW['minType'] == 'D' && $validQtty < $infoW["minValue"]) {
return $this->errorOrQuiet(_('coupon requirements not met'), $quiet);
}
break;
case 'C': // must purchase at least amount in qty (count) from department
case 'C+': // must purchase more than amount in qty (count) from department
case 'C!':
case 'C^':
$minQ = "select case when sum(ItemQtty) is null
then 0 else sum(ItemQtty) end
" . $this->baseSQL($transDB, $coupID, 'department') . "
AND l.trans_type IN ('I','D')";
if ($infoW['minType'] == 'C!' || $infoW['minType'] == 'C^') {
$minQ .= ' AND l.discounttype=0 ';
}
$minR = $transDB->query($minQ);
$minW = $transDB->fetch_row($minR);
$validQtty = $minW[0];
if ($infoW['minType'] == 'C+' && $validQtty <= $infoW["minValue"]) {
return $this->errorOrQuiet(_('coupon requirements not met'), $quiet);
} elseif ($infoW['minType'] == 'C^' && $validQtty <= $infoW["minValue"]) {
return $this->errorOrQuiet(_('coupon requirements not met'), $quiet);
} elseif ($infoW['minType'] == 'C' && $validQtty < $infoW["minValue"]) {
return $this->errorOrQuiet(_('coupon requirements not met'), $quiet);
} elseif ($infoW['minType'] == 'C!' && $validQtty < $infoW["minValue"]) {
return $this->errorOrQuiet(_('coupon requirements not met'), $quiet);
}
break;
case 'M': // must purchase at least X qualifying items
// and some quantity corresponding discount items
$minQ = "select case when sum(ItemQtty) is null then 0 else
sum(ItemQtty) end
" . $this->baseSQL($transDB, $coupID, 'upc') . "
and h.type = ";
//$minR = $transDB->query($minQ . "'QUALIFIER'");
$minR = $transDB->query($minQ . "'QUALIFIER' OR h.type = 'BOTH'");
$minW = $transDB->fetch_row($minR);
$validQtty = $minW[0];
$min2R = $transDB->query($minQ . "'DISCOUNT'");
$min2W = $transDB->fetch_row($min2R);
$validQtty2 = $min2W[0];
if ($validQtty < $infoW["minValue"] || $validQtty2 <= 0) {
return $this->errorOrQuiet(_('coupon requirements not met'), $quiet);
}
break;
case 'MX': // must purchase at least $ from qualifying departments
// and some quantity discount items
// (mix "cross")
$minQ = "select case when sum(total) is null
then 0 else sum(total) end
" . $this->baseSQL($transDB, $coupID, 'department') . "
AND h.type='QUALIFIER'";
$minR = $transDB->query($minQ);
$minW = $transDB->fetch_row($minR);
$validQtty = $minW[0];
$min2Q = "select case when sum(ItemQtty) is null then 0 else
sum(ItemQtty) end
" . $this->baseSQL($transDB, $coupID, 'upc') . "
and h.type = 'DISCOUNT'";
$min2R = $transDB->query($min2Q);
$min2W = $transDB->fetch_row($min2R);
$validQtty2 = $min2W[0];
if ($validQtty < $infoW["minValue"] || $validQtty2 <= 0) {
return $this->errorOrQuiet(_('coupon requirements not met'), $quiet);
}
break;
case '$': // must purchase at least $ total items
case '$+': // must purchase more than $ total items
$minQ = "SELECT sum(total) FROM localtemptrans
WHERE trans_type IN ('I', 'D', 'M')";
$minR = $transDB->query($minQ);
$minW = $transDB->fetch_row($minR);
$validAmt = $minW[0];
if ($infoW['minType'] == '$+' && $validAmt <= $infoW["minValue"]) {
return $this->errorOrQuiet(_('coupon requirements not met'), $quiet);
} elseif ($infoW['minType'] == '$' && $validAmt < $infoW["minValue"]) {
return $this->errorOrQuiet(_('coupon requirements not met'), $quiet);
}
break;
case '': // no minimum
case ' ':
break;
default:
return $this->errorOrQuiet(_('unknown minimum type ') . $infoW['minType']);
}
return true;
}
/**
Check how many times the coupon has been used and
compare against usage limits - e.g., one per transaction,
one per member, etc. This is a separate method from
checkQualifications() so that calling code has the option
of working around limits via voids or amount adjustments
@param $coupID [int] coupon ID
@return [boolean] true or [string] error message
*/
public function checkLimits($coupID)
{
$infoW = $this->lookupCoupon($coupID);
if ($infoW === false) {
return $this->errorOrQuiet(_('coupon not found'), false);
}
/* limit types
0 => total coupons used,
1 => total coupons used today,
*/
$limitType = 0;
if ($infoW['limit'] > 100 && $infoW['limit'] < 1000) {
$limitType = 1;
}
$prefix = $this->session->get('houseCouponPrefix');
if ($prefix == '') {
$prefix = '00499999';
}
$upc = $prefix . str_pad($coupID, 5, '0', STR_PAD_LEFT);
/* check the number of times this coupon
* has been used in this transaction
* against the limit */
$transDB = Database::tDataConnect();
$limitQ = "select case when sum(ItemQtty) is null
then 0 else sum(ItemQtty) end
from localtemptrans where
upc = '" . $upc . "'" ;
$limitR = $transDB->query($limitQ);
$limitW = $transDB->fetch_row($limitR);
$timesUsed = $limitW[0];
if ($timesUsed >= $infoW["limit"]) {
return $this->errorOrQuiet(_('coupon already applied'), false);
}
/**
For members, enforce limits against longer
transaction history
*/
if ($infoW["memberOnly"] == 1 && $this->session->get("standalone")==0
&& $this->session->get('memberID') != $this->session->get('visitingMem')) {
/**
A virtual-only coupon MUST exist in the houseVirtualCoupons table for the
current member. If a record is present the coupon is allowed.
*/
if ($infoW['virtualOnly']) {
$opDB = Database::pDataConnect();
$chkP = $opDB->prepare("SELECT coupID FROM houseVirtualCoupons WHERE coupID=? AND card_no=?");
$chkR = $opDB->execute($chkP, array($coupID, $this->session->get('memberID')));
if ($opDB->numRows($chkR) === 0) {
return $this->errorOrQuiet(_('coupon not available<br />on this account'), false);
}
return true;
}
$mDB = Database::mDataConnect();
$tDB = Database::tDataConnect();
if ($mDB === false) {
return true;
}
$mAlt = Database::mAltName();
// Find qty of coupons applied in past 90 days
$altQ = "SELECT upc, card_no, SUM(quantity) AS quantity, MAX(tdate) AS tdate
FROM {$mAlt}dlog_90_view
WHERE trans_type='T'
AND trans_subtype='IC'
AND upc='$upc'
AND card_no=" . ((int)$this->session->get('memberID')) . "
AND tdate BETWEEN '{$infoW['startDate']}' AND '{$infoW['endDate']}'
GROUP BY upc, card_no";
// find qty of coupon applied today
$lim1Qm = "SELECT upc, card_no, SUM(quantity) AS quantity, MAX(tdate) AS tdate
FROM {$mAlt}dlog
WHERE trans_type='T'
AND trans_subtype='IC'
AND upc='$upc'
AND card_no=" . ((int)$this->session->get('memberID')) . "
GROUP BY upc, card_no";
$lim1Qt = "SELECT SUM(quantity) AS quantity
FROM localtemptrans
WHERE trans_subtype = 'IC'
AND upc = '$upc'";
if ($limitType == 1) {
// limit = qty of coupons used today
$mRes = $mDB->query($lim1Qm);
$tRes = $tDB->query($lim1Qt);
if ($mDB->num_rows($mRes) > 0 || $tDB->num_rows($tRes) > 0) {
$mRow = $mDB->fetch_row($mRes);
$tRow = $tDB->fetch_row($tRes);
$uses = $mRow['quantity'] + $tRow['quantity'];
if ($uses >= $infoW["limit"] - 100) {
return $this->errorOrQuiet(_('daily coupon limit already reached<br />on this membership'), false);
}
}
} else {
// limit = qty of coupons used in last 90 days
$mRes = $mDB->query($altQ);
if ($mDB->num_rows($mRes) > 0) {
$mRow = $mDB->fetch_row($mRes);
$uses = $mRow['quantity'];
if ($uses >= $infoW["limit"]) {
return $this->errorOrQuiet(_('coupon already used<br />on this membership'), false);
}
}
}
}
return true;
}
/**
Get information about how much the coupon is worth
@param $coupID [int] coupon ID
@return array with keys:
value => [float] coupon value
department => [int] department number for the coupon
description => [string] description for coupon
discountable => [int] whether the coupon should be included in discountable total
*/
public function getValue($coupID)
{
$infoW = $this->lookupCoupon($coupID);
if ($infoW === false) {
return array('value' => 0, 'department' => 0, 'description' => '');
}
$transDB = Database::tDataConnect();
/* if we got this far, the coupon
* should be valid
*/
$value = 0;
$description = isset($infoW['description']) ? $infoW['description'] : '';
$discountable = 1;
$tax = 0;
switch ($infoW["discountType"]) {
case "Q": // quantity discount
// discount = coupon's discountValue
// times the cheapeast coupon item
$valQ = "select CASE WHEN l.scale=1 OR l.upc LIKE '002%' THEN total ELSE unitPrice END as unitPrice,
department, trans_id
" . $this->baseSQL($transDB, $coupID, 'upc') . "
and h.type in ('BOTH', 'DISCOUNT')
and l.total > 0
order by unitPrice asc ";
$valR = $transDB->query($valQ);
$valW = $transDB->fetch_row($valR);
$value = $valW[0] * $infoW["discountValue"];
// if the item is free, auto-remove tax
// partial removal for partially discounted items
// is not currently an option
if ($infoW['discountValue'] == 1) {
$prep = $transDB->prepare("UPDATE localtemptrans SET tax=0 WHERE trans_id=?");
$transDB->execute($prep, array($valW['trans_id']));
}
break;
case 'BG': // BOGO
$valQ = 'SELECT SUM(l.total), SUM(l.quantity) '
. $this->baseSQL($transDB, $coupID, 'upc') . "
and h.type in ('BOTH', 'DISCOUNT')";
$valP = $transDB->prepare($valQ);
$valW = $transDB->getRow($valP);
$value = $valW[0];
$qty = $valW[1];
if ($qty % 2 != 0) {
$value -= ($value/$qty);
}
$value = MiscLib::truncate2($value/2);
if ($value > 0 && $value > $infoW['discountValue']) {
$value = $infoW['discountValue'];
}
break;
case 'BI': // BOGO Mixed Item
$qtyQ = 'SELECT SUM(l.quantity) '
. $this->baseSQL($transDB, $coupID, 'upc') . "
and h.type in ('BOTH')";
$qtyP = $transDB->prepare($qtyQ);
$qtyW = $transDB->getRow($qtyP);
$qty = floor($qtyW[0]/2);
if ($infoW['discountValue']*2 < $qty) {
$qty = $infoW['discountValue'];
}
$valQ = 'SELECT l.total, l.quantity, l.unitPrice'
. $this->baseSQL($transDB, $coupID, 'upc') . "
and h.type in ('BOTH')
ORDER BY unitPrice ASC
";
$valP = $transDB->prepare($valQ);
$valR = $transDB->execute($valP);
$tmpVal = 0;
$arr = array();
while ($row = $transDB->fetchRow($valR)) {
if ($row['quantity'] > 1) {
for ($i=$row['quantity']; $i>1; $i--) {
$arr[] = $row['unitPrice'];
}
} else {
$arr[] = $row['total'];
}
}
for ($i=0; $i<$qty; $i++) {
$tmpVal += $arr[$i];
}
$value = $tmpVal;
break;
case 'BQ': // Quantity-capped BOGO
// get total number of coupon items
$priceQ = 'SELECT unitPrice, quantity '
. $this->baseSQL($transDB, $coupID, 'upc') . "
and h.type in ('BOTH', 'DISCOUNT')
ORDER BY unitPrice ASC";
$priceR = $transDB->query($priceQ);
$value = 0;
$tmp = array();
while ($priceW = $transDB->fetchRow($priceR)) {
$x = $priceW['quantity'];
for ($x; $x > 0; $x--) {
$tmp[] = $priceW['unitPrice'];
}
}
$f = floor(count($tmp) / 2);
for ($f; $f > 0; $f--) {
$value += $tmp[$f-1];
}
break;
case 'BH': // BOHO
$valQ = 'SELECT SUM(l.total), SUM(l.quantity) '
. $this->baseSQL($transDB, $coupID, 'upc') . "
and h.type in ('BOTH', 'DISCOUNT')";
$valP = $transDB->prepare($valQ);
$valW = $transDB->getRow($valP);
$value = $valW[0];
$qty = $valW[1];
if ($qty % 2 != 0) {
$value -= ($value/$qty);
}
$value *= 0.5;
$value = MiscLib::truncate2($value/2);
if ($value > 0 && $value > $infoW['discountValue']) {
$value = $infoW['discountValue'];
}
break;
case 'BM': // BOHO - mixed item. Buy one, get a diff one half-off
$qualQ = 'SELECT SUM(l.total), SUM(l.quantity) '
. $this->baseSQL($transDB, $coupID, 'upc') . '
AND h.type in ("QUALIFIER")';
$qualP = $transDB->prepare($qualQ);
$qualW = $transDB->getRow($qualP);
$qualValue = $qualW[0];
$qualQty = $qualW[1];
$valQ = 'SELECT l.total '
. $this->baseSQL($transDB, $coupID, 'upc') . "
and h.type in ('BOTH', 'DISCOUNT')
ORDER BY total ASC LIMIT " . $qualQty;
$valP = $transDB->prepare($valQ);
$valW = $transDB->execute($valP);
$value = 0;
while ($row = $transDB->fetchRow($valW)) {
$value += $row['total'];
}
$value = MiscLib::truncate2($value/2);
break;
case 'B+': // BOHO - variably priced items
$discoVal = $infoW["discountValue"];
// get number of qualifiers in transaction
$qualQ = 'SELECT SUM(l.quantity) '
. $this->baseSQL($transDB, $coupID, 'upc') . '
AND h.type in ("QUALIFIER", "BOTH")';
$qualP = $transDB->prepare($qualQ);
$qualW = $transDB->getRow($qualP);
// qualQty = total quantity of qualifier items found
$qualQty = $qualW[0] / 2;
$qualQty = floor($qualQty);
$deptQ = "SELECT total AS value, quantity
" . $this->baseSQL($transDB, $coupID, 'upc') . "
AND h.type IN ('BOTH', 'DISCOUNT')
AND l.total > 0
ORDER BY unitPrice ASC
LIMIT " . $qualQty;
$deptP = $transDB->prepare($deptQ);
$deptR = $transDB->execute($deptP);
$j = 0;
$curQty = null;
$deptPrice = 0;
while ($row = $transDB->fetchRow($deptR)) {
if ($j < $qualQty && $j < $discoVal) {
unset($curQty);
$curQty = $row['quantity'];
for ($i=0; $i<$curQty; $i++) {
$deptPrice += $row['value'];
$j++;
}
}
}
$value = $deptPrice / 2;
break;
case "P": // discount price
// query to get the item's department and current value
// current value minus the discount price is how much to
// take off
$value = $infoW["discountValue"];
$deptQ = "select department, (total/quantity) as value
" . $this->baseSQL($transDB, $coupID, 'upc') . "
and h.type in ('BOTH', 'DISCOUNT')
and l.total > 0
order by unitPrice asc ";
$deptR = $transDB->query($deptQ);
$row = $transDB->fetch_row($deptR);
$value = $row[1] - $value;
break;
case "P+": // set price, mixed prices
$discoVal = $infoW["discountValue"];
// get number of qualifiers in transaction
$qualQ = 'SELECT SUM(l.quantity) '
. $this->baseSQL($transDB, $coupID, 'upc') . '
AND h.type in ("QUALIFIER")';
$qualP = $transDB->prepare($qualQ);
$qualW = $transDB->getRow($qualP);
// qualQty = total quantity of qualifier items found
$qualQty = $qualW[0];
$deptQ = "SELECT (total/quantity) AS value, quantity, tax, foodstamp
" . $this->baseSQL($transDB, $coupID, 'upc') . "
AND h.type IN ('BOTH', 'DISCOUNT')
AND l.total > 0
AND unitPrice < 10
ORDER BY unitPrice ASC
LIMIT " . $qualQty;
$deptP = $transDB->prepare($deptQ);
$deptR = $transDB->execute($deptP);
// $j = number of eligible discount items found;
$j = 0;
// $deptPrice = total price of discount items found
$deptPrice = 0;
$findTax = 999;
while ($row = $transDB->fetchRow($deptR)) {
// only tally discount item price if elible based on qualifier qty
if ($j < $qualQty) {
unset($curQty);
$curQty = $row['quantity'];
for ($i=0; $i<$curQty; $i++) {
$deptPrice += $row['value'];
$j++;
}
if ($row['foodstamp'] && $row['tax'] < $findTax) {
$findTax = $row['tax'];
}
}
}
if ($findTax < 999) {
$tax = $findTax;
}
$price = $discoVal * $j;
$value = ($deptPrice != 0) ? $price - $deptPrice : 0;
$value *= -1;
break;
case "FD": // flat discount for departments
// simply take off the requested amount
// scales with quantity for by-weight items
$value = $infoW["discountValue"];
$valQ = "select department, quantity
" . $this->baseSQL($transDB, $coupID, 'department') . "
and h.type in ('BOTH', 'DISCOUNT')
and l.total > 0
order by unitPrice asc ";
$valR = $transDB->query($valQ);
$row = $transDB->fetch_row($valR);
$value = $row[1] * $value;
break;
case "MD": // mix discount for departments
// take off item value or discount value
// whichever is less
$value = $infoW["discountValue"];
$valQ = "select department, l.total
" . $this->baseSQL($transDB, $coupID, 'department') . "
and h.type in ('BOTH', 'DISCOUNT')
and l.total > 0
order by l.total desc ";
$valR = $transDB->query($valQ);
$row = $transDB->fetch_row($valR);
$value = ($row[1] < $value) ? $row[1] : $value;
break;
case "AD": // all department discount
// apply discount across all items
// scales with quantity for by-weight items
$value = $infoW["discountValue"];
$valQ = "select sum(quantity)
" . $this->baseSQL($transDB, $coupID, 'department') . "
and h.type in ('BOTH', 'DISCOUNT')
and l.total > 0
order by unitPrice asc ";
$valR = $transDB->query($valQ);
$row = $transDB->fetch_row($valR);
$value = $row[1] * $value;
break;
case "FI": // flat discount for items
// simply take off the requested amount
// scales with quantity for by-weight items
$value = $infoW["discountValue"];
$valQ = "select l.upc, quantity
" . $this->baseSQL($transDB, $coupID, 'upc') . "
and h.type in ('BOTH', 'DISCOUNT')
and l.total > 0
order by unitPrice asc";
$valR = $transDB->query($valQ);
$row = $transDB->fetch_row($valR);
$value = $row[1] * $value;
break;
case 'PI': // per-item discount
// take of the request amount times the
// number of matching items.
$value = $infoW["discountValue"];
$valQ = "
SELECT
SUM(CASE WHEN ItemQtty IS NULL THEN 0 ELSE ItemQtty END) AS qty
" . $this->baseSQL($transDB, $coupID, 'upc') . "
and h.type in ('BOTH', 'DISCOUNT')";
$valR = $transDB->query($valQ);
$row = $transDB->fetch_row($valR);
if ($row['qty'] > $infoW['minValue'] && $infoW['minType'] == 'Q-') {
$value *= $infoW['minValue'];
} else {
$value = $row['qty'] * $value;
}
break;
case 'PS': // per set of items
$value = $infoW["discountValue"];
$qualQ = "
SELECT
SUM(CASE WHEN ItemQtty IS NULL THEN 0 ELSE ItemQtty END) AS qty
" . $this->baseSQL($transDB, $coupID, 'upc') . "
and h.type in ('BOTH', 'QUALIFIER')";
$qualR = $transDB->query($qualQ);
$qualW = $transDB->fetch_row($qualR);
$discQ = "
SELECT
SUM(CASE WHEN ItemQtty IS NULL THEN 0 ELSE ItemQtty END) AS qty
" . $this->baseSQL($transDB, $coupID, 'upc') . "
and h.type in ('BOTH', 'DISCOUNT')";
$discR = $transDB->query($discQ);
$discW = $transDB->fetch_row($discR);
$sets = ($qualW['qty'] > $discW['qty']) ? $discW['qty'] : $qualW['qty'];
$sets = ($sets % 2 == 0) ? $sets : $sets -= 1;
$value = $sets * $value;
break;
case 'SC':
$giftQ = "
SELECT COUNT(*) AS cards,
SUM(CASE WHEN total IS NULL THEN 0 ELSE total END) AS ttl
" . $this->baseSQL($transDB, $coupID, 'department') . "
and h.type in ('BOTH', 'DISCOUNT') AND l.trans_type='D'";
$giftR = $transDB->query($giftQ);
$giftW = $transDB->fetchRow($giftR);
$freeCards = floor($giftW['ttl'] / $infoW['minValue']);
$value = $infoW['discountValue'] * $freeCards;
if ($value > 80) {
$value = 80;
}
$discountable = 0;
$curR = $transDB->prepare("SELECT SUM(-1 * total) AS ttl FROM translog.localtemptrans WHERE upc='0049999900467'");
$current = $transDB->getValue($curR);
if ($value - $current) {
TransRecord::addtender('Store Credit', 'SC', $value - $current);
}
\CoreLocal::set("receiptToggle",1);
break;
case "F": // completely flat; no scaling for weight
$value = $infoW["discountValue"];
$discountable = 0;
break;
case "FC": // flat but capped at current amount due
$value = $infoW["discountValue"];
Database::getsubtotals();
if ($value > $this->session->get('amtdue')) {
$value = $this->session->get('amtdue');
}
break;
case "%": // percent discount on all items
Database::getsubtotals();
$value = $infoW["discountValue"] * $this->session->get("discountableTotal");
break;
case "%B": // better percent discount applies
Database::getsubtotals();
$couponDiscount = (int)($infoW['discountValue']*100);
$value = 0;
if ($couponDiscount > $this->session->get('percentDiscount')) {
// coupon discount is better than customer's discount
// apply coupon & zero out customer's discount
$value = $infoW["discountValue"] * $this->session->get("discountableTotal");
$this->session->set('percentDiscount', 0);
$transDB->query('UPDATE localtemptrans SET percentDiscount=0');
}
$discountable = 0;
break;
case "%I": // percent discount on all relevant items
$valQ = "select sum(total), max(tax)
" . $this->baseSQL($transDB, $coupID, 'upc') . "
and h.type in ('BOTH', 'DISCOUNT')";
$valR = $transDB->query($valQ);
$row = $transDB->fetch_row($valR);
$value = $row[0] * $infoW["discountValue"];
$tax = $row[1];
break;
case "%D": // percent discount on all items in give department(s)
$valQ = "select sum(total)
" . $this->baseSQL($transDB, $coupID, 'department') . "
and h.type in ('BOTH', 'DISCOUNT') AND l.discountable >= 0";
$valR = $transDB->query($valQ);
$row = $transDB->fetch_row($valR);
$value = $row[0] * $infoW["discountValue"];
break;
case "%S": // percent discount on all items in give department(s)
// excluding sale items
$valQ = "select sum(total)
" . $this->baseSQL($transDB, $coupID, 'department') . "
and h.type in ('BOTH', 'DISCOUNT') AND l.discounttype = 0";
$valR = $transDB->query($valQ);
$row = $transDB->fetch_row($valR);
$value = $row[0] * $infoW["discountValue"];
break;
case "%E": // better percent discount applies to specified department only
Database::getsubtotals();
$couponDiscount = (int)($infoW['discountValue']*100);
$value = 0;
if ($couponDiscount > $this->session->get('percentDiscount')) {
// coupon discount is better than customer's discount
// apply coupon & exclude those items from customer's discount
$valQ = "select sum(total)
" . $this->baseSQL($transDB, $coupID, 'department') . "
and h.type in ('BOTH', 'DISCOUNT')";
$valR = $transDB->query($valQ);
$row = $transDB->fetch_row($valR);
$value = $row[0] * $infoW["discountValue"];
$clearQ = "
UPDATE localtemptrans AS l
INNER JOIN " . $this->session->get('pDatabase') . $transDB->sep() . "houseCouponItems AS h ON l.department = h.upc
SET l.discountable=0
WHERE h.coupID = " . $coupID . "
AND h.type IN ('BOTH', 'DISCOUNT')";
$clearR = $transDB->query($clearQ);
}
break;
case 'PD': // modify customer percent discount
// rather than add line-item
$couponPD = $infoW['discountValue'] * 100;
DiscountModule::updateDiscount(new DiscountModule($couponPD, 'HouseCoupon'));
// still need to add a line-item with the coupon UPC to the
// transaction to track usage
$value = 0;
$description = $couponPD . ' % Discount Coupon';
break;
case 'OD': // override customer percent discount
// rather than add line-item
$couponPD = $infoW['discountValue'] * 100;
DiscountModule::updateDiscount(new DiscountModule(0, 'custdata'));
DiscountModule::updateDiscount(new DiscountModule($couponPD, 'HouseCoupon'));
// still need to add a line-item with the coupon UPC to the
// transaction to track usage
$value = 0;
$description = $couponPD . ' % Discount Coupon';
break;
}
if ($infoW['maxValue'] > 0 && $value > $infoW['maxValue']) {
$value = $infoW['maxValue'];
}
return array('value' => $value, 'department' => $infoW['department'], 'description' => $description, 'discountable'=>$discountable, 'tax'=>$tax);
}
/**
This FROM/WHERE is super repetitive
*/
private function baseSQL($dbc, $coupID, $mode='upc')
{
$ret = '
FROM localtemptrans AS l
INNER JOIN ' . $this->session->get('pDatabase') . $dbc->sep() . 'houseCouponItems AS h
ON h.upc=' . ($mode=='upc' ? 'l.upc' : 'l.department') . '
WHERE h.coupID=' . ((int)$coupID);
return $ret;
}
}