fannie/ordering/OrderItemLib.php
<?php
/**
@class OrderItemLib
Collection of methods to handle adding
different types of items to special orders.
*/
class OrderItemLib
{
private static $generic_item = array(
'upc' => '',
'sku' => '',
'brand' => '',
'description' => '',
'normal_price' => 0,
'special_price' => 0,
'cost' => 0,
'saleCost' => 0,
'discounttype' => 0,
'discountable' => 1,
'caseSize' => 1,
'department' => 0,
'stocked' => 0,
'vendorID' => 0,
'vendorName' => '',
);
/**
Lookup an item by UPC or SKU and return a
standardized array representation
*/
public static function getItem($upc)
{
if (trim($upc) !== '' && !preg_match('/^0+$/', trim($upc))) {
$item = self::getItemByUPC($upc);
if ($item !== false) {
return $item;
}
$item = self::getItemBySKU($upc);
if ($item !== false) {
return $item;
}
}
$item = self::$generic_item;
$item['description'] = $upc;
$item['upc'] = $upc;
return $item;
}
/**
Find an item by UPC
*/
private static function getItemByUPC($upc)
{
$dbc = self::dbc();
$upc = BarcodeLib::padUPC($upc);
/**
Lookup one:
Item should be found in products, may optionally
have info in the vendorItems table. Not in use items
are purposely excluded. Their price and cost information
may well not be up-to-date.
*/
$prodP = $dbc->prepare('
SELECT p.brand,
p.description,
p.normal_price,
p.special_price,
CASE WHEN p.cost=0 AND v.cost<>0 THEN v.cost ELSE p.cost END AS cost,
COALESCE(v.saleCost, 0) AS saleCost,
p.discounttype,
p.discount AS discountable,
COALESCE(v.units, 1) AS caseSize,
1 AS stocked,
p.default_vendor_id AS vendorID,
COALESCE(n.vendorName, \'\') AS vendorName,
p.upc,
COALESCE(v.sku, \'\') AS sku,
p.department,
r.priceRuleTypeID
FROM products AS p
LEFT JOIN vendorItems AS v ON p.upc=v.upc AND p.default_vendor_id=v.vendorID
LEFT JOIN vendors AS n ON p.default_vendor_id=n.vendorID
LEFT JOIN PriceRules AS r ON p.price_rule_id=r.priceRuleID
WHERE p.upc=?
AND (p.inUse=1 OR p.discounttype=1)
');
$prodR = $dbc->execute($prodP, array($upc));
if ($prodR && $dbc->numRows($prodR) > 0) {
return self::resultToItem($dbc, $prodR);
}
/**
Lookup two:
Not in products. Try vendorItems for not-stocked
items.
*/
$prodP = $dbc->prepare('
SELECT v.brand,
v.description,
v.SRP AS normal_price,
0 AS special_price,
v.cost,
v.saleCost,
0 AS discounttype,
1 AS discountable,
v.units AS caseSize,
0 AS stocked,
v.vendorID,
COALESCE(n.vendorName, \'\') AS vendorName,
v.upc,
v.sku,
0 AS department,
0 AS priceRuleTypeID
FROM vendorItems AS v
LEFT JOIN vendors AS n ON v.vendorID=n.vendorID
WHERE v.upc=?
ORDER BY v.vendorID
');
$prodR = $dbc->execute($prodP, array($upc));
if ($prodR && $dbc->numRows($prodR) > 0) {
return self::resultToItem($dbc, $prodR);
}
// found nothing.
return false;
}
private static function getItemBySKU($sku)
{
$dbc = self::dbc();
// legacy behavior. + forces entry to be treated
// as a SKU instead of a UPC.
if (substr($sku, 0, 1) == '+') {
$sku = substr($sku, 1);
}
/**
Lookup one:
Item should be found in products, may optionally
have info in the vendorItems table. Not in use items
are purposely excluded. Their price and cost information
may well not be up-to-date.
*/
$prodP = $dbc->prepare('
SELECT p.brand,
p.description,
p.normal_price,
p.special_price,
CASE WHEN p.cost=0 AND v.cost<>0 THEN v.cost ELSE p.cost END AS cost,
COALESCE(v.saleCost, 0) AS saleCost,
p.discounttype,
p.discount AS discountable,
COALESCE(v.units, 1) AS caseSize,
1 AS stocked,
p.default_vendor_id AS vendorID,
COALESCE(n.vendorName, \'\') AS vendorName,
p.upc,
COALESCE(v.sku, \'\') AS sku,
p.department,
r.priceRuleTypeID
FROM products AS p
LEFT JOIN vendorItems AS v ON p.upc=v.upc AND p.default_vendor_id=v.vendorID
LEFT JOIN vendors AS n ON p.default_vendor_id=n.vendorID
LEFT JOIN PriceRules AS r ON p.price_rule_id=r.priceRuleID
WHERE v.sku LIKE ?
AND (p.inUse=1 OR p.discounttype=1)
');
$prodR = $dbc->execute($prodP, array('%' . $sku));
if ($prodR && $dbc->numRows($prodR) > 0) {
return self::resultToItem($dbc, $prodR);
}
/**
Lookup two:
Not in products. Try vendorItems for not-stocked
items.
*/
$prodP = $dbc->prepare('
SELECT v.brand,
v.description,
v.SRP AS normal_price,
0 AS special_price,
v.cost,
v.saleCost,
0 AS discounttype,
1 AS discountable,
v.units AS caseSize,
0 AS stocked,
v.vendorID,
v.upc,
v.sku,
COALESCE(n.vendorName, \'\') AS vendorName,
0 AS department,
0 AS priceRuleTypeID
FROM vendorItems AS v
LEFT JOIN vendors AS n ON v.vendorID=n.vendorID
WHERE v.sku LIKE ?
ORDER BY v.vendorID
');
$prodR = $dbc->execute($prodP, array('%' . $sku));
if ($prodR && $dbc->numRows($prodR) > 0) {
return self::resultToItem($dbc, $prodR);
}
// found nothing.
return false;
}
/**
Helper: merge a SQL result with the generic item record
*/
private static function resultToItem($dbc, $prodR)
{
$prodW = $dbc->fetchRow($prodR);
$ret = self::$generic_item;
foreach ($ret as $key => $val) {
if (isset($prodW[$key])) {
$ret[$key] = $prodW[$key];
}
}
if (FannieConfig::config('COOP_ID') === 'WFC_Duluth'
&& ($prodW['priceRuleTypeID'] == 6 || $prodW['priceRuleTypeID'] == 7 || $prodW['priceRuleTypeID'] == 8)) {
$ret['discountable'] = 0;
}
return $ret;
}
/**
Helper: get database handle
*/
private static function dbc()
{
return FannieDB::get(FannieConfig::config('OP_DB'));
}
/**
Lookup special order department mapping
*/
public static function mapDepartment($deptID)
{
$dbc = self::dbc();
$mapP = $dbc->prepare('
SELECT map_to
FROM ' . FannieConfig::config('TRANS_DB') . $dbc->sep() . 'SpecialOrderDeptMap
WHERE dept_ID=?
');
$mapR = $dbc->execute($mapP, array($deptID));
if ($mapR === false || $dbc->numRows($mapR) == 0) {
return $deptID;
} else {
$row = $dbc->fetchRow($mapR);
return $row['map_to'];
}
}
public static function getUnitPrice($item, $is_member)
{
if ($item['stocked'] && self::useSalePrice($item, $is_member)) {
return $item['special_price'];
}
$altSale = self::alternateSaleApplies($item);
return $altSale ? $altSale : $item['normal_price'];
}
/**
Get the unit price for an item based on pricing
rules
*/
public static function getEffectiveUnit($item, $is_member)
{
if ($item['stocked'] && self::useSalePrice($item, $is_member)) {
return $item['special_price'];
} elseif ($item['stocked']) {
$altSale = self::alternateSaleApplies($item);
return $altSale ? $altSale : self::stockedUnitPrice($item, $is_member);
}
return self::notStockedUnitPrice($item, $is_member);
}
public static function getCasePrice($item, $is_member)
{
return $item['caseSize'] * self::getEffectiveUnit($item, $is_member);
}
/**
Apply pricing rules for items that are not
currently stocked in-store
*/
private static function notStockedUnitPrice($item, $is_member)
{
if ($item['discountable']) {
return self::markUpOrDown($item, $is_member);
}
return $item['normal_price'];
}
/**
Apply pricing rules for items that are
currently stocked in-store
*/
private static function stockedUnitPrice($item, $is_member)
{
if ($item['discountable']) {
return self::markUpOrDown($item, $is_member);
}
return $item['normal_price'];
}
public static function markUpOrDown($item, $is_member)
{
if ($is_member['type'] == 'markdown') {
return (1.0-$is_member['amount']) * $item['normal_price'];
} elseif ($is_member['type'] == 'markup') {
return (1.0+$is_member['amount']) * $item['cost'];
} else {
return $item['normal_price'];
}
}
/**
Decide if the sale price be used for this item
*/
public static function useSalePrice($item, $is_member)
{
/**
@Configurability: need to be able to turn off sale
pricing entirely if desired rather than just for
specific batch types
*/
if ($item['special_price'] == 0) {
return false;
} elseif ($item['discounttype'] == 1) {
return self::saleApplies($item);
} elseif ($item['discounttype'] == 2 && $is_member['isMember']) {
return self::saleApplies($item);
}
return false;
}
/**
Find the batch and check whether that batch type
is so-eligible
*/
private static function saleApplies($item)
{
$dbc = self::dbc();
$saleP = $dbc->prepare('
SELECT t.specialOrderEligible
FROM batchList AS l
INNER JOIN batches AS b ON l.batchID=b.batchID
INNER JOIN batchType AS t ON t.batchTypeID=b.batchType
WHERE l.upc=?
AND l.salePrice=?
AND b.startDate <= ' . $dbc->curdate() . '
AND b.endDate >= ' . $dbc->curdate()
);
$eligible = $dbc->getValue($saleP, array($item['upc'], $item['special_price']));
if ($eligible) {
return true;
}
$lcP = $dbc->prepare('SELECT likeCode FROM upcLike WHERE upc=?');
$like = $dbc->getValue($lcP, array($item['upc']));
if (!$like) {
return false;
}
$eligible = $dbc->getValue($saleP, array('LC' . $like, $item['special_price']));
return $eligible ? true : false;
}
/**
Batches may overlap. An item will actively be in the batch with
the lowest sale price but may be in an additional batch as well.
Further, not all batch types may be special-order-eligible.
E.g., an item is in two batches:
1. DISCONTINUED batch type, $1.79, not SPO eligible
2. REGULAR SALE batch type, $1.99, SPO eligible
The item will be actively in the DISCONINTUED batch and self::saleApplies()
will return false since that batch is not SPO eligible. This method
however will return 1.99 as an alternate sale price point.
*/
private static function alternateSaleApplies($item)
{
$dbc = self::dbc();
$saleP = $dbc->prepare("
SELECT MIN(l.salePrice)
FROM batchList AS l
INNER JOIN batches AS b ON l.batchId=b.batchID
INNER JOIN batchType AS t ON t.batchTypeID=b.batchType
WHERE l.upc=?
AND t.specialOrderEligible=1
AND l.salePrice < ?
AND b.startDate <= " . $dbc->curdate() . "
AND b.endDate >= " . $dbc->curdate()
);
$altSale = $dbc->getValue($saleP, array($item['upc'], $item['normal_price']));
return $altSale ? $altSale : false;
}
/**
Check whether a manual quantity is required for this item
@return [boolean] false if not required or
[integer] default quantity
*/
public static function manualQuantityRequired($item)
{
if (isset($item['stocked']) && isset($item['caseSize'])) {
return $item['stocked'] && $item['caseSize'] == 1;
}
$dbc = self::dbc();
$table = FannieConfig::config('TRANS_DB') . $dbc->sep() . 'SpecialOrderDeptMap';
$superP = $dbc->prepare("
SELECT minQty
FROM " . $table . "
WHERE dept_ID=?");
$req = $dbc->getValue($superP, array($item['department']));
if ($req > 0) {
return $req;
} else {
return false;
}
}
public static function memPricing($cardno)
{
$default = array(
'type' => 'markdown',
'amount' => 0,
'isMember' => false,
);
$dbc = self::dbc();
$table = FannieConfig::config('TRANS_DB') . $dbc->sep() . 'SpecialOrderMemDiscounts';
$prep = $dbc->prepare('
SELECT d.type,
d.amount,
c.Type AS cType
FROM ' . $table . ' AS d
INNER JOIN custdata AS c ON c.memType=d.memType
WHERE c.CardNo=?
AND c.personNum=1
');
$res = $dbc->getRow($prep, array($cardno));
if ($res) {
return array(
'type' => $res['type'],
'amount' => $res['amount'],
'isMember' => $res['cType'] == 'PC' ? true : false,
);
} else {
return $default;
}
}
}