public function definition()
{
$FANNIE_AR_DEPARTMENTS = FannieConfig::config('AR_DEPARTMENTS', '');
$ret = preg_match_all('/[0-9]+/', $FANNIE_AR_DEPARTMENTS, $depts);
if ($ret == 0) {
$depts = array(-999);
} else {
$depts = array_pop($depts);
}
$in = '';
foreach ($depts as $d) {
$in .= sprintf('%d,', $d);
}
$in = substr($in, 0, strlen($in) - 1);
return '
SELECT card_no,
SUM(CASE WHEN trans_subtype=\'MI\' THEN -total ELSE 0 END) AS charges,
SUM(CASE WHEN department IN (' . $in . ') THEN total ELSE 0 END) AS payments,
SUM(CASE WHEN trans_subtype=\'MI\' THEN -total ELSE 0 END)
- SUM(CASE WHEN department IN (' . $in . ') THEN total ELSE 0 END) AS balance
FROM dlog
WHERE (trans_subtype=\'MI\' OR department IN (' . $in . '))
AND ' . $this->connection->datediff('tdate', $this->connection->now()) . ' = 0
GROUP BY card_no';
}
public function verboseDebugging()
{
if (class_exists('FannieConfig') && FannieConfig::config('CUSTOM_ERRORS') >= 1) {
return true;
} else {
return false;
}
}
/**
Standardization method to ensure shelf tag
fields are calculated consistently for a given
item.
@param $price [optional, default false] use a specified price
rather than the product's current price
@return [keyed array] of tag data with the following keys:
- upc
- description
- brand
- normal_price
- sku
- size
- units
- vendor
- pricePerUnit
*/
public function getTagData($price = false)
{
/**
If a custom data source has been specified, let
that handle the calculations
*/
if (FannieConfig::config('TAG_DATA_SOURCE') !== '' && class_exists(FannieConfig::config('TAG_DATA_SOURCE'))) {
$source = FannieConfig::config('TAG_DATA_SOURCE');
$obj = new $source();
return $obj->getTagData($this->connection, $this->upc(), $price);
}
$query = '
SELECT p.upc,
p.description,
p.normal_price,
COALESCE(p.brand, x.manufacturer) AS brand,
COALESCE(v.vendorName, x.distributor) AS vendor,
p.size AS p_size,
p.unitofmeasure,
i.sku,
i.units,
i.size AS vi_size
FROM products AS p
LEFT JOIN prodExtra AS x ON p.upc=x.upc
LEFT JOIN vendors AS v ON p.default_vendor_id=v.vendorID
LEFT JOIN vendorItems AS i ON p.upc=i.upc AND v.vendorID=i.vendorID
WHERE p.upc=?';
$prep = $this->connection->prepare($query);
$res = $this->connection->execute($prep, array($this->upc()));
$ret = array('upc' => $this->upc(), 'description' => $this->description(), 'brand' => $this->brand(), 'normal_price' => $this->normal_price(), 'sku' => '', 'size' => '', 'units' => '', 'vendor' => '', 'pricePerUnit' => '');
if (!$res || $this->connection->numRows($res) == 0) {
return $ret;
}
$row = $this->connection->fetchRow($res);
$ret['description'] = $row['description'];
$ret['brand'] = $row['brand'];
$ret['vendor'] = $row['vendor'];
$ret['sku'] = $row['sku'];
$ret['units'] = $row['units'];
if ($price !== false) {
$ret['normal_price'] = $price;
} else {
$ret['normal_price'] = $row['normal_price'];
}
if (is_numeric($row['p_size']) && !empty($row['p_size']) && !empty($row['unitofmeasure'])) {
$ret['size'] = $row['p_size'] . ' ' . $row['unitofmeasure'];
} elseif (!empty($row['p_size'])) {
$ret['size'] = $row['p_size'];
} elseif (!empty($row['vi_size'])) {
$ret['size'] = $row['vi_size'];
}
$ret['pricePerUnit'] = \COREPOS\Fannie\API\lib\PriceLib::pricePerUnit($ret['normal_price'], $ret['size']);
return $ret;
}
/**
Assign batch to all stores
@param $batchID [int] batch ID
*/
public static function initBatch($batchID)
{
$dbc = FannieDB::get(FannieConfig::config('OP_DB'));
$map = new StoreBatchMapModel($dbc);
$stores = new StoresModel($dbc);
foreach ($stores->find() as $s) {
$map->storeID($s->storeID());
$map->batchID($batchID);
$map->save();
}
}
public function __construct()
{
$this->start_timestamp = microtime(true);
$auth_default = FannieConfig::config('AUTH_DEFAULT', false);
$coop_id = FannieConfig::config('COOP_ID');
if ($auth_default && !$this->must_authenticate) {
$this->must_authenticate = $auth_default;
}
if (isset($coop_id) && $coop_id == 'WEFC_Toronto') {
$this->auth_classes[] = 'admin';
}
}
function deleteProductAllLanes($upc)
{
$FANNIE_OP_DB = FannieConfig::config('OP_DB');
$FANNIE_LANES = FannieConfig::config('LANES');
$laneupdate_sql = FannieDB::get($FANNIE_OP_DB);
for ($i = 0; $i < count($FANNIE_LANES); $i++) {
$tmp = new SQLManager($FANNIE_LANES[$i]['host'], $FANNIE_LANES[$i]['type'], $FANNIE_LANES[$i]['op'], $FANNIE_LANES[$i]['user'], $FANNIE_LANES[$i]['pw']);
if (!isset($tmp->connections[$FANNIE_LANES[$i]['op']]) || $tmp->connections[$FANNIE_LANES[$i]['op']] === false) {
// connect failed
continue;
}
$delQ = $tmp->prepare_statement("DELETE FROM products WHERE upc=?");
$delR = $tmp->exec_statement($delQ, array($upc), $FANNIE_LANES[$i]['op']);
}
}
public function showEditForm($upc, $display_mode = 1, $expand_mode = 1)
{
$FANNIE_LANES = FannieConfig::config('LANES');
$upc = BarcodeLib::padUPC($upc);
$queryItem = "SELECT * FROM products WHERE upc = ?";
$ret = '<div id="AllLanesFieldset" class="panel panel-default">';
$ret .= "<div class=\"panel-heading\"><a href=\"\" onclick=\"\$('#AllLanesFieldsetContent').toggle();return false;\">\n Lane Status\n </a></div>";
$css = $expand_mode == 1 ? '' : ' collapse';
$ret .= '<div id="AllLanesFieldsetContent" class="panel-body' . $css . '">';
for ($i = 0; $i < count($FANNIE_LANES); $i++) {
$f = $FANNIE_LANES[$i];
$sql = new SQLManager($f['host'], $f['type'], $f['op'], $f['user'], $f['pw']);
if (!is_object($sql) || $sql->connections[$f['op']] === False) {
$ret .= "<li class=\"alert-danger\">Can't connect to lane " . ($i + 1) . "</li>";
continue;
}
$prep = $sql->prepare_statement($queryItem);
$resultItem = $sql->exec_statement($prep, array($upc));
$num = $sql->num_rows($resultItem);
if ($num == 0) {
$ret .= "<li class=\"alert-danger\">Item <strong>{$upc}</strong> not found on Lane " . ($i + 1) . "</li>";
} else {
if ($num > 1) {
$ret .= "<li class=\"alert-danger\">Item <strong>{$upc}</strong> found multiple times on Lane " . ($i + 1);
$ret .= '<ul>';
while ($rowItem = $sql->fetch_array($resultItem)) {
$ret .= "<li>{$rowItem['upc']} {$rowItem['description']}</li>";
}
$ret .= '</ul></li>';
} else {
$rowItem = $sql->fetch_array($resultItem);
$ret .= "<li>Item <span style=\"color:red;\">{$upc}</span> on Lane " . ($i + 1) . "<ul>";
$ret .= "<li>Price: {$rowItem['normal_price']}</li>";
if ($rowItem['discounttype'] != 0) {
$ret .= "<li class=\"alert-success\">ON SALE: {$rowItem['special_price']}</li>";
}
$ret .= "</ul></li>";
}
}
}
$ret .= '</ul>';
$ret .= '</div>';
$ret .= '</div>';
return $ret;
}
/**
Check submitted credentials. Redirect to destination
on success, proceed to error message on failure
*/
public function post_name_password_handler()
{
$name = FormLib::get('name');
$password = FormLib::get('password');
$login = login($name, $password);
$redirect = FormLib::get('redirect', 'menu.php');
if (!$login && FannieConfig::config('AUTH_LDAP', false)) {
$login = ldap_login($name, $password);
}
if (!$login && FannieConfig::config('AUTH_SHADOW', false)) {
$login = shadow_login($name, $password);
}
if ($login) {
header("Location: {$redirect}");
return false;
} else {
return true;
}
}
public function testFannieSignage()
{
$dbc = FannieDB::get(FannieConfig::config('OP_DB'));
$dbc->throwOnFailure(true);
$signs = new \COREPOS\Fannie\API\item\FannieSignage(array(), 'shelftags', 1);
$signs->setDB($dbc);
$this->assertInternalType('array', $signs->loadItems());
$signs = new \COREPOS\Fannie\API\item\FannieSignage(array(), 'batchbarcodes', 1);
$signs->setDB($dbc);
$this->assertInternalType('array', $signs->loadItems());
$signs = new \COREPOS\Fannie\API\item\FannieSignage(array(), 'batch', 1);
$signs->setDB($dbc);
$this->assertInternalType('array', $signs->loadItems());
foreach (range(0, 3) as $i) {
$signs = new \COREPOS\Fannie\API\item\FannieSignage(array('0000000000111'), '', $i);
$signs->setDB($dbc);
$this->assertInternalType('array', $signs->loadItems());
}
}
public function showEditForm($upc, $display_mode = 1, $expand_mode = 1)
{
$FANNIE_URL = FannieConfig::config('URL');
$upc = BarcodeLib::padUPC($upc);
$ret = '';
$ret = '<div id="LinksFieldset" class="panel panel-default">';
$ret .= "<div class=\"panel-heading\">\n <a href=\"\" onclick=\"\$('#LinksContents').toggle();return false;\">\n Links\n </a></div>";
$css = $expand_mode == 1 ? '' : ' collapse';
$ret .= '<div id="LinksContents" class="panel-body' . $css . '">';
// class="col-lg-1" works pretty well with META_WIDTH_HALF
$ret .= '<div id="LinksList" class="col-sm-5">';
$dbc = $this->db();
$p = $dbc->prepare_statement('SELECT upc FROM products WHERE upc=?');
$r = $dbc->exec_statement($p, array($upc));
if ($dbc->num_rows($r) > 0) {
$ret .= '<div style="width:40%; float:left;">';
$ret .= "<li><a href=\"javascript:shelftag('{$upc}');\">" . "New Shelf Tag</a></li>";
$ret .= "<li><a href=\"{$FANNIE_URL}item/DeleteItemPage.php?id={$upc}" . "\">Delete this item</a></li>";
$ret .= '</div>';
$ret .= '<div style="width:40%; float:left;">';
$ret .= "<li><a href=\"{$FANNIE_URL}reports/PriceHistory/?upc={$upc}\" " . "target=\"_price_history\">Price History</a></li>";
$ret .= "<li><a href=\"{$FANNIE_URL}reports/RecentSales/?upc={$upc}\" " . "target=\"_recentsales\">Recent Sales History</a></li>";
$ret .= '</div>';
$ret .= '<div style="clear:left;"></div>';
$ret .= "<script type=\"text/javascript\">";
$ret .= "function shelftag(u){";
$ret .= "testwindow= window.open (\"addShelfTag.php?upc=\"+u, " . "\"New Shelftag\",\"location=0,status=1,scrollbars=1,width=300," . "height=650\");";
$ret .= "testwindow.moveTo(50,50);";
$ret .= "}";
$ret .= "</script>";
} else {
$ret .= sprintf('<input type="checkbox" name="newshelftag" value="%s" />
Create Shelf Tag</li>', $upc);
}
$ret .= '</div>' . '<!-- /#LinksList -->';
$ret .= '</div>' . '<!-- /#LinksContents -->';
$ret .= '</div>' . '<!-- /#LinksFieldset -->';
return $ret;
}
public function definition()
{
$FANNIE_EQUITY_DEPARTMENTS = FannieConfig::config('EQUITY_DEPARTMENTS', '');
$ret = preg_match_all('/[0-9]+/', $FANNIE_EQUITY_DEPARTMENTS, $depts);
if ($ret == 0) {
$depts = array(-999);
} else {
$depts = array_pop($depts);
}
$in = '';
foreach ($depts as $d) {
$in .= sprintf('%d,', $d);
}
$in = substr($in, 0, strlen($in) - 1);
return '
SELECT card_no,
SUM(CASE WHEN department IN (' . $in . ') THEN total ELSE 0 END) AS totPayments,
MIN(tdate) AS startdate
FROM dlog
WHERE department IN (' . $in . ')
AND ' . $this->connection->datediff('tdate', $this->connection->now()) . ' = 0
GROUP BY card_no';
}
public function showEditForm($upc, $display_mode = 1, $expand_mode = 1)
{
$upc = BarcodeLib::padUPC($upc);
$dbc = $this->db();
$prod = new ProductsModel($dbc);
$prod->upc($upc);
if (FannieConfig::config('STORE_MODE') == 'HQ') {
$prod->store_id(FannieConfig::config('STORE_ID'));
}
$prod->load();
$ret = '<div id="FreshDealsFieldset" class="panel panel-default">';
$ret .= "<div class=\"panel-heading\">\n <a href=\"\" onclick=\"\$('#FreshDealsDiv').toggle();return false;\">\n Fresh Deals</a>\n </div>";
$ret .= '<div id="FreshDealsDiv" class="panel-body">';
$ret .= sprintf('<table class="table table-bordered"><tr>
<td>%s</td>
<td>%s</td>
<td>%s</td>
<td>$%.2f</td>
<td>$%.2f</td>
</tr></table>', $prod->brand(), $prod->description(), $prod->upc(), $prod->cost(), $prod->normal_price());
$ret .= '</div></div>';
return $ret;
}
public function showEditForm($upc, $display_mode = 1, $expand_mode = 1)
{
$FANNIE_URL = FannieConfig::config('URL');
$upc = BarcodeLib::padUPC($upc);
$dbc = $this->db();
$ret = '<div id="WebListingFieldset" class="panel panel-default">';
$ret .= "<div class=\"panel-heading\">\n <a href=\"\" onclick=\"\$('#WebListingFieldsetContent').toggle();return false;\">\n Website Listing</a>\n </div>";
$pu = new ProductUserModel($dbc);
$pu->upc($upc);
$pu->load();
$css = $expand_mode == 1 || $pu->enableOnline() ? '' : ' collapse';
$ret .= '<div id="WebListingFieldsetContent" class="panel-body' . $css . '">';
$ret .= '<div class="form-group">
<label>
<input type="checkbox" name="u_online" value="1" ' . ($pu->enableOnline() ? 'checked' : '') . ' />
Sell Online
</label>
</div>';
$ret .= '<div class="form-group">
<label>
<input type="checkbox" name="u_soldout" value="1" ' . ($pu->soldOut() ? 'checked' : '') . ' />
Sold Out
</label>
</div>';
$ret .= '<input type="hidden" name="u_already_online" value="' . ($pu->enableOnline() ? 1 : 0) . '" />';
if ($dbc->tableExists('productExpires')) {
$e = new ProductExpiresModel($dbc);
$e->upc($upc);
$e->load();
$ret .= '<div class="form-group">' . '<label>Expires</label> ' . '<input type="text" class="form-control date-field" id="u_expires" name="u_expires"
value="' . ($e->expires() == '' ? '' : date('Y-m-d', strtotime($e->expires()))) . '" />' . '</div>';
}
$ret .= '</div>';
$ret .= '</div>';
return $ret;
}
/**
Cleanup after forcibly starting or stopping a sales batch
- Update lane item records to reflect on/off sale
- Log changes to prodUpdate
@param $id [int] batchID
@param $updateType [cost] ProdUpdateModel update type
@param $has_limit [boolean] products.special_limit and batches.transLimit
columns are present
Separate method since it's identical for starting
and stopping
*/
private function finishForce($id, $updateType, $has_limit = true)
{
$columnsP = $this->connection->prepare('
SELECT p.upc,
p.normal_price,
p.special_price,
p.modified,
p.specialpricemethod,
p.specialquantity,
p.specialgroupprice,
' . ($has_limit ? 'p.special_limit,' : '') . '
p.discounttype,
p.mixmatchcode,
p.start_date,
p.end_date
FROM products AS p
INNER JOIN batchList AS b ON p.upc=b.upc
WHERE b.batchID=?');
$lcColumnsP = $this->connection->prepare('
SELECT p.upc,
p.normal_price,
p.special_price,
p.modified,
p.specialpricemethod,
p.specialquantity,
p.specialgroupprice,
' . ($has_limit ? 'p.special_limit,' : '') . '
p.discounttype,
p.mixmatchcode,
p.start_date,
p.end_date
FROM products AS p
INNER JOIN upcLike AS u ON p.upc=u.upc
INNER JOIN batchList AS b
ON b.upc = ' . $this->connection->concat("'LC'", $this->connection->convert('u.likeCode', 'CHAR'), '') . '
WHERE b.batchID=?');
/**
Get changed columns for each product record
*/
$upcs = array();
$columnsR = $this->connection->execute($columnsP, array($id));
while ($w = $this->connection->fetch_row($columnsR)) {
$upcs[$w['upc']] = $w;
}
$columnsR = $this->connection->execute($lcColumnsP, array($id));
while ($w = $this->connection->fetch_row($columnsR)) {
$upcs[$w['upc']] = $w;
}
$updateQ = '
UPDATE products AS p SET
p.normal_price = ?,
p.special_price = ?,
p.modified = ?,
p.specialpricemethod = ?,
p.specialquantity = ?,
p.specialgroupprice = ?,
p.discounttype = ?,
p.mixmatchcode = ?,
p.start_date = ?,
p.end_date = ?
' . ($has_limit ? ',p.special_limit = ?' : '') . '
WHERE p.upc = ?';
/**
Update all records on each lane before proceeding
to the next lane. Hopefully faster / more efficient
*/
$FANNIE_LANES = FannieConfig::config('LANES');
for ($i = 0; $i < count($FANNIE_LANES); $i++) {
$lane_sql = new SQLManager($FANNIE_LANES[$i]['host'], $FANNIE_LANES[$i]['type'], $FANNIE_LANES[$i]['op'], $FANNIE_LANES[$i]['user'], $FANNIE_LANES[$i]['pw']);
if (!isset($lane_sql->connections[$FANNIE_LANES[$i]['op']]) || $lane_sql->connections[$FANNIE_LANES[$i]['op']] === false) {
// connect failed
continue;
}
$updateP = $lane_sql->prepare($updateQ);
foreach ($upcs as $upc => $data) {
$args = array($data['normal_price'], $data['special_price'], $data['modified'], $data['specialpricemethod'], $data['specialquantity'], $data['specialgroupprice'], $data['discounttype'], $data['mixmatchcode'], $data['start_date'], $data['end_date']);
if ($has_limit) {
$args[] = $data['special_limit'];
}
$args[] = $upc;
$lane_sql->execute($updateP, $args);
}
}
$update = new ProdUpdateModel($this->connection);
$update->logManyUpdates(array_keys($upcs), $updateType);
}
function auth_enabled()
{
if (!class_exists('FannieConfig')) {
include dirname(__FILE__) . '/../classlib2.0/FannieConfig.php';
}
return FannieConfig::config('AUTH_ENABLED', false);
}
public static function getDB()
{
$settings = FannieConfig::config('PLUGIN_SETTINGS');
return FannieDB::get($settings[static::$db_setting]);
}
/**
Generate FROM and WHERE clauses with appropriate parameters
and joins based on the standard form submissions.
@return [keyed array]
- query [string] from and where clauses
- args [array] corresponding parameters
*/
public static function standardItemFromWhere()
{
$op_db = FannieConfig::config('OP_DB');
$dbc = FannieDB::getReadOnly($op_db);
$start_date = self::getDate('date1', date('Y-m-d'));
$end_date = self::getDate('date2', date('Y-m-d'));
$dlog = DTransactionsModel::selectDlog($start_date, $end_date);
$lookupType = self::get('lookup-type', 'dept');
$query = '
FROM ' . $dlog . ' AS t
LEFT JOIN departments AS d ON t.department=d.dept_no
' . DTrans::joinProducts('t') . '
LEFT JOIN MasterSuperDepts AS m ON t.department=m.dept_ID
LEFT JOIN subdepts AS b ON p.subdept=b.subdept_no
LEFT JOIN vendors AS v ON p.default_vendor_id=v.vendorID
LEFT JOIN prodExtra AS x ON t.upc=x.upc ';
$args = array();
switch ($lookupType) {
case 'dept':
$super = FormLib::get('super-dept');
if ($super !== '' && $super >= 0) {
$query .= ' LEFT JOIN superdepts AS s ON t.department=s.dept_ID ';
}
break;
case 'manu':
break;
case 'vendor':
$query .= ' LEFT JOIN vendors AS z ON x.distributor=z.vendorName ';
break;
case 'likecode':
$query .= ' LEFT JOIN upcLike AS u ON t.upc=u.upc ';
break;
}
$query .= ' WHERE t.tdate BETWEEN ? AND ? ';
$args[] = $start_date . ' 00:00:00';
$args[] = $end_date . ' 23:59:59';
switch ($lookupType) {
case 'dept':
$super = FormLib::get('super-dept');
if ($super !== '' && $super >= 0) {
$query .= ' AND s.superID=? ';
$args[] = $super;
if (is_array(FormLib::get('departments')) && count(FormLib::get('departments')) > 0) {
$query .= ' AND t.department IN (';
foreach (FormLib::get('departments') as $d) {
$query .= '?,';
$args[] = $d;
}
$query = substr($query, 0, strlen($query) - 1) . ')';
} elseif (FormLib::get('dept-start') !== '' && FormLib::get('dept-end') !== '') {
$query .= ' AND t.department BETWEEN ? AND ? ';
$args[] = FormLib::get('dept-start');
$args[] = FormLib::get('dept-end');
}
} elseif ($super !== '' && $super == -2) {
$query .= ' AND m.superID <> 0 ';
if (is_array(FormLib::get('departments')) && count(FormLib::get('departments')) > 0) {
$query .= ' AND t.department IN (';
foreach (FormLib::get('departments') as $d) {
$query .= '?,';
$args[] = $d;
}
$query = substr($query, 0, strlen($query) - 1) . ')';
} elseif (FormLib::get('dept-start') !== '' && FormLib::get('dept-end') !== '') {
$query .= ' AND t.department BETWEEN ? AND ? ';
$args[] = FormLib::get('dept-start');
$args[] = FormLib::get('dept-end');
}
} elseif ($super === '') {
if (is_array(FormLib::get('departments')) && count(FormLib::get('departments')) > 0) {
$query .= ' AND t.department IN (';
foreach (FormLib::get('departments') as $d) {
$query .= '?,';
$args[] = $d;
}
$query = substr($query, 0, strlen($query) - 1) . ')';
} else {
$query .= ' AND t.department BETWEEN ? AND ? ';
$args[] = FormLib::get('dept-start', 1);
$args[] = FormLib::get('dept-end', 1);
}
}
if (is_array(FormLib::get('subdepts')) && count(FormLib::get('subdepts')) > 0) {
$query .= ' AND p.subdept IN (';
foreach (FormLib::get('subdepts') as $s) {
$query .= '?,';
$args[] = $s;
}
$query = substr($query, 0, strlen($query) - 1) . ')';
}
break;
case 'manu':
$mtype = FormLib::get('mtype');
if ($mtype == 'prefix') {
$query .= ' AND t.upc LIKE ? ';
$args[] = '%' . FormLib::get('manufacturer') . '%';
} else {
$query .= ' AND (p.brand LIKE ? OR x.manufacturer LIKE ?) ';
$manu = '%' . FormLib::get('manufacturer') . '%';
$args[] = $manu;
$args[] = $manu;
$optimizeP = $dbc->prepare('
SELECT p.department
FROM products AS p
LEFT JOIN prodExtra AS x ON p.upc=x.upc
WHERE (p.brand LIKE ? OR x.manufacturer LIKE ?)
GROUP BY p.department');
$optimizeR = $dbc->execute($optimizeP, array($manu, $manu));
$dept_in = '';
while ($optimizeW = $dbc->fetch_row($optimizeR)) {
$dept_in .= '?,';
$args[] = $optimizeW['department'];
}
if ($dept_in !== '') {
$dept_in = substr($dept_in, 0, strlen($dept_in) - 1);
$query .= ' AND t.department IN (' . $dept_in . ') ';
}
}
break;
case 'vendor':
$query .= ' AND (p.default_vendor_id=? OR z.vendorID=?) ';
$vID = FormLib::get('vendor', 1);
$args[] = $vID;
$args[] = $vID;
$optimizeP = $dbc->prepare('
SELECT p.department
FROM products AS p
LEFT JOIN prodExtra AS x ON p.upc=x.upc
LEFT JOIN vendors AS v ON x.distributor=v.vendorName
WHERE (p.default_vendor_id=? OR v.vendorID=?
GROUP BY p.department');
$optimizeR = $dbc->execute($optimizeP, array($vID, $vID));
$dept_in = '';
while ($optimizeW = $dbc->fetch_row($optimizeR)) {
$dept_in .= '?,';
$args[] = $optimizeW['department'];
}
if ($dept_in !== '') {
$dept_in = substr($dept_in, 0, strlen($dept_in) - 1);
$query .= ' AND t.department IN (' . $dept_in . ') ';
}
break;
case 'likecode':
$query .= ' AND u.likeCode BETWEEN ? AND ? ';
$args[] = FormLib::get('lc-start', 1);
$args[] = FormLib::get('lc-end', 1);
$optimizeP = $dbc->prepare('
SELECT p.department
FROM products AS p
INNER JOIN upcLike AS u ON p.upc=u.upc
WHERE u.likeCode BETWEEN ? AND ?
GROUP BY p.department');
$optimizeR = $dbc->execute($optimizeP, array(FormLib::get('lc-start', 1), FormLib::get('lc-end', 1)));
$dept_in = '';
while ($optimizeW = $dbc->fetch_row($optimizeR)) {
$dept_in .= '?,';
$args[] = $optimizeW['department'];
}
if ($dept_in !== '') {
$dept_in = substr($dept_in, 0, strlen($dept_in) - 1);
$query .= ' AND t.department IN (' . $dept_in . ') ';
}
break;
case 'u':
$upcs = FormLib::get('u', array());
if (count($upcs) == 0) {
$upcs[] = 'NOTREALUPC';
}
$query .= ' AND t.upc IN (';
foreach ($upcs as $u) {
$query .= '?,';
$args[] = BarcodeLib::padUPC($u);
}
$query = substr($query, 0, strlen($query) - 1) . ') ';
break;
}
return array('query' => $query, 'args' => $args);
}
public function showEditForm($upc, $display_mode = 1, $expand_mode = 1)
{
$FANNIE_CSS_PRIMARY_COLOR = FannieConfig::config('CSS_PRIMARY_COLOR');
$upc = BarcodeLib::padUPC($upc);
$ret = '<div id="VendorItemsFieldset" class="panel panel-default">';
$ret .= "<div class=\"panel-heading\">\n <a href=\"\" onclick=\"\$('#VendorItemsFieldsetContent').toggle(); return false;\">\n Vendor Items\n </a></div>";
$css = $expand_mode == 1 ? '' : ' collapse';
$ret .= '<div id="VendorItemsFieldsetContent" class="panel-body' . $css . '">';
$dbc = $this->db();
$p = $dbc->prepare_statement('SELECT vendorID,vendorName FROM vendors ORDER BY vendorName');
$r = $dbc->exec_statement($p);
if ($dbc->num_rows($r) == 0) {
return '';
}
// no vendors available
$vendors = array();
while ($w = $dbc->fetch_row($r)) {
$vendors[$w['vendorID']] = $w['vendorName'];
}
$product = new ProductsModel($dbc);
$product->upc($upc);
$product->load();
$my_vendor = $product->default_vendor_id();
$matched = false;
$hilite = 'style="color:' . $FANNIE_CSS_PRIMARY_COLOR . ';"';
$ret .= '<select class="form-control"
onchange="$(\'.vtable\').hide();$(\'#vtable\'+this.value).show();">';
foreach ($vendors as $id => $name) {
$ret .= sprintf('<option %s value="%d">%s%s</option>', $my_vendor == $id ? 'selected ' . $hilite : '', $id, $name, $my_vendor == $id ? ' [current]' : '');
if ($my_vendor == $id) {
$matched = true;
}
}
$ret .= '</select>';
$prep = $dbc->prepare_statement('SELECT * FROM vendorItems WHERE vendorID=? AND upc=?');
$style = $matched ? 'display:none;' : 'display:table;';
$cost_class = '';
foreach ($vendors as $id => $name) {
if ($matched && $id == $my_vendor) {
$table_class = '';
$cost_class = 'default_vendor_cost';
} else {
$table_class = 'collapse';
}
$ret .= "<table id=\"vtable{$id}\"\n class=\"vtable table table-bordered {$table_class}\">";
$row = array('cost' => 0, 'sku' => '', 'units' => 1, 'size' => '');
$res = $dbc->exec_statement($prep, array($id, $upc));
if ($dbc->num_rows($res) > 0) {
$row = $dbc->fetch_row($res);
}
$ret .= '<tr>
<th>SKU</th>
<td colspan="3">
<input type="text" class="form-control" name="v_sku[]"
id="vsku' . $id . '"
onchange="$(\'#product-sku-field\').val(this.value);"
value="' . $row['sku'] . '" />
</td>';
$ret .= sprintf('<th>Unit Cost</th><td>
<div class="input-group">
<span class="input-group-addon">$</span><input type="text"
name="v_cost[]" id="vcost%d" class="form-control %s" value="%.2f"
onchange="vprice(%d);"
/></div>
</td></tr>', $id, $cost_class, $row['cost'], $id);
$ret .= '<tr>
<th>Units/Case</th>
<td>
<input type="text" class="form-control" name="v_units[]"
id="vunits' . $id . '" value="' . $row['units'] . '"
onchange="vprice(' . $id . '); $(\'#product-case-size\').val(this.value);" />
</td>
<th>Unit Size</th>
<td>
<input type="text" class="form-control" name="v_size[]"
id="vsize' . $id . '" value="' . $row['size'] . '"
onchange="$(\'#product-pack-size\').val(this.value); " />
</td>
</td>';
$ret .= sprintf('<th>Case Cost</th><td id="vcc%d">$%.2f</td></tr>', $id, $row['units'] * $row['cost']);
$ret .= '<input type="hidden" name="v_id[]" value="' . $id . '" />';
$ret .= '</table>';
$style = 'display:none;';
}
$ret .= '</div>';
$ret .= '</div>';
return $ret;
}
public static function receiptLink($date, $trans_num)
{
$date = date('Y-m-d', strtotime($date));
return sprintf('<a href="%sadmin/LookupReceipt/RenderReceiptPage.php?date=%s&receipt=%s">%s</a>', \FannieConfig::config('URL'), $date, $trans_num, $trans_num);
}
public function testSampleData()
{
$op_db = FannieConfig::config('OP_DB');
$con = FannieDB::get($op_db);
$samples = array('batchType', 'custdata', 'departments', 'employees', 'memtype', 'originCountry', 'originStateProv', 'products', 'superdepts', 'superDeptNames', 'tenders');
foreach ($samples as $sample) {
$con->query('TRUNCATE TABLE ' . $con->identifierEscape($sample));
ob_start();
$loaded = \COREPOS\Fannie\API\data\DataLoad::loadSampleData($con, $sample);
$output = ob_get_clean();
$this->assertEquals(true, $loaded, 'Error loading ' . $sample . ' (' . $output . ')');
}
}
/**
Do whatever the service is supposed to do.
Should override this.
@param $args array of data
@return an array of data
*/
public function run($args = array())
{
$ret = array();
if (!property_exists($args, 'upc')) {
// missing required arguments
$ret['error'] = array('code' => -32602, 'message' => 'Invalid parameters needs type');
return $ret;
}
if (!is_array($args->upc)) {
$args->upc = array($args->upc);
}
$dbc = \FannieDB::get(\FannieConfig::config('OP_DB'));
$storeID = \FannieConfig::get('STORE_ID');
/**
In "fast" mode, look up the items and run UPDATE queries
on each lane. This reduces overhead substantially but will
overlook brand-new items since there's no check whether the
item exists on the lane.
If "fast" is not specified, each UPC record is copied to the
lane exactly using models. This mode is preferrable unless
performance becomes an issue.
*/
if (property_exists($args, 'fast')) {
$upc_data = array();
$query = '
SELECT normal_price,
pricemethod,
quantity,
groupprice,
special_price,
specialpricemethod,
specialquantity,
specialgroupprice,
discounttype,
mixmatchcode,
department,
tax,
foodstamp,
discount,
qttyEnforced,
idEnforced,
inUse,
upc
FROM products
WHERE store_id=?
AND upc IN (';
$params = array($storeID);
foreach ($args->upc as $upc) {
$query .= '?,';
$params[] = \BarcodeLib::padUPC($upc);
}
$query = substr($query, 0, strlen($query) - 1);
$prep = $dbc->prepare($query);
$result = $dbc->execute($prep, $params);
while ($w = $dbc->fetchRow($result)) {
$upc_data[$w['upc']] = $w;
}
$updateQ = '
UPDATE products AS p SET
p.normal_price = ?,
p.pricemethod = ?,
p.quantity = ?,
p.groupprice = ?,
p.special_price = ?,
p.specialpricemethod = ?,
p.specialquantity = ?,
p.specialgroupprice = ?,
p.discounttype = ?,
p.mixmatchcode = ?,
p.department = ?,
p.tax = ?,
p.foodstamp = ?,
p.discount=?,
p.qttyEnforced=?,
p.idEnforced=?,
p.inUse=?
WHERE p.upc = ?';
$FANNIE_LANES = \FannieConfig::config('LANES');
for ($i = 0; $i < count($FANNIE_LANES); $i++) {
$lane_sql = new \SQLManager($FANNIE_LANES[$i]['host'], $FANNIE_LANES[$i]['type'], $FANNIE_LANES[$i]['op'], $FANNIE_LANES[$i]['user'], $FANNIE_LANES[$i]['pw']);
if (!isset($lane_sql->connections[$FANNIE_LANES[$i]['op']]) || $lane_sql->connections[$FANNIE_LANES[$i]['op']] === false) {
// connect failed
continue;
}
$updateP = $lane_sql->prepare($updateQ);
foreach ($upc_data as $upc => $data) {
$lane_args = array($data['normal_price'], $data['pricemethod'], $data['quantity'], $data['groupprice'], $data['special_price'], $data['specialpricemethod'], $data['specialquantity'], $data['specialgroupprice'], $data['discounttype'], $data['mixmatchcode'], $data['department'], $data['tax'], $data['foodstamp'], $data['discount'], $data['qttyEnforced'], $data['idEnforced'], $data['inUse'], $upc);
$lane_sql->execute($updateP, $lane_args);
}
}
} else {
$product = new \ProductsModel($dbc);
$ret['synced'] = array();
foreach ($args->upc as $upc) {
$upc = \BarcodeLib::padUPC($upc);
$product->upc($upc);
$product->store_id($storeID);
if ($product->load()) {
$product->pushToLanes();
$ret['synced'][] = $upc;
}
}
}
return $ret;
}
function showInstallTabsLane($current, $path = '')
{
$ret = "";
$ret .= "<ul class='installTabList2'>";
$url = FannieConfig::config('URL');
$installTabs = array('Lane Necessities' => 'LaneNecessitiesPage.php', 'Additional Configuration' => 'LaneAdditionalConfigPage.php', 'Scanning Options' => 'LaneScanningPage.php', 'Security' => 'LaneSecurityPage.php', 'Text Strings' => $url . '/admin/ReceiptText/LaneTextStringPage.php');
/* Original
$installTabs = array(
'Lane Necessities'=>'index.php',
'Additional Configuration' => 'extra_config.php',
'Scanning Options' => 'scanning.php',
'Security' => 'security.php',
'Text Strings' => 'text.php'
);
*/
foreach ($installTabs as $key => $loc) {
if ($key == $current) {
$ret .= "<li class='installTab2'>{$key}</li>";
} else {
$ret .= "<li class='installTab2'><a href='{$path}{$loc}'>{$key}</a></li>";
}
}
$ret .= "</ul>";
$ret .= "<br style='clear:both;' />";
return $ret;
// showInstallTabsLane()
}
public function getFormJavascript($upc)
{
ob_start();
?>
function updateMarginMod(){
var store_id = $(this).data('store-id');
$.ajax({
url: '<?php
echo FannieConfig::config('URL');
?>
item/modules/ItemMarginModule.php',
data: 'p='+$('#price'+store_id).val()+'&d='+$('#department'+store_id).val()+'&c='+$('#cost'+store_id).val()+'&u=<?php
echo $upc;
?>
',
cache: false,
success: function(data){
$('#ItemMarginMeter').html(data);
}
});
}
function nosubmit(event)
{
if (event.which == 13) {
event.preventDefault();
event.stopPropagation();
updateMarginMod();
return false;
}
}
<?php
return ob_get_clean();
}
private static function autoCompleteEmail($val)
{
if (\FannieConfig::config('CUST_SCHEMA') == 1) {
$query = 'SELECT email
FROM Customers
WHERE email LIKE ?
GROUP BY email
ORDER BY email';
} else {
$query = 'SELECT email_1
FROM meminfo
WHERE email_1 LIKE ?
GROUP BY email_1
ORDER BY email_1';
}
return array($query, array('%' . $val . '%'));
}
function SaveFormData($upc)
{
$FANNIE_PRODUCT_MODULES = FannieConfig::config('PRODUCT_MODULES', array());
$upc = BarcodeLib::padUPC($upc);
$dbc = $this->db();
$model = new ProductsModel($dbc);
$model->upc($upc);
if (!$model->load()) {
// fully init new record
$model->special_price(0);
$model->specialpricemethod(0);
$model->specialquantity(0);
$model->specialgroupprice(0);
$model->advertised(0);
$model->tareweight(0);
$model->start_date('0000-00-00');
$model->end_date('0000-00-00');
$model->discounttype(0);
$model->wicable(0);
$model->scaleprice(0);
$model->inUse(1);
}
$stores = FormLib::get('store_id', array());
for ($i = 0; $i < count($stores); $i++) {
$model->store_id($stores[$i]);
$taxes = FormLib::get('tax');
if (isset($taxes[$i])) {
$model->tax($taxes[$i]);
}
$fs = FormLib::get('FS', array());
if (in_array($stores[$i], $fs)) {
$model->foodstamp(1);
} else {
$model->foodstamp(0);
}
$scale = FormLib::get('Scale', array());
if (in_array($stores[$i], $scale)) {
$model->scale(1);
} else {
$model->scale(0);
}
$qtyFrc = FormLib::get('QtyFrc', array());
if (in_array($stores[$i], $qtyFrc)) {
$model->qttyEnforced(1);
} else {
$model->qttyEnforced(0);
}
$wic = FormLib::get('prod-wicable', array());
if (in_array($stores[$i], $wic)) {
$model->wicable(1);
} else {
$model->wicable(0);
}
$discount_setting = FormLib::get('discount');
if (isset($discount_setting[$i])) {
switch ($discount_setting[$i]) {
case 0:
$model->discount(0);
$model->line_item_discountable(0);
break;
case 1:
$model->discount(1);
$model->line_item_discountable(1);
break;
case 2:
$model->discount(1);
$model->line_item_discountable(0);
break;
case 3:
$model->discount(0);
$model->line_item_discountable(1);
break;
}
}
$price = FormLib::get('price');
if (isset($price[$i])) {
$model->normal_price($price[$i]);
}
$cost = FormLib::get('cost');
if (isset($cost[$i])) {
$model->cost($cost[$i]);
}
$desc = FormLib::get('descript');
if (isset($desc[$i])) {
$model->description(str_replace("'", '', $desc[$i]));
}
$brand = FormLib::get('manufacturer');
if (isset($brand[$i])) {
$model->brand(str_replace("'", '', $brand[$i]));
}
$model->pricemethod(0);
$model->groupprice(0.0);
$model->quantity(0);
$dept = FormLib::get('department');
if (isset($dept[$i])) {
$model->department($dept[$i]);
}
$size = FormLib::get('size');
if (isset($size[$i])) {
$model->size($size[$i]);
}
$model->modified(date('Y-m-d H:i:s'));
$unit = FormLib::get('unitm');
if (isset($unit[$i])) {
$model->unitofmeasure($unit[$i]);
}
$subdept = FormLib::get('subdept');
if (isset($subdept[$i])) {
$model->subdept($subdept[$i]);
}
// lookup vendorID by name
$vendorID = 0;
$v_input = FormLib::get('distributor');
if (isset($v_input[$i])) {
$vendor = new VendorsModel($dbc);
$vendor->vendorName($v_input[$i]);
foreach ($vendor->find('vendorID') as $obj) {
$vendorID = $obj->vendorID();
break;
}
}
$model->default_vendor_id($vendorID);
$inUse = FormLib::get('prod-in-use', array());
if (in_array($stores[$i], $inUse)) {
$model->inUse(1);
} else {
$model->inUse(0);
}
$idEnf = FormLib::get('id-enforced', array());
if (isset($idEnf[$i])) {
$model->idEnforced($idEnf[$i]);
}
$local = FormLib::get('prod-local');
if (isset($local[$i])) {
$model->local($local[$i]);
}
$deposit = FormLib::get('deposit-upc');
if (isset($deposit[$i])) {
if ($deposit[$i] == '') {
$deposit[$i] = 0;
}
$model->deposit($deposit[$i]);
}
/* products.formatted_name is intended to be maintained automatically.
* Get all enabled plugins and standard modules of the base.
* Run plugins first, then standard modules.
*/
$formatters = FannieAPI::ListModules('ProductNameFormatter');
$fmt_name = "";
$fn_params = array('index' => $i);
foreach ($formatters as $formatter_name) {
$formatter = new $formatter_name();
$fmt_name = $formatter->compose($fn_params);
if (isset($formatter->this_mod_only) && $formatter->this_mod_only) {
break;
}
}
$model->formatted_name($fmt_name);
$model->save();
}
/**
If a vendor is selected, intialize
a vendorItems record
*/
if ($vendorID != 0) {
$vitem = new VendorItemsModel($dbc);
$vitem->vendorID($vendorID);
$vitem->upc($upc);
$sku = FormLib::get('vendorSKU');
if (empty($sku)) {
$sku = $upc;
} else {
/**
If a SKU is provided, update any
old record that used the UPC as a
placeholder SKU.
*/
$existsP = $dbc->prepare('
SELECT sku
FROM vendorItems
WHERE sku=?
AND upc=?
AND vendorID=?');
$existsR = $dbc->execute($existsP, array($sku, $upc, $vendorID));
if ($dbc->numRows($existsR) > 0 && $sku != $upc) {
$delP = $dbc->prepare('
DELETE FROM vendorItems
WHERE sku =?
AND upc=?
AND vendorID=?');
$dbc->execute($delP, array($upc, $upc, $vendorID));
} else {
$fixSkuP = $dbc->prepare('
UPDATE vendorItems
SET sku=?
WHERE sku=?
AND vendorID=?');
$dbc->execute($fixSkuP, array($sku, $upc, $vendorID));
}
}
$vitem->sku($sku);
$vitem->size($model->size());
$vitem->description($model->description());
$vitem->brand($model->brand());
$vitem->units(FormLib::get('caseSize', 1));
$vitem->cost($model->cost());
$vitem->save();
}
if ($dbc->table_exists('prodExtra')) {
$extra = new ProdExtraModel($dbc);
$extra->upc($upc);
if (!$extra->load()) {
$extra->variable_pricing(0);
$extra->margin(0);
$extra->case_quantity('');
$extra->case_cost(0.0);
$extra->case_info('');
}
$brand = FormLib::get('manufacturer');
if (isset($brand[0])) {
$extra->manufacturer(str_replace("'", '', $brand[0]));
}
$dist = FormLib::get('distributor');
if (isset($dist[0])) {
$extra->distributor(str_replace("'", '', $dist[0]));
}
$cost = FormLib::get('cost');
if (isset($cost[0])) {
$extra->cost($cost[0]);
}
$extra->save();
}
if (!isset($FANNIE_PRODUCT_MODULES['ProdUserModule'])) {
if ($dbc->table_exists('productUser')) {
$ldesc = FormLib::get_form_value('puser_description');
$model = new ProductUserModel($dbc);
$model->upc($upc);
$model->description($ldesc);
$model->save();
}
}
}
function removeAllLanes($upc, $table_name)
{
$FANNIE_OP_DB = FannieConfig::config('OP_DB');
$FANNIE_LANES = FannieConfig::config('LANES');
for ($i = 0; $i < count($FANNIE_LANES); $i++) {
$tmp = new SQLManager($FANNIE_LANES[$i]['host'], $FANNIE_LANES[$i]['type'], $FANNIE_LANES[$i]['op'], $FANNIE_LANES[$i]['user'], $FANNIE_LANES[$i]['pw']);
if ($tmp->table_exists("{$table_name}")) {
$delQ = "DELETE FROM {$table_name} WHERE upc='{$upc}'";
$delR = $tmp->query($delQ, $FANNIE_LANES[$i]['op']);
}
}
}
function showEditForm($memNum, $country = "US")
{
$FANNIE_URL = FannieConfig::config('URL');
$FANNIE_MEMBER_UPC_PREFIX = FannieConfig::config('FANNIE_MEMBER_UPC_PREFIX');
$account = self::getAccount();
$prefix = isset($FANNIE_MEMBER_UPC_PREFIX) ? $FANNIE_MEMBER_UPC_PREFIX : "";
$plen = strlen($prefix);
$ret = "<div class=\"panel panel-default\">\n <div class=\"panel-heading\">Membership Card</div>\n <div class=\"panel-body\">";
$upc = $account['idCardUPC'];
if ($prefix && strpos("{$upc}", "{$prefix}") === 0) {
$upc = substr($upc, $plen);
$upc = ltrim($upc, "0");
}
$ret .= '<div class="form-group form-inline">
<span class="label primaryBackground">Card#</span>
<input type="text" name="memberCard" class="form-control"
value="' . $upc . '" />
</div>';
$ret .= "</div>";
$ret .= "</div>";
return $ret;
// showEditForm
}
public function summaryRows($upc)
{
$FANNIE_URL = FannieConfig::config('URL');
$form = sprintf('<form id="newSignForm" method="post" action="%sadmin/labels/SignFromSearch.php">
<input type="hidden" name="u[]" value="%s" />
</form>', $FANNIE_URL, $upc);
$ret = '<td>' . $form . '<a href="" onclick="$(\'#newSignForm\').submit();return false;">Create Sign</a></td>';
return array($ret);
}
protected function listFromUpcomingSale($dbc)
{
$ids = '';
$args = array();
foreach ($this->items as $id) {
$args[] = $id;
$ids .= '?,';
}
$ids = substr($ids, 0, strlen($ids) - 1);
$query = 'SELECT p.upc,
l.salePrice AS normal_price,
p.normal_price AS nonSalePrice,
' . \COREPOS\Fannie\API\item\ItemText::longDescriptionSQL() . ',
p.description AS posDescription,
' . \COREPOS\Fannie\API\item\ItemText::longBrandSQL() . ',
v.units,
' . \COREPOS\Fannie\API\item\ItemText::signSizeSQL() . ',
v.sku,
\'\' AS pricePerUnit,
n.vendorName AS vendor,
p.scale,
p.numflag,
b.startDate,
b.endDate,
o.name AS originName,
o.shortName AS originShortName
FROM products AS p
LEFT JOIN productUser AS u ON p.upc=u.upc
LEFT JOIN vendors AS n ON p.default_vendor_id=n.vendorID
LEFT JOIN vendorItems AS v ON p.upc=v.upc AND p.default_vendor_id=v.vendorID
LEFT JOIN origins AS o ON p.current_origin_id=o.originID
LEFT JOIN batchList AS l ON p.upc=l.upc
LEFT JOIN batches AS b ON l.batchID=b.batchID
WHERE p.upc IN (' . $ids . ')
AND b.discounttype <> 0
AND b.startDate > ' . $dbc->now() . ' ';
if (\FannieConfig::config('STORE_MODE') == 'HQ') {
$query .= ' AND p.store_id=? ';
$args[] = \FannieConfig::config('STORE_ID');
}
$query .= 'ORDER BY p.department, p.upc';
return array('query' => $query, 'args' => $args);
}
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;
}
}
