public function testPages()
{
$pages = FannieAPI::listModules('FanniePage', true);
$config = FannieConfig::factory();
$logger = new FannieLogger();
$op_db = $config->get('OP_DB');
$dbc = FannieDB::get($op_db);
$dbc->throwOnFailure(true);
foreach ($pages as $page_class) {
$obj = new $page_class();
$obj->setConfig($config);
$obj->setLogger($logger);
$dbc->selectDB($op_db);
$obj->setConnection($dbc);
if ($page_class == 'WfcHtViewSalaryPage') {
continue;
}
// header/redirect problem
ob_start();
$pre = $obj->preprocess();
ob_end_clean();
$this->assertInternalType('boolean', $pre);
$help = $obj->helpContent();
$this->assertInternalType('string', $help);
$auth = $obj->checkAuth();
$this->assertInternalType('boolean', $pre);
$obj->unitTest($this);
}
}
/**
Get connection to member database
@return [SQLManager object]
*/
public function db()
{
if (!class_exists('FannieDB')) {
include_once dirname(__FILE__) . '/../data/FannieDB.php';
}
return \FannieDB::get(\FannieConfig::factory()->get('OP_DB'));
}
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;
}
}
/**
Generate a singleton FannieConfig instance as needed
*/
public static function factory()
{
if (!self::$instance instanceof FannieConfig) {
self::$instance = new FannieConfig();
self::$instance->reload();
}
return self::$instance;
}
protected static function getPluginList()
{
$plugin_list = \FannieConfig::factory()->get('PLUGIN_LIST');
if (is_array($plugin_list)) {
return $plugin_list;
} else {
return array();
}
}
/**
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();
}
}
/**
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, 'type')) {
// missing required arguments
$ret['error'] = array('code' => -32602, 'message' => 'Invalid parameters needs type');
return $ret;
}
// validate additional arguments
switch (strtolower($args->type)) {
case 'vendor':
if (!property_exists($args, 'vendor_id')) {
// vendor ID required
$ret['error'] = array('code' => -32602, 'message' => 'Invalid parameters needs vendor_id');
return $ret;
} elseif (!property_exists($args, 'sku') && !property_exists($args, 'upc')) {
// either sku or upc is required
$ret['error'] = array('code' => -32602, 'message' => 'Invalid parameters needs sku or upc');
return $ret;
}
break;
default:
// unknown type argument
$ret['error'] = array('code' => -32602, 'message' => 'Invalid parameters');
return $ret;
}
// lookup results
$dbc = \FannieDB::getReadOnly(\FannieConfig::factory()->get('OP_DB'));
switch (strtolower($args->type)) {
case 'vendor':
$vendor = new \VendorItemsModel($dbc);
$vendor->vendorID($args->vendor_id);
if (property_exists($args, 'sku')) {
$vendor->sku($args->sku);
} elseif (property_exists($args, 'upc')) {
$vendor->upc($args->upc);
}
foreach ($vendor->find() as $v) {
$ret['sku'] = $v->sku();
$ret['upc'] = $v->upc();
$ret['size'] = $v->size();
$ret['units'] = $v->units();
$ret['brand'] = $v->brand();
$ret['description'] = $v->description();
$ret['cost'] = $v->cost();
break;
}
return $ret;
}
}
public function testItemFlags()
{
$config = FannieConfig::factory();
$connection = FannieDB::get($config->OP_DB);
/**
Setup preconditions for the test
*/
$upc = BarcodeLib::padUPC('16');
$product = new ProductsModel($connection);
$product->upc($upc);
$product->store_id(0);
$product->load();
if ($product->numflag() != 0) {
$product->numflag(0);
}
$product->save();
$module = new ItemFlagsModule();
$module->setConnection($connection);
$module->setConfig($config);
$form = new \COREPOS\common\mvc\ValueContainer();
$module->setForm($form);
$saved = $module->saveFormData($upc);
$this->assertEquals(true, $saved, 'Handled empty input');
$product->reset();
$product->upc($upc);
$product->load();
$this->assertEquals(0, $product->numflag(), 'Wrong numflag value ' . $product->numflag());
/**
Simulate real form input
*/
$form = new \COREPOS\common\mvc\ValueContainer();
$form->flags = array(1, 3);
// 0b101 == 5
$module->setForm($form);
$saved = $module->saveFormData($upc);
$this->assertEquals(true, $saved, 'Saving item flags failed');
$product->reset();
$product->upc($upc);
$product->load();
$this->assertEquals(5, $product->numflag(), 'Wrong numflag value ' . $product->numflag());
/* put record back to normal */
$product->numflag(0);
$product->save();
$form = new \COREPOS\common\mvc\ValueContainer();
$form->flags = 'not_an_array';
$module->setForm($form);
$saved = $module->saveFormData($upc);
$this->assertEquals(false, $saved, 'Accepted invalid input');
}
/**
Fetch data for the specified report
@param [string] $report_class_name name of report
@param [FannieConfig] $config current configuration
@param [SQLManager] $connection database connection
@return [array] report records or [boolean] false
if this source cannot handle the request
*/
public function fetchReportData($report_class_name, \FannieConfig $config, \SQLManager $connection)
{
$date1 = \FormLib::get_form_value('date1', date('Y-m-d'));
$date2 = \FormLib::get_form_value('date2', date('Y-m-d'));
$type = \FormLib::get_form_value('report-basis', 'Purchases');
$exclude = \FormLib::get_form_value('excludes', '');
if ($type == 'Join Date') {
return false;
}
$ex = preg_split('/\\D+/', $exclude, 0, PREG_SPLIT_NO_EMPTY);
$exCondition = '';
$exArgs = array();
foreach ($ex as $num) {
$exCondition .= '?,';
$exArgs[] = $num;
}
$exCondition = substr($exCondition, 0, strlen($exCondition) - 1);
$originalDB = $connection->defaultDatabase();
$plugin_settings = $config->get('PLUGIN_SETTINGS');
$connection->selectDB($plugin_settings['WarehouseDatabase']);
$query = "\n SELECT \n CASE WHEN m.zip='' THEN 'none' ELSE m.zip END as zipcode,\n COUNT(*) as num_trans, \n SUM(total) as spending,\n COUNT(DISTINCT s.card_no) as uniques\n FROM sumMemSalesByDay AS s \n INNER JOIN " . $config->get('OP_DB') . $connection->sep() . "meminfo AS m ON s.card_no=m.card_no \n WHERE ";
if (!empty($exArgs)) {
$query .= "s.card_no NOT IN ({$exCondition}) AND ";
}
$query .= "s.date_id BETWEEN ? AND ?\n GROUP BY zipcode\n ORDER BY SUM(total) DESC";
$exArgs[] = $this->dateToID($date1);
$exArgs[] = $this->dateToID($date2);
$prep = $connection->prepare($query);
$result = $connection->execute($prep, $exArgs);
while ($row = $connection->fetchRow($result)) {
$record = array($row['zipcode'], $row['num_trans'], $row['uniques'], $row['spending']);
$data[] = $record;
}
$connection->setDefaultDB($originalDB);
return $data;
}
public function export_order($id)
{
$config = FannieConfig::factory();
$dbc = FannieDB::get($config->get('OP_DB'));
$items = new PurchaseOrderItemsModel($dbc);
$items->orderID($id);
$NL = "\r\n";
echo 'productCode,quantity' . $NL;
foreach ($items->find() as $item) {
echo str_pad($item->sku(), 7, '0', STR_PAD_LEFT);
echo ',';
echo $item->quantity();
echo $NL;
}
}
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;
}
public function testEquityHistory()
{
$config = FannieConfig::factory();
$config->set('FANNIE_EQUITY_DEPARTMENTS', '1 2');
$logger = new FannieLogger();
$trans_db = $config->get('TRANS_DB');
$dbc = FannieDB::get($trans_db);
// create two test rows in dlog_15
$today = date('Y-m-d');
$trans_num = '1-1-1';
$dlog = new Dlog15Model($dbc);
$dlog->tdate($today);
$dlog->trans_num($trans_num);
$dlog->department(1);
$dlog->total(10);
$dlog->card_no(1);
$dlog->trans_id(1);
$dlog->save();
$dlog->trans_id(2);
$dlog->save();
$task = new EquityHistoryTask();
$task->setConfig($config);
$task->setLogger($logger);
$task->run();
// verify test rows were logged
$dbc->selectDB($trans_db);
$query = 'SELECT SUM(stockPurchase), COUNT(*) FROM stockpurchases WHERE card_no=1';
$res = $dbc->query($query);
$row = $dbc->fetchRow($res);
$this->assertEquals(20, $row[0]);
$this->assertEquals(2, $row[1]);
// add a third test row
$dlog->department(2);
$dlog->trans_id(3);
$dlog->save();
$task->run();
// verify only the new row is logged
$dbc->selectDB($trans_db);
$query = 'SELECT SUM(stockPurchase), COUNT(*) FROM stockpurchases WHERE card_no=1';
$res = $dbc->query($query);
$row = $dbc->fetchRow($res);
$this->assertEquals(30, $row[0]);
$this->assertEquals(3, $row[1]);
}
/**
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;
}
/**
Build a standardized tooltip
@param $text the full help text
@param $doc_link URL into CORE documentation [optional]
@param $tag HTML tag type for text [default is span]
@return an HTML string
*/
public static function toolTip($text, $doc_link = False, $tag = 'span')
{
$id = '_fhtt' . rand(0, 999999);
$img = \FannieConfig::factory()->get('URL') . 'src/img/buttons/help16.png';
$text = preg_replace('/\\s\\s+/', ' ', $text);
$snippet = strlen($text) > 100 ? strip_tags(substr($text, 0, 100)) . ' ...' : False;
if ($snippet || $doc_link) {
$snippet .= ' (Click for more)';
}
if ($doc_link) {
if (!$snippet) {
$snippet = $text;
}
$text .= sprintf(' (<a href="%s">CORE Documentation</a>)', $doc_link);
}
if ($snippet || $doc_link) {
return sprintf('<a href=""
onclick="$(\'#%s\').toggle();return false;"><img src="%s" title="%s" /></a>
<%s id="%s" style="display:none;">%s</%s>', $id, $img, $snippet, $tag, $id, $text, $tag);
} else {
return sprintf('<a href="" onclick="return false;"><img src="%s" title="%s" /></a>', $img, $text);
}
}
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;
}
public static function aggregateStruct(SQLManager $connection, $dlog, $start_date, $end_date, stdclass $where, $groupby = array())
{
$base_table = self::selectStruct($dlog, $start_date, $end_date);
$dt_col = $dlog ? 'tdate' : 'datetime';
$clone_table = $dlog ? 'dlog_15' : 'transarchive';
/**
Grouping is required
*/
if (!is_array($groupby) || count($groupby) == 0) {
return $base_table;
}
/**
Validate group by columns
*/
$model = new DTransactionsModel(null);
$columns = $model->getColumns();
$insert_cols = array();
$select_cols = array();
for ($i = 0; $i < count($groupby); $i++) {
$group = $groupby[$i];
if (isset($columns[$group])) {
$insert_cols[] = $group;
$select_cols[] = $group;
} elseif (preg_match('/(.+)\\s+AS\\s+(\\w+)$/', $group, $matches)) {
$col_definition = $matches[1];
$col_alias = $matches[2];
if (isset($columns[$col_alias])) {
$insert_cols[] = $col_alias;
$select_cols[] = $group;
$groupby[$i] = $col_definition;
} else {
return $base_table;
}
} else {
return $base_table;
}
}
/**
Always include a datetime column
*/
if (!in_array($dt_col, $insert_cols)) {
$insert_cols[] = $dt_col;
$select_cols[] = 'MAX(' . $dt_col . ') AS ' . $dt_col;
}
/**
Create randomly named temporary table based
on the structure of dlog_15 or transachive
*/
$config = FannieConfig::factory();
$sep = $connection->sep();
$random_name = uniqid('temp' . rand(1000, 9999));
$temp_table = $config->get('ARCHIVE_DB') . $sep . $random_name;
$clone_table = $config->get('TRANS_DB') . $sep . $clone_table;
$temp_name = $connection->temporaryTable($temp_table, $clone_table);
if ($temp_name === false) {
return $base_table;
}
/**
Build a query to insert aggregated rows into
the temporary table
*/
$query = 'INSERT INTO ' . $temp_name . '(';
foreach ($insert_cols as $c) {
$query .= $c . ',';
}
$query .= 'total, quantity) ';
$query .= ' SELECT ';
foreach ($select_cols as $c) {
$query .= $c . ',';
}
/**
Always aggregate by total & quantity
*/
$query .= ' SUM(total) AS total, ' . DTrans::sumQuantity() . ' AS quantity
FROM __TRANSACTION_TABLE__
WHERE ' . $dt_col . ' BETWEEN ? AND ? ';
$params = array($start_date . ' 00:00:00', $end_date . ' 23:59:59');
/**
Add a where clause if one has been specified
*/
if (property_exists($where, 'sql') && is_array($where->sql)) {
foreach ($where->sql as $sql) {
$query .= ' AND ' . $sql;
}
}
if (property_exists($where, 'params') && is_array($where->params)) {
foreach ($where->params as $p) {
$params[] = $p;
}
}
/**
Add the group by clause
*/
$query .= ' GROUP BY ';
foreach ($groupby as $group) {
$query .= $group . ',';
}
$query = substr($query, 0, strlen($query) - 1);
/**
Split monthly archive union if needed
*/
$source_tables = array();
if (strstr($base_table, ' UNION ')) {
preg_match_all('/\\s+FROM\\s+(\\w+)\\s+/', $base_table, $matches);
foreach ($matches[1] as $m) {
$source_tables[] = $m;
}
} else {
$source_tables = array($base_table);
}
/**
Load data into temporary table from source table(s)
using built query
*/
foreach ($source_tables as $source_table) {
$insertQ = str_replace('__TRANSACTION_TABLE__', $source_table, $query);
$prep = $connection->prepare($insertQ);
if (!$connection->execute($prep, $params)) {
return $base_table;
}
}
return $temp_name;
}
/**
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, 'type')) {
// missing required arguments
$ret['error'] = array('code' => -32602, 'message' => 'Invalid parameters');
return $ret;
}
// validate additional arguments
switch (strtolower($args->type)) {
case 'settings':
if (!property_exists($args, 'dept_no')) {
// missing required arguments
$ret['error'] = array('code' => -32602, 'message' => 'Invalid parameters');
return $ret;
}
break;
case 'children':
if (!property_exists($args, 'superID') && !property_exists($args, 'dept_no')) {
// missing required arguments
$ret['error'] = array('code' => -32602, 'message' => 'Invalid parameters');
return $ret;
}
if (property_exists($args, 'superID') && is_array($args->superID) && count($args->superID) != 2) {
// range must specify exactly two superIDs
$ret['error'] = array('code' => -32602, 'message' => 'Invalid parameters');
return $ret;
}
if (property_exists($args, 'dept_no') && is_array($args->dept_no) && count($args->dept_no) != 2) {
// range must specify exactly two dept_nos
$et['error'] = array('code' => -32602, 'message' => 'Invalid parameters');
return $ret;
}
break;
default:
// unknown type argument
$ret['error'] = array('code' => -32602, 'message' => 'Invalid parameters');
return $ret;
}
// lookup results
$dbc = \FannieDB::getReadOnly(\FannieConfig::factory()->get('OP_DB'));
switch (strtolower($args->type)) {
case 'settings':
$model = new DepartmentsModel($dbc);
$model->dept_no($args->dept_no);
$model->load();
$ret['tax'] = $model->dept_tax();
$ret['fs'] = $model->dept_fs();
$ret['discount'] = $model->dept_discount();
$ret['seeID'] = $model->dept_see_id();
$ret['margin'] = $model->margin();
return $ret;
case 'children':
$query = '';
$params = array();
if (property_exists($args, 'dept_no')) {
$query = '
SELECT s.subdept_no AS id,
s.subdept_name AS name
FROM departments AS d
INNER JOIN subdepts AS s ON d.dept_no=s.dept_ID ';
if (property_exists($args, 'superID') && is_numeric($args->superID)) {
$query .= ' INNER JOIN superdepts AS a ON d.dept_no=a.dept_ID ';
}
if (is_array($args->dept_no)) {
$query .= ' WHERE d.dept_no BETWEEN ? AND ? ';
$params[] = $args->dept_no[0];
$params[] = $args->dept_no[1];
} else {
$query .= ' WHERE d.dept_no = ? ';
$params[] = $args->dept_no;
}
if (property_exists($args, 'superID') && is_numeric($args->superID)) {
$query .= ' AND a.superID = ? ';
$params[] = $args->superID;
}
$query .= ' ORDER BY s.subdept_no';
} else {
$query = '
SELECT d.dept_no AS id,
d.dept_name AS name
FROM superdepts AS s
INNER JOIN departments AS d ON d.dept_no=s.dept_ID ';
if (is_array($args->superID)) {
$query .= ' WHERE s.superID BETWEEN ? AND ? ';
$params[] = $args->superID[0];
$params[] = $args->superID[1];
} else {
$query .= ' WHERE s.superID = ? ';
$params[] = $args->superID;
}
$query .= ' ORDER BY d.dept_no';
// support meta-options for all departments
if (!is_array($args->superID) && $args->superID < 0) {
if ($args->superID == -1) {
$query = '
SELECT d.dept_no AS id,
d.dept_name AS name
FROM departments AS d
ORDER BY d.dept_no';
$params = array();
} elseif ($args->superID == -2) {
$query = '
SELECT d.dept_no AS id,
d.dept_name AS name
FROM departments AS d
INNER JOIN MasterSuperDepts AS m ON d.dept_no=m.dept_ID
WHERE m.superID <> 0
ORDER BY d.dept_no';
$params = array();
}
}
}
$prep = $dbc->prepare($query);
$res = $dbc->execute($prep, $params);
while ($w = $dbc->fetch_row($res)) {
$ret[] = array('id' => $w['id'], 'name' => $w['name']);
}
return $ret;
}
}
/**
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, 'field') || !property_exists($args, 'search')) {
// missing required arguments
$ret['error'] = array('code' => -32602, 'message' => 'Invalid parameters');
return $ret;
} else {
if (strlen($args->search) < 1) {
// search term is too short
$ret['error'] = array('code' => -32602, 'message' => 'Invalid parameters');
return $ret;
}
}
$dbc = \FannieDB::getReadOnly(\FannieConfig::factory()->get('OP_DB'));
switch (strtolower($args->field)) {
case 'item':
$res = false;
if (!is_numeric($args->search)) {
$prep = $dbc->prepare('SELECT p.upc,
p.description
FROM products AS p
LEFT JOIN productUser AS u ON u.upc=p.upc
WHERE p.description LIKE ?
OR p.brand LIKE ?
OR u.description LIKE ?
OR u.brand LIKE ?
GROUP BY p.upc,
p.description
ORDER BY p.description');
$term = '%' . $args->search . '%';
$res = $dbc->execute($prep, array($term, $term, $term, $term));
} elseif (ltrim($args->search, '0') != '') {
$prep = $dbc->prepare('
SELECT p.upc,
p.upc AS description
FROM products AS p
WHERE p.upc LIKE ?
GROUP BY p.upc');
$res = $dbc->execute($prep, array('%' . $args->search . '%'));
}
while ($res && ($row = $dbc->fetch_row($res))) {
$ret[] = array('label' => $row['description'], 'value' => $row['upc']);
}
case 'brand':
$prep = $dbc->prepare('SELECT brand
FROM products
WHERE brand LIKE ?
GROUP BY brand
ORDER BY brand');
$res = $dbc->execute($prep, array($args->search . '%'));
while ($row = $dbc->fetch_row($res)) {
$ret[] = $row['brand'];
}
return $ret;
case 'long_brand':
$prep = $dbc->prepare('
SELECT u.brand
FROM productUser AS u
' . DTrans::joinProducts('u', 'p', 'INNER') . '
WHERE u.brand LIKE ?
GROUP BY u.brand
ORDER BY u.brand');
$res = $dbc->execute($prep, array($args->search . '%'));
while ($row = $dbc->fetch_row($res)) {
$ret[] = $row['brand'];
}
return $ret;
case 'vendor':
$prep = $dbc->prepare('SELECT vendorID,
vendorName
FROM vendors
WHERE vendorName LIKE ?
ORDER BY vendorName');
$res = $dbc->execute($prep, array($args->search . '%'));
while ($row = $dbc->fetch_row($res)) {
$ret[] = $row['vendorName'];
}
if ($dbc->tableExists('prodExtra')) {
$prep = $dbc->prepare('SELECT distributor
FROM prodExtra
WHERE distributor LIKE ?
GROUP BY distributor
ORDER BY distributor');
$res = $dbc->execute($prep, array($args->search . '%'));
while ($row = $dbc->fetch_row($res)) {
if (!in_array($row['distributor'], $ret)) {
$ret[] = $row['distributor'];
}
}
}
return $ret;
case 'mfirstname':
case 'mlastname':
case 'maddress':
case 'mcity':
case 'memail':
return \COREPOS\Fannie\API\member\MemberREST::autoComplete($args->field, $args->search);
case 'sku':
$query = 'SELECT sku
FROM vendorItems
WHERE sku LIKE ? ';
$param = array($args->search . '%');
if (property_exists($args, 'vendor_id')) {
$query .= ' AND vendorID=? ';
$param[] = $args->vendor_id;
}
$query .= 'GROUP BY sku
ORDER BY sku';
$prep = $dbc->prepare($query);
$res = $dbc->execute($prep, $param);
while ($row = $dbc->fetch_row($res)) {
$ret[] = $row['sku'];
if (count($ret) > 50) {
break;
}
}
return $ret;
case 'unit':
$query = '
SELECT unitofmeasure
FROM products
WHERE unitofmeasure LIKE ?
GROUP BY unitofmeasure
ORDER BY unitofmeasure';
$param = array($args->search . '%');
$prep = $dbc->prepare($query);
$res = $dbc->execute($prep, $param);
while ($row = $dbc->fetchRow($res)) {
$ret[] = $row['unitofmeasure'];
if (count($ret) > 50) {
break;
}
}
return $ret;
default:
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()
}
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
}
function auth_enabled()
{
if (!class_exists('FannieConfig')) {
include dirname(__FILE__) . '/../classlib2.0/FannieConfig.php';
}
return FannieConfig::config('AUTH_ENABLED', false);
}
/**
Transition mechanism. Auto-append store_id value
if only a UPC has been specified.
*/
public function load()
{
if (!isset($this->instance['store_id'])) {
$config = FannieConfig::factory();
$this->store_id($config->get('STORE_ID'));
}
return parent::load();
}
/**
Copy a table from the lanes to the server
@param $table string table name
@param $db string 'op' or 'trans'
(default is 'trans')
@param $truncate integer
(default is TRUNCATE_SOURCE)
@return array
- sending => boolean attempted to copy table
- messages => string result information
*/
public static function pullTable($table, $db = 'trans', $truncate = self::TRUNCATE_SOURCE)
{
$config = \FannieConfig::factory();
$op_db = $config->get('OP_DB');
$trans_db = $config->get('TRANS_DB');
$lanes = $config->get('LANES');
$ret = array('sending' => True, 'messages' => '');
$db = strtolower($db);
if ($db != 'op' && $db != 'trans') {
$ret['sending'] = False;
$ret['messages'] = 'Error: Invalid database: ' . $db;
return $ret;
} elseif (empty($table)) {
$ret['sending'] = False;
$ret['messages'] = 'Error: No table given';
return $ret;
} elseif (!preg_match('/^[A-Za-z0-9_]$/', $table)) {
$ret['sending'] = False;
$ret['messages'] = 'Error: Illegal table name: ' . $table;
return $ret;
}
// use the transfer option in SQLManager to copy
// records from each lane
$server_db = $db == 'op' ? $op_db : $trans_db;
$dbc = \FannieDB::get($server_db);
if ($truncate & self::TRUNCATE_DESTINATION) {
$dbc->query("TRUNCATE TABLE {$table}", $server_db);
}
$laneNumber = 1;
foreach ($lanes as $lane) {
$dbc->add_connection($lane['host'], $lane['type'], $lane[$db], $lane['user'], $lane['pw']);
if ($dbc->connections[$lane[$db]]) {
$success = $dbc->transfer($lane[$db], "SELECT * FROM {$table}", $server_db, "INSERT INTO {$table}");
if ($truncate & self::TRUNCATE_SOURCE) {
$dbc->query("TRUNCATE TABLE {$table}", $lane[$db]);
}
$dbc->close($lane[$db]);
if ($success) {
$ret['messages'] .= "Lane {$laneNumber} ({$lane['host']}) {$table} completed successfully";
} else {
$ret['messages'] .= "Error: Lane {$laneNumber} ({$lane['host']}) {$table} completed but with some errors";
}
} else {
$ret['messages'] .= "Error: Couldn't connect to lane {$laneNumber} ({$lane['host']})";
}
$laneNumber++;
}
return $ret;
}
public function guessAccounts()
{
$dbc = $this->connection;
$detailP = $dbc->prepare('
SELECT o.sku,
o.internalUPC,
o.receivedTotalCost
FROM PurchaseOrderItems AS o
WHERE o.internalUPC NOT IN (
SELECT upc FROM products
)
AND o.orderID=?
AND o.receivedTotalCost <> 0');
$detailR = $dbc->execute($detailP, array($this->orderID()));
$config = FannieConfig::factory();
$soP1 = $dbc->prepare('
SELECT d.salesCode
FROM ' . $config->get('TRANS_DB') . $dbc->sep() . 'CompleteSpecialOrder AS o
INNER JOIN departments AS d ON o.department=d.dept_no
WHERE o.upc=?');
$soP2 = $dbc->prepare('
SELECT d.salesCode
FROM ' . $config->get('TRANS_DB') . $dbc->sep() . 'PendingSpecialOrder AS o
INNER JOIN departments AS d ON o.department=d.dept_no
WHERE o.upc=?');
$vdP = $dbc->prepare('
SELECT d.salesCode
FROM vendorItems AS v
INNER JOIN vendorDepartments AS p ON v.vendorDept = p.deptID
INNER JOIN departments AS d ON p.deptID=d.dept_no
WHERE v.sku=?
AND v.vendorID=?');
$coding = array('n/a' => 0.0);
while ($w = $dbc->fetchRow($detailR)) {
$soR = $dbc->execute($soP1, array($w['internalUPC']));
if ($dbc->numRows($soR) > 0) {
$soW = $dbc->fetchRow($soR);
if (!isset($coding[$soW['salesCode']])) {
$coding[$soW['salesCode']] = 0.0;
}
$coding[$soW['salesCode']] += $w['receivedTotalCost'];
continue;
}
$soR = $dbc->execute($soP2, array($w['internalUPC']));
if ($dbc->numRows($soR) > 0) {
$soW = $dbc->fetchRow($soR);
if (!isset($coding[$soW['salesCode']])) {
$coding[$soW['salesCode']] = 0.0;
}
$coding[$soW['salesCode']] += $w['receivedTotalCost'];
continue;
}
$soR = $dbc->execute($vdP, array($w['sku'], $this->vendorID()));
if ($dbc->numRows($soR) > 0) {
$soW = $dbc->fetchRow($soR);
if (!isset($coding[$soW['salesCode']])) {
$coding[$soW['salesCode']] = 0.0;
}
$coding[$soW['salesCode']] += $w['receivedTotalCost'];
continue;
}
$coding['n/a'] += $w['receivedTotalCost'];
}
return $coding;
}
