inventory/includes/db/items_codes_db.inc

   1 <?php
   2 /**********************************************************************
   3     Copyright (C) FrontAccounting, LLC.
   4         Released under the terms of the GNU General Public License, GPL,
   5         as published by the Free Software Foundation, either version 3
   6         of the License, or (at your option) any later version.
   7     This program is distributed in the hope that it will be useful,
   8     but WITHOUT ANY WARRANTY; without even the implied warranty of
   9     MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
  10     See the License here <http://www.gnu.org/licenses/gpl-3.0.html>.
  11 ***********************************************************************/
  12 /*
  13         item_codes table is used to store both multiply foreign codes and
  14         sale kits definition.
  15 */
  16 function update_item_code($id, $item_code, $stock_id, $description, $category, $qty, $foreign=0)
  17 {
  18         $sql = "UPDATE ".TB_PREF."item_codes SET
  19                 item_code = ".db_escape($item_code).",
  20                 stock_id = ".db_escape($stock_id).",
  21                 description = ".db_escape($description).",
  22                 category_id = ".db_escape($category).",
  23                 quantity = ".db_escape($qty).",
  24                 is_foreign = ".db_escape($foreign)."
  25                 WHERE ";
  26
  27         if ($id == -1) // update with unknown $id i.e. from items table editor
  28                 $sql .= "item_code = ".db_escape($item_code)
  29                 ." AND stock_id = ".db_escape($stock_id);
  30         else
  31                 $sql .= "id = ".db_escape($id);
  32
  33         db_query($sql,"an item code could not be updated");
  34 }
  35
  36 function add_item_code($item_code, $stock_id, $description, $category, $qty, $foreign=0)
  37 {
  38         $sql = "INSERT INTO ".TB_PREF."item_codes
  39                         (item_code, stock_id, description, category_id, quantity, is_foreign)
  40                         VALUES( ".db_escape($item_code).",".db_escape($stock_id).",
  41                         ".db_escape($description).",".db_escape($category)
  42                         .",".db_escape($qty).",".db_escape($foreign).")";
  43
  44         db_query($sql,"an item code could not be added");
  45 }
  46
  47 function delete_item_code($id)
  48 {
  49         $sql="DELETE FROM ".TB_PREF."item_codes WHERE id=".db_escape($id);
  50         db_query($sql,"an item code could not be deleted");
  51 }
  52
  53 function get_item_code($id)
  54 {
  55         $sql="SELECT * FROM ".TB_PREF."item_codes WHERE id=".db_escape($id);
  56
  57         $result = db_query($sql,"item code could not be retrieved");
  58
  59         return db_fetch($result);
  60 }
  61
  62 function get_all_item_codes($stock_id, $foreign=1)
  63 {
  64         $sql="SELECT i.*, c.description as cat_name FROM "
  65                 .TB_PREF."item_codes as i,"
  66                 .TB_PREF."stock_category as c
  67                 WHERE stock_id=".db_escape($stock_id)."
  68                 AND i.category_id=c.category_id
  69                 AND i.is_foreign=".db_escape($foreign);
  70
  71         $result = db_query($sql,"all item codes could not be retrieved");
  72
  73         return $result;
  74 }
  75
  76 function delete_item_kit($item_code)
  77 {
  78         $sql="DELETE FROM ".TB_PREF."item_codes WHERE item_code=".db_escape($item_code);
  79         db_query($sql,"an item kit could not be deleted");
  80 }
  81
  82 function get_item_kit($item_code)
  83 {
  84         $sql="SELECT DISTINCT kit.*, item.units, comp.description as comp_name
  85                 FROM "
  86                 .TB_PREF."item_codes kit,"
  87                 .TB_PREF."item_codes comp
  88                 LEFT JOIN "
  89                 .TB_PREF."stock_master item
  90                 ON
  91                         item.stock_id=comp.item_code
  92                 WHERE
  93                         kit.stock_id=comp.item_code
  94                         AND kit.item_code=".db_escape($item_code);
  95
  96         $result = db_query($sql,"item kit could not be retrieved");
  97
  98         return $result;
  99 }
 100
 101 function get_item_code_dflts($stock_id)
 102 {
 103         $sql = "SELECT units, decimals, description, category_id
 104                 FROM ".TB_PREF."stock_master,".TB_PREF."item_units
 105                 WHERE stock_id=".db_escape($stock_id);
 106
 107         $result = db_query($sql,"item code defaults could not be retrieved");
 108         return db_fetch($result);
 109 }
 110 //
 111 //      Check if kit contains given item, optionally recursive.
 112 //
 113 function check_item_in_kit($old_id, $kit_code, $item_code, $recurse=false)
 114 {
 115         $result = get_item_kit($kit_code);
 116         if ($result != 0)
 117         {
 118                 while ($myrow = db_fetch($result))
 119                 {
 120                         if ($myrow['id'] == $old_id)
 121                                 continue;
 122
 123                         if ($myrow['stock_id'] == $item_code)
 124                         {
 125                                 return 1;
 126                         }
 127
 128                         if ($recurse && $myrow['item_code'] != $myrow['stock_id']
 129                                 && check_item_in_kit($old_id, $item_code, $myrow['stock_id'], true))
 130                         {
 131                                 return 1;
 132                         }
 133                 }
 134         }
 135         return 0;
 136 }
 137
 138 function get_kit_props($kit_code)
 139 {
 140         $sql = "SELECT description, category_id FROM ".TB_PREF."item_codes "
 141                 . " WHERE item_code=".db_escape($kit_code);
 142         $res = db_query($sql, "kit name query failed");
 143         return db_fetch($res);
 144 }
 145
 146 function update_kit_props($kit_code, $name, $category)
 147 {
 148         $sql = "UPDATE ".TB_PREF."item_codes SET description="
 149                 . db_escape($name).",category_id=".db_escape($category)
 150                 . " WHERE item_code=".db_escape($kit_code);
 151         db_query($sql, "kit name update failed");
 152 }
 153
 154 function get_where_used($item_code)
 155 {
 156         $sql = "SELECT item_code, description FROM "
 157                 .TB_PREF."item_codes "
 158                 . " WHERE stock_id=".db_escape($item_code)."
 159                         AND item_code!=".db_escape($item_code);
 160         return db_query($sql, "where used query failed");
 161 }
 162 ?>