Example #1
0
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;
}
Example #2
0
File: txn.php Project: jacques/scat
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;
}