public function fetch_report_data() { global $FANNIE_OP_DB; $dbc = FannieDB::get($FANNIE_OP_DB); $date1 = $this->form->date1; $date2 = $this->form->date2; $qty = FormLib::get('qty', 1); $create = $dbc->prepare_statement("CREATE TABLE groupingTempBS (upc VARCHAR(13), quantity double, total decimal(10,2), trans_num varchar(50))"); $dbc->exec_statement($create); $dlog = DTransactionsModel::selectDlog($date1, $date2); $setupQ = $dbc->prepare_statement("INSERT INTO groupingTempBS\n SELECT upc, quantity, total, trans_num\n FROM {$dlog} AS d WHERE tdate BETWEEN ? AND ?\n AND trans_type IN ('I','D')\n GROUP BY year(tdate),month(tdate),day(tdate),trans_num \n HAVING COUNT(*) <= ?"); $dbc->exec_statement($setupQ, array($date1 . ' 00:00:00', $date2 . ' 23:59:59', $qty)); $reportQ = $dbc->prepare_statement(' SELECT g.upc, p.description, SUM(g.quantity) AS qty, COUNT(DISTINCT trans_num) AS num, SUM(total) AS ttl FROM groupingTempBS as g ' . DTrans::joinProducts('g', 'p') . ' GROUP BY g.upc, p.description HAVING sum(total) <> 0 ORDER BY count(*) DESC '); $reportR = $dbc->exec_statement($reportQ); $data = array(); while ($w = $dbc->fetch_row($reportR)) { $record = array($w['upc'], empty($w['description']) ? 'n/a' : $w['description'], $w[3], sprintf('%.2f', $w[2]), sprintf('%.2f', $w[4])); $data[] = $record; } $drop = $dbc->prepare_statement("DROP TABLE groupingTempBS"); $dbc->exec_statement($drop); return $data; }
public function fetch_report_data() { $dbc = $this->connection; $dbc->selectDB($this->config->get('OP_DB')); $query = ' SELECT b.batchID, b.batchName, l.upc, p.normal_price, l.salePrice, CASE WHEN b.startDate > ' . $dbc->curdate() . ' THEN 0 ELSE 1 END AS current FROM batches AS b INNER JOIN batchList AS l ON b.batchID=l.batchID ' . DTrans::joinProducts('l', 'p', 'INNER') . ' WHERE b.endDate >= ' . $dbc->curdate() . ' AND b.discounttype <> 0 AND l.salePrice >= p.normal_price'; $result = $dbc->query($query); $data = array(); while ($w = $dbc->fetchRow($result)) { $record = array('<a href="' . $this->config->get('URL') . 'batches/newbatch/EditBatchPage.php?id=' . $w['batchID'] . '">' . $w['batchName'] . '</a>', $w['upc'], $w['current'] == 1 ? 'Yes' : 'No', $w['normal_price'], $w['salePrice']); $data[] = $record; } return $data; }
public function fetch_report_data() { $dbc = $this->connection; $dbc->selectDB($this->config->get('OP_DB')); $date1 = $this->form->date1; $date2 = $this->form->date2; $deptStart = FormLib::get('deptStart'); $deptEnd = FormLib::get('deptEnd'); $deptMulti = FormLib::get('departments', array()); $buyer = FormLib::get('buyer', ''); // args/parameters differ with super // vs regular department $args = array($date1 . ' 00:00:00', $date2 . ' 23:59:59'); $where = ' 1=1 '; if ($buyer !== '') { if ($buyer == -2) { $where .= ' AND s.superID != 0 '; } elseif ($buyer != -1) { $where .= ' AND s.superID=? '; $args[] = $buyer; } } if ($buyer != -1) { list($conditional, $args) = DTrans::departmentClause($deptStart, $deptEnd, $deptMulti, $args); $where .= $conditional; } $dlog = DTransactionsModel::selectDlog($date1, $date2); $query = "\n SELECT d.upc,\n p.brand,\n p.description," . DTrans::sumQuantity('d') . " AS qty,\n CASE WHEN memDiscount <> 0 AND memType <> 0 THEN unitPrice - memDiscount ELSE unitPrice END as price,\n d.department, \n t.dept_name, \n SUM(total) AS total\n FROM {$dlog} AS d " . DTrans::joinProducts('d', 'p', 'inner') . DTrans::joinDepartments('d', 't'); // join only needed with specific buyer if ($buyer !== '' && $buyer > -1) { $query .= 'LEFT JOIN superdepts AS s ON d.department=s.dept_ID '; } elseif ($buyer !== '' && $buyer == -2) { $query .= 'LEFT JOIN MasterSuperDepts AS s ON d.department=s.dept_ID '; } $query .= "\n WHERE tdate BETWEEN ? AND ?\n AND {$where}\n GROUP BY d.upc,p.description,price,d.department,t.dept_name\n ORDER BY d.upc"; $prep = $dbc->prepare_statement($query); $result = $dbc->exec_statement($query, $args); $data = array(); while ($row = $dbc->fetch_row($result)) { $record = array($row['upc'], $row['brand'], $row['description'], $row['department'], $row['dept_name'], sprintf('%.2f', $row['price']), sprintf('%.2f', $row['qty']), sprintf('%.2f', $row['total'])); $data[] = $record; } // bold items that sold at multiple prices for ($i = 0; $i < count($data); $i++) { if (!isset($data[$i + 1])) { continue; } if ($data[$i][0] == $data[$i + 1][0]) { $data[$i]['meta'] = FannieReportPage::META_BOLD; $data[$i + 1]['meta'] = FannieReportPage::META_BOLD; } } return $data; }
public function fetch_report_data() { global $FANNIE_OP_DB, $FANNIE_SERVER_DBMS; // creates a temporary table so requesting a writable connection // does make sense here $dbc = FannieDB::get($FANNIE_OP_DB); $depts = FormLib::get('depts', array()); $upc = FormLib::get('upc'); $date1 = $this->form->date1; $date2 = $this->form->date2; $filters = FormLib::get('filters', array()); list($dClause, $dArgs) = $dbc->safeInClause($depts); $where = "d.department IN ({$dClause})"; $inv = "d.department NOT IN ({$dClause})"; if ($upc != "") { $upc = BarcodeLib::padUPC($upc); $where = "d.upc = ?"; $inv = "d.upc <> ?"; $dArgs = array($upc); } $dlog = DTransactionsModel::selectDlog($date1, $date2); $filter = ""; $fArgs = array(); if (is_array($filters) && count($filters) > 0) { $fClause = ""; foreach ($filters as $f) { $fClause .= "?,"; $fArgs[] = $f; } $fClause = "(" . rtrim($fClause, ",") . ")"; $filter = "AND d.department IN {$fClause}"; } $query = $dbc->prepare_statement("CREATE TABLE groupingTemp (tdate varchar(11), emp_no int, register_no int, trans_no int)"); $dbc->exec_statement($query); $dateConvertStr = $FANNIE_SERVER_DBMS == 'MSSQL' ? 'convert(char(11),d.tdate,110)' : 'convert(date(d.tdate),char)'; $loadQ = $dbc->prepare_statement("INSERT INTO groupingTemp\n SELECT {$dateConvertStr} as tdate,\n emp_no,register_no,trans_no FROM {$dlog} AS d\n WHERE {$where} AND tdate BETWEEN ? AND ?\n GROUP BY {$dateConvertStr}, emp_no,register_no,trans_no"); $dArgs[] = $date1 . ' 00:00:00'; $dArgs[] = $date2 . ' 23:59:59'; $dbc->exec_statement($loadQ, $dArgs); $dataQ = $dbc->prepare_statement("\n SELECT d.upc,\n p.description,\n t.dept_no,\n t.dept_name,\n SUM(d.quantity) AS quantity\n FROM {$dlog} AS d \n INNER JOIN groupingTemp AS g ON \n {$dateConvertStr} = g.tdate\n AND g.emp_no = d.emp_no\n AND g.register_no = d.register_no\n AND g.trans_no = d.trans_no " . DTrans::joinProducts('d', 'p') . DTrans::joinDepartments('d', 't') . "\n WHERE {$inv} \n AND trans_type IN ('I','D')\n AND d.tdate BETWEEN ? AND ?\n AND d.trans_status=''\n {$filter}\n GROUP BY d.upc,\n p.description,\n t.dept_no,\n t.dept_name\n ORDER BY SUM(d.quantity) DESC"); foreach ($fArgs as $f) { $dArgs[] = $f; } $dataR = $dbc->exec_statement($dataQ, $dArgs); $data = array(); while ($dataW = $dbc->fetch_row($dataR)) { $record = array($dataW['upc'], $dataW['description'], $dataW['dept_no'] . ' ' . $dataW['dept_name'], sprintf('%.2f', $dataW['quantity'])); $data[] = $record; } $drop = $dbc->prepare_statement("DROP TABLE groupingTemp"); $dbc->exec_statement($drop); return $data; }
function fetch_report_data() { $dbc = $this->connection; $dbc->selectDB($this->config->get('OP_DB')); $month1 = $this->form->month1; $month2 = $this->form->month2; $year1 = FormLib::get_form_value('year1', date('Y')); $year2 = FormLib::get_form_value('year2', date('Y')); $date1 = date('Y-m-d', mktime(0, 0, 0, $month1, 1, $year1)); $date2 = date('Y-m-t', mktime(0, 0, 0, $month2, 1, $year2)); $dlog = DTransactionsModel::selectDlog($date1, $date2); $date1 .= ' 00:00:00'; $date2 .= ' 00:00:00'; $qArgs = array($date1, $date2); $query = ""; $type = FormLib::get_form_value('mtype', 'upc'); if ($type == 'upc') { $inClause = "("; $vals = preg_split("/\\D+/", FormLib::get_form_value('upcs', '')); foreach ($vals as $v) { $qArgs[] = BarcodeLib::padUPC($v); $inClause .= "?,"; } $inClause = rtrim($inClause, ",") . ")"; $query = "SELECT t.upc,\n p.description, " . DTrans::sumQuantity('t') . " AS qty,\n SUM(total) AS sales, \n MONTH(tdate) AS month, \n YEAR(tdate) AS year\n FROM {$dlog} AS t " . DTrans::joinProducts('t', 'p') . " \n WHERE t.trans_status <> 'M'\n AND tdate BETWEEN ? AND ?\n AND t.upc IN {$inClause}\n GROUP BY YEAR(tdate),\n MONTH(tdate),\n t.upc,\n p.description\n ORDER BY YEAR(tdate),\n MONTH(tdate),\n t.upc,\n p.description"; } else { $dept1 = FormLib::get_form_value('dept1', 1); $dept2 = FormLib::get_form_value('dept2', 1); $qArgs[] = $dept1; $qArgs[] = $dept2; $query = "SELECT t.department,d.dept_name,SUM(t.quantity) as qty,\n SUM(total) as sales, MONTH(tdate) as month, YEAR(tdate) as year\n FROM {$dlog} AS t\n LEFT JOIN departments AS d ON t.department=d.dept_no\n WHERE t.trans_status <> 'M'\n AND tdate BETWEEN ? AND ?\n AND t.department BETWEEN ? AND ?\n GROUP BY YEAR(tdate),MONTH(tdate),t.department,d.dept_name\n ORDER BY YEAR(tdate),MONTH(tdate),t.department,d.dept_name"; } $queryP = $dbc->prepare_statement($query); $result = $dbc->exec_statement($queryP, $qArgs); $ret = array(); while ($row = $dbc->fetch_array($result)) { if (!isset($ret[$row[0]])) { $ret[$row[0]] = array('num' => $row[0], 'desc' => $row[1]); foreach ($this->months as $mkey) { $ret[$row[0]][$mkey] = 0; } } if (FormLib::get_form_value('results', 'Sales') == 'Sales') { $ret[$row[0]][$row['year'] . '-' . $row['month']] = $row['sales']; } else { $ret[$row[0]][$row['year'] . '-' . $row['month']] = $row['qty']; } } return $this->dekey_array($ret); }
public function fetch_report_data() { $dbc = $this->connection; $dbc->selectDB($this->config->get('OP_DB')); $date1 = $this->form->date1; $date2 = $this->form->date2; $manu = FormLib::get_form_value('manu', ''); $type = FormLib::get_form_value('type', ''); $groupby = FormLib::get_form_value('groupby', 'upc'); $dlog = DTransactionsModel::selectDlog($date1, $date2); $type_condition = "p.brand LIKE ?"; $args = array('%' . $manu . '%'); if ($type == 'prefix') { $type_condition = 't.upc LIKE ?'; } $query = ""; $args[] = $date1 . ' 00:00:00'; $args[] = $date2 . ' 23:59:59'; switch ($groupby) { case 'upc': $query = "\n SELECT t.upc,\n p.brand,\n p.description, " . DTrans::sumQuantity('t') . " AS qty,\n SUM(t.total) AS ttl,\n d.dept_no,\n d.dept_name,\n s.superID\n FROM {$dlog} AS t " . DTrans::joinProducts('t', 'p', 'INNER') . DTrans::joinDepartments('t', 'd') . "\n LEFT JOIN MasterSuperDepts AS s ON d.dept_no = s.dept_ID\n WHERE {$type_condition}\n AND t.tdate BETWEEN ? AND ?\n GROUP BY t.upc,\n p.description,\n d.dept_no,\n d.dept_name,\n s.superID\n ORDER BY SUM(t.total) DESC"; break; case 'date': $query = "\n SELECT YEAR(t.tdate) AS year,\n MONTH(t.tdate) AS month,\n DAY(t.tdate) AS day, " . DTrans::sumQuantity('t') . " AS qty,\n SUM(t.total) AS ttl\n FROM {$dlog} AS t " . DTrans::joinProducts('t', 'p', 'INNER') . "\n WHERE {$type_condition}\n AND t.tdate BETWEEN ? AND ?\n GROUP BY YEAR(t.tdate),\n MONTH(t.tdate),\n DAY(t.tdate)\n ORDER BY YEAR(t.tdate),\n MONTH(t.tdate),\n DAY(t.tdate)"; break; case 'dept': $query = "\n SELECT d.dept_no,\n d.dept_name, " . DTrans::sumQuantity('t') . " AS qty,\n SUM(t.total) AS ttl,\n s.superID\n FROM {$dlog} AS t " . DTrans::joinProducts('t', 'p', 'INNER') . DTrans::joinDepartments('t', 'd') . "\n LEFT JOIN MasterSuperDepts AS s ON d.dept_no=s.dept_ID\n WHERE {$type_condition}\n AND t.tdate BETWEEN ? AND ?\n GROUP BY d.dept_no,\n d.dept_name,\n s.superID\n ORDER BY SUM(t.total) DESC"; break; } $prep = $dbc->prepare_statement($query); $result = $dbc->exec_statement($prep, $args); $ret = array(); while ($row = $dbc->fetch_array($result)) { $record = array(); if ($groupby == "date") { $record[] = $row['month'] . '/' . $row['day'] . '/' . $row['year']; $record[] = number_format($row['qty'], 2); $record[] = number_format($row['ttl'], 2); } else { for ($i = 0; $i < $dbc->num_fields($result); $i++) { if ($dbc->field_name($result, $i) == 'qty' || $dbc->field_name($result, $i) == 'ttl') { $row[$i] = sprintf('%.2f', $row[$i]); } $record[] .= $row[$i]; } } $ret[] = $record; } return $ret; }
function lookupItem($store, $sec, $subsec, $sh_set, $shelf, $loc) { global $FANNIE_OP_DB; $dbc = FannieDB::get($FANNIE_OP_DB); $q = $dbc->prepare_statement("SELECT l.upc,p.description FROM prodPhysicalLocation AS l\n " . DTrans::joinProducts('l') . "\n WHERE l.store_id=? AND section=? AND subsection=?\n AND shelf_set=? AND shelf=? AND location=?"); $args = array($store, $sec, $subsec, $sh_set, $shelf, $loc); $r = $dbc->exec_statement($q, $args); $ret = array('upc' => '', 'description' => 'no item at this location'); if ($dbc->num_rows($r) > 0) { $w = $dbc->fetch_row($r); $ret['upc'] = $w['upc']; $ret['description'] = $w['description']; } return $ret; }
public function guessCode() { $dbc = $this->connection; // case 1: item exists in products $deptP = $dbc->prepare(' SELECT d.salesCode FROM products AS p INNER JOIN departments AS d ON p.department=d.dept_no WHERE p.upc=?'); $deptR = $dbc->execute($deptP, array($this->internalUPC())); if ($dbc->numRows($deptR)) { $w = $dbc->fetchRow($deptR); return $w['salesCode']; } $order = new PurchaseOrderModel($dbc); $order->orderID($this->orderID()); $order->load(); // case 2: item is SKU-mapped but the order record // does not reflect the internal PLU $deptP = $dbc->prepare(' SELECT d.salesCode FROM vendorSKUtoPLU AS v ' . DTrans::joinProducts('v', 'p', 'INNER') . ' INNER JOIN departments AS d ON p.department=d.dept_no WHERE v.sku=? AND v.vendorID=?'); $deptR = $dbc->execute($deptP, array($this->sku(), $order->vendorID())); if ($dbc->numRows($deptR)) { $w = $dbc->fetchRow($deptR); return $w['salesCode']; } // case 3: item is not normally carried but is in a vendor catalog // that has vendor => POS department mapping $deptP = $dbc->prepare(' SELECT d.salesCode FROM vendorItems AS v INNER JOIN vendorDepartments AS z ON v.vendorDept=z.deptID AND v.vendorID=z.vendorID INNER JOIN departments AS d ON z.posDeptID=d.dept_no WHERE v.sku=? AND v.vendorID=?'); $deptR = $dbc->execute($deptP, array($this->sku(), $order->vendorID())); if ($dbc->numRows($deptR)) { $w = $dbc->fetchRow($deptR); return $w['salesCode']; } return false; }
function fetch_report_data() { $dbc = $this->connection; $dbc->selectDB($this->config->get('OP_DB')); $date1 = $this->form->date1; $date2 = $this->form->date2; $upc = $this->form->upc; if (is_numeric($upc)) { $upc = BarcodeLib::padUPC($upc); } $dlog = DTransactionsModel::selectDlog($date1, $date2); $query = "SELECT \n MONTH(t.tdate),\n DAY(t.tdate),\n YEAR(t.tdate),\n t.upc,\n p.brand,\n p.description,\n " . DTrans::sumQuantity('t') . " AS qty,\n SUM(t.total) AS total\n FROM {$dlog} AS t \n " . DTrans::joinProducts('t', 'p', 'LEFT') . "\n WHERE t.upc = ? AND\n t.tdate BETWEEN ? AND ?\n GROUP BY \n YEAR(t.tdate),\n MONTH(t.tdate),\n DAY(t.tdate),\n t.upc,\n p.description\n ORDER BY year(t.tdate),month(t.tdate),day(t.tdate)"; $args = array($upc, $date1 . ' 00:00:00', $date2 . ' 23:59:59'); if (strtolower($upc) == "rrr" || $upc == "0000000000052") { if ($dlog == "dlog_90_view" || $dlog == "dlog_15") { $dlog = "transarchive"; } else { $dlog = "trans_archive.bigArchive"; } $query = "select MONTH(datetime),DAY(datetime),YEAR(datetime),\n upc,'' AS brand,'RRR' AS description,\n sum(case when upc <> 'rrr' then quantity when volSpecial is null or volSpecial > 9999 then 0 else volSpecial end) as qty,\n sum(t.total) AS total from\n {$dlog} as t\n where upc = ?\n AND datetime BETWEEN ? AND ?\n and emp_no <> 9999 and register_no <> 99\n and trans_status <> 'X'\n GROUP BY YEAR(datetime),MONTH(datetime),DAY(datetime)\n ORDER BY YEAR(datetime),MONTH(datetime),DAY(datetime)"; } else { if (!is_numeric($upc)) { $dlog = DTransactionsModel::selectDTrans($date1, $date2); $query = "select MONTH(datetime),DAY(datetime),YEAR(datetime),\n upc,'' AS brand, description,\n sum(CASE WHEN quantity=0 THEN 1 ELSE quantity END) as qty,\n sum(t.total) AS total from\n {$dlog} as t\n where upc = ?\n AND datetime BETWEEN ? AND ?\n and emp_no <> 9999 and register_no <> 99\n and (trans_status <> 'X' || trans_type='L')\n GROUP BY YEAR(datetime),MONTH(datetime),DAY(datetime)"; } } $prep = $dbc->prepare_statement($query); $result = $dbc->exec_statement($prep, $args); /** Simple report Issue a query, build array of results */ $ret = array(); while ($row = $dbc->fetch_array($result)) { $record = array(); $record[] = $row[0] . "/" . $row[1] . "/" . $row[2]; $record[] = $row['upc']; $record[] = $row['brand'] === null ? '' : $row['brand']; $record[] = $row['description'] === null ? '' : $row['description']; $record[] = sprintf('%.2f', $row['qty']); $record[] = sprintf('%.2f', $row['total']); $ret[] = $record; } return $ret; }
public function fetch_report_data() { $dbc = $this->connection; $dbc->selectDB($this->config->get('OP_DB')); $date1 = $this->form->date1; $date2 = $this->form->date2; $vendor = FormLib::get_form_value('vendor', ''); $groupby = FormLib::get_form_value('groupby', 'upc'); $dlog = DTransactionsModel::selectDlog($date1, $date2); $query = ""; switch ($groupby) { case 'upc': $query = "\n SELECT t.upc,\n COALESCE(p.brand, x.manufacturer) AS brand,\n p.description, " . DTrans::sumQuantity('t') . " AS qty,\n SUM(t.total) AS ttl,\n d.dept_no,\n d.dept_name,\n s.super_name\n FROM {$dlog} AS t " . DTrans::joinProducts('t', 'p', 'INNER') . DTrans::joinDepartments('t', 'd') . "\n LEFT JOIN vendors AS v ON p.default_vendor_id = v.vendorID\n LEFT JOIN prodExtra AS x ON p.upc=x.upc\n LEFT JOIN MasterSuperDepts AS s ON d.dept_no = s.dept_ID\n WHERE (v.vendorName LIKE ? OR x.distributor LIKE ?)\n AND t.tdate BETWEEN ? AND ?\n GROUP BY t.upc,\n COALESCE(p.brand, x.manufacturer),\n p.description,\n d.dept_no,\n d.dept_name,\n s.super_name\n ORDER BY SUM(t.total) DESC"; break; case 'date': $query = "\n SELECT YEAR(t.tdate) AS year,\n MONTH(t.tdate) AS month,\n DAY(t.tdate) AS day, " . DTrans::sumQuantity('t') . " AS qty,\n SUM(t.total) AS ttl\n FROM {$dlog} AS t " . DTrans::joinProducts('t', 'p') . "\n LEFT JOIN vendors AS v ON p.default_vendor_id = v.vendorID\n LEFT JOIN prodExtra AS x ON p.upc=x.upc\n WHERE (v.vendorName LIKE ? OR x.distributor LIKE ?)\n AND t.tdate BETWEEN ? AND ?\n GROUP BY YEAR(t.tdate),\n MONTH(t.tdate),\n DAY(t.tdate)\n ORDER BY YEAR(t.tdate),\n MONTH(t.tdate),\n DAY(t.tdate)"; break; case 'dept': $query = "\n SELECT d.dept_no,\n d.dept_name, " . DTrans::sumQuantity('t') . " AS qty,\n SUM(t.total) AS ttl,\n s.super_name\n FROM {$dlog} AS t " . DTrans::joinProducts('t', 'p', 'INNER') . DTrans::joinDepartments('t', 'd') . "\n LEFT JOIN vendors AS v ON p.default_vendor_id = v.vendorID\n LEFT JOIN MasterSuperDepts AS s ON d.dept_no=s.dept_ID\n LEFT JOIN prodExtra AS x ON p.upc=x.upc\n WHERE (v.vendorName LIKE ? OR x.distributor LIKE ?)\n AND t.tdate BETWEEN ? AND ?\n GROUP BY d.dept_no,\n d.dept_name,\n s.super_name\n ORDER BY SUM(t.total) DESC"; break; } $args = array('%' . $vendor . '%', '%' . $vendor . '%', $date1 . ' 00:00:00', $date2 . ' 23:59:59'); $prep = $dbc->prepare_statement($query); $result = $dbc->exec_statement($prep, $args); $ret = array(); while ($row = $dbc->fetch_array($result)) { $record = array(); if ($groupby == "date") { $record[] = $row['month'] . '/' . $row['day'] . '/' . $row['year']; $record[] = number_format($row['qty'], 2); $record[] = number_format($row['ttl'], 2); } else { for ($i = 0; $i < $dbc->num_fields($result); $i++) { if ($dbc->field_name($result, $i) == 'qty' || $dbc->field_name($result, $i) == 'ttl') { $row[$i] = number_format($row[$i], 2); } $record[] .= $row[$i]; } } $ret[] = $record; } return $ret; }
public function run() { $dbc = FannieDB::get($this->config->get('OP_DB')); $yesterday = date('Y-m-d', strtotime('yesterday')); $url = $this->config->get('URL'); $host = php_uname('n'); $dtrans = DTransactionsModel::selectDTrans($yesterday); $findP = $dbc->prepare(' SELECT d.upc, d.description, d.department, p.inUse, count(*) AS occurences FROM ' . $dtrans . ' AS d ' . DTrans::joinProducts('d') . ' WHERE d.trans_type=\'L\' AND d.trans_subtype=\'OG\' AND d.charflag=\'IU\' AND d.emp_no <> 9999 AND d.register_no <> 99 AND d.datetime BETWEEN ? AND ? GROUP BY d.upc, d.description, d.department, p.inUse HAVING SUM(d.quantity) <> 0 '); $findR = $dbc->execute($findP, array($yesterday . ' 00:00:00', $yesterday . ' 23:59:59')); while ($w = $dbc->fetchRow($findR)) { $msg = sprintf('%s (%s) was sold %d times while not inUse', $w['description'], $w['upc'], $w['occurences']); $email = \COREPOS\Fannie\API\lib\AuditLib::getAddresses($w['department']); if ($email) { $subject = 'Not In Use Report'; $from = "From: automail\r\n"; $msg .= "\n"; $msg .= "http://{$host}/{$url}item/ItemEditorPage.php?searchupc={$w['upc']}\n"; mail($email, $subject, $msg, $from); } else { $this->cronMsg($msg); } } }
public function form_content() { $dbc = $this->connection; $dbc->selectDB($this->config->get('OP_DB')); $set = FormLib::get('deal-set'); $optsR = $dbc->query(' SELECT dealSet FROM CoopDealsItems GROUP BY dealSet ORDER BY MAX(coopDealsItemID) DESC'); $opts = ''; while ($optsW = $dbc->fetchRow($optsR)) { if ($set === '') { $set = $optsW['dealSet']; } $opts .= sprintf('<option %s>%s</option>', $set == $optsW['dealSet'] ? 'selected' : '', $optsW['dealSet']); } $query = $dbc->prepare_statement("\n SELECT\n t.upc,\n p.brand,\n p.description,\n t.price,\n CASE WHEN s.super_name IS NULL THEN 'sale' ELSE s.super_name END as batch,\n t.abtpr as subbatch\n FROM CoopDealsItems as t\n " . DTrans::joinProducts('t', 'p', 'INNER') . "\n LEFT JOIN MasterSuperDepts AS s ON p.department=s.dept_ID\n WHERE t.dealSet=?\n AND p.inUse=1\n ORDER BY s.super_name,t.upc\n "); $result = $dbc->exec_statement($query, array($set)); $ret = "<form action=CoopDealsReviewPage.php method=post>\n <div class=\"form-group\">\n <label>Month</label>\n <select name=\"deal-set\" class=\"form-control\" \n onchange=\"location='?deal-set='+this.value;\">\n " . $opts . "\n </select>\n </div>\n <table class=\"table table-bordered table-striped tablesorter tablesorter-core small\">\n <thead>\n <tr><th>UPC</th><th>Brand</th><th>Desc</th><th>Sale Price</th>\n <th>New Batch Name</th></tr>\n\n </thead><tbody>"; while ($row = $dbc->fetch_row($result)) { $ret .= sprintf('<tr> <td>%s</td> <td>%s</td> <td>%s</td> <td>%.2f</td> <td><span class="superNameSpan">%s </span>Co-op Deals %s</td> </tr>' . "\n", $row['upc'], $row['brand'], $row['description'], $row['price'], $row['batch'], $row['subbatch']); } $ret .= <<<html </tbody> </table><p /> <div class="row form-horizontal form-group"> <label class="col-sm-2 control-label">A Start</label> <div class="col-sm-4"> <input type="text" name="start" id="start" class="form-control date-field" /> </div> <label class="col-sm-2 control-label">B Start</label> <div class="col-sm-4"> <input type="text" name="bstart" id="bstart" class="form-control date-field" /> </div> </div> <div class="row form-horizontal form-group"> <label class="col-sm-2 control-label">A End</label> <div class="col-sm-4"> <input type="text" name="end" id="end" class="form-control date-field" /> </div> <label class="col-sm-2 control-label">B End</label> <div class="col-sm-4"> <input type="text" name="bend" id="bend" class="form-control date-field" /> </div> </div> <div class="row form-horizontal form-group"> <label class="col-sm-2 control-label">Month</label> <div class="col-sm-4"> <input type="text" name="naming" class="form-control" value="{{set}}" /> </div> <label class="col-sm-6"> <input type="checkbox" name="group_by_superdepts" checked="true" onchange="\$('.superNameSpan').toggle(); " /> Group sale batches by Superdepartment </label> </div> <p> <button type=submit class="btn btn-default">Create Batch(es)</button> <a href="CoopDealsMergePage.php" class="pull-right btn btn-default">Merge New Items into Existing Batch(es)</a> </p> </form> html; $ret = str_replace('{{set}}', $set, $ret); return $ret; }
public function get_view() { $dbc = $this->connection; $dbc->selectDB($this->config->get('OP_DB')); $set = FormLib::get('deal-set'); $optsR = $dbc->query(' SELECT dealSet FROM CoopDealsItems GROUP BY dealSet ORDER BY MAX(coopDealsItemID) DESC'); $deal_opts = ''; while ($optsW = $dbc->fetchRow($optsR)) { if ($set === '') { $set = $optsW['dealSet']; } $deal_opts .= sprintf('<option %s>%s</option>', $set == $optsW['dealSet'] ? 'selected' : '', $optsW['dealSet']); } $query = $dbc->prepare_statement("\n SELECT\n t.upc,\n p.description,\n p.brand,\n t.price,\n CASE WHEN s.super_name IS NULL THEN 'sale' ELSE s.super_name END as batch,\n t.abtpr as subbatch,\n multiplier\n FROM\n CoopDealsItems as t\n " . DTrans::joinProducts('t', 'p', 'INNER') . "\n LEFT JOIN MasterSuperDepts AS s ON p.department=s.dept_ID\n WHERE p.inUse=1\n AND t.price < p.normal_price\n AND t.dealSet=?\n ORDER BY s.super_name,t.upc\n "); $result = $dbc->exec_statement($query, array($set)); $upcomingP = $dbc->prepare(' SELECT batchName FROM batchList AS l INNER JOIN batches AS b ON l.batchID=b.batchID WHERE l.upc=? AND b.endDate >= ' . $dbc->curdate()); $allR = $dbc->query(' SELECT batchID, batchName FROM batches WHERE endDate >= ' . $dbc->curdate()); $opts = array(); while ($allW = $dbc->fetchRow($allR)) { $opts[$allW['batchID']] = $allW['batchName']; } $ret = "<form action=CoopDealsMergePage.php method=post>\n <div class=\"form-group\">\n <label>Month</label>\n <select name=\"deal-set\" class=\"form-control\" \n onchange=\"location='?deal-set='+this.value;\">\n " . $deal_opts . "\n </select>\n </div>\n <table class=\"table table-bordered table-striped tablesorter tablesorter-core small\">\n <thead>\n <tr><th>UPC</th><th>Brand</th><th>Desc</th><th>Sale Price</th>\n <th>Add to Batch</th></tr>\n\n </thead><tbody>"; while ($row = $dbc->fetch_row($result)) { $upcoming = $dbc->getValue($upcomingP, array($row['upc'])); if ($upcoming) { continue; } $name = $row['batch'] . ' Co-op Deals ' . $row['subbatch']; $ret .= sprintf('<tr> <td><input type="hidden" name="upc[]" value="%s"/>%s <input type="hidden" name="mult[]" value="%d" /> </td> <td>%s</td> <td>%s</td> <td><input type="hidden" name="price[]" value="%.2f"/>%.2f</td> <td><select class="form-control input-sm" name="batchID[]"> <option value="">Select batch...</option>', $row['upc'], \COREPOS\Fannie\API\lib\FannieUI::itemEditorLink($row['upc']), $row['multiplier'], $row['brand'], $row['description'], $row['price'], $row['price']); foreach ($opts as $id => $batch) { $ret .= sprintf('<option %s value="%d">%s</option>', strstr($batch, $name) ? 'selected' : '', $id, $batch); } $ret .= '</select></td></tr>'; } $ret .= <<<html </tbody> </table> <p> <button type=submit class="btn btn-default">Merge Items into Batch(es)</button> <a href="CoopDealsReviewPage.php" class="pull-right btn btn-default">Create New Batch(es)</a> </p> </form> html; return $ret; }
/** Lots of options on this report. */ function fetch_report_data() { $dbc = $this->connection; $dbc->selectDB($this->config->get('OP_DB')); $date1 = $this->form->date1; $date2 = $this->form->date2; $deptStart = FormLib::get_form_value('deptStart', ''); $deptEnd = FormLib::get_form_value('deptEnd', ''); $deptMulti = FormLib::get('departments', array()); $buyer = FormLib::get_form_value('buyer', ''); $groupby = FormLib::get_form_value('sort', 'PLU'); $store = FormLib::get('store', 0); $superP = $dbc->prepare('SELECT dept_ID FROM superdepts WHERE superID=?'); /** Build a WHERE condition for later. Superdepartment (buyer) takes precedence over department and negative values have special meaning Extra lookup to write condition in terms of transaction.department seems to result in better index utilization and faster queries */ $filter_condition = 't.department BETWEEN ? AND ?'; $args = array($deptStart, $deptEnd); if (count($deptMulti) > 0) { $filter_condition = 't.department IN ('; $args = array(); foreach ($deptMulti as $d) { $filter_condition .= '?,'; $args[] = $d; } $filter_condition = substr($filter_condition, 0, strlen($filter_condition) - 1) . ')'; } if ($buyer !== "" && $buyer > 0) { $filter_condition .= ' AND s.superID=? '; $args[] = $buyer; /* $superR = $dbc->execute($superP, array($buyer)); $filter_condition = 't.department IN ('; $args = array(); while ($superW = $dbc->fetch_row($superR)) { $filter_condition .= '?,'; $args[] = $superW['dept_ID']; } $filter_condition = substr($filter_condition, 0, strlen($filter_condition)-1) . ')'; $filter_condition .= ' AND s.superID=?'; $args[] = $buyer; */ } else { if ($buyer !== "" && $buyer == -1) { $filter_condition = "1=1"; $args = array(); } else { if ($buyer !== "" && $buyer == -2) { $superR = $dbc->execute($superP, array(0)); $filter_condition = 't.department NOT IN (0,'; $args = array(); while ($superW = $dbc->fetch_row($superR)) { $filter_condition .= '?,'; $args[] = $superW['dept_ID']; } $filter_condition = substr($filter_condition, 0, strlen($filter_condition) - 1) . ')'; $filter_condition .= ' AND s.superID <> 0'; } } } /** * Provide more WHERE conditions to filter irrelevant * transaction records, as a stop-gap until this is * handled more uniformly across the application. */ $filter_transactions = "t.trans_status NOT IN ('D','X','Z')\n AND t.emp_no <> 9999\n AND t.register_no <> 99"; $filter_transactions = DTrans::isValid() . ' AND ' . DTrans::isNotTesting(); /** Select a summary table. For UPC results, per-unique-ring summary is needed. For date/dept/weekday results the per-department summary is fine (and a smaller table) */ $dlog = DTransactionsModel::selectDlog($date1, $date2); /** Build an appropriate query depending on the grouping option */ $query = ""; $superTable = $buyer !== "" && $buyer > 0 ? 'superdepts' : 'MasterSuperDepts'; $args[] = $date1 . ' 00:00:00'; $args[] = $date2 . ' 23:59:59'; $args[] = $store; switch ($groupby) { case 'PLU': $query = "SELECT t.upc,\n CASE WHEN p.description IS NULL THEN t.description ELSE p.description END as description, \n SUM(CASE WHEN trans_status IN('','0','R') THEN 1 WHEN trans_status='V' THEN -1 ELSE 0 END) as rings," . DTrans::sumQuantity('t') . " as qty,\n SUM(t.total) AS total,\n d.dept_no,d.dept_name,s.superID,\n COALESCE(v.vendorName,x.distributor) AS distributor\n FROM {$dlog} as t " . DTrans::joinProducts() . DTrans::joinDepartments() . "LEFT JOIN {$superTable} AS s ON t.department = s.dept_ID\n LEFT JOIN prodExtra as x on t.upc = x.upc\n LEFT JOIN vendors AS v ON p.default_vendor_id=v.vendorID\n WHERE {$filter_condition}\n AND t.trans_type IN ('I', 'D')\n AND tdate BETWEEN ? AND ?\n AND {$filter_transactions}\n AND " . DTrans::isStoreID($store, 't') . "\n GROUP BY t.upc,\n CASE WHEN p.description IS NULL THEN t.description ELSE p.description END,\n CASE WHEN t.trans_status = 'R' THEN 'Refund' ELSE 'Sale' END,\n d.dept_no,d.dept_name,s.superID,distributor ORDER BY SUM(t.total) DESC"; break; case 'Department': $query = "SELECT t.department,d.dept_name," . DTrans::sumQuantity('t') . " as qty,\n SUM(total) as Sales \n FROM {$dlog} as t " . DTrans::joinDepartments() . "LEFT JOIN {$superTable} AS s ON s.dept_ID = t.department \n WHERE {$filter_condition}\n AND tdate BETWEEN ? AND ?\n AND t.trans_type IN ('I', 'D')\n AND {$filter_transactions}\n AND " . DTrans::isStoreID($store, 't') . "\n GROUP BY t.department,d.dept_name ORDER BY SUM(total) DESC"; break; case 'Date': $query = "SELECT year(tdate),month(tdate),day(tdate)," . DTrans::sumQuantity('t') . " as qty,\n SUM(total) as Sales ,\n MAX(" . $dbc->dayofweek('tdate') . ") AS dow\n FROM {$dlog} as t " . DTrans::joinDepartments() . "LEFT JOIN {$superTable} AS s ON s.dept_ID = t.department\n WHERE {$filter_condition}\n AND tdate BETWEEN ? AND ?\n AND t.trans_type IN ('I', 'D')\n AND {$filter_transactions}\n AND " . DTrans::isStoreID($store, 't') . "\n GROUP BY year(tdate),month(tdate),day(tdate) \n ORDER BY year(tdate),month(tdate),day(tdate)"; break; case 'Weekday': $cols = $dbc->dayofweek("tdate") . ",CASE \n WHEN " . $dbc->dayofweek("tdate") . "=1 THEN 'Sun'\n WHEN " . $dbc->dayofweek("tdate") . "=2 THEN 'Mon'\n WHEN " . $dbc->dayofweek("tdate") . "=3 THEN 'Tue'\n WHEN " . $dbc->dayofweek("tdate") . "=4 THEN 'Wed'\n WHEN " . $dbc->dayofweek("tdate") . "=5 THEN 'Thu'\n WHEN " . $dbc->dayofweek("tdate") . "=6 THEN 'Fri'\n WHEN " . $dbc->dayofweek("tdate") . "=7 THEN 'Sat'\n ELSE 'Err' END"; $query = "SELECT {$cols}," . DTrans::sumQuantity('t') . " as qty,\n SUM(total) as Sales \n FROM {$dlog} as t " . DTrans::joinDepartments() . "LEFT JOIN {$superTable} AS s ON s.dept_ID = t.department \n WHERE {$filter_condition}\n AND tdate BETWEEN ? AND ?\n AND t.trans_type IN ('I', 'D')\n AND {$filter_transactions}\n AND " . DTrans::isStoreID($store, 't') . "\n GROUP BY {$cols}\n ORDER BY " . $dbc->dayofweek('tdate'); break; } /** Copy the results into an array. Date requires a special case to combine year, month, and day into a single field */ $prep = $dbc->prepare_statement($query); $result = $dbc->exec_statement($prep, $args); $ret = array(); while ($row = $dbc->fetch_array($result)) { $record = array(); if ($groupby == "Date") { $record[] = $row[1] . "/" . $row[2] . "/" . $row[0]; $record[] = date('l', strtotime($record[0])); $record[] = sprintf('%.2f', $row[3]); $record[] = sprintf('%.2f', $row[4]); } else { for ($i = 0; $i < $dbc->num_fields($result); $i++) { if (preg_match('/^\\d+\\.\\d+$/', $row[$i])) { $row[$i] = sprintf('%.2f', $row[$i]); } $record[] .= $row[$i]; } } $ret[] = $record; } return $ret; }
function get_view() { global $FANNIE_OP_DB, $FANNIE_TRANS_DB; /** Excludes: Values with spaces (fixed in lanecode going forward) One and two digit PLUs (likely simply miskeys) Values with no leading zeroes (EAN-13 and UPC-A should have at least one. I do have some values with no leading zeroes but not sure yet what they are. Do not appear to be GTIN-14). */ $dbc = FannieDB::get($FANNIE_OP_DB); $query = "SELECT t.upc, COUNT(t.upc) AS instances,\n MIN(datetime) as oldest,\n MAX(datetime) as newest,\n p.description as prod,\n MAX(v.description) as vend, MAX(n.vendorName) as vendorName, MAX(v.srp) as srp\n FROM " . $FANNIE_TRANS_DB . $dbc->sep() . "transarchive AS t\n " . DTrans::joinProducts('t') . "\n LEFT JOIN vendorItems AS v ON t.upc=v.upc\n LEFT JOIN vendors AS n ON v.vendorID=n.vendorID\n WHERE t.trans_type='L' AND t.description='BADSCAN'\n AND t.upc NOT LIKE '% %'\n AND t.upc NOT LIKE '00000000000%'\n AND (t.upc NOT LIKE '00000000%' OR p.upc IS NOT NULL OR v.upc IS NOT NULL)"; if ($this->date_restrict) { $query .= ' AND datetime >= ' . date('\'Y-m-d 00:00:00\'', strtotime('-8 days')); } $query .= "GROUP BY t.upc, p.description\n ORDER BY t.upc DESC"; if ($this->date_restrict == 2) { $query = str_replace('transarchive', 'dtransactions', $query); } $result = $dbc->query($query); $data = array(); while ($row = $dbc->fetch_row($result)) { $data[] = $row; } // stick a total in the cache along with SQL results $dbc = FannieDB::get($FANNIE_TRANS_DB); $query = "SELECT COUNT(*) FROM transarchive WHERE trans_type='I' AND upc <> '0'"; $result = $dbc->query($query); $row = $dbc->fetch_row($result); $data['itemTTL'] = $row[0]; $ret = ''; $ret .= '<div class="nav">'; $ret .= '<a href="BadScanTool.php?lastquarter=1" class="btn btn-default navbar-btn' . (!$this->date_restrict ? ' active' : '') . '">View Last Quarter</a>'; $ret .= ' '; $ret .= '<a href="BadScanTool.php" class="btn btn-default navbar-btn' . ($this->date_restrict == 1 ? ' active' : '') . '">View Last Week</a>'; $ret .= ' '; $ret .= '<a href="BadScanTool.php?today=1" class="btn btn-default navbar-btn' . ($this->date_restrict == 2 ? ' active' : '') . '">View Today</a>'; $ret .= '</div>'; $ret .= '<br /><b>Show</b>: '; $ret .= '<input type="radio" name="rdo" id="rdoa" onclick="showAll();" /> <label for="rdoa">All</label>'; $ret .= ' '; $ret .= '<input type="radio" name="rdo" id="rdom" onclick="showMultiple();" /> <label for="rdom">Repeats</label>'; $ret .= ' '; $ret .= '<input type="radio" name="rdo" id="rdof" onclick="showFixable();" checked /> <label for="rdof">Fixable</label>'; $ret .= '<br />'; $ret .= '<div class="well">'; $ret .= '<span class="alert-success">Green items have been entered in POS</span>. '; $ret .= '<span class="alert-danger">Red items can be added from vendor catalogs</span>. '; $ret .= '<span class="alert-info">Blue items can also be added from vendor catalogs but may not be needed. All scans are within a 5 minute window. May indicate a special order case scanned by mistake or a bulk purchase in a barcoded container.</span> '; $ret .= 'Other items are not identifiable with available information'; $ret .= '</div>'; $ret .= '<table id="scantable" class="table"><thead>'; $ret .= '<tr id="tableheader"><th>UPC</th><th># Scans</th><th>Oldest</th><th>Newest</th> <th>In POS</th><th>In Vendor Catalog</th><th>SRP</th></tr>'; $ret .= '</thead><tbody>'; $scanCount = 0; foreach ($data as $row) { if (count($row) == 1) { // cached item total continue; } $css = ''; $fixButton = ''; $span = strtotime($row['newest']) - strtotime($row['oldest']); if (!empty($row['prod'])) { $css = 'class="fixed alert alert-success collapse"'; } else { if (!empty($row['vend']) && !empty($row['srp'])) { if ($span > 300) { $css = 'class="fixable alert alert-danger"'; } else { $css = 'class="semiFixable alert alert-info"'; } $fixButton = ' <a href="ItemEditorPage.php?searchupc= ' . $row['upc'] . '" target="_new' . $row['upc'] . '">ADD</a>'; } else { if ($row['instances'] == 1) { $css = 'class="loner collapse"'; } else { $css = 'class="collapse"'; } } } $ret .= sprintf('<tr %s><td>%s</td><td>%d</td><td>%s</td><td>%s</td> <td>%s</td><td>%s</td><td>%s</td> <td><a href="OpenRingReceipts.php?upc=%s&date1=%s&date2=%s">View Receipts</a></td> </tr>', $css, $row['upc'], $row['instances'], $row['oldest'], $row['newest'], !empty($row['prod']) ? "Yes ({$row['prod']})" : 'No', !empty($row['vend']) ? "Yes ({$row['vendorName']} {$row['vend']})" : 'No', !empty($row['srp']) ? $row['srp'] . $fixButton : 'n/a', $row['upc'], $row['oldest'], $row['newest']); $scanCount += $row['instances']; } $ret .= '</tbody></table>'; $ret .= '<div id="ratio">'; $ret .= sprintf('Approx. bad scan rate: %.2f%%', $data['itemTTL'] == 0 ? 0 : (double) $scanCount / ((double) $data['itemTTL'] != 0) * 100); $ret .= '</div>'; $this->addScript('../src/javascript/tablesorter/jquery.tablesorter.min.js'); $this->addOnloadCommand("\$('#scantable').tablesorter();\n"); return $ret; }
public function run() { global $argv, $FANNIE_OP_DB, $FANNIE_PLUGIN_SETTINGS; $dbc = FannieDB::get($FANNIE_OP_DB); $iso_week = date('W'); $iso_week--; $year = date('Y'); if ($iso_week <= 0) { $iso_week = 52; $year--; } $upload = true; /** Handle additional args */ if (isset($argv) && is_array($argv)) { foreach ($argv as $arg) { if (is_numeric($arg)) { $iso_week = $arg; } else { if ($arg == '--file') { $upload = false; } } } } /** Keep SPINS week number separate for logging purposes */ $spins_week = $iso_week; if (isset($FANNIE_PLUGIN_SETTINGS['SpinsOffset'])) { $iso_week += $FANNIE_PLUGIN_SETTINGS['SpinsOffset']; } // First day of ISO week is a Monday $start = strtotime($year . 'W' . str_pad($iso_week, 2, '0', STR_PAD_LEFT)); // if the SpinsOffset results in non-existant week 0, // use ISO week 1 and go back seven days if ($iso_week == 0) { $start = strtotime($year . 'W01'); $start = mktime(0, 0, 0, date('n', $start), date('j', $start) - 7, date('Y', $start)); } // walk forward to Sunday $end = $start; while (date('w', $end) != 0) { $end = mktime(0, 0, 0, date('n', $end), date('j', $end) + 1, date('Y', $end)); } $dlog = DTransactionsModel::selectDlog(date('Y-m-d', $start), date('Y-m-d', $end)); $lastDay = date("M d, Y", $end) . ' 11:59PM'; $this->cronMsg('SPINS data for week #' . $spins_week . '(' . date('Y-m-d', $start) . ' to ' . date('Y-m-d', $end) . ')', FannieLogger::INFO); // Odd "CASE" statement is to deal with special order // line items the have case size & number of cases $dataQ = "SELECT d.upc, p.description,\n SUM(CASE WHEN d.quantity <> d.ItemQtty AND d.ItemQtty <> 0 THEN d.quantity*d.ItemQtty ELSE d.quantity END) as quantity,\n SUM(d.total) AS dollars,\n '{$lastDay}' AS lastDay\n FROM {$dlog} AS d\n " . DTrans::joinProducts('d', 'p', 'INNER') . "\n WHERE p.Scale = 0\n AND d.upc > '0000000999999' \n AND tdate BETWEEN ? AND ?\n GROUP BY d.upc, p.description"; $filename = date('mdY', $end) . '.csv'; $outfile = sys_get_temp_dir() . "/" . $filename; $fp = fopen($outfile, "w"); $dataP = $dbc->prepare($dataQ); $args = array(date('Y-m-d 00:00:00', $start), date('Y-m-d 23:59:59', $end)); $dataR = $dbc->execute($dataP, $args); while ($row = $dbc->fetch_row($dataR)) { for ($i = 0; $i < 4; $i++) { if ($i == 2 || $i == 3) { $row[$i] = sprintf('%.2f', $row[$i]); } fwrite($fp, "\"" . $row[$i] . "\","); } fwrite($fp, "\"" . $row[4] . "\"\n"); } fclose($fp); if ($upload) { $conn_id = ftp_connect('ftp.spins.com'); $login_id = ftp_login($conn_id, $FANNIE_PLUGIN_SETTINGS['SpinsFtpUser'], $FANNIE_PLUGIN_SETTINGS['SpinsFtpPw']); if (!$conn_id || !$login_id) { $this->cronMsg('FTP Connection failed', FannieLogger::ERROR); } else { ftp_chdir($conn_id, "data"); ftp_pasv($conn_id, true); $uploaded = ftp_put($conn_id, $filename, $outfile, FTP_ASCII); if (!$uploaded) { $this->cronMsg('FTP upload failed', FannieLogger::ERROR); } else { $this->cronMsg('FTP upload successful', FannieLogger::INFO); } ftp_close($conn_id); } unlink($outfile); } else { rename($outfile, './' . $filename); $this->cronMsg('Generated file: ' . $filename, FannieLogger::INFO); } }
protected function get_id_view() { $query = ' SELECT l.upc, CASE WHEN u.brand IS NOT NULL AND u.brand <> \'\' THEN u.brand ELSE p.brand END as brand, CASE WHEN u.description IS NOT NULL AND u.description <> \'\' THEN u.description ELSE p.description END as description, p.normal_price, l.salePrice, l.signMultiplier FROM batchList AS l ' . DTrans::joinProducts('l', 'p', 'INNER') . ' LEFT JOIN productUser AS u ON l.upc=u.upc WHERE l.batchID=? '; $args = array($this->id); if ($this->config->get('STORE_MODE') === 'HQ') { $query .= ' AND p.store_id=? '; $args[] = $this->config->get('STORE_ID'); } $query .= ' ORDER BY l.upc'; $prep = $this->connection->prepare($query); $res = $this->connection->execute($prep, $args); $rows = array(); while ($row = $this->connection->fetchRow($res)) { $rows[] = $row; } $query = ' SELECT l.upc, \'\' AS brand, c.likeCodeDesc AS description, 0 AS normal_price, l.salePrice, l.signMultiplier FROM batchList AS l LEFT JOIN likeCodes AS c ON l.upc=' . $this->connection->concat("'LC'", 'c.likeCode', '') . ' WHERE l.batchID=? AND l.upc LIKE \'LC%\''; $args = array($this->id); $prep = $this->connection->prepare($query); $res = $this->connection->execute($prep, $args); while ($row = $this->connection->fetchRow($res)) { $rows[] = $row; } $args = array($this->id); while ($row = $this->connection->fetchRow($res)) { $rows[] = $row; } $ret = '<form method="post"> <table class="table table-bordered"><thead><tr> <th>UPC</th><th>Brand</th><th>Description</th> <th>Normal Price</th><th>Sale Price</th><th>Sign</th> </tr></thead><tbody>'; $styles = $this->getStyles(); foreach ($rows as $row) { $ret .= sprintf('<tr> <td>%s</td> <td>%s</td> <td>%s</td> <td>%.2f</td> <td>%.2f</td> <td><select class="form-control" name="style[]"> %s </select> <input type="hidden" name="upc[]" value="%s" /> </td> </tr>', \COREPOS\Fannie\API\lib\FannieUI::itemEditorLink($row['upc']), $row['brand'], $row['description'], $row['normal_price'], $row['salePrice'], $this->styleToOptions($styles, $row['signMultiplier']), $row['upc']); } $ret .= '</tbody></table> <p> <button type="submit" class="btn btn-default btn-core">Save</button> <a href="EditBatchPage.php?id=' . $this->id . '" class="btn btn-default btn-reset">Back to Batch</a> </p> <input type="hidden" name="id" value="' . $this->id . '" /> </form>'; return $ret; }
CORE-POS 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 *********************************************************************************/ include dirname(__FILE__) . '/../../config.php'; if (!class_exists('FannieAPI')) { include_once $FANNIE_ROOT . 'classlib2.0/FannieAPI.php'; } if (basename($_SERVER['PHP_SELF']) != basename(__FILE__)) { return; } $dbc = FannieDB::get($FANNIE_OP_DB); switch (FormLib::get_form_value('action')) { case 'fetch': $prep = $dbc->prepare_statement("SELECT u.upc,p.description FROM\n upcLike AS u \n " . DTrans::joinProducts('u', 'p', 'INNER') . "\n WHERE u.likeCode=?\n ORDER BY p.description"); $res = $dbc->exec_statement($prep, array(FormLib::get_form_value('lc', 0))); $ret = ""; while ($row = $dbc->fetch_row($res)) { $ret .= "<a style=\"font-size:90%;\" href={$FANNIE_URL}item/itemMaint.php?upc={$row['0']}>"; $ret .= $row[0] . "</a> " . substr($row[1], 0, 25) . "<br />"; } echo $ret; break; }
public function fetch_report_data() { $dbc = $this->connection; $dbc->selectDB($this->config->get('OP_DB')); $date1 = $this->form->date1; $date2 = $this->form->date2; $deptStart = FormLib::get('deptStart', 1); $deptEnd = FormLib::get('deptEnd', 1); $deptMulti = FormLib::get('departments', array()); $buyer = FormLib::get('buyer', ''); $dlog = DTransactionsModel::selectDlog($date1, $date2); /** Report Query notes: * Combining vendorName and prodExtra.distributor is a nod to legacy data. Eventually data should be fully normalized on products.default_vendor_id * Excluding prodExtra.distributor empty string combines those records with SQL NULL. Having two different "blank" rows is confusing for users. * Joins are only needed is a super department condition is involved. WHERE clause changes similarly. */ $query = ' SELECT COALESCE(v.vendorName, x.distributor) AS vendor, ' . DTrans::sumQuantity('t') . ' AS qty, SUM(t.total) AS ttl FROM ' . $dlog . ' AS t ' . DTrans::joinProducts('t', 'p', 'LEFT') . ' LEFT JOIN vendors AS v ON p.default_vendor_id=v.vendorID LEFT JOIN prodExtra AS x ON p.upc=x.upc '; if ($buyer !== '' && $buyer > -1) { $query .= ' LEFT JOIN superdepts AS s ON t.department=s.dept_ID '; } elseif ($buyer !== '' && $buyer == -2) { $query .= ' LEFT JOIN MasterSuperDepts AS s ON t.department=s.dept_ID '; } $query .= ' WHERE t.tdate BETWEEN ? AND ? AND t.trans_type IN (\'I\',\'D\') '; $args = array($date1 . ' 00:00:00', $date2 . ' 23:59:59'); if ($buyer !== '') { if ($buyer == -2) { $query .= ' AND s.superID != 0 '; } elseif ($buyer != -1) { $query .= ' AND s.superID=? '; $args[] = $buyer; } } if ($buyer != -1) { list($conditional, $args) = DTrans::departmentClause($deptStart, $deptEnd, $deptMulti, $args, 't'); $query .= $conditional; } $query .= ' GROUP BY COALESCE(v.vendorName, x.distributor) ORDER BY SUM(total) DESC'; $prep = $dbc->prepare($query); $result = $dbc->execute($prep, $args); $data = array(); $total_sales = 0.0; while ($w = $dbc->fetch_row($result)) { $data[] = array($w['vendor'], sprintf('%.2f', $w['qty']), sprintf('%.2f', $w['ttl']), 0.0); $total_sales += $w['ttl']; } for ($i = 0; $i < count($data); $i++) { $data[$i][3] = sprintf('%.2f%%', $data[$i][2] / $total_sales * 100); $data[$i][2] = '$' . $data[$i][2]; } return $data; }
public function process_file($linedata) { $dbc = $this->connection; $dbc->selectDB($this->config->get('OP_DB')); $UPC = $this->get_column_index('upc'); $SKU = $this->get_column_index('sku'); $PRICE = $this->get_column_index('price'); $rm_checks = FormLib::get('rm_cds') != '' ? true : false; $ruleType = FormLib::get('ruleType'); $review = FormLib::get('reviewDate'); $upcP = $dbc->prepare('SELECT upc, price_rule_id FROM products WHERE upc=? AND inUse=1'); $ruleP = $dbc->prepare('SELECT * FROM PriceRules WHERE priceRuleID=?'); $skuP = $dbc->prepare(' SELECT s.upc, p.price_rule_id FROM vendorSKUtoPLU AS s INNER JOIN vendors AS v ON s.vendorID=v.vendorID ' . DTrans::joinProducts('s', 'p', 'INNER') . ' WHERE s.sku=? AND v.vendorName LIKE \'%UNFI%\''); $insP = $dbc->prepare(' INSERT INTO PriceRules (priceRuleTypeID, maxPrice, reviewDate, details) VALUES (?, ?, ?, ?) '); $upP = $dbc->prepare(' UPDATE PriceRules SET priceRuleTypeID=?, maxPrice=?, reviewDate=?, details=? WHERE priceRuleID=?'); $extraP = $dbc->prepare('UPDATE prodExtra SET variable_pricing=1 WHERE upc=?'); $prodP = $dbc->prepare('UPDATE products SET price_rule_id=? WHERE upc=?'); foreach ($linedata as $data) { if (!is_array($data)) { continue; } $upc = str_replace("-", "", $data[$UPC]); $upc = str_replace(" ", "", $upc); if ($rm_checks) { $upc = substr($upc, 0, strlen($upc) - 1); } $upc = BarcodeLib::padUPC($upc); $rule_id = 0; // try to find item by SKU if not in products $lookup = $dbc->execute($upcP, array($upc)); if ($dbc->numRows($lookup) == 0 && $SKU !== false) { $sku = str_replace('-', '', $data[$SKU]); $found = false; $look2 = $dbc->exec_statement($skuP, array($sku)); if ($dbc->numRows($look2)) { $w = $dbc->fetchRow($look2); $upc = $w['upc']; $rule_id = $w['price_rule_id']; $found = true; } $sku = str_pad($sku, 7, '0', STR_PAD_LEFT); $look3 = $dbc->execute($skuP, array($sku)); if ($dbc->numRows($look3)) { $w = $dbc->fetchRow($look3); $upc = $w['upc']; $rule_id = $w['price_rule_id']; $found = true; } if (!$found) { continue; } } else { $w = $dbc->fetchRow($lookup); $rule_id = $w['price_rule_id']; } $price = trim($data[$PRICE], "\$ "); if (strstr($price, '-')) { // pull the max from a range of prices list($garbage, $price) = explode('-', $price, 2); $price = trim($price); } if (!is_numeric($price)) { continue; } $ruleR = $dbc->execute($ruleP, array($rule_id)); if ($rule_id > 1 && $dbc->numRows($ruleR)) { // update existing rule with latest price $args = array($ruleType, $price, $review, 'NCG MAX ' . $price, $rule_id); $dbc->execute($upP, $args); $dbc->execute($extraP, array($upc)); } else { // create a new pricing rule // attach it to the item $args = array($ruleType, $price, $review, 'NCG MAX ' . $price); $dbc->execute($insP, $args); $rule_id = $dbc->insertID(); $dbc->execute($extraP, array($upc)); $dbc->execute($prodP, array($rule_id, $upc)); } } return true; }
private function upcQuery($dlog, $items) { $args = array_filter($items, function ($i) { return trim($i) !== '' ? true : false; }); $args = array_map(function ($i) { $item = trim($i); // trim check digit based on dashes or spacing if (strlen($item) > 2 && ($item[strlen($item) - 2] == ' ' || $item[strlen($item) - 2] == '-') && is_numeric($item[strlen($item) - 1])) { $item = substr($item, 0, strlen($item) - 2); } $item = str_replace(' ', '', $item); $item = str_replace('-', '', $item); return BarcodeLib::padUPC($item); }, $items); $inStr = str_repeat('?,', count($args)); $inStr = substr($inStr, 0, strlen($inStr) - 1); $query = ' SELECT t.upc, p.brand, p.description, ' . DTrans::sumQuantity('t') . ' as qty, CASE WHEN p.scale=1 THEN \'LB\' ELSE p.size END AS size, p.cost, p.normal_price FROM ' . $dlog . ' AS t ' . DTrans::joinProducts() . ' WHERE t.upc IN (' . $inStr . ') AND t.tdate BETWEEN ? AND ? GROUP BY t.upc, p.brand, p.description'; return array($query, $args); }
private function getList() { $settings = $this->config->get('PLUGIN_SETTINGS'); $prep = $this->connection->prepare(' SELECT s.upc, p.brand, p.description, p.size, s.quantity as qty, v.sku, n.vendorName FROM ' . $settings['ShelfAuditDB'] . $this->connection->sep() . 'SaList AS s ' . DTrans::joinProducts('s') . ' 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 WHERE s.clear=0 AND s.quantity <> 0 ORDER BY s.tdate DESC '); $res = $this->connection->execute($prep); $ret = ' <div class="table-responsive"> <table class="table table-bordered table-striped small"> <tr> <th>UPC</th> <th>SKU</th> <th>Vendor</th> <th>Brand</th> <th>Description</th> <th>Size</th> <th>Qty</th> </tr>'; while ($row = $this->connection->fetchRow($res)) { $ret .= sprintf('<tr> <td>%s</td> <td>%s</td> <td>%s</td> <td>%s</td> <td>%s</td> <td>%s</td> <td>%d</td> </tr>', $row['upc'], $row['sku'], $row['vendorName'], $row['brand'], $row['description'], $row['size'], $row['qty']); } $ret .= '</table></div>'; $ret .= '<p> <a href="?clear=1" class="btn btn-default btn-danger" onclick="return window.confirm(\'Clear list?\');"> Clear List </a> </p>'; return $ret; }
public static function batchNotification($batchID, $upc, $type, $is_likecode = false) { $conf = \FannieConfig::factory(); $dbc = \FannieDB::getReadOnly($conf->get('OP_DB')); $likecode = ''; $desc = ''; $dept = 0; if ($is_likecode) { if (substr($upc, 0, 2) == 'LC') { $likecode = substr($upc, 2); } else { $likecode = $upc; } // upc is a like code. find the description // and a valid upc (hence inner join) $infoQ = 'SELECT p.department, l.likeCodeDesc FROM upcLike AS u ' . DTrans::joinProducts('u', 'p', 'INNER') . ' LEFT JOIN likeCodes AS l ON u.likeCode=l.likeCode WHERE u.likeCode=?'; $infoP = $dbc->prepare($infoQ); $infoR = $dbc->execute($infoP, array($likecode)); if ($dbc->num_rows($infoR) == 0) { // invalid like code return false; } $infoW = $dbc->fetch_row($infoR); $desc = $infoW['likeCodeDesc']; $dept = $infoW['department']; } else { $product = new \ProductsModel($dbc); $product->upc($upc); $product->load(); $desc = $product->description(); $dept = $product->department(); } $to_addr = self::getAddresses($dept); if ($to_addr === false) { // no one set to receive notices return false; } $batch = new \BatchesModel($dbc); $batch->batchID($batchID); $batch->load(); $batchList = new \BatchListModel($dbc); $batchList->upc($upc); $batchList->batchID($batchID); $batchList->load(); $subject = "Batch Update notification: " . $batch->batchName(); $message = "Batch " . $batch->batchName() . " has been changed\n"; if ($is_likecode) { $message .= 'Like code ' . $likecode . '(' . $desc . ') '; } else { $message .= 'Item ' . $upc . '(' . $desc . ') '; } switch ($type) { case self::BATCH_ADD: $message .= "has been added to the batch\n"; $message .= 'Sale Price: $' . $batchList->salePrice() . "\n"; break; case self::BATCH_EDIT: $message .= "has been re-priced\n"; $message .= 'Sale Price: $' . $batchList->salePrice() . "\n"; break; case self::BATCH_DELETE: $message .= "has been deleted from the batch\n"; break; default: $message .= "may have experienced unknown changes\n"; return false; // remove after testing; don't send lots of these in error break; } $message .= "\n"; $message .= "View this batch:\n"; $url = $conf->get('URL'); $server_name = filter_input(INPUT_SERVER, 'SERVER_NAME'); $message .= "http://{$server_name}{$url}batches/newbatch/EditBatchPage.php?id={$batchID}\n"; $message .= "\n"; $message .= "View this item:\n"; $message .= "http://{$server_name}/{$url}item/ItemEditorPage.php?searchupc={$upc}\n"; $message .= "\n"; $username = \FannieAuth::checkLogin(); if (!$username) { $username = '******'; } $message .= "This change was made by user {$username}\n"; $from = "From: automail\r\n"; mail($to_addr, $subject, $message, $from); return true; }
protected function unrollLikeCode($dbc, $code, $item) { $likeP = $dbc->prepare(' SELECT u.upc, ' . \COREPOS\Fannie\API\item\ItemText::longBrandSQL('s', 'p') . ', CASE WHEN s.description IS NULL OR s.description=\'\' THEN l.likeCodeDesc ELSE s.description END AS likeCodeDesc, p.normal_price, p.scale, p.numflag, p.size, COALESCE(s.signCount, 1) AS signCount FROM upcLike AS u INNER JOIN likeCodes AS l ON u.likeCode=l.likeCode ' . \DTrans::joinProducts('u', 'p', 'INNER') . ' LEFT JOIN productUser AS s ON u.upc=s.upc WHERE u.likeCode=? ORDER BY u.upc '); $info = $dbc->getRow($likeP, array($code)); $item['description'] = $info['likeCodeDesc']; $item['brand'] = $info['brand']; $item['posDescription'] = $info['likeCodeDesc']; $item['nonSalePrice'] = $info['normal_price']; $item['scale'] = $info['scale']; $item['numflag'] = $info['numflag']; $item['upc'] = $info['upc']; $item['size'] = $info['size']; $item['signCount'] = $info['signCount']; return $item; }
/** 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 run() { global $FANNIE_OP_DB, $FANNIE_TRANS_DB, $FANNIE_ARCHIVE_DB; $sql = FannieDB::get($FANNIE_OP_DB); $chk = $sql->query("TRUNCATE TABLE batchMergeTable"); if ($chk === false) { $this->cronMsg("Could not truncate batchMergeTable", FannieLogger::WARNING); } $chk = $sql->query("INSERT INTO batchMergeTable\n SELECT b.startDate,b.endDate,p.upc,p.description,b.batchID\n FROM batches AS b LEFT JOIN batchList AS l\n ON b.batchID=l.batchID \n " . DTrans::joinProducts('l', 'p', 'INNER')); if ($chk === false) { $this->cronMsg("Could not load batch reporting data for UPCs", FannieLogger::WARNING); } $chk = $sql->query("INSERT INTO batchMergeTable \n SELECT b.startDate, b.endDate, p.upc, p.description, b.batchID\n FROM batchList AS l LEFT JOIN batches AS b\n ON b.batchID=l.batchID INNER JOIN upcLike AS u\n ON l.upc = " . $sql->concat("'LC'", $sql->convert('u.likeCode', 'CHAR'), '') . "\n " . DTrans::joinProducts('u', 'p', 'INNER') . "\n WHERE p.upc IS NOT NULL"); if ($chk === false) { $this->cronMsg("Could not load batch reporting data for likecodes", FannieLogger::WARNING); } $sql = FannieDB::get($FANNIE_TRANS_DB); $cashierPerformanceSQL = "\n SELECT\n min(tdate) as proc_date,\n max(emp_no) as emp_no,\n max(trans_num) as Trans_Num,\n min(tdate) as startTime,\n max(tdate) as endTime,\n CASE WHEN " . $sql->seconddiff('min(tdate)', 'max(tdate)') . " =0 \n then 1 else \n " . $sql->seconddiff('min(tdate)', 'max(tdate)') . "\n END as transInterval,\n sum(CASE WHEN abs(quantity) > 30 THEN 1 else abs(quantity) END) as items,\n Count(upc) as rings,\n SUM(case when trans_status = 'V' then 1 ELSE 0 END) AS Cancels,\n max(card_no) as card_no\n from dlog_90_view \n where trans_type IN ('I','D','0','C')\n group by year(tdate),month(tdate),day(tdate),trans_num"; if (!$sql->isView('CashPerformDay')) { $chk = $sql->query("TRUNCATE TABLE CashPerformDay"); if ($chk === false) { $this->cronMsg("Could not truncate CashPerformDay", FannieLogger::WARNING); } $chk = $sql->query("INSERT INTO CashPerformDay " . $cashierPerformanceSQL); if ($chk === false) { $this->cronMsg("Could not load data for CashPerformDay", FannieLogger::WARNING); } } if ($sql->tableExists('CashPerformDay_cache')) { $chk = $sql->query("TRUNCATE TABLE CashPerformDay_cache"); if ($chk === false) { $this->cronMsg("Could not truncate CashPerformDay_cache", FannieLogger::WARNING); } $chk = $sql->query("INSERT INTO CashPerformDay_cache " . $cashierPerformanceSQL); if ($chk === false) { $this->cronMsg("Could not load data for CashPerformDay_cache", FannieLogger::WARNING); } } $sql = FannieDB::get($FANNIE_ARCHIVE_DB); if ($sql->table_exists("reportDataCache")) { $sql->query("DELETE FROM reportDataCache WHERE expires < " . $sql->now()); } $daily = \COREPOS\Fannie\API\data\DataCache::fileCacheDir('daily'); if ($daily) { $dh = opendir($daily); while (($file = readdir($dh)) !== false) { if (is_file($daily . '/' . $file)) { unlink($daily . '/' . $file); } } closedir($dh); } $monthly = \COREPOS\Fannie\API\data\DataCache::fileCacheDir('monthly'); if ($monthly && date('j') == 1) { $dh = opendir($monthly); while (($file = readdir($dh)) !== false) { if (is_file($monthly . '/' . $file)) { unlink($monthly . '/' . $file); } } closedir($dh); } }
/** 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; } }
public function fetch_report_data() { /* provide a department range and date range to get history for all products in those departments for that time period AND current price provide just a upc to get history for that upc */ $dept1 = FormLib::get('dept1'); $dept2 = FormLib::get('dept2'); $upc = FormLib::get('upc'); if ($upc !== '') { $upc = BarcodeLib::padUPC($upc); } $start_date = FormLib::get('date1', date('Y-m-d')); $end_date = FormLib::get('date2', date('Y-m-d')); $manu = FormLib::get('manufacturer'); $mtype = FormLib::get('mtype', 'upc'); $q = ""; $args = array(); $sql = $this->connection; $sql->selectDB($this->config->get('OP_DB')); $type = FormLib::get('type'); if ($type === '') { // not set $q = "\n SELECT h.upc,\n p.description,\n price,\n h.modified,\n p.normal_price \n FROM prodPriceHistory AS h \n " . DTrans::joinProducts('h') . "\n WHERE h.upc = ?\n ORDER BY h.upc,\n h.modified DESC"; $args = array($upc); } else { if ($type == 'upc') { $q = "\n SELECT h.upc,\n p.description,\n price,\n h.modified,\n p.normal_price \n FROM prodPriceHistory AS h \n " . DTrans::joinProducts('h') . "\n WHERE h.upc = ?\n AND h.modified BETWEEN ? AND ?\n ORDER BY h.upc,\n h.modified DESC"; $args = array($upc, $start_date . ' 00:00:00', $end_date . ' 23:59:59'); } else { if ($type == 'department') { $q = "\n SELECT h.upc,\n p.description,\n price,\n h.modified,\n p.normal_price \n FROM prodPriceHistory AS h \n " . DTrans::joinProducts('h') . "\n WHERE department BETWEEN ? AND ?\n AND h.modified BETWEEN ? AND ?\n ORDER BY h.upc,\n h.modified DESC"; $args = array($dept1, $dept2, $start_date . ' 00:00:00', $end_date . ' 23:59:59'); $upc = ''; // if UPC and dept submitted, unset UPC } else { if ($mtype == 'upc') { $q = "\n SELECT h.upc,\n p.description,\n price,\n h.modified,\n p.normal_price \n FROM prodPriceHistory AS h \n " . DTrans::joinProducts('h') . "\n WHERE h.upc LIKE ?\n AND h.modified BETWEEN ? AND ?\n ORDER BY h.upc,\n h.modified DESC"; $args = array('%' . $manu . '%', $start_date . ' 00:00:00', $end_date . ' 23:59:59'); } else { $q = "\n SELECT h.upc,\n p.description,\n price,\n h.modified,\n p.normal_price \n FROM prodPriceHistory AS h \n " . DTrans::joinProducts('h') . "\n WHERE x.brand LIKE ?\n AND h.modified BETWEEN ? AND ?\n ORDER BY h.upc,\n h.modified DESC"; $args = array($manu, $start_date . ' 00:00:00', $end_date . ' 23:59:59'); } $upc = ''; // if UPC and manu submitted, unset UPC } } } $def = $sql->tableDefinition('prodPriceHistory'); if (isset($def['storeID']) && $this->config->get('STORE_ID')) { $q = str_replace('h.upc=p.upc', 'h.upc=p.upc AND h.storeID=p.store_id', $q); } $p = $sql->prepare_statement($q); $r = $sql->exec_statement($p, $args); if ($upc !== '') { $this->report_headers[] = 'Current Price'; } $data = array(); while ($row = $sql->fetch_array($r)) { $record = array($row['upc'], $row['description'], sprintf('%.2f', $row['price']), $row['modified']); if ($upc !== '') { $record[] = $row['normal_price']; } $data[] = $record; } return $data; }
public function get_id_view() { $dbc = FannieDB::get($this->config->get('OP_DB')); $prep = $dbc->prepare("\n SELECT m.sku,\n m.upc,\n v.description AS vendorDescript,\n p.description as storeDescript\n FROM VendorBreakdowns AS m\n " . DTrans::joinProducts('m') . "\n LEFT JOIN vendorItems AS v ON v.sku=m.sku AND v.vendorID=m.vendorID\n WHERE m.vendorID = ?\n ORDER BY m.upc\n "); $ret = ''; $ret .= '<form action="' . $_SERVER['PHP_SELF'] . '" method="get">'; $ret .= '<div class="form-group form-inline"> <label>SKU</label> <input type="text" class="form-control" name="sku" placeholder="Vendor SKU" /> <label>PLU/UPC</label> <input type="text" class="form-control" name="plu" placeholder="Our PLU" /> <button type="submit" class="btn btn-default">Add Entry</button> <input type="hidden" name="id" value="' . $this->id . '" /> <a href="?id=' . $this->id . '&break=1" class="btn btn-default">Run Breakdowns</a> <a href="VendorIndexPage.php?vid=' . $this->id . '" class="btn btn-default">Home</a> </div> </form>'; $ret .= '<table class="table table-bordered">'; $ret .= '<thead><tr> <th>Vendor SKU</th> <th>Our PLU</th> <th>Vendor Description</th> <th>Our Description</th> <th> </th> </tr></thead><tbody>'; $res = $dbc->execute($prep, array($this->id)); while ($row = $dbc->fetchRow($res)) { if (empty($row['vendorDescript'])) { $row['vendorDescript'] = '<span class="alert-danger">Discontinued by vendor?</span>'; } if (empty($row['storeDescript'])) { $row['storeDescript'] = '<span class="alert-danger">Discontinued by us?</span>'; } $ret .= sprintf(' <tr> <td>%s</td> <td><a href="../ItemEditorPage.php?searchupc=%s">%s</a></td> <td>%s</td> <td>%s</td> <td> <a href="?_method=delete&id=%d&sku=%s&plu=%s" onclick="return confirm(\'Delete entry for PLU #%s?\');">%s</a> </td> </tr>', $row['sku'], $row['upc'], $row['upc'], $row['vendorDescript'], $row['storeDescript'], $this->id, $row['sku'], $row['upc'], $row['upc'], FannieUI::deleteIcon()); } $ret .= '</tbody></table>'; $this->addScript('../../src/javascript/tablesorter/jquery.tablesorter.js'); $this->addOnloadCommand("\$('.table').tablesorter([[1,0]]);\n"); return $ret; }
function SaveFormData($upc) { $upc = BarcodeLib::padUPC($upc); $brand = FormLib::get('lf_brand'); $desc = FormLib::get('lf_desc'); $origin = FormLib::get('origin', 0); $floorID = FormLib::get('floor-id', 0); $text = FormLib::get('lf_text'); $text = str_replace("\r", '', $text); $text = str_replace("\n", '<br />', $text); // strip non-ASCII (word copy/paste artifacts) $text = preg_replace("/[^-]/", "", $text); $signs = FormLib::get('sign-count', 1); if ($signs < 1) { $signs = 1; } $dbc = $this->db(); $loc = new ProdPhysicalLocationModel($dbc); $loc->upc($upc); $loc->floorSectionID($floorID); $loc->save(); $model = new ProductUserModel($dbc); $model->upc($upc); $model->brand($brand); $model->description($desc); $model->long_text($text); $model->signCount($signs); $multiOrigin = FormLib::get('otherOrigin', array()); $originMap = array(); if ($origin != 0) { $originMap[] = $origin; } foreach ($multiOrigin as $originID) { if ($originID != 0) { $originMap[] = $originID; } } $mapP = $dbc->prepare('DELETE FROM ProductOriginsMap WHERE upc=?'); $addP = $dbc->prepare('INSERT INTO ProductOriginsMap (originID, upc, active) VALUES (?, ?, 1)'); $lcP = $dbc->prepare('SELECT u.upc FROM upcLike AS u ' . DTrans::joinProducts('u', 'p', 'INNER') . ' WHERE u.likeCode IN ( SELECT l.likeCode FROM upcLike AS l WHERE l.upc = ? )'); $lcR = $dbc->execute($lcP, array($upc)); $items = array($upc); while ($w = $dbc->fetch_row($lcR)) { if ($w['upc'] == $upc) { continue; } $items[] = $w['upc']; } $prod = new ProductsModel($dbc); $stores = new StoresModel($dbc); foreach ($items as $item) { $prod->upc($item); $prod->store_id(1); $prod->current_origin_id($origin); $prod->save(); $dbc->execute($mapP, array($item)); foreach ($originMap as $originID) { $dbc->execute($addP, array($originID, $item)); } } return $model->save(); }