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_crm_person($ref, $name, $name2, $address, $phone, $phone2, $fax, $email, $lang, $notes,
14 $cat_ids=null, $entity=null)
16 begin_transaction(__FUNCTION__, func_get_args());
17 $sql = "INSERT INTO ".TB_PREF."crm_persons (ref, name, name2, address,
18 phone, phone2, fax, email, lang, notes)
20 .db_escape($ref) . ", "
21 .db_escape($name) . ", "
22 .db_escape($name2) . ", "
23 .db_escape($address) . ", "
24 .db_escape($phone) . ", "
25 .db_escape($phone2) . ", "
26 .db_escape($fax) . ", "
27 .db_escape($email) . ", "
28 .db_escape($lang) . ", "
31 $ret = db_query($sql, "Can't insert crm person");
34 if ($ret && $cat_ids) {
35 if(!update_person_contacts($id, $cat_ids, $entity))
42 function update_crm_person($id, $ref, $name, $name2, $address, $phone, $phone2, $fax, $email,
43 $lang, $notes, $cat_ids, $entity=null, $type=null)
45 begin_transaction(__FUNCTION__, func_get_args());
47 $sql = "UPDATE ".TB_PREF."crm_persons SET "
48 ."ref=".db_escape($ref) . ", "
49 ."name=".db_escape($name) . ", "
50 ."name2=".db_escape($name2) . ", "
51 ."address=".db_escape($address) . ", "
52 ."phone=".db_escape($phone) . ", "
53 ."phone2=".db_escape($phone2) . ", "
54 ."fax=".db_escape($fax) . ", "
55 ."email=".db_escape($email) . ", "
56 ."lang=".db_escape($lang) . ", "
57 ."notes=".db_escape($notes)
58 ." WHERE id = ".db_escape($id);
60 $ret = db_query($sql, "Can't update crm person");
62 if(!update_person_contacts($id, $cat_ids, $entity, $type))
69 function delete_crm_person($person, $with_contacts=false)
71 begin_transaction(__FUNCTION__, func_get_args());
74 $sql = "DELETE FROM ".TB_PREF."crm_contacts WHERE person_id=".db_escape($person);
75 db_query($sql, "Can't delete crm contacts");
77 $sql = "DELETE FROM ".TB_PREF."crm_persons WHERE id=".db_escape($person);
78 $ret = db_query($sql, "Can't delete crm person");
84 Retrieve full contact data from database for selected type/action/entity or person
86 function get_crm_persons($type=null, $action=null, $entity=null, $person=null, $unique=false)
88 $sql = "SELECT t.*, p.*, r.id as contact_id FROM ".TB_PREF."crm_persons p,"
89 .TB_PREF."crm_categories t, "
90 .TB_PREF."crm_contacts r WHERE ";
91 $sel = array('r.type=t.type', 'r.action=t.action', 'r.person_id=p.id');
94 $sel[] = 't.type='.db_escape($type);
97 $sel[] = 't.action='.db_escape($action);
100 $sel[] = 'r.entity_id='.db_escape($entity);
103 $sel[] = 'r.person_id='.db_escape($person);
105 $sql .= implode (" AND ", $sel);
108 $sql .= " GROUP BY person_id";
110 $sql .= " ORDER BY contact_id";
112 $result = db_query($sql, "Can't get crm persons");
113 // fallback to general contacts
114 if (!db_num_rows($result) && $action && $action != 'general')
115 return get_crm_persons($type, 'general', $entity, $person, $unique);
120 function get_crm_person($id)
122 $sql = "SELECT * FROM ".TB_PREF."crm_persons WHERE id=".db_escape($id);
124 $res = db_query($sql, "Can't get crm persons");
126 $person = db_fetch($res);
127 $person['contacts'] = get_person_contacts($id);
133 Returns all contacts for given person id
135 function get_person_contacts($id)
137 $sql = "SELECT t.id FROM "
138 .TB_PREF."crm_categories t, "
139 .TB_PREF."crm_contacts r WHERE r.type=t.type AND r.action=t.action
140 AND r.person_id=".db_escape($id);
143 $ret = db_query($sql, "Can't get crm person contacts");
144 while($cont = db_fetch_row($ret))
145 $contacts[] = $cont[0];
149 function update_person_contacts($id, $cat_ids, $entity_id=null, $type=null)
151 begin_transaction(__FUNCTION__, func_get_args());
153 $sql = "DELETE FROM ".TB_PREF."crm_contacts WHERE person_id=".db_escape($id);
155 $sql .= " AND type=".db_escape($type);
158 $ret = db_query($sql, "Can't delete person contacts");
160 foreach($cat_ids as $n => $cid)
161 $cat_ids[$n] = db_escape($cid);
163 if($ret && count($cat_ids)) {
164 array_walk($cat_ids,'db_escape');
165 $sql = "INSERT INTO ".TB_PREF."crm_contacts (person_id,type,action,entity_id)
166 SELECT ".db_escape($id).",t.type, t.action,".db_escape($entity_id, true)."
167 FROM ".TB_PREF."crm_categories t WHERE t.id=".implode(' OR t.id=', $cat_ids);
169 $sql .= " AND t.type=".db_escape($type);
170 $ret = db_query($sql, "Can't update person contacts");
172 commit_transaction();
176 function delete_entity_contacts($class, $entity)
178 begin_transaction(__FUNCTION__, func_get_args());
180 delete_crm_contacts(null, $class, $entity);
182 $res = get_crm_persons($class, null, $entity, null, true);
183 while($person = db_fetch($res)) {
184 $rels = get_person_contacts($person['id']);
185 if (count($rels) == 0) {
186 delete_crm_person($person['id']);
190 commit_transaction();
193 //-----------------------------------------------------------------------------------------------
195 function add_crm_category($type, $action, $name, $description)
197 begin_transaction(__FUNCTION__, func_get_args());
199 $sql = "INSERT INTO ".TB_PREF."crm_categories (type, action, name, description)
200 VALUES (".db_escape($type) . ", "
201 .db_escape($action) . ", "
202 .db_escape($name) . ", "
203 .db_escape($description)
205 db_query($sql,"The insert of the crm category failed");
207 commit_transaction();
210 function update_crm_category($selected_id, $type, $action, $name, $description)
212 begin_transaction(__FUNCTION__, func_get_args());
214 $sql = "UPDATE ".TB_PREF."crm_categories SET ";
216 $sql .= "type=".db_escape($type) . ",";
218 $sql .= "action=".db_escape($action) . ",";
219 $sql .= "name=".db_escape($name) . ","
220 ."description=".db_escape($description)
221 ." WHERE id = ".db_escape($selected_id);
222 db_query($sql,"The update of the crm category failed");
224 commit_transaction();
227 function delete_crm_category($selected_id)
229 begin_transaction(__FUNCTION__, func_get_args());
231 // preserve system categories
232 $sql="DELETE FROM ".TB_PREF."crm_categories WHERE system=0 AND id=".db_escape($selected_id);
233 db_query($sql,"could not delete crm category");
235 commit_transaction();
238 function get_crm_categories($show_inactive)
240 $sql = "SELECT * FROM ".TB_PREF."crm_categories";
241 if (!$show_inactive) $sql .= " WHERE !inactive";
242 $sql .= " ORDER BY type, action";
243 return db_query($sql,"could not get areas");
246 function get_crm_category($selected_id)
248 $sql = "SELECT * FROM ".TB_PREF."crm_categories WHERE id=".db_escape($selected_id);
250 $result = db_query($sql,"could not get crm category");
251 return db_fetch($result);
254 function get_crm_category_name($id)
256 $sql = "SELECT name FROM ".TB_PREF."crm_categories WHERE id=".db_escape($id);
258 $result = db_query($sql, "could not get sales type");
260 $row = db_fetch_row($result);
264 //----------------------------------------------------------------------------------------
265 // Contact is relation between person and entity in some category
267 function add_crm_contact($type, $action, $entity_id, $person_id)
269 $sql = "INSERT INTO ".TB_PREF."crm_contacts (person_id, type, action, entity_id) VALUES ("
270 .db_escape($person_id) . ","
271 .db_escape($type) . ","
272 .db_escape($action) . ","
273 .db_escape($entity_id) . ")";
274 return db_query($sql, "Can't insert crm contact");
277 Delete contact selected by unique id.
279 function delete_crm_contact($id)
281 $sql = "DELETE FROM ".TB_PREF."crm_contacts WHERE id=".db_escape($id);
283 return db_query($sql, "Can't delete crm contact");
286 Delete selected contacts for given person
288 function delete_crm_contacts($person_id = null, $type = null, $entity_id=null, $action = null)
290 $sql = "DELETE FROM ".TB_PREF."crm_contacts WHERE ";
293 $where[] = 'person_id='.db_escape($person_id);
295 $where[] = 'type='.db_escape($type);
297 $where[] = 'entity_id='.db_escape($entity_id);
299 $where[] = 'action='.db_escape($action);
301 return db_query($sql.implode(' AND ', $where), "Can't delete crm contact");
305 Returns person data for given contact id
307 function get_crm_contact($id)
309 $sql = "SELECT t.type, t.action, p.*, r.person_id, r.id FROM ".TB_PREF."crm_persons p,"
310 .TB_PREF."crm_categories t, "
311 .TB_PREF."crm_contacts r WHERE r.type=t.type AND r.action=t.action AND r.person_id=p.id"
312 ." AND r.id=".db_escape($id);
313 $ret = db_query($sql, "Can't get crm contact");
315 return db_fetch($ret, "Can't fetch contact data");
320 Check for whether category is used in contacts.
322 function is_crm_category_used($id)
324 $row = get_crm_category($id);
325 $sql = "SELECT COUNT(*) FROM ".TB_PREF."crm_contacts WHERE type='".$row['type']."' AND action='".$row['action']."'";
326 $result = db_query($sql, "check relations for crm_contacts failed");
327 $contacts = db_fetch($result);