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 ***********************************************************************/
13 function add_tag($type, $name, $description)
15 begin_transaction(__FUNCTION__, func_get_args());
17 $sql = "INSERT INTO ".TB_PREF."tags (type, name, description)
18 VALUES (".db_escape($type).", ".db_escape($name).", ".db_escape($description).")";
20 $result = db_query($sql);
25 //--------------------------------------------------------------------------------------
27 function update_tag($id, $name, $description, $type=null)
29 begin_transaction(__FUNCTION__, func_get_args());
30 $sql = "UPDATE ".TB_PREF."tags SET name=".db_escape($name).",
31 description=".db_escape($description);
33 $sql .= ", type=".db_escape($type);
35 $sql .= " WHERE id = ".db_escape($id);
37 $result = db_query($sql);
42 //--------------------------------------------------------------------------------------
44 function get_tags($type, $all=false)
46 $sql = "SELECT * FROM ".TB_PREF."tags WHERE type=".db_escape($type);
48 if (!$all) $sql .= " AND !inactive";
50 $sql .= " ORDER BY name";
52 return db_query($sql, "could not get tags");
55 //--------------------------------------------------------------------------------------
59 $sql = "SELECT * FROM ".TB_PREF."tags WHERE id = ".db_escape($id);
61 $result = db_query($sql, "could not get tag");
63 return db_fetch($result);
66 //--------------------------------------------------------------------------------------
68 function get_tag_type($id)
70 $sql = "SELECT type FROM ".TB_PREF."tags WHERE id = ".db_escape($id);
72 $result = db_query($sql, "could not get tag type");
74 $row = db_fetch_row($result);
78 //--------------------------------------------------------------------------------------
80 function get_tag_name($id)
82 $sql = "SELECT name FROM ".TB_PREF."tags WHERE id = ".db_escape($id);
84 $result = db_query($sql, "could not get tag name");
86 $row = db_fetch_row($result);
90 //----------------------------------------------------------------------------------------------------
91 function get_tag_names($tags)
98 $tag = get_tag_name($id);
106 //--------------------------------------------------------------------------------------
108 function get_tag_description($id)
110 $sql = "SELECT description FROM ".TB_PREF."tags WHERE id = ".db_escape($id);
112 $result = db_query($sql, "could not get tag description");
114 $row = db_fetch_row($result);
118 //--------------------------------------------------------------------------------------
120 function delete_tag($id)
122 begin_transaction(__FUNCTION__, func_get_args());
124 $sql = "DELETE FROM ".TB_PREF."tags WHERE id = ".db_escape($id);
125 db_query($sql, "could not delete tag");
127 commit_transaction();
130 //--------------------------------------------------------------------------------------
132 function add_tag_associations($recordid, $tagids)
134 begin_transaction(__FUNCTION__, func_get_args());
136 foreach($tagids as $tagid) {
137 if (!$tagid) continue;
138 $sql = "INSERT INTO ".TB_PREF."tag_associations (record_id, tag_id)
139 VALUES (".db_escape($recordid).", ".db_escape($tagid).")";
141 db_query($sql, "could not add tag association");
144 commit_transaction();
147 //--------------------------------------------------------------------------------------
149 function update_tag_associations($type, $recordid, $tagids)
151 begin_transaction(__FUNCTION__, func_get_args());
153 // Delete the old associations
154 delete_tag_associations($type, $recordid, false);
155 // Add the new associations
156 add_tag_associations($recordid, $tagids);
158 commit_transaction();
161 //--------------------------------------------------------------------------------------
162 // To delete tag associations, we need to specify the tag type.
163 // Otherwise we may inadvertantly delete records for another type of tag
165 function delete_tag_associations($type, $recordid, $all=false)
167 begin_transaction(__FUNCTION__, func_get_args());
168 /* multiply table DELETE syntax available since MySQL 4.0.0:
169 $sql = "DELETE ta FROM ".TB_PREF."tag_associations ta
170 INNER JOIN ".TB_PREF."tags tags ON tags.id = ta.tag_id
171 WHERE tags.type = ".db_escape($type)." AND ta.record_id = ".db_escape($recordid);
173 // To support MySQL 3.xx we have to use multiply queries
174 $sql = "SELECT * FROM ".TB_PREF."tag_associations ta
175 INNER JOIN ".TB_PREF."tags tags ON tags.id = ta.tag_id
176 WHERE tags.type = ".db_escape($type)." AND ta.record_id = ".db_escape($recordid);
178 $sql .= " AND tags.inactive = 0";
179 $result = db_query($sql, "could not select tag associations");
181 while($ta = db_fetch($result)) {
182 $sql2 = "DELETE FROM ".TB_PREF."tag_associations WHERE
183 record_id = '".$ta['record_id']."' AND tag_id=".$ta['tag_id'];
184 db_query($sql2, "could not delete tag associations");
187 commit_transaction();
190 //--------------------------------------------------------------------------------------
192 function get_records_associated_with_tag($id)
194 // Which table we query is based on the tag type
195 $type = get_tag_type($id);
200 $table = TB_PREF."chart_master";
201 $key = "account_code";
204 $table = TB_PREF."dimensions";
209 $sql = "SELECT $table.* FROM $table
210 INNER JOIN ".TB_PREF."tag_associations AS ta ON ta.record_id = $table.$key
211 INNER JOIN ".TB_PREF."tags AS tags ON ta.tag_id = tags.id
212 WHERE tags.id = ".db_escape($id);
214 return db_query($sql, "could not get tag associations for tag");
217 //--------------------------------------------------------------------------------------
219 function get_tags_associated_with_record($type, $recordid)
221 $sql = "SELECT tags.* FROM ".TB_PREF."tag_associations AS ta
222 INNER JOIN ".TB_PREF."tags AS tags ON tags.id = ta.tag_id
223 WHERE tags.type = $type AND ta.record_id = ".db_escape($recordid);
225 return db_query($sql, "could not get tags associations for record");
228 //--------------------------------------------------------------------------------------
230 function is_record_in_tags($tags, $type, $recordid)
232 foreach($tags as $id)
234 $sql = "SELECT ta.record_id FROM ".TB_PREF."tag_associations AS ta
235 INNER JOIN ".TB_PREF."tags AS tags ON tags.id = ta.tag_id
236 WHERE tags.type = $type AND tags.id = $id AND ta.record_id = ".db_escape($recordid);
237 $res = db_query($sql, "could not get tags associations for record");
238 if (db_num_rows($res) == 0)