Context Navigation

source: subversion/trunk/roundcubemail/program/include/rcube_contacts.php @ 5850

Last change on this file since 5850 was 5850, checked in by thomasb, 16 months ago
Allow users to choose cols for contacts list sorting
Property svn:keywords set to `Id`
File size: 32.6 KB

Line
1	<?php
2
3	/*
4	+-----------------------------------------------------------------------+
5	\| program/include/rcube_contacts.php \|
6	\| \|
7	\| This file is part of the Roundcube Webmail client \|
8	\| Copyright (C) 2006-2012, The Roundcube Dev Team \|
9	\| \|
10	\| Licensed under the GNU General Public License version 3 or \|
11	\| any later version with exceptions for skins & plugins. \|
12	\| See the README file for a full license statement. \|
13	\| \|
14	\| PURPOSE: \|
15	\| Interface to the local address book database \|
16	\| \|
17	+-----------------------------------------------------------------------+
18	\| Author: Thomas Bruederli <roundcube@gmail.com> \|
19	+-----------------------------------------------------------------------+
20
21	$Id$
22
23	*/
24
25
26	/**
27	* Model class for the local address book database
28	*
29	* @package Addressbook
30	*/
31	class rcube_contacts extends rcube_addressbook
32	{
33	// protected for backward compat. with some plugins
34	protected $db_name = 'contacts';
35	protected $db_groups = 'contactgroups';
36	protected $db_groupmembers = 'contactgroupmembers';
37	protected $vcard_fieldmap = array();
38
39	/**
40	* Store database connection.
41	*
42	* @var rcube_mdb2
43	*/
44	private $db = null;
45	private $user_id = 0;
46	private $filter = null;
47	private $result = null;
48	private $cache;
49	private $table_cols = array('name', 'email', 'firstname', 'surname');
50	private $fulltext_cols = array('name', 'firstname', 'surname', 'middlename', 'nickname',
51	'jobtitle', 'organization', 'department', 'maidenname', 'email', 'phone',
52	'address', 'street', 'locality', 'zipcode', 'region', 'country', 'website', 'im', 'notes');
53
54	// public properties
55	public $primary_key = 'contact_id';
56	public $name;
57	public $readonly = false;
58	public $groups = true;
59	public $undelete = true;
60	public $list_page = 1;
61	public $page_size = 10;
62	public $group_id = 0;
63	public $ready = false;
64	public $coltypes = array('name', 'firstname', 'surname', 'middlename', 'prefix', 'suffix', 'nickname',
65	'jobtitle', 'organization', 'department', 'assistant', 'manager',
66	'gender', 'maidenname', 'spouse', 'email', 'phone', 'address',
67	'birthday', 'anniversary', 'website', 'im', 'notes', 'photo');
68
69	const SEPARATOR = ',';
70
71
72	/**
73	* Object constructor
74	*
75	* @param object Instance of the rcube_db class
76	* @param integer User-ID
77	*/
78	function __construct($dbconn, $user)
79	{
80	$this->db = $dbconn;
81	$this->user_id = $user;
82	$this->ready = $this->db && !$this->db->is_error();
83	}
84
85
86	/**
87	* Returns addressbook name
88	*/
89	function get_name()
90	{
91	return $this->name;
92	}
93
94
95	/**
96	* Save a search string for future listings
97	*
98	* @param string SQL params to use in listing method
99	*/
100	function set_search_set($filter)
101	{
102	$this->filter = $filter;
103	$this->cache = null;
104	}
105
106
107	/**
108	* Getter for saved search properties
109	*
110	* @return mixed Search properties used by this class
111	*/
112	function get_search_set()
113	{
114	return $this->filter;
115	}
116
117
118	/**
119	* Setter for the current group
120	* (empty, has to be re-implemented by extending class)
121	*/
122	function set_group($gid)
123	{
124	$this->group_id = $gid;
125	$this->cache = null;
126	}
127
128
129	/**
130	* Reset all saved results and search parameters
131	*/
132	function reset()
133	{
134	$this->result = null;
135	$this->filter = null;
136	$this->cache = null;
137	}
138
139
140	/**
141	* List all active contact groups of this source
142	*
143	* @param string Search string to match group name
144	* @return array Indexed list of contact groups, each a hash array
145	*/
146	function list_groups($search = null)
147	{
148	$results = array();
149
150	if (!$this->groups)
151	return $results;
152
153	$sql_filter = $search ? " AND " . $this->db->ilike('name', '%'.$search.'%') : '';
154
155	$sql_result = $this->db->query(
156	"SELECT * FROM ".get_table_name($this->db_groups).
157	" WHERE del<>1".
158	" AND user_id=?".
159	$sql_filter.
160	" ORDER BY name",
161	$this->user_id);
162
163	while ($sql_result && ($sql_arr = $this->db->fetch_assoc($sql_result))) {
164	$sql_arr['ID'] = $sql_arr['contactgroup_id'];
165	$results[] = $sql_arr;
166	}
167
168	return $results;
169	}
170
171
172	/**
173	* Get group properties such as name and email address(es)
174	*
175	* @param string Group identifier
176	* @return array Group properties as hash array
177	*/
178	function get_group($group_id)
179	{
180	$sql_result = $this->db->query(
181	"SELECT * FROM ".get_table_name($this->db_groups).
182	" WHERE del<>1".
183	" AND contactgroup_id=?".
184	" AND user_id=?",
185	$group_id, $this->user_id);
186
187	if ($sql_result && ($sql_arr = $this->db->fetch_assoc($sql_result))) {
188	$sql_arr['ID'] = $sql_arr['contactgroup_id'];
189	return $sql_arr;
190	}
191
192	return null;
193	}
194
195	/**
196	* List the current set of contact records
197	*
198	* @param array List of cols to show, Null means all
199	* @param int Only return this number of records, use negative values for tail
200	* @param boolean True to skip the count query (select only)
201	* @return array Indexed list of contact records, each a hash array
202	*/
203	function list_records($cols=null, $subset=0, $nocount=false)
204	{
205	if ($nocount \|\| $this->list_page <= 1) {
206	// create dummy result, we don't need a count now
207	$this->result = new rcube_result_set();
208	} else {
209	// count all records
210	$this->result = $this->count();
211	}
212
213	$start_row = $subset < 0 ? $this->result->first + $this->page_size + $subset : $this->result->first;
214	$length = $subset != 0 ? abs($subset) : $this->page_size;
215
216	if ($this->group_id)
217	$join = " LEFT JOIN ".get_table_name($this->db_groupmembers)." AS m".
218	" ON (m.contact_id = c.".$this->primary_key.")";
219
220	$order_col = (in_array($this->sort_col, $this->table_cols) ? $this->sort_col : 'name');
221	$order_cols = array('c.'.$order_col);
222	if ($order_col == 'firstname')
223	$order_cols[] = 'c.surname';
224	else if ($order_col == 'surname')
225	$order_cols[] = 'c.firstname';
226	if ($order_col != 'name')
227	$order_cols[] = 'c.name';
228	$order_cols[] = 'c.email';
229
230	$sql_result = $this->db->limitquery(
231	"SELECT * FROM ".get_table_name($this->db_name)." AS c" .
232	$join .
233	" WHERE c.del<>1" .
234	" AND c.user_id=?" .
235	($this->group_id ? " AND m.contactgroup_id=?" : "").
236	($this->filter ? " AND (".$this->filter.")" : "") .
237	" ORDER BY ". $this->db->concat($order_cols) .
238	" " . $this->sort_order,
239	$start_row,
240	$length,
241	$this->user_id,
242	$this->group_id);
243
244	// determine whether we have to parse the vcard or if only db cols are requested
245	$read_vcard = !$cols \|\| count(array_intersect($cols, $this->table_cols)) < count($cols);
246
247	while ($sql_result && ($sql_arr = $this->db->fetch_assoc($sql_result))) {
248	$sql_arr['ID'] = $sql_arr[$this->primary_key];
249
250	if ($read_vcard)
251	$sql_arr = $this->convert_db_data($sql_arr);
252	else {
253	$sql_arr['email'] = explode(self::SEPARATOR, $sql_arr['email']);
254	$sql_arr['email'] = array_map('trim', $sql_arr['email']);
255	}
256
257	// make sure we have a name to display
258	if (empty($sql_arr['name'])) {
259	if (empty($sql_arr['email']))
260	$sql_arr['email'] = $this->get_col_values('email', $sql_arr, true);
261	$sql_arr['name'] = $sql_arr['email'][0];
262	}
263
264	$this->result->add($sql_arr);
265	}
266
267	$cnt = count($this->result->records);
268
269	// update counter
270	if ($nocount)
271	$this->result->count = $cnt;
272	else if ($this->list_page <= 1) {
273	if ($cnt < $this->page_size && $subset == 0)
274	$this->result->count = $cnt;
275	else if (isset($this->cache['count']))
276	$this->result->count = $this->cache['count'];
277	else
278	$this->result->count = $this->_count();
279	}
280
281	return $this->result;
282	}
283
284
285	/**
286	* Search contacts
287	*
288	* @param mixed $fields The field name of array of field names to search in
289	* @param mixed $value Search value (or array of values when $fields is array)
290	* @param int $mode Matching mode:
291	* 0 - partial (abc),
292	* 1 - strict (=),
293	* 2 - prefix (abc*)
294	* @param boolean $select True if results are requested, False if count only
295	* @param boolean $nocount True to skip the count query (select only)
296	* @param array $required List of fields that cannot be empty
297	*
298	* @return object rcube_result_set Contact records and 'count' value
299	*/
300	function search($fields, $value, $mode=0, $select=true, $nocount=false, $required=array())
301	{
302	if (!is_array($fields))
303	$fields = array($fields);
304	if (!is_array($required) && !empty($required))
305	$required = array($required);
306
307	$where = $and_where = array();
308	$mode = intval($mode);
309	$WS = ' ';
310	$AS = self::SEPARATOR;
311
312	foreach ($fields as $idx => $col) {
313	// direct ID search
314	if ($col == 'ID' \|\| $col == $this->primary_key) {
315	$ids = !is_array($value) ? explode(self::SEPARATOR, $value) : $value;
316	$ids = $this->db->array2list($ids, 'integer');
317	$where[] = 'c.' . $this->primary_key.' IN ('.$ids.')';
318	continue;
319	}
320	// fulltext search in all fields
321	else if ($col == '*') {
322	$words = array();
323	foreach (explode($WS, self::normalize_string($value)) as $word) {
324	switch ($mode) {
325	case 1: // strict
326	$words[] = '(' . $this->db->ilike('words', $word . '%')
327	. ' OR ' . $this->db->ilike('words', '%' . $WS . $word . $WS . '%')
328	. ' OR ' . $this->db->ilike('words', '%' . $WS . $word) . ')';
329	break;
330	case 2: // prefix
331	$words[] = '(' . $this->db->ilike('words', $word . '%')
332	. ' OR ' . $this->db->ilike('words', '%' . $WS . $word . '%') . ')';
333	break;
334	default: // partial
335	$words[] = $this->db->ilike('words', '%' . $word . '%');
336	}
337	}
338	$where[] = '(' . join(' AND ', $words) . ')';
339	}
340	else {
341	$val = is_array($value) ? $value[$idx] : $value;
342	// table column
343	if (in_array($col, $this->table_cols)) {
344	switch ($mode) {
345	case 1: // strict
346	$where[] = '(' . $this->db->quoteIdentifier($col) . ' = ' . $this->db->quote($val)
347	. ' OR ' . $this->db->ilike($col, $val . $AS . '%')
348	. ' OR ' . $this->db->ilike($col, '%' . $AS . $val . $AS . '%')
349	. ' OR ' . $this->db->ilike($col, '%' . $AS . $val) . ')';
350	break;
351	case 2: // prefix
352	$where[] = '(' . $this->db->ilike($col, $val . '%')
353	. ' OR ' . $this->db->ilike($col, $AS . $val . '%') . ')';
354	break;
355	default: // partial
356	$where[] = $this->db->ilike($col, '%' . $val . '%');
357	}
358	}
359	// vCard field
360	else {
361	if (in_array($col, $this->fulltext_cols)) {
362	foreach (explode(" ", self::normalize_string($val)) as $word) {
363	switch ($mode) {
364	case 1: // strict
365	$words[] = '(' . $this->db->ilike('words', $word . $WS . '%')
366	. ' OR ' . $this->db->ilike('words', '%' . $AS . $word . $WS .'%')
367	. ' OR ' . $this->db->ilike('words', '%' . $AS . $word) . ')';
368	break;
369	case 2: // prefix
370	$words[] = '(' . $this->db->ilike('words', $word . '%')
371	. ' OR ' . $this->db->ilike('words', $AS . $word . '%') . ')';
372	break;
373	default: // partial
374	$words[] = $this->db->ilike('words', '%' . $word . '%');
375	}
376	}
377	$where[] = '(' . join(' AND ', $words) . ')';
378	}
379	if (is_array($value))
380	$post_search[$col] = mb_strtolower($val);
381	}
382	}
383	}
384
385	foreach (array_intersect($required, $this->table_cols) as $col) {
386	$and_where[] = $this->db->quoteIdentifier($col).' <> '.$this->db->quote('');
387	}
388
389	if (!empty($where)) {
390	// use AND operator for advanced searches
391	$where = join(is_array($value) ? ' AND ' : ' OR ', $where);
392	}
393
394	if (!empty($and_where))
395	$where = ($where ? "($where) AND " : '') . join(' AND ', $and_where);
396
397	// Post-searching in vCard data fields
398	// we will search in all records and then build a where clause for their IDs
399	if (!empty($post_search)) {
400	$ids = array(0);
401	// build key name regexp
402	$regexp = '/^(' . implode(array_keys($post_search), '\|') . ')(?:.*)$/';
403	// use initial WHERE clause, to limit records number if possible
404	if (!empty($where))
405	$this->set_search_set($where);
406
407	// count result pages
408	$cnt = $this->count();
409	$pages = ceil($cnt / $this->page_size);
410	$scnt = count($post_search);
411
412	// get (paged) result
413	for ($i=0; $i<$pages; $i++) {
414	$this->list_records(null, $i, true);
415	while ($row = $this->result->next()) {
416	$id = $row[$this->primary_key];
417	$found = array();
418	foreach (preg_grep($regexp, array_keys($row)) as $col) {
419	$pos = strpos($col, ':');
420	$colname = $pos ? substr($col, 0, $pos) : $col;
421	$search = $post_search[$colname];
422	foreach ((array)$row[$col] as $value) {
423	// composite field, e.g. address
424	foreach ((array)$value as $val) {
425	$val = mb_strtolower($val);
426	switch ($mode) {
427	case 1:
428	$got = ($val == $search);
429	break;
430	case 2:
431	$got = ($search == substr($val, 0, strlen($search)));
432	break;
433	default:
434	$got = (strpos($val, $search) !== false);
435	break;
436	}
437
438	if ($got) {
439	$found[$colname] = true;
440	break 2;
441	}
442	}
443	}
444	}
445	// all fields match
446	if (count($found) >= $scnt) {
447	$ids[] = $id;
448	}
449	}
450	}
451
452	// build WHERE clause
453	$ids = $this->db->array2list($ids, 'integer');
454	$where = 'c.' . $this->primary_key.' IN ('.$ids.')';
455	// reset counter
456	unset($this->cache['count']);
457
458	// when we know we have an empty result
459	if ($ids == '0') {
460	$this->set_search_set($where);
461	return ($this->result = new rcube_result_set(0, 0));
462	}
463	}
464
465	if (!empty($where)) {
466	$this->set_search_set($where);
467	if ($select)
468	$this->list_records(null, 0, $nocount);
469	else
470	$this->result = $this->count();
471	}
472
473	return $this->result;
474	}
475
476
477	/**
478	* Count number of available contacts in database
479	*
480	* @return rcube_result_set Result object
481	*/
482	function count()
483	{
484	$count = isset($this->cache['count']) ? $this->cache['count'] : $this->_count();
485
486	return new rcube_result_set($count, ($this->list_page-1) * $this->page_size);
487	}
488
489
490	/**
491	* Count number of available contacts in database
492	*
493	* @return int Contacts count
494	*/
495	private function _count()
496	{
497	if ($this->group_id)
498	$join = " LEFT JOIN ".get_table_name($this->db_groupmembers)." AS m".
499	" ON (m.contact_id=c.".$this->primary_key.")";
500
501	// count contacts for this user
502	$sql_result = $this->db->query(
503	"SELECT COUNT(c.contact_id) AS rows".
504	" FROM ".get_table_name($this->db_name)." AS c".
505	$join.
506	" WHERE c.del<>1".
507	" AND c.user_id=?".
508	($this->group_id ? " AND m.contactgroup_id=?" : "").
509	($this->filter ? " AND (".$this->filter.")" : ""),
510	$this->user_id,
511	$this->group_id
512	);
513
514	$sql_arr = $this->db->fetch_assoc($sql_result);
515
516	$this->cache['count'] = (int) $sql_arr['rows'];
517
518	return $this->cache['count'];
519	}
520
521
522	/**
523	* Return the last result set
524	*
525	* @return mixed Result array or NULL if nothing selected yet
526	*/
527	function get_result()
528	{
529	return $this->result;
530	}
531
532
533	/**
534	* Get a specific contact record
535	*
536	* @param mixed record identifier(s)
537	* @return mixed Result object with all record fields or False if not found
538	*/
539	function get_record($id, $assoc=false)
540	{
541	// return cached result
542	if ($this->result && ($first = $this->result->first()) && $first[$this->primary_key] == $id)
543	return $assoc ? $first : $this->result;
544
545	$this->db->query(
546	"SELECT * FROM ".get_table_name($this->db_name).
547	" WHERE contact_id=?".
548	" AND user_id=?".
549	" AND del<>1",
550	$id,
551	$this->user_id
552	);
553
554	if ($sql_arr = $this->db->fetch_assoc()) {
555	$record = $this->convert_db_data($sql_arr);
556	$this->result = new rcube_result_set(1);
557	$this->result->add($record);
558	}
559
560	return $assoc && $record ? $record : $this->result;
561	}
562
563
564	/**
565	* Get group assignments of a specific contact record
566	*
567	* @param mixed Record identifier
568	* @return array List of assigned groups as ID=>Name pairs
569	*/
570	function get_record_groups($id)
571	{
572	$results = array();
573
574	if (!$this->groups)
575	return $results;
576
577	$sql_result = $this->db->query(
578	"SELECT cgm.contactgroup_id, cg.name FROM " . get_table_name($this->db_groupmembers) . " AS cgm" .
579	" LEFT JOIN " . get_table_name($this->db_groups) . " AS cg ON (cgm.contactgroup_id = cg.contactgroup_id AND cg.del<>1)" .
580	" WHERE cgm.contact_id=?",
581	$id
582	);
583	while ($sql_result && ($sql_arr = $this->db->fetch_assoc($sql_result))) {
584	$results[$sql_arr['contactgroup_id']] = $sql_arr['name'];
585	}
586
587	return $results;
588	}
589
590
591	/**
592	* Check the given data before saving.
593	* If input not valid, the message to display can be fetched using get_error()
594	*
595	* @param array Assoziative array with data to save
596	* @param boolean Try to fix/complete record automatically
597	* @return boolean True if input is valid, False if not.
598	*/
599	public function validate(&$save_data, $autofix = false)
600	{
601	// validate e-mail addresses
602	$valid = parent::validate($save_data, $autofix);
603
604	// require at least one e-mail address (syntax check is already done)
605	if ($valid && !array_filter($this->get_col_values('email', $save_data, true))) {
606	$this->set_error(self::ERROR_VALIDATE, 'noemailwarning');
607	$valid = false;
608	}
609
610	return $valid;
611	}
612
613
614	/**
615	* Create a new contact record
616	*
617	* @param array Associative array with save data
618	* @return integer\|boolean The created record ID on success, False on error
619	*/
620	function insert($save_data, $check=false)
621	{
622	if (!is_array($save_data))
623	return false;
624
625	$insert_id = $existing = false;
626
627	if ($check) {
628	foreach ($save_data as $col => $values) {
629	if (strpos($col, 'email') === 0) {
630	foreach ((array)$values as $email) {
631	if ($existing = $this->search('email', $email, false, false))
632	break 2;
633	}
634	}
635	}
636	}
637
638	$save_data = $this->convert_save_data($save_data);
639	$a_insert_cols = $a_insert_values = array();
640
641	foreach ($save_data as $col => $value) {
642	$a_insert_cols[] = $this->db->quoteIdentifier($col);
643	$a_insert_values[] = $this->db->quote($value);
644	}
645
646	if (!$existing->count && !empty($a_insert_cols)) {
647	$this->db->query(
648	"INSERT INTO ".get_table_name($this->db_name).
649	" (user_id, changed, del, ".join(', ', $a_insert_cols).")".
650	" VALUES (".intval($this->user_id).", ".$this->db->now().", 0, ".join(', ', $a_insert_values).")"
651	);
652
653	$insert_id = $this->db->insert_id($this->db_name);
654	}
655
656	// also add the newly created contact to the active group
657	if ($insert_id && $this->group_id)
658	$this->add_to_group($this->group_id, $insert_id);
659
660	$this->cache = null;
661
662	return $insert_id;
663	}
664
665
666	/**
667	* Update a specific contact record
668	*
669	* @param mixed Record identifier
670	* @param array Assoziative array with save data
671	* @return boolean True on success, False on error
672	*/
673	function update($id, $save_cols)
674	{
675	$updated = false;
676	$write_sql = array();
677	$record = $this->get_record($id, true);
678	$save_cols = $this->convert_save_data($save_cols, $record);
679
680	foreach ($save_cols as $col => $value) {
681	$write_sql[] = sprintf("%s=%s", $this->db->quoteIdentifier($col), $this->db->quote($value));
682	}
683
684	if (!empty($write_sql)) {
685	$this->db->query(
686	"UPDATE ".get_table_name($this->db_name).
687	" SET changed=".$this->db->now().", ".join(', ', $write_sql).
688	" WHERE contact_id=?".
689	" AND user_id=?".
690	" AND del<>1",
691	$id,
692	$this->user_id
693	);
694
695	$updated = $this->db->affected_rows();
696	$this->result = null; // clear current result (from get_record())
697	}
698
699	return $updated;
700	}
701
702
703	private function convert_db_data($sql_arr)
704	{
705	$record = array();
706	$record['ID'] = $sql_arr[$this->primary_key];
707
708	if ($sql_arr['vcard']) {
709	unset($sql_arr['email']);
710	$vcard = new rcube_vcard($sql_arr['vcard'], RCMAIL_CHARSET, false, $this->vcard_fieldmap);
711	$record += $vcard->get_assoc() + $sql_arr;
712	}
713	else {
714	$record += $sql_arr;
715	$record['email'] = explode(self::SEPARATOR, $record['email']);
716	$record['email'] = array_map('trim', $record['email']);
717	}
718
719	return $record;
720	}
721
722
723	private function convert_save_data($save_data, $record = array())
724	{
725	$out = array();
726	$words = '';
727
728	// copy values into vcard object
729	$vcard = new rcube_vcard($record['vcard'] ? $record['vcard'] : $save_data['vcard'], RCMAIL_CHARSET, false, $this->vcard_fieldmap);
730	$vcard->reset();
731	foreach ($save_data as $key => $values) {
732	list($field, $section) = explode(':', $key);
733	$fulltext = in_array($field, $this->fulltext_cols);
734	foreach ((array)$values as $value) {
735	if (isset($value))
736	$vcard->set($field, $value, $section);
737	if ($fulltext && is_array($value))
738	$words .= ' ' . self::normalize_string(join(" ", $value));
739	else if ($fulltext && strlen($value) >= 3)
740	$words .= ' ' . self::normalize_string($value);
741	}
742	}
743	$out['vcard'] = $vcard->export(false);
744
745	foreach ($this->table_cols as $col) {
746	$key = $col;
747	if (!isset($save_data[$key]))
748	$key .= ':home';
749	if (isset($save_data[$key])) {
750	if (is_array($save_data[$key]))
751	$out[$col] = join(self::SEPARATOR, $save_data[$key]);
752	else
753	$out[$col] = $save_data[$key];
754	}
755	}
756
757	// save all e-mails in database column
758	$out['email'] = join(self::SEPARATOR, $vcard->email);
759
760	// join words for fulltext search
761	$out['words'] = join(" ", array_unique(explode(" ", $words)));
762
763	return $out;
764	}
765
766
767	/**
768	* Mark one or more contact records as deleted
769	*
770	* @param array Record identifiers
771	* @param boolean Remove record(s) irreversible (unsupported)
772	*/
773	function delete($ids, $force=true)
774	{
775	if (!is_array($ids))
776	$ids = explode(self::SEPARATOR, $ids);
777
778	$ids = $this->db->array2list($ids, 'integer');
779
780	// flag record as deleted (always)
781	$this->db->query(
782	"UPDATE ".get_table_name($this->db_name).
783	" SET del=1, changed=".$this->db->now().
784	" WHERE user_id=?".
785	" AND contact_id IN ($ids)",
786	$this->user_id
787	);
788
789	$this->cache = null;
790
791	return $this->db->affected_rows();
792	}
793
794
795	/**
796	* Undelete one or more contact records
797	*
798	* @param array Record identifiers
799	*/
800	function undelete($ids)
801	{
802	if (!is_array($ids))
803	$ids = explode(self::SEPARATOR, $ids);
804
805	$ids = $this->db->array2list($ids, 'integer');
806
807	// clear deleted flag
808	$this->db->query(
809	"UPDATE ".get_table_name($this->db_name).
810	" SET del=0, changed=".$this->db->now().
811	" WHERE user_id=?".
812	" AND contact_id IN ($ids)",
813	$this->user_id
814	);
815
816	$this->cache = null;
817
818	return $this->db->affected_rows();
819	}
820
821
822	/**
823	* Remove all records from the database
824	*/
825	function delete_all()
826	{
827	$this->cache = null;
828
829	$this->db->query("UPDATE ".get_table_name($this->db_name).
830	" SET del=1, changed=".$this->db->now().
831	" WHERE user_id = ?", $this->user_id);
832
833	return $this->db->affected_rows();
834	}
835
836
837	/**
838	* Create a contact group with the given name
839	*
840	* @param string The group name
841	* @return mixed False on error, array with record props in success
842	*/
843	function create_group($name)
844	{
845	$result = false;
846
847	// make sure we have a unique name
848	$name = $this->unique_groupname($name);
849
850	$this->db->query(
851	"INSERT INTO ".get_table_name($this->db_groups).
852	" (user_id, changed, name)".
853	" VALUES (".intval($this->user_id).", ".$this->db->now().", ".$this->db->quote($name).")"
854	);
855
856	if ($insert_id = $this->db->insert_id($this->db_groups))
857	$result = array('id' => $insert_id, 'name' => $name);
858
859	return $result;
860	}
861
862
863	/**
864	* Delete the given group (and all linked group members)
865	*
866	* @param string Group identifier
867	* @return boolean True on success, false if no data was changed
868	*/
869	function delete_group($gid)
870	{
871	// flag group record as deleted
872	$sql_result = $this->db->query(
873	"UPDATE ".get_table_name($this->db_groups).
874	" SET del=1, changed=".$this->db->now().
875	" WHERE contactgroup_id=?".
876	" AND user_id=?",
877	$gid, $this->user_id
878	);
879
880	$this->cache = null;
881
882	return $this->db->affected_rows();
883	}
884
885
886	/**
887	* Rename a specific contact group
888	*
889	* @param string Group identifier
890	* @param string New name to set for this group
891	* @return boolean New name on success, false if no data was changed
892	*/
893	function rename_group($gid, $newname)
894	{
895	// make sure we have a unique name
896	$name = $this->unique_groupname($newname);
897
898	$sql_result = $this->db->query(
899	"UPDATE ".get_table_name($this->db_groups).
900	" SET name=?, changed=".$this->db->now().
901	" WHERE contactgroup_id=?".
902	" AND user_id=?",
903	$name, $gid, $this->user_id
904	);
905
906	return $this->db->affected_rows() ? $name : false;
907	}
908
909
910	/**
911	* Add the given contact records the a certain group
912	*
913	* @param string Group identifier
914	* @param array List of contact identifiers to be added
915	* @return int Number of contacts added
916	*/
917	function add_to_group($group_id, $ids)
918	{
919	if (!is_array($ids))
920	$ids = explode(self::SEPARATOR, $ids);
921
922	$added = 0;
923	$exists = array();
924
925	// get existing assignments ...
926	$sql_result = $this->db->query(
927	"SELECT contact_id FROM ".get_table_name($this->db_groupmembers).
928	" WHERE contactgroup_id=?".
929	" AND contact_id IN (".$this->db->array2list($ids, 'integer').")",
930	$group_id
931	);
932	while ($sql_result && ($sql_arr = $this->db->fetch_assoc($sql_result))) {
933	$exists[] = $sql_arr['contact_id'];
934	}
935	// ... and remove them from the list
936	$ids = array_diff($ids, $exists);
937
938	foreach ($ids as $contact_id) {
939	$this->db->query(
940	"INSERT INTO ".get_table_name($this->db_groupmembers).
941	" (contactgroup_id, contact_id, created)".
942	" VALUES (?, ?, ".$this->db->now().")",
943	$group_id,
944	$contact_id
945	);
946
947	if (!$this->db->db_error)
948	$added++;
949	}
950
951	return $added;
952	}
953
954
955	/**
956	* Remove the given contact records from a certain group
957	*
958	* @param string Group identifier
959	* @param array List of contact identifiers to be removed
960	* @return int Number of deleted group members
961	*/
962	function remove_from_group($group_id, $ids)
963	{
964	if (!is_array($ids))
965	$ids = explode(self::SEPARATOR, $ids);
966
967	$ids = $this->db->array2list($ids, 'integer');
968
969	$sql_result = $this->db->query(
970	"DELETE FROM ".get_table_name($this->db_groupmembers).
971	" WHERE contactgroup_id=?".
972	" AND contact_id IN ($ids)",
973	$group_id
974	);
975
976	return $this->db->affected_rows();
977	}
978
979
980	/**
981	* Check for existing groups with the same name
982	*
983	* @param string Name to check
984	* @return string A group name which is unique for the current use
985	*/
986	private function unique_groupname($name)
987	{
988	$checkname = $name;
989	$num = 2; $hit = false;
990
991	do {
992	$sql_result = $this->db->query(
993	"SELECT 1 FROM ".get_table_name($this->db_groups).
994	" WHERE del<>1".
995	" AND user_id=?".
996	" AND name=?",
997	$this->user_id,
998	$checkname);
999
1000	// append number to make name unique
1001	if ($hit = $this->db->num_rows($sql_result))
1002	$checkname = $name . ' ' . $num++;
1003	} while ($hit > 0);
1004
1005	return $checkname;
1006	}
1007
1008	}

Note: See TracBrowser for help on using the repository browser.

Download in other formats: