function item_find($db, $q, $options)
{
list($sql_criteria, $begin) = item_terms_to_sql($db, $q, $options);
$extra = "";
if (!$begin) {
$begin = date("Y-m-d", time() - 7 * 24 * 3600);
}
if ($options & FIND_SALES) {
$extra = "(SELECT SUM(allocated) * -1\n FROM txn_line JOIN txn ON txn.id = txn_line.txn\n WHERE txn_line.item = item.id\n AND type = 'customer'\n AND filled >= '{$begin}') sold,";
}
$q = "SELECT\n item.id, item.code, item.name,\n brand.id brand_id, brand.name brand,\n retail_price retail_price,\n IF(item.discount_type,\n CASE item.discount_type\n WHEN 'percentage' THEN ROUND(retail_price * ((100 - item.discount) / 100), 2)\n WHEN 'relative' THEN (retail_price - item.discount) \n WHEN 'fixed' THEN (item.discount)\n END,\n NULL) sale_price,\n item.discount_type, item.discount,\n CASE item.discount_type\n WHEN 'percentage' THEN CONCAT(ROUND(item.discount), '% off')\n WHEN 'relative' THEN CONCAT('\$', item.discount, ' off')\n ELSE ''\n END discount_label,\n (SELECT SUM(allocated) FROM txn_line WHERE item = item.id) stock,\n (SELECT retail_price\n FROM txn_line JOIN txn ON (txn_line.txn = txn.id)\n WHERE txn_line.item = item.id AND txn.type = 'vendor'\n AND filled IS NOT NULL\n ORDER BY filled DESC\n LIMIT 1) last_net,\n minimum_quantity,\n GROUP_CONCAT(CONCAT(barcode.code, '!', barcode.quantity)\n SEPARATOR ',') barcodes,\n {$extra}\n active\n FROM item\n LEFT JOIN brand ON (item.brand = brand.id)\n LEFT JOIN barcode ON (item.id = barcode.item)\n WHERE {$sql_criteria}\n GROUP BY item.id\n ORDER BY 2";
$r = $db->query($q) or die($db->error);
$items = array();
while ($item = $r->fetch_assoc()) {
$item['active'] = (int) $item['active'];
$item['stock'] = (int) $item['stock'];
$item['minimum_quantity'] = (int) $item['minimum_quantity'];
$barcodes = explode(',', $item['barcodes']);
$item['barcode'] = $item['barcode_list'] = array();
foreach ($barcodes as $barcode) {
list($code, $quantity) = explode('!', $barcode);
$item['barcode'][$code] = $quantity;
$item['barcode_list'][] = array('code' => $code, 'quantity' => $quantity);
}
$item['fake_barcode'] = generate_upc(sprintf("4004%07d", $item['id']));
$items[] = $item;
}
return $items;
}
<?php
include '../scat.php';
include '../lib/item.php';
$begin = $_REQUEST['begin'];
$end = $_REQUEST['end'];
$items = "1=1";
if ($_REQUEST['items']) {
list($items, $x) = item_terms_to_sql($db, $_REQUEST['items'], FIND_OR | FIND_ALL);
}
if (!$begin) {
$days = $_REQUEST['days'];
if (!$days) {
$days = 10;
}
$begin = "DATE(NOW() - INTERVAL 10 DAY)";
} else {
$begin = "'" . $db->escape($begin) . "'";
}
if (!$end) {
$end = "DATE(NOW() + INTERVAL 1 DAY)";
} else {
$end = "'" . $db->escape($end) . "' + INTERVAL 1 DAY";
}
$span = $_REQUEST['span'];
switch ($span) {
case 'all':
$format = 'All';
break;
case 'month':
$format = '%Y-%m';
}
window.location.href= 'item.php?id=' + data.item.id;
});
});
</script>
<br>
<?php
if (!$search) {
exit;
}
$begin = false;
$options = 0;
if ($_REQUEST['all']) {
$options |= FIND_ALL;
}
list($sql_criteria, $begin) = item_terms_to_sql($db, $search, $options);
$extra = "";
if (!$begin) {
$begin = date("Y-m-d", time() - 90 * 24 * 3600);
}
# XXX allow option to include inactive and/or deleted
if (!$_REQUEST['all']) {
$criteria[] = "(active AND NOT deleted)";
}
$q = "SELECT\n item.id AS meta,\n item.code Code\$item,\n item.name Name\$name,\n brand.name Brand\$brand,\n retail_price MSRP\$dollar,\n IF(item.discount_type,\n CASE item.discount_type\n WHEN 'percentage' THEN ROUND(retail_price * ((100 - item.discount) / 100), 2)\n WHEN 'relative' THEN (retail_price - item.discount) \n WHEN 'fixed' THEN (item.discount)\n END,\n NULL) Sale\$dollar,\n CASE item.discount_type\n WHEN 'percentage' THEN CONCAT(ROUND(item.discount), '% off')\n WHEN 'relative' THEN CONCAT('\$', item.discount, ' off')\n END Discount\$discount,\n (SELECT SUM(allocated) FROM txn_line WHERE item = item.id) Stock\$right,\n minimum_quantity Minimum\$right,\n (SELECT -1 * SUM(allocated)\n FROM txn_line JOIN txn ON (txn = txn.id)\n WHERE type = 'customer'\n AND item = item.id AND filled > NOW() - INTERVAL 3 MONTH)\n AS Last3Months\$right,\n active Active\$bool\n FROM item\n LEFT JOIN brand ON (item.brand = brand.id)\n LEFT JOIN barcode ON (item.id = barcode.item)\n WHERE {$sql_criteria}\n GROUP BY item.id\n ORDER BY 2";
$r = $db->query($q) or die($db->error);
if ($r->num_rows == 1) {
$row = $r->fetch_assoc();
ob_end_clean();
header("Location: item.php?id=" . $row['meta']);
exit;
<?php
require 'scat.php';
require 'lib/item.php';
$sql_criteria = "1=1";
if ($items = $_REQUEST['items']) {
list($sql_criteria, $x) = item_terms_to_sql($db, $_REQUEST['items'], FIND_OR);
}
$begin = $_REQUEST['begin'];
$end = $_REQUEST['end'];
if (!$begin) {
$begin = date('Y-m-d', time() - 3 * 24 * 3600);
} else {
$begin = $db->escape($begin);
}
if (!$end) {
$end = date('Y-m-d', time());
} else {
$end = $db->escape($end);
}
head("Price Increases @ Scat", true);
?>
<form id="report-params" class="form-horizontal" role="form"
action="<?php
echo $_SERVER['PHP_SELF'];
?>
">
<div class="form-group">
<label for="datepicker" class="col-sm-2 control-label">
Dates
</label>