inventory/includes/db/items_codes_db.inc

   1 <?php
   2 /*
   3         item_codes table is used to store both multiply foreign codes and
   4         sale kits definition.
   5 */
   6 function update_item_code($id, $item_code, $stock_id, $description, $category, $qty, $foreign=0)
   7 {
   8         $sql = "UPDATE ".TB_PREF."item_codes SET
   9                 item_code = ".db_escape($item_code).",
  10                 stock_id = ".db_escape($stock_id).",
  11                 description = ".db_escape($description).",
  12                 category_id = $category,
  13                 quantity = ".db_escape($qty).",
  14                 is_foreign = ".db_escape($foreign)."
  15                 WHERE ";
  16
  17         if ($id == -1) // update with unknown $id i.e. from items table editor
  18                 $sqk .= "item_code = ".db_escape($item_code)
  19                 ." AND stock_id = ".db_escape($stock_id);
  20         else
  21                 $sql .= "id = $id";
  22
  23         db_query($sql,"an item code could not be updated");
  24 }
  25
  26 function add_item_code($item_code, $stock_id, $description, $category, $qty, $foreign=0)
  27 {
  28         $sql = "INSERT INTO ".TB_PREF."item_codes
  29                         (item_code, stock_id, description, category_id, quantity, is_foreign)
  30                         VALUES( ".db_escape($item_code).",".db_escape($stock_id).",
  31                         ".db_escape($description).",$category,".db_escape($qty).",".$foreign.")";
  32
  33         db_query($sql,"an item code could not be added");
  34 }
  35
  36 function delete_item_code($id)
  37 {
  38         $sql="DELETE FROM ".TB_PREF."item_codes WHERE id='$id'";
  39         db_query($sql,"an item code could not be deleted");
  40 }
  41
  42 function get_item_code($id)
  43 {
  44         $sql="SELECT * FROM ".TB_PREF."item_codes WHERE id='$id'";
  45
  46         $result = db_query($sql,"item code could not be retrieved");
  47
  48         return db_fetch($result);
  49 }
  50
  51 function get_all_item_codes($stock_id, $foreign=1)
  52 {
  53         $sql="SELECT i.*, c.description as cat_name FROM "
  54                 .TB_PREF."item_codes as i,"
  55                 .TB_PREF."stock_category as c
  56                 WHERE stock_id='$stock_id'
  57                 AND i.category_id=c.category_id
  58                 AND i.is_foreign=$foreign";
  59
  60         $result = db_query($sql,"all item codes could not be retrieved");
  61
  62         return $result;
  63 }
  64
  65 function delete_item_kit($item_code)
  66 {
  67         $sql="DELETE FROM ".TB_PREF."item_codes WHERE item_code='$item_code'";
  68         db_query($sql,"an item kit could not be deleted");
  69 }
  70
  71 function get_item_kit($item_code)
  72 {
  73         $sql="SELECT DISTINCT kit.*, item.units, comp.description as comp_name
  74                 FROM "
  75                 .TB_PREF."item_codes kit,"
  76                 .TB_PREF."item_codes comp
  77                 LEFT JOIN "
  78                 .TB_PREF."stock_master item
  79                 ON
  80                         item.stock_id=comp.item_code
  81                 WHERE
  82                         kit.stock_id=comp.item_code
  83                         AND kit.item_code='$item_code'";
  84
  85         $result = db_query($sql,"item kit could not be retrieved");
  86
  87         return $result;
  88 }
  89
  90 function get_item_code_dflts($stock_id)
  91 {
  92         $sql = "SELECT units, decimals, description, category_id
  93                 FROM ".TB_PREF."stock_master,".TB_PREF."item_units
  94                 WHERE stock_id='$stock_id'";
  95
  96         $result = db_query($sql,"item code defaults could not be retrieved");
  97         return db_fetch($result);
  98 }
  99 //
 100 //      Check if kit contains given item, optionally recursive.
 101 //
 102 function check_item_in_kit($old_id, $kit_code, $item_code, $recurse=false)
 103 {
 104         $result = get_item_kit($kit_code);
 105         if ($result != 0)
 106         {
 107                 while ($myrow = db_fetch($result))
 108                 {
 109                         if ($myrow['id'] == $old_id)
 110                                 continue;
 111
 112                         if ($myrow['stock_id'] == $item_code)
 113                         {
 114                                 return 1;
 115                         }
 116
 117                         if ($recurse && $myrow['item_code'] != $myrow['stock_id']
 118                                 && check_item_in_kit($old_id, $item_code, $myrow['stock_id'], true))
 119                         {
 120                                 return 1;
 121                         }
 122                 }
 123         }
 124         return 0;
 125 }
 126
 127 function get_kit_props($kit_code)
 128 {
 129         $sql = "SELECT description, category_id FROM ".TB_PREF."item_codes "
 130                 . " WHERE item_code='$kit_code'";
 131         $res = db_query($sql, "kit name query failed");
 132         return db_fetch($res);
 133 }
 134
 135 function update_kit_props($kit_code, $name, $category)
 136 {
 137         $sql = "UPDATE ".TB_PREF."item_codes SET description="
 138                 . db_escape($name).",category_id=".db_escape($category)
 139                 . " WHERE item_code='$kit_code'";
 140         db_query($sql, "kit name update failed");
 141 }
 142
 143 function get_where_used($item_code)
 144 {
 145         $sql = "SELECT item_code, description FROM "
 146                 .TB_PREF."item_codes "
 147                 . " WHERE stock_id='$item_code'
 148                         AND item_code!='$item_code'";
 149         return db_query($sql, "where used query failed");
 150 }
 151 ?>