4 +-----------------------------------------------------------------------+
5 | program/include/rcube_contacts.php |
7 | This file is part of the Roundcube Webmail client |
8 | Copyright (C) 2006-2010, Roundcube Dev. - Switzerland |
9 | Licensed under the GNU GPL |
12 | Interface to the local address book database |
14 +-----------------------------------------------------------------------+
15 | Author: Thomas Bruederli <roundcube@gmail.com> |
16 +-----------------------------------------------------------------------+
18 $Id: rcube_contacts.php 4145 2010-10-27 07:23:57Z alec $
24 * Model class for the local address book database
26 * @package Addressbook
28 class rcube_contacts extends rcube_addressbook
30 // protected for backward compat. with some plugins
31 protected $db_name = 'contacts';
32 protected $db_groups = 'contactgroups';
33 protected $db_groupmembers = 'contactgroupmembers';
36 * Store database connection.
42 private $filter = null;
43 private $result = null;
44 private $search_fields;
45 private $search_string;
47 private $table_cols = array('name', 'email', 'firstname', 'surname', 'vcard');
50 var $primary_key = 'contact_id';
51 var $readonly = false;
62 * @param object Instance of the rcube_db class
63 * @param integer User-ID
65 function __construct($dbconn, $user)
68 $this->user_id = $user;
69 $this->ready = $this->db && !$this->db->is_error();
74 * Save a search string for future listings
76 * @param string SQL params to use in listing method
78 function set_search_set($filter)
80 $this->filter = $filter;
86 * Getter for saved search properties
88 * @return mixed Search properties used by this class
90 function get_search_set()
97 * Setter for the current group
98 * (empty, has to be re-implemented by extending class)
100 function set_group($gid)
102 $this->group_id = $gid;
108 * Reset all saved results and search parameters
112 $this->result = null;
113 $this->filter = null;
114 $this->search_fields = null;
115 $this->search_string = null;
121 * List all active contact groups of this source
123 * @param string Search string to match group name
124 * @return array Indexed list of contact groups, each a hash array
126 function list_groups($search = null)
133 $sql_filter = $search ? " AND " . $this->db->ilike('name', '%'.$search.'%') : '';
135 $sql_result = $this->db->query(
136 "SELECT * FROM ".get_table_name($this->db_groups).
143 while ($sql_result && ($sql_arr = $this->db->fetch_assoc($sql_result))) {
144 $sql_arr['ID'] = $sql_arr['contactgroup_id'];
145 $results[] = $sql_arr;
153 * List the current set of contact records
155 * @param array List of cols to show
156 * @param int Only return this number of records, use negative values for tail
157 * @param boolean True to skip the count query (select only)
158 * @return array Indexed list of contact records, each a hash array
160 function list_records($cols=null, $subset=0, $nocount=false)
162 if ($nocount || $this->list_page <= 1) {
163 // create dummy result, we don't need a count now
164 $this->result = new rcube_result_set();
167 $this->result = $this->count();
170 $start_row = $subset < 0 ? $this->result->first + $this->page_size + $subset : $this->result->first;
171 $length = $subset != 0 ? abs($subset) : $this->page_size;
174 $join = " LEFT JOIN ".get_table_name($this->db_groupmembers)." AS m".
175 " ON (m.contact_id = c.".$this->primary_key.")";
177 $sql_result = $this->db->limitquery(
178 "SELECT * FROM ".get_table_name($this->db_name)." AS c" .
182 ($this->group_id ? " AND m.contactgroup_id=?" : "").
183 ($this->filter ? " AND (".$this->filter.")" : "") .
190 while ($sql_result && ($sql_arr = $this->db->fetch_assoc($sql_result))) {
191 $sql_arr['ID'] = $sql_arr[$this->primary_key];
192 // make sure we have a name to display
193 if (empty($sql_arr['name']))
194 $sql_arr['name'] = $sql_arr['email'];
195 $this->result->add($sql_arr);
198 $cnt = count($this->result->records);
202 $this->result->count = $cnt;
203 else if ($this->list_page <= 1) {
204 if ($cnt < $this->page_size && $subset == 0)
205 $this->result->count = $cnt;
206 else if (isset($this->cache['count']))
207 $this->result->count = $this->cache['count'];
209 $this->result->count = $this->_count();
212 return $this->result;
219 * @param array List of fields to search in
220 * @param string Search value
221 * @param boolean True for strict (=), False for partial (LIKE) matching
222 * @param boolean True if results are requested, False if count only
223 * @param boolean True to skip the count query (select only)
224 * @param array List of fields that cannot be empty
225 * @return Indexed list of contact records and 'count' value
227 function search($fields, $value, $strict=false, $select=true, $nocount=false, $required=array())
229 if (!is_array($fields))
230 $fields = array($fields);
231 if (!is_array($required) && !empty($required))
232 $required = array($required);
234 $where = $and_where = array();
236 foreach ($fields as $col) {
237 if ($col == 'ID' || $col == $this->primary_key) {
238 $ids = !is_array($value) ? explode(',', $value) : $value;
239 $ids = $this->db->array2list($ids, 'integer');
240 $where[] = 'c.' . $this->primary_key.' IN ('.$ids.')';
243 $where[] = $this->db->quoteIdentifier($col).' = '.$this->db->quote($value);
245 $where[] = $this->db->ilike($col, '%'.$value.'%');
248 foreach ($required as $col) {
249 $and_where[] = $this->db->quoteIdentifier($col).' <> '.$this->db->quote('');
253 $where = join(' OR ', $where);
255 if (!empty($and_where))
256 $where = ($where ? "($where) AND " : '') . join(' AND ', $and_where);
258 if (!empty($where)) {
259 $this->set_search_set($where);
261 $this->list_records(null, 0, $nocount);
263 $this->result = $this->count();
266 return $this->result;
271 * Count number of available contacts in database
273 * @return rcube_result_set Result object
277 $count = isset($this->cache['count']) ? $this->cache['count'] : $this->_count();
279 return new rcube_result_set($count, ($this->list_page-1) * $this->page_size);
284 * Count number of available contacts in database
286 * @return int Contacts count
288 private function _count()
291 $join = " LEFT JOIN ".get_table_name($this->db_groupmembers)." AS m".
292 " ON (m.contact_id=c.".$this->primary_key.")";
294 // count contacts for this user
295 $sql_result = $this->db->query(
296 "SELECT COUNT(c.contact_id) AS rows".
297 " FROM ".get_table_name($this->db_name)." AS c".
301 ($this->group_id ? " AND m.contactgroup_id=?" : "").
302 ($this->filter ? " AND (".$this->filter.")" : ""),
307 $sql_arr = $this->db->fetch_assoc($sql_result);
309 $this->cache['count'] = (int) $sql_arr['rows'];
311 return $this->cache['count'];
316 * Return the last result set
318 * @return mixed Result array or NULL if nothing selected yet
320 function get_result()
322 return $this->result;
327 * Get a specific contact record
329 * @param mixed record identifier(s)
330 * @return mixed Result object with all record fields or False if not found
332 function get_record($id, $assoc=false)
334 // return cached result
335 if ($this->result && ($first = $this->result->first()) && $first[$this->primary_key] == $id)
336 return $assoc ? $first : $this->result;
339 "SELECT * FROM ".get_table_name($this->db_name).
340 " WHERE contact_id=?".
347 if ($sql_arr = $this->db->fetch_assoc()) {
348 $sql_arr['ID'] = $sql_arr[$this->primary_key];
349 $this->result = new rcube_result_set(1);
350 $this->result->add($sql_arr);
353 return $assoc && $sql_arr ? $sql_arr : $this->result;
358 * Get group assignments of a specific contact record
360 * @param mixed Record identifier
361 * @return array List of assigned groups as ID=>Name pairs
363 function get_record_groups($id)
370 $sql_result = $this->db->query(
371 "SELECT cgm.contactgroup_id, cg.name FROM " . get_table_name($this->db_groupmembers) . " AS cgm" .
372 " LEFT JOIN " . get_table_name($this->db_groups) . " AS cg ON (cgm.contactgroup_id = cg.contactgroup_id AND cg.del<>1)" .
373 " WHERE cgm.contact_id=?",
376 while ($sql_result && ($sql_arr = $this->db->fetch_assoc($sql_result))) {
377 $results[$sql_arr['contactgroup_id']] = $sql_arr['name'];
385 * Create a new contact record
387 * @param array Associative array with save data
388 * @return integer|boolean The created record ID on success, False on error
390 function insert($save_data, $check=false)
392 if (is_object($save_data) && is_a($save_data, rcube_result_set))
393 return $this->insert_recset($save_data, $check);
395 $insert_id = $existing = false;
398 $existing = $this->search('email', $save_data['email'], true, false);
400 $a_insert_cols = $a_insert_values = array();
402 foreach ($this->table_cols as $col)
403 if (isset($save_data[$col])) {
404 $a_insert_cols[] = $this->db->quoteIdentifier($col);
405 $a_insert_values[] = $this->db->quote($save_data[$col]);
408 if (!$existing->count && !empty($a_insert_cols)) {
410 "INSERT INTO ".get_table_name($this->db_name).
411 " (user_id, changed, del, ".join(', ', $a_insert_cols).")".
412 " VALUES (".intval($this->user_id).", ".$this->db->now().", 0, ".join(', ', $a_insert_values).")"
415 $insert_id = $this->db->insert_id($this->db_name);
418 // also add the newly created contact to the active group
419 if ($insert_id && $this->group_id)
420 $this->add_to_group($this->group_id, $insert_id);
429 * Insert new contacts for each row in set
431 function insert_recset($result, $check=false)
434 while ($row = $result->next()) {
435 if ($insert = $this->insert($row, $check))
443 * Update a specific contact record
445 * @param mixed Record identifier
446 * @param array Assoziative array with save data
447 * @return boolean True on success, False on error
449 function update($id, $save_cols)
452 $write_sql = array();
454 foreach ($this->table_cols as $col)
455 if (isset($save_cols[$col]))
456 $write_sql[] = sprintf("%s=%s", $this->db->quoteIdentifier($col),
457 $this->db->quote($save_cols[$col]));
459 if (!empty($write_sql)) {
461 "UPDATE ".get_table_name($this->db_name).
462 " SET changed=".$this->db->now().", ".join(', ', $write_sql).
463 " WHERE contact_id=?".
470 $updated = $this->db->affected_rows();
478 * Mark one or more contact records as deleted
480 * @param array Record identifiers
482 function delete($ids)
485 $ids = explode(',', $ids);
487 $ids = $this->db->array2list($ids, 'integer');
489 // flag record as deleted
491 "UPDATE ".get_table_name($this->db_name).
492 " SET del=1, changed=".$this->db->now().
494 " AND contact_id IN ($ids)",
500 return $this->db->affected_rows();
505 * Remove all records from the database
507 function delete_all()
509 $this->db->query("DELETE FROM ".get_table_name($this->db_name)." WHERE user_id = ?", $this->user_id);
511 return $this->db->affected_rows();
516 * Create a contact group with the given name
518 * @param string The group name
519 * @return mixed False on error, array with record props in success
521 function create_group($name)
525 // make sure we have a unique name
526 $name = $this->unique_groupname($name);
529 "INSERT INTO ".get_table_name($this->db_groups).
530 " (user_id, changed, name)".
531 " VALUES (".intval($this->user_id).", ".$this->db->now().", ".$this->db->quote($name).")"
534 if ($insert_id = $this->db->insert_id($this->db_groups))
535 $result = array('id' => $insert_id, 'name' => $name);
542 * Delete the given group (and all linked group members)
544 * @param string Group identifier
545 * @return boolean True on success, false if no data was changed
547 function delete_group($gid)
549 // flag group record as deleted
550 $sql_result = $this->db->query(
551 "UPDATE ".get_table_name($this->db_groups).
552 " SET del=1, changed=".$this->db->now().
553 " WHERE contactgroup_id=?",
559 return $this->db->affected_rows();
564 * Rename a specific contact group
566 * @param string Group identifier
567 * @param string New name to set for this group
568 * @return boolean New name on success, false if no data was changed
570 function rename_group($gid, $newname)
572 // make sure we have a unique name
573 $name = $this->unique_groupname($newname);
575 $sql_result = $this->db->query(
576 "UPDATE ".get_table_name($this->db_groups).
577 " SET name=?, changed=".$this->db->now().
578 " WHERE contactgroup_id=?",
582 return $this->db->affected_rows() ? $name : false;
587 * Add the given contact records the a certain group
589 * @param string Group identifier
590 * @param array List of contact identifiers to be added
591 * @return int Number of contacts added
593 function add_to_group($group_id, $ids)
596 $ids = explode(',', $ids);
601 // get existing assignments ...
602 $sql_result = $this->db->query(
603 "SELECT contact_id FROM ".get_table_name($this->db_groupmembers).
604 " WHERE contactgroup_id=?".
605 " AND contact_id IN (".$this->db->array2list($ids, 'integer').")",
608 while ($sql_result && ($sql_arr = $this->db->fetch_assoc($sql_result))) {
609 $exists[] = $sql_arr['contact_id'];
611 // ... and remove them from the list
612 $ids = array_diff($ids, $exists);
614 foreach ($ids as $contact_id) {
616 "INSERT INTO ".get_table_name($this->db_groupmembers).
617 " (contactgroup_id, contact_id, created)".
618 " VALUES (?, ?, ".$this->db->now().")",
623 if (!$this->db->db_error)
632 * Remove the given contact records from a certain group
634 * @param string Group identifier
635 * @param array List of contact identifiers to be removed
636 * @return int Number of deleted group members
638 function remove_from_group($group_id, $ids)
641 $ids = explode(',', $ids);
643 $ids = $this->db->array2list($ids, 'integer');
645 $sql_result = $this->db->query(
646 "DELETE FROM ".get_table_name($this->db_groupmembers).
647 " WHERE contactgroup_id=?".
648 " AND contact_id IN ($ids)",
652 return $this->db->affected_rows();
657 * Check for existing groups with the same name
659 * @param string Name to check
660 * @return string A group name which is unique for the current use
662 private function unique_groupname($name)
665 $num = 2; $hit = false;
668 $sql_result = $this->db->query(
669 "SELECT 1 FROM ".get_table_name($this->db_groups).
676 // append number to make name unique
677 if ($hit = $this->db->num_rows($sql_result))
678 $checkname = $name . ' ' . $num++;