inventory/includes/db/items_codes_db.inc

   1 <?php
   2 /**********************************************************************
   3     Copyright (C) FrontAccounting, LLC.
   4         Released under the terms of the GNU Affero General Public License,
   5         AGPL, as published by the Free Software Foundation, either version
   6         3 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/agpl-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 = $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 = $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).",$category,".db_escape($qty).",".$foreign.")";
  42
  43         db_query($sql,"an item code could not be added");
  44 }
  45
  46 function delete_item_code($id)
  47 {
  48         $sql="DELETE FROM ".TB_PREF."item_codes WHERE id='$id'";
  49         db_query($sql,"an item code could not be deleted");
  50 }
  51
  52 function get_item_code($id)
  53 {
  54         $sql="SELECT * FROM ".TB_PREF."item_codes WHERE id='$id'";
  55
  56         $result = db_query($sql,"item code could not be retrieved");
  57
  58         return db_fetch($result);
  59 }
  60
  61 function get_all_item_codes($stock_id, $foreign=1)
  62 {
  63         $sql="SELECT i.*, c.description as cat_name FROM "
  64                 .TB_PREF."item_codes as i,"
  65                 .TB_PREF."stock_category as c
  66                 WHERE stock_id='$stock_id'
  67                 AND i.category_id=c.category_id
  68                 AND i.is_foreign=$foreign";
  69
  70         $result = db_query($sql,"all item codes could not be retrieved");
  71
  72         return $result;
  73 }
  74
  75 function delete_item_kit($item_code)
  76 {
  77         $sql="DELETE FROM ".TB_PREF."item_codes WHERE item_code='$item_code'";
  78         db_query($sql,"an item kit could not be deleted");
  79 }
  80
  81 function get_item_kit($item_code)
  82 {
  83         $sql="SELECT DISTINCT kit.*, item.units, comp.description as comp_name
  84                 FROM "
  85                 .TB_PREF."item_codes kit,"
  86                 .TB_PREF."item_codes comp
  87                 LEFT JOIN "
  88                 .TB_PREF."stock_master item
  89                 ON
  90                         item.stock_id=comp.item_code
  91                 WHERE
  92                         kit.stock_id=comp.item_code
  93                         AND kit.item_code='$item_code'";
  94
  95         $result = db_query($sql,"item kit could not be retrieved");
  96
  97         return $result;
  98 }
  99
 100 function get_item_code_dflts($stock_id)
 101 {
 102         $sql = "SELECT units, decimals, description, category_id
 103                 FROM ".TB_PREF."stock_master,".TB_PREF."item_units
 104                 WHERE stock_id='$stock_id'";
 105
 106         $result = db_query($sql,"item code defaults could not be retrieved");
 107         return db_fetch($result);
 108 }
 109 //
 110 //      Check if kit contains given item, optionally recursive.
 111 //
 112 function check_item_in_kit($old_id, $kit_code, $item_code, $recurse=false)
 113 {
 114         $result = get_item_kit($kit_code);
 115         if ($result != 0)
 116         {
 117                 while ($myrow = db_fetch($result))
 118                 {
 119                         if ($myrow['id'] == $old_id)
 120                                 continue;
 121
 122                         if ($myrow['stock_id'] == $item_code)
 123                         {
 124                                 return 1;
 125                         }
 126
 127                         if ($recurse && $myrow['item_code'] != $myrow['stock_id']
 128                                 && check_item_in_kit($old_id, $item_code, $myrow['stock_id'], true))
 129                         {
 130                                 return 1;
 131                         }
 132                 }
 133         }
 134         return 0;
 135 }
 136
 137 function get_kit_props($kit_code)
 138 {
 139         $sql = "SELECT description, category_id FROM ".TB_PREF."item_codes "
 140                 . " WHERE item_code='$kit_code'";
 141         $res = db_query($sql, "kit name query failed");
 142         return db_fetch($res);
 143 }
 144
 145 function update_kit_props($kit_code, $name, $category)
 146 {
 147         $sql = "UPDATE ".TB_PREF."item_codes SET description="
 148                 . db_escape($name).",category_id=".db_escape($category)
 149                 . " WHERE item_code='$kit_code'";
 150         db_query($sql, "kit name update failed");
 151 }
 152
 153 function get_where_used($item_code)
 154 {
 155         $sql = "SELECT item_code, description FROM "
 156                 .TB_PREF."item_codes "
 157                 . " WHERE stock_id='$item_code'
 158                         AND item_code!='$item_code'";
 159         return db_query($sql, "where used query failed");
 160 }
 161 ?>