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;
}
function txn_load_items($db, $id)
{
$q = "SELECT\n txn_line.id AS line_id, item.code, item.id AS item_id,\n IFNULL(override_name, item.name) name,\n txn_line.retail_price msrp,\n IF(txn_line.discount_type,\n CASE txn_line.discount_type\n WHEN 'percentage' THEN CAST(ROUND_TO_EVEN(txn_line.retail_price * ((100 - txn_line.discount) / 100), 2) AS DECIMAL(9,2))\n WHEN 'relative' THEN (txn_line.retail_price - txn_line.discount) \n WHEN 'fixed' THEN (txn_line.discount)\n END,\n txn_line.retail_price) price,\n (IF(type = 'customer', -1, 1) * ordered *\n CASE txn_line.discount_type\n WHEN 'percentage' THEN txn_line.retail_price *\n ((100 - txn_line.discount) / 100)\n WHEN 'relative' THEN (txn_line.retail_price -\n txn_line.discount) \n WHEN 'fixed' THEN (txn_line.discount)\n ELSE txn_line.retail_price\n END) AS ext_price,\n IFNULL(CONCAT(IF(item.retail_price, 'MSRP \$', 'List \$'),\n txn_line.retail_price,\n CASE txn_line.discount_type\n WHEN 'percentage' THEN\n CONCAT(' / Sale: ',\n ROUND(txn_line.discount), '% off')\n WHEN 'relative' THEN\n CONCAT(' / Sale: \$', txn_line.discount, ' off')\n WHEN 'fixed' THEN\n ''\n END), '') discount,\n ordered * IF(txn.type = 'customer', -1, 1) AS quantity,\n allocated * IF(txn.type = 'customer', -1, 1) AS allocated,\n (SELECT SUM(allocated) FROM txn_line WHERE item = item.id) AS stock\n FROM txn\n LEFT JOIN txn_line ON (txn.id = txn_line.txn)\n JOIN item ON (txn_line.item = item.id)\n WHERE txn.id = {$id}\n ORDER BY txn_line.id ASC";
$r = $db->query($q) or die_query($db, $q);
$items = array();
while ($row = $r->fetch_assoc()) {
/* force numeric values to numeric type */
$row['msrp'] = (double) $row['msrp'];
$row['price'] = (double) $row['price'];
$row['quantity'] = (int) $row['quantity'];
$row['stock'] = (int) $row['stock'];
$row['fake_barcode'] = generate_upc(sprintf("4004%07d", $row['item_id']));
$items[] = $row;
}
return $items;
}