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