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 $sql = "INSERT INTO ".TB_PREF."tags (type, name, description)
16 VALUES (".db_escape($type).", ".db_escape($name).", ".db_escape($description).")";
18 return db_query($sql);
21 //--------------------------------------------------------------------------------------
23 function update_tag($id, $name, $description, $type=null)
25 $sql = "UPDATE ".TB_PREF."tags SET name=".db_escape($name).",
26 description=".db_escape($description);
28 $sql .= ", type=".db_escape($type);
30 $sql .= " WHERE id = ".db_escape($id);
32 return db_query($sql);
35 //--------------------------------------------------------------------------------------
37 function get_tags($type, $all=false)
39 $sql = "SELECT * FROM ".TB_PREF."tags WHERE type=".db_escape($type);
41 if (!$all) $sql .= " AND !inactive";
43 $sql .= " ORDER BY name";
45 return db_query($sql, "could not get tags");
48 //--------------------------------------------------------------------------------------
52 $sql = "SELECT * FROM ".TB_PREF."tags WHERE id = ".db_escape($id);
54 $result = db_query($sql, "could not get tag");
56 return db_fetch($result);
59 //--------------------------------------------------------------------------------------
61 function get_tag_type($id)
63 $sql = "SELECT type FROM ".TB_PREF."tags WHERE id = ".db_escape($id);
65 $result = db_query($sql, "could not get tag type");
67 $row = db_fetch_row($result);
71 //--------------------------------------------------------------------------------------
73 function get_tag_name($id)
75 $sql = "SELECT name FROM ".TB_PREF."tags WHERE id = ".db_escape($id);
77 $result = db_query($sql, "could not get tag name");
79 $row = db_fetch_row($result);
83 //----------------------------------------------------------------------------------------------------
84 function get_tag_names($tags)
91 $tag = get_tag_name($id);
99 //--------------------------------------------------------------------------------------
101 function get_tag_description($id)
103 $sql = "SELECT description FROM ".TB_PREF."tags WHERE id = ".db_escape($id);
105 $result = db_query($sql, "could not get tag description");
107 $row = db_fetch_row($result);
111 //--------------------------------------------------------------------------------------
113 function delete_tag($id)
115 $sql = "DELETE FROM ".TB_PREF."tags WHERE id = ".db_escape($id);
117 db_query($sql, "could not delete tag");
120 //--------------------------------------------------------------------------------------
122 function add_tag_associations($recordid, $tagids)
124 foreach($tagids as $tagid) {
125 if (!$tagid) continue;
126 $sql = "INSERT INTO ".TB_PREF."tag_associations (record_id, tag_id)
127 VALUES (".db_escape($recordid).", ".db_escape($tagid).")";
129 db_query($sql, "could not add tag association");
133 //--------------------------------------------------------------------------------------
135 function update_tag_associations($type, $recordid, $tagids)
137 // Delete the old associations
138 delete_tag_associations($type, $recordid, false);
139 // Add the new associations
140 add_tag_associations($recordid, $tagids);
143 //--------------------------------------------------------------------------------------
144 // To delete tag associations, we need to specify the tag type.
145 // Otherwise we may inadvertantly delete records for another type of tag
147 function delete_tag_associations($type, $recordid, $all=false)
149 /* multiply table DELETE syntax available since MySQL 4.0.0:
150 $sql = "DELETE ta FROM ".TB_PREF."tag_associations ta
151 INNER JOIN ".TB_PREF."tags tags ON tags.id = ta.tag_id
152 WHERE tags.type = ".db_escape($type)." AND ta.record_id = ".db_escape($recordid);
154 // To support MySQL 3.xx we have to use multiply queries
155 $sql = "SELECT * FROM ".TB_PREF."tag_associations ta
156 INNER JOIN ".TB_PREF."tags tags ON tags.id = ta.tag_id
157 WHERE tags.type = ".db_escape($type)." AND ta.record_id = ".db_escape($recordid);
159 $sql .= " AND tags.inactive = 0";
160 $result = db_query($sql, "could not select tag associations");
162 while($ta = db_fetch($result)) {
163 $sql2 = "DELETE FROM ".TB_PREF."tag_associations WHERE
164 record_id = '".$ta['record_id']."' AND tag_id=".$ta['tag_id'];
165 db_query($sql2, "could not delete tag associations");
169 //--------------------------------------------------------------------------------------
171 function get_records_associated_with_tag($id)
173 // Which table we query is based on the tag type
174 $type = get_tag_type($id);
179 $table = TB_PREF."chart_master";
180 $key = "account_code";
183 $table = TB_PREF."dimensions";
188 $sql = "SELECT $table.* FROM $table
189 INNER JOIN ".TB_PREF."tag_associations AS ta ON ta.record_id = $table.$key
190 INNER JOIN ".TB_PREF."tags AS tags ON ta.tag_id = tags.id
191 WHERE tags.id = ".db_escape($id);
193 return db_query($sql, "could not get tag associations for tag");
196 //--------------------------------------------------------------------------------------
198 function get_tags_associated_with_record($type, $recordid)
200 $sql = "SELECT tags.* FROM ".TB_PREF."tag_associations AS ta
201 INNER JOIN ".TB_PREF."tags AS tags ON tags.id = ta.tag_id
202 WHERE tags.type = $type AND ta.record_id = ".db_escape($recordid);
204 return db_query($sql, "could not get tags associations for record");
207 //--------------------------------------------------------------------------------------
209 function is_record_in_tags($tags, $type, $recordid)
211 foreach($tags as $id)
213 $sql = "SELECT ta.record_id FROM ".TB_PREF."tag_associations AS ta
214 INNER JOIN ".TB_PREF."tags AS tags ON tags.id = ta.tag_id
215 WHERE tags.type = $type AND tags.id = $id AND ta.record_id = ".db_escape($recordid);
216 $res = db_query($sql, "could not get tags associations for record");
217 if (db_num_rows($res) == 0)