Mercurial > hg > rc2
annotate program/lib/Roundcube/rcube_contacts.php @ 25:bea5a38be938
More cleaning up php8 Warnings/deprecations
| author | Charlie Root |
|---|---|
| date | Sat, 18 Oct 2025 12:24:31 -0400 |
| parents | aff04b06b685 |
| 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 { | |
| 25 | 206 $join = ""; |
| 0 | 207 if ($nocount || $this->list_page <= 1) { |
| 208 // create dummy result, we don't need a count now | |
| 209 $this->result = new rcube_result_set(); | |
| 210 } else { | |
| 211 // count all records | |
| 212 $this->result = $this->count(); | |
| 213 } | |
| 214 | |
| 215 $start_row = $subset < 0 ? $this->result->first + $this->page_size + $subset : $this->result->first; | |
| 216 $length = $subset != 0 ? abs($subset) : $this->page_size; | |
| 217 | |
| 218 if ($this->group_id) | |
| 219 $join = " LEFT JOIN " . $this->db->table_name($this->db_groupmembers, true) . " AS m". | |
| 220 " ON (m.`contact_id` = c.`".$this->primary_key."`)"; | |
| 221 | |
| 222 $order_col = (in_array($this->sort_col, $this->table_cols) ? $this->sort_col : 'name'); | |
| 223 $order_cols = array("c.`$order_col`"); | |
| 224 if ($order_col == 'firstname') | |
| 225 $order_cols[] = 'c.`surname`'; | |
| 226 else if ($order_col == 'surname') | |
| 227 $order_cols[] = 'c.`firstname`'; | |
| 228 if ($order_col != 'name') | |
| 229 $order_cols[] = 'c.`name`'; | |
| 230 $order_cols[] = 'c.`email`'; | |
| 231 | |
| 232 $sql_result = $this->db->limitquery( | |
| 233 "SELECT * FROM " . $this->db->table_name($this->db_name, true) . " AS c" . | |
| 234 $join . | |
| 235 " WHERE c.`del` <> 1" . | |
| 236 " AND c.`user_id` = ?" . | |
| 237 ($this->group_id ? " AND m.`contactgroup_id` = ?" : ""). | |
| 238 ($this->filter ? " AND ".$this->filter : "") . | |
| 239 " ORDER BY ". $this->db->concat($order_cols) . | |
| 240 " " . $this->sort_order, | |
| 241 $start_row, | |
| 242 $length, | |
| 243 $this->user_id, | |
| 244 $this->group_id); | |
| 245 | |
| 246 // determine whether we have to parse the vcard or if only db cols are requested | |
| 247 $read_vcard = !$cols || count(array_intersect($cols, $this->table_cols)) < count($cols); | |
| 248 | |
| 249 while ($sql_result && ($sql_arr = $this->db->fetch_assoc($sql_result))) { | |
| 250 $sql_arr['ID'] = $sql_arr[$this->primary_key]; | |
| 251 | |
| 252 if ($read_vcard) | |
| 253 $sql_arr = $this->convert_db_data($sql_arr); | |
| 254 else { | |
| 255 $sql_arr['email'] = $sql_arr['email'] ? explode(self::SEPARATOR, $sql_arr['email']) : array(); | |
| 256 $sql_arr['email'] = array_map('trim', $sql_arr['email']); | |
| 257 } | |
| 258 | |
| 259 $this->result->add($sql_arr); | |
| 260 } | |
| 261 | |
| 262 $cnt = count($this->result->records); | |
| 263 | |
| 264 // update counter | |
| 265 if ($nocount) | |
| 266 $this->result->count = $cnt; | |
| 267 else if ($this->list_page <= 1) { | |
| 268 if ($cnt < $this->page_size && $subset == 0) | |
| 269 $this->result->count = $cnt; | |
| 270 else if (isset($this->cache['count'])) | |
| 271 $this->result->count = $this->cache['count']; | |
| 272 else | |
| 273 $this->result->count = $this->_count(); | |
| 274 } | |
| 275 | |
| 276 return $this->result; | |
| 277 } | |
| 278 | |
| 279 /** | |
| 280 * Search contacts | |
| 281 * | |
| 282 * @param mixed $fields The field name or array of field names to search in | |
| 283 * @param mixed $value Search value (or array of values when $fields is array) | |
| 284 * @param int $mode Search mode. Sum of rcube_addressbook::SEARCH_* | |
| 285 * @param boolean $select True if results are requested, False if count only | |
| 286 * @param boolean $nocount True to skip the count query (select only) | |
| 287 * @param array $required List of fields that cannot be empty | |
| 288 * | |
| 289 * @return object rcube_result_set Contact records and 'count' value | |
| 290 */ | |
| 291 function search($fields, $value, $mode = 0, $select = true, $nocount = false, $required = array()) | |
| 292 { | |
| 293 if (!is_array($required) && !empty($required)) { | |
| 294 $required = array($required); | |
| 295 } | |
| 296 | |
| 297 $where = $and_where = $post_search = array(); | |
| 298 $mode = intval($mode); | |
| 299 $WS = ' '; | |
| 300 $AS = self::SEPARATOR; | |
| 301 | |
| 302 // direct ID search | |
| 303 if ($fields == 'ID' || $fields == $this->primary_key) { | |
| 304 $ids = !is_array($value) ? explode(self::SEPARATOR, $value) : $value; | |
| 305 $ids = $this->db->array2list($ids, 'integer'); | |
| 306 $where[] = 'c.' . $this->primary_key.' IN ('.$ids.')'; | |
| 307 } | |
| 308 else if (is_array($value)) { | |
| 309 foreach ((array)$fields as $idx => $col) { | |
| 310 $val = $value[$idx]; | |
| 311 | |
| 312 if (!strlen($val)) { | |
| 313 continue; | |
| 314 } | |
| 315 | |
| 316 // table column | |
| 317 if (in_array($col, $this->table_cols)) { | |
| 318 $where[] = $this->fulltext_sql_where($val, $mode, $col); | |
| 319 } | |
| 320 // vCard field | |
| 321 else { | |
| 322 if (in_array($col, $this->fulltext_cols)) { | |
| 323 $where[] = $this->fulltext_sql_where($val, $mode, 'words'); | |
| 324 } | |
| 325 $post_search[$col] = mb_strtolower($val); | |
| 326 } | |
| 327 } | |
| 328 } | |
| 329 // fulltext search in all fields | |
| 330 else if ($fields == '*') { | |
| 331 $where[] = $this->fulltext_sql_where($value, $mode, 'words'); | |
| 332 } | |
| 333 else { | |
| 334 // require each word in to be present in one of the fields | |
| 335 $words = ($mode & rcube_addressbook::SEARCH_STRICT) ? array($value) : rcube_utils::tokenize_string($value, 1); | |
| 336 foreach ($words as $word) { | |
| 337 $groups = array(); | |
| 338 foreach ((array)$fields as $idx => $col) { | |
| 339 $groups[] = $this->fulltext_sql_where($word, $mode, $col); | |
| 340 } | |
| 341 $where[] = '(' . join(' OR ', $groups) . ')'; | |
| 342 } | |
| 343 } | |
| 344 | |
| 345 foreach (array_intersect($required, $this->table_cols) as $col) { | |
| 346 $where[] = $this->db->quote_identifier($col).' <> '.$this->db->quote(''); | |
| 347 } | |
| 348 $required = array_diff($required, $this->table_cols); | |
| 349 | |
| 350 if (!empty($where)) { | |
| 351 // use AND operator for advanced searches | |
| 352 $where = join(" AND ", $where); | |
| 353 } | |
| 354 | |
| 355 // Post-searching in vCard data fields | |
| 356 // we will search in all records and then build a where clause for their IDs | |
| 357 if (!empty($post_search) || !empty($required)) { | |
| 358 $ids = array(0); | |
| 359 // 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
|
360 $regexp = '/^(' . implode('|', array_keys($post_search)) . ')(?:.*)$/'; |
| 0 | 361 // use initial WHERE clause, to limit records number if possible |
| 362 if (!empty($where)) | |
| 363 $this->set_search_set($where); | |
| 364 | |
| 365 // count result pages | |
| 366 $cnt = $this->count()->count; | |
| 367 $pages = ceil($cnt / $this->page_size); | |
| 368 $scnt = !empty($post_search) ? count($post_search) : 0; | |
| 369 | |
| 370 // get (paged) result | |
| 371 for ($i=0; $i<$pages; $i++) { | |
| 372 $this->list_records(null, $i, true); | |
| 373 while ($row = $this->result->next()) { | |
| 374 $id = $row[$this->primary_key]; | |
| 375 $found = array(); | |
| 376 if (!empty($post_search)) { | |
| 377 foreach (preg_grep($regexp, array_keys($row)) as $col) { | |
| 378 $pos = strpos($col, ':'); | |
| 379 $colname = $pos ? substr($col, 0, $pos) : $col; | |
| 380 $search = $post_search[$colname]; | |
| 381 foreach ((array)$row[$col] as $value) { | |
| 382 if ($this->compare_search_value($colname, $value, $search, $mode)) { | |
| 383 $found[$colname] = true; | |
| 384 break 2; | |
| 385 } | |
| 386 } | |
| 387 } | |
| 388 } | |
| 389 // check if required fields are present | |
| 390 if (!empty($required)) { | |
| 391 foreach ($required as $req) { | |
| 392 $hit = false; | |
| 393 foreach ($row as $c => $values) { | |
| 394 if ($c === $req || strpos($c, $req.':') === 0) { | |
| 395 if ((is_string($row[$c]) && strlen($row[$c])) || !empty($row[$c])) { | |
| 396 $hit = true; | |
| 397 break; | |
| 398 } | |
| 399 } | |
| 400 } | |
| 401 if (!$hit) { | |
| 402 continue 2; | |
| 403 } | |
| 404 } | |
| 405 } | |
| 406 // all fields match | |
| 407 if (count($found) >= $scnt) { | |
| 408 $ids[] = $id; | |
| 409 } | |
| 410 } | |
| 411 } | |
| 412 | |
| 413 // build WHERE clause | |
| 414 $ids = $this->db->array2list($ids, 'integer'); | |
| 415 $where = 'c.`' . $this->primary_key.'` IN ('.$ids.')'; | |
| 416 // reset counter | |
| 417 unset($this->cache['count']); | |
| 418 | |
| 419 // when we know we have an empty result | |
| 420 if ($ids == '0') { | |
| 421 $this->set_search_set($where); | |
| 422 return ($this->result = new rcube_result_set(0, 0)); | |
| 423 } | |
| 424 } | |
| 425 | |
| 426 if (!empty($where)) { | |
| 427 $this->set_search_set($where); | |
| 428 if ($select) | |
| 429 $this->list_records(null, 0, $nocount); | |
| 430 else | |
| 431 $this->result = $this->count(); | |
| 432 } | |
| 433 | |
| 434 return $this->result; | |
| 435 } | |
| 436 | |
| 437 /** | |
| 438 * Helper method to compose SQL where statements for fulltext searching | |
| 439 */ | |
| 440 private function fulltext_sql_where($value, $mode, $col = 'words', $bool = 'AND') | |
| 441 { | |
| 442 $WS = ' '; | |
| 443 $AS = $col == 'words' ? $WS : self::SEPARATOR; | |
| 444 $words = $col == 'words' ? rcube_utils::normalize_string($value, true) : array($value); | |
| 445 | |
| 446 $where = array(); | |
| 447 foreach ($words as $word) { | |
| 448 if ($mode & rcube_addressbook::SEARCH_STRICT) { | |
| 449 $where[] = '(' . $this->db->ilike($col, $word) | |
| 450 . ' OR ' . $this->db->ilike($col, $word . $AS . '%') | |
| 451 . ' OR ' . $this->db->ilike($col, '%' . $AS . $word . $AS . '%') | |
| 452 . ' OR ' . $this->db->ilike($col, '%' . $AS . $word) . ')'; | |
| 453 } | |
| 454 else if ($mode & rcube_addressbook::SEARCH_PREFIX) { | |
| 455 $where[] = '(' . $this->db->ilike($col, $word . '%') | |
| 456 . ' OR ' . $this->db->ilike($col, '%' . $AS . $word . '%') . ')'; | |
| 457 } | |
| 458 else { | |
| 459 $where[] = $this->db->ilike($col, '%' . $word . '%'); | |
| 460 } | |
| 461 } | |
| 462 | |
| 463 return count($where) ? '(' . join(" $bool ", $where) . ')' : ''; | |
| 464 } | |
| 465 | |
| 466 /** | |
| 467 * Count number of available contacts in database | |
| 468 * | |
| 469 * @return rcube_result_set Result object | |
| 470 */ | |
| 471 function count() | |
| 472 { | |
| 473 $count = isset($this->cache['count']) ? $this->cache['count'] : $this->_count(); | |
| 474 | |
| 475 return new rcube_result_set($count, ($this->list_page-1) * $this->page_size); | |
| 476 } | |
| 477 | |
| 478 /** | |
| 479 * Count number of available contacts in database | |
| 480 * | |
| 481 * @return int Contacts count | |
| 482 */ | |
| 483 private function _count() | |
| 484 { | |
| 25 | 485 $join = ""; |
| 0 | 486 if ($this->group_id) |
| 487 $join = " LEFT JOIN " . $this->db->table_name($this->db_groupmembers, true) . " AS m". | |
| 488 " ON (m.`contact_id` = c.`".$this->primary_key."`)"; | |
| 489 | |
| 490 // count contacts for this user | |
| 491 $sql_result = $this->db->query( | |
| 492 "SELECT COUNT(c.`contact_id`) AS cnt". | |
| 493 " FROM " . $this->db->table_name($this->db_name, true) . " AS c". | |
| 494 $join. | |
| 495 " WHERE c.`del` <> 1". | |
| 496 " AND c.`user_id` = ?". | |
| 497 ($this->group_id ? " AND m.`contactgroup_id` = ?" : ""). | |
| 498 ($this->filter ? " AND (".$this->filter.")" : ""), | |
| 499 $this->user_id, | |
| 500 $this->group_id | |
| 501 ); | |
| 502 | |
| 503 $sql_arr = $this->db->fetch_assoc($sql_result); | |
| 504 | |
| 505 $this->cache['count'] = (int) $sql_arr['cnt']; | |
| 506 | |
| 507 return $this->cache['count']; | |
| 508 } | |
| 509 | |
| 510 /** | |
| 511 * Return the last result set | |
| 512 * | |
| 513 * @return mixed Result array or NULL if nothing selected yet | |
| 514 */ | |
| 515 function get_result() | |
| 516 { | |
| 517 return $this->result; | |
| 518 } | |
| 519 | |
| 520 /** | |
| 521 * Get a specific contact record | |
| 522 * | |
| 523 * @param mixed $id Record identifier(s) | |
| 524 * @param bool $assoc Enables returning associative array | |
| 525 * | |
| 526 * @return rcube_result_set|array Result object with all record fields | |
| 527 */ | |
| 528 function get_record($id, $assoc = false) | |
| 529 { | |
| 530 // return cached result | |
| 531 if ($this->result && ($first = $this->result->first()) && $first[$this->primary_key] == $id) { | |
| 532 return $assoc ? $first : $this->result; | |
| 533 } | |
| 534 | |
| 535 $this->db->query( | |
| 536 "SELECT * FROM " . $this->db->table_name($this->db_name, true). | |
| 537 " WHERE `contact_id` = ?". | |
| 538 " AND `user_id` = ?". | |
| 539 " AND `del` <> 1", | |
| 540 $id, | |
| 541 $this->user_id | |
| 542 ); | |
| 543 | |
| 544 $this->result = null; | |
| 545 | |
| 546 if ($sql_arr = $this->db->fetch_assoc()) { | |
| 547 $record = $this->convert_db_data($sql_arr); | |
| 548 $this->result = new rcube_result_set(1); | |
| 549 $this->result->add($record); | |
| 550 } | |
| 551 | |
| 552 return $assoc && $record ? $record : $this->result; | |
| 553 } | |
| 554 | |
| 555 /** | |
| 556 * Get group assignments of a specific contact record | |
| 557 * | |
| 558 * @param mixed $id Record identifier | |
| 559 * | |
| 560 * @return array List of assigned groups as ID=>Name pairs | |
| 561 */ | |
| 562 function get_record_groups($id) | |
| 563 { | |
| 564 $results = array(); | |
| 565 | |
| 566 if (!$this->groups) { | |
| 567 return $results; | |
| 568 } | |
| 569 | |
| 570 $sql_result = $this->db->query( | |
| 571 "SELECT cgm.`contactgroup_id`, cg.`name` " | |
| 572 . " FROM " . $this->db->table_name($this->db_groupmembers, true) . " AS cgm" | |
| 573 . " LEFT JOIN " . $this->db->table_name($this->db_groups, true) . " AS cg" | |
| 574 . " ON (cgm.`contactgroup_id` = cg.`contactgroup_id` AND cg.`del` <> 1)" | |
| 575 . " WHERE cgm.`contact_id` = ?", | |
| 576 $id | |
| 577 ); | |
| 578 | |
| 579 while ($sql_result && ($sql_arr = $this->db->fetch_assoc($sql_result))) { | |
| 580 $results[$sql_arr['contactgroup_id']] = $sql_arr['name']; | |
| 581 } | |
| 582 | |
| 583 return $results; | |
| 584 } | |
| 585 | |
| 586 /** | |
| 587 * Check the given data before saving. | |
| 588 * If input not valid, the message to display can be fetched using get_error() | |
| 589 * | |
| 590 * @param array &$save_data Associative array with data to save | |
| 591 * @param boolean $autofix Try to fix/complete record automatically | |
| 592 * | |
| 593 * @return boolean True if input is valid, False if not. | |
| 594 */ | |
| 595 public function validate(&$save_data, $autofix = false) | |
| 596 { | |
| 597 // validate e-mail addresses | |
| 598 $valid = parent::validate($save_data, $autofix); | |
| 599 | |
| 600 // require at least one email address or a name | |
| 601 if ($valid && !strlen($save_data['firstname'].$save_data['surname'].$save_data['name']) && !array_filter($this->get_col_values('email', $save_data, true))) { | |
| 602 $this->set_error(self::ERROR_VALIDATE, 'noemailwarning'); | |
| 603 $valid = false; | |
| 604 } | |
| 605 | |
| 606 return $valid; | |
| 607 } | |
| 608 | |
| 609 /** | |
| 610 * Create a new contact record | |
| 611 * | |
| 612 * @param array $save_data Associative array with save data | |
| 613 * @param bool $check Enables validity checks | |
| 614 * | |
| 615 * @return integer|boolean The created record ID on success, False on error | |
| 616 */ | |
| 617 function insert($save_data, $check = false) | |
| 618 { | |
| 619 if (!is_array($save_data)) { | |
| 620 return false; | |
| 621 } | |
| 622 | |
| 623 $insert_id = $existing = false; | |
| 624 | |
| 625 if ($check) { | |
| 626 foreach ($save_data as $col => $values) { | |
| 627 if (strpos($col, 'email') === 0) { | |
| 628 foreach ((array)$values as $email) { | |
| 629 if ($existing = $this->search('email', $email, false, false)) | |
| 630 break 2; | |
| 631 } | |
| 632 } | |
| 633 } | |
| 634 } | |
| 635 | |
| 636 $save_data = $this->convert_save_data($save_data); | |
| 637 $a_insert_cols = $a_insert_values = array(); | |
| 638 | |
| 639 foreach ($save_data as $col => $value) { | |
| 640 $a_insert_cols[] = $this->db->quote_identifier($col); | |
| 641 $a_insert_values[] = $this->db->quote($value); | |
| 642 } | |
| 643 | |
| 644 if (!$existing->count && !empty($a_insert_cols)) { | |
| 645 $this->db->query( | |
| 646 "INSERT INTO " . $this->db->table_name($this->db_name, true). | |
| 647 " (`user_id`, `changed`, `del`, ".join(', ', $a_insert_cols).")". | |
| 648 " VALUES (".intval($this->user_id).", ".$this->db->now().", 0, ".join(', ', $a_insert_values).")" | |
| 649 ); | |
| 650 | |
| 651 $insert_id = $this->db->insert_id($this->db_name); | |
| 652 } | |
| 653 | |
| 654 $this->cache = null; | |
| 655 | |
| 656 return $insert_id; | |
| 657 } | |
| 658 | |
| 659 /** | |
| 660 * Update a specific contact record | |
| 661 * | |
| 662 * @param mixed $id Record identifier | |
| 663 * @param array $save_cols Associative array with save data | |
| 664 * | |
| 665 * @return boolean True on success, False on error | |
| 666 */ | |
| 667 function update($id, $save_cols) | |
| 668 { | |
| 669 $updated = false; | |
| 670 $write_sql = array(); | |
| 671 $record = $this->get_record($id, true); | |
| 672 $save_cols = $this->convert_save_data($save_cols, $record); | |
| 673 | |
| 674 foreach ($save_cols as $col => $value) { | |
| 675 $write_sql[] = sprintf("%s=%s", $this->db->quote_identifier($col), $this->db->quote($value)); | |
| 676 } | |
| 677 | |
| 678 if (!empty($write_sql)) { | |
| 679 $this->db->query( | |
| 680 "UPDATE " . $this->db->table_name($this->db_name, true). | |
| 681 " SET `changed` = ".$this->db->now().", ".join(', ', $write_sql). | |
| 682 " WHERE `contact_id` = ?". | |
| 683 " AND `user_id` = ?". | |
| 684 " AND `del` <> 1", | |
| 685 $id, | |
| 686 $this->user_id | |
| 687 ); | |
| 688 | |
| 689 $updated = $this->db->affected_rows(); | |
| 690 $this->result = null; // clear current result (from get_record()) | |
| 691 } | |
| 692 | |
| 693 return !empty($updated); | |
| 694 } | |
| 695 | |
| 696 /** | |
| 697 * Convert data stored in the database into output format | |
| 698 */ | |
| 699 private function convert_db_data($sql_arr) | |
| 700 { | |
| 701 $record = array(); | |
| 702 $record['ID'] = $sql_arr[$this->primary_key]; | |
| 703 | |
| 704 if ($sql_arr['vcard']) { | |
| 705 unset($sql_arr['email']); | |
| 706 $vcard = new rcube_vcard($sql_arr['vcard'], RCUBE_CHARSET, false, $this->vcard_fieldmap); | |
| 707 $record += $vcard->get_assoc() + $sql_arr; | |
| 708 } | |
| 709 else { | |
| 710 $record += $sql_arr; | |
| 711 $record['email'] = explode(self::SEPARATOR, $record['email']); | |
| 712 $record['email'] = array_map('trim', $record['email']); | |
| 713 } | |
| 714 | |
| 715 return $record; | |
| 716 } | |
| 717 | |
| 718 /** | |
| 719 * Convert input data for storing in the database | |
| 720 */ | |
| 721 private function convert_save_data($save_data, $record = array()) | |
| 722 { | |
| 723 $out = array(); | |
| 724 $words = ''; | |
| 725 | |
| 726 // copy values into vcard object | |
| 727 $vcard = new rcube_vcard($record['vcard'] ?: $save_data['vcard'], RCUBE_CHARSET, false, $this->vcard_fieldmap); | |
| 728 $vcard->reset(); | |
| 729 | |
| 730 // don't store groups in vCard (#1490277) | |
| 731 $vcard->set('groups', null); | |
| 732 unset($save_data['groups']); | |
| 733 | |
| 734 foreach ($save_data as $key => $values) { | |
| 735 list($field, $section) = explode(':', $key); | |
| 736 $fulltext = in_array($field, $this->fulltext_cols); | |
| 737 // avoid casting DateTime objects to array | |
| 738 if (is_object($values) && is_a($values, 'DateTime')) { | |
| 739 $values = array(0 => $values); | |
| 740 } | |
| 741 foreach ((array)$values as $value) { | |
| 742 if (isset($value)) | |
| 743 $vcard->set($field, $value, $section); | |
| 744 if ($fulltext && is_array($value)) | |
| 745 $words .= ' ' . rcube_utils::normalize_string(join(" ", $value)); | |
| 746 else if ($fulltext && strlen($value) >= 3) | |
| 747 $words .= ' ' . rcube_utils::normalize_string($value); | |
| 748 } | |
| 749 } | |
| 750 $out['vcard'] = $vcard->export(false); | |
| 751 | |
| 752 foreach ($this->table_cols as $col) { | |
| 753 $key = $col; | |
| 754 if (!isset($save_data[$key])) | |
| 755 $key .= ':home'; | |
| 756 if (isset($save_data[$key])) { | |
| 757 if (is_array($save_data[$key])) | |
| 758 $out[$col] = join(self::SEPARATOR, $save_data[$key]); | |
| 759 else | |
| 760 $out[$col] = $save_data[$key]; | |
| 761 } | |
| 762 } | |
| 763 | |
| 764 // save all e-mails in database column | |
| 765 $out['email'] = join(self::SEPARATOR, $vcard->email); | |
| 766 | |
| 767 // join words for fulltext search | |
| 768 $out['words'] = join(" ", array_unique(explode(" ", $words))); | |
| 769 | |
| 770 return $out; | |
| 771 } | |
| 772 | |
| 773 /** | |
| 774 * Mark one or more contact records as deleted | |
| 775 * | |
| 776 * @param array $ids Record identifiers | |
| 777 * @param boolean $force Remove record(s) irreversible (unsupported) | |
| 778 */ | |
| 779 function delete($ids, $force = true) | |
| 780 { | |
| 781 if (!is_array($ids)) { | |
| 782 $ids = explode(self::SEPARATOR, $ids); | |
| 783 } | |
| 784 | |
| 785 $ids = $this->db->array2list($ids, 'integer'); | |
| 786 | |
| 787 // flag record as deleted (always) | |
| 788 $this->db->query( | |
| 789 "UPDATE " . $this->db->table_name($this->db_name, true). | |
| 790 " SET `del` = 1, `changed` = ".$this->db->now(). | |
| 791 " WHERE `user_id` = ?". | |
| 792 " AND `contact_id` IN ($ids)", | |
| 793 $this->user_id | |
| 794 ); | |
| 795 | |
| 796 $this->cache = null; | |
| 797 | |
| 798 return $this->db->affected_rows(); | |
| 799 } | |
| 800 | |
| 801 /** | |
| 802 * Undelete one or more contact records | |
| 803 * | |
| 804 * @param array $ids Record identifiers | |
| 805 */ | |
| 806 function undelete($ids) | |
| 807 { | |
| 808 if (!is_array($ids)) { | |
| 809 $ids = explode(self::SEPARATOR, $ids); | |
| 810 } | |
| 811 | |
| 812 $ids = $this->db->array2list($ids, 'integer'); | |
| 813 | |
| 814 // clear deleted flag | |
| 815 $this->db->query( | |
| 816 "UPDATE " . $this->db->table_name($this->db_name, true). | |
| 817 " SET `del` = 0, `changed` = ".$this->db->now(). | |
| 818 " WHERE `user_id` = ?". | |
| 819 " AND `contact_id` IN ($ids)", | |
| 820 $this->user_id | |
| 821 ); | |
| 822 | |
| 823 $this->cache = null; | |
| 824 | |
| 825 return $this->db->affected_rows(); | |
| 826 } | |
| 827 | |
| 828 /** | |
| 829 * Remove all records from the database | |
| 830 * | |
| 831 * @param bool $with_groups Remove also groups | |
| 832 * | |
| 833 * @return int Number of removed records | |
| 834 */ | |
| 835 function delete_all($with_groups = false) | |
| 836 { | |
| 837 $this->cache = null; | |
| 838 | |
| 839 $now = $this->db->now(); | |
| 840 | |
| 841 $this->db->query("UPDATE " . $this->db->table_name($this->db_name, true) | |
| 842 . " SET `del` = 1, `changed` = $now" | |
| 843 . " WHERE `user_id` = ?", $this->user_id); | |
| 844 | |
| 845 $count = $this->db->affected_rows(); | |
| 846 | |
| 847 if ($with_groups) { | |
| 848 $this->db->query("UPDATE " . $this->db->table_name($this->db_groups, true) | |
| 849 . " SET `del` = 1, `changed` = $now" | |
| 850 . " WHERE `user_id` = ?", $this->user_id); | |
| 851 | |
| 852 $count += $this->db->affected_rows(); | |
| 853 } | |
| 854 | |
| 855 return $count; | |
| 856 } | |
| 857 | |
| 858 /** | |
| 859 * Create a contact group with the given name | |
| 860 * | |
| 861 * @param string $name The group name | |
| 862 * | |
| 863 * @return mixed False on error, array with record props in success | |
| 864 */ | |
| 865 function create_group($name) | |
| 866 { | |
| 867 $result = false; | |
| 868 | |
| 869 // make sure we have a unique name | |
| 870 $name = $this->unique_groupname($name); | |
| 871 | |
| 872 $this->db->query( | |
| 873 "INSERT INTO " . $this->db->table_name($this->db_groups, true). | |
| 874 " (`user_id`, `changed`, `name`)". | |
| 875 " VALUES (".intval($this->user_id).", ".$this->db->now().", ".$this->db->quote($name).")" | |
| 876 ); | |
| 877 | |
| 878 if ($insert_id = $this->db->insert_id($this->db_groups)) { | |
| 879 $result = array('id' => $insert_id, 'name' => $name); | |
| 880 } | |
| 881 | |
| 882 return $result; | |
| 883 } | |
| 884 | |
| 885 /** | |
| 886 * Delete the given group (and all linked group members) | |
| 887 * | |
| 888 * @param string $gid Group identifier | |
| 889 * | |
| 890 * @return boolean True on success, false if no data was changed | |
| 891 */ | |
| 892 function delete_group($gid) | |
| 893 { | |
| 894 // flag group record as deleted | |
| 895 $this->db->query( | |
| 896 "UPDATE " . $this->db->table_name($this->db_groups, true) | |
| 897 . " SET `del` = 1, `changed` = " . $this->db->now() | |
| 898 . " WHERE `contactgroup_id` = ?" | |
| 899 . " AND `user_id` = ?", | |
| 900 $gid, $this->user_id | |
| 901 ); | |
| 902 | |
| 903 $this->cache = null; | |
| 904 | |
| 905 return $this->db->affected_rows(); | |
| 906 } | |
| 907 | |
| 908 /** | |
| 909 * Rename a specific contact group | |
| 910 * | |
| 911 * @param string $gid Group identifier | |
| 912 * @param string $name New name to set for this group | |
| 913 * @param string $new_gid (not used) | |
| 914 * | |
| 915 * @return boolean New name on success, false if no data was changed | |
| 916 */ | |
| 917 function rename_group($gid, $name, &$new_gid) | |
| 918 { | |
| 919 // make sure we have a unique name | |
| 920 $name = $this->unique_groupname($name); | |
| 921 | |
| 922 $sql_result = $this->db->query( | |
| 923 "UPDATE " . $this->db->table_name($this->db_groups, true). | |
| 924 " SET `name` = ?, `changed` = ".$this->db->now(). | |
| 925 " WHERE `contactgroup_id` = ?". | |
| 926 " AND `user_id` = ?", | |
| 927 $name, $gid, $this->user_id | |
| 928 ); | |
| 929 | |
| 930 return $this->db->affected_rows($sql_result) ? $name : false; | |
| 931 } | |
| 932 | |
| 933 /** | |
| 934 * Add the given contact records the a certain group | |
| 935 * | |
| 936 * @param string Group identifier | |
| 937 * @param array|string List of contact identifiers to be added | |
| 938 * | |
| 939 * @return int Number of contacts added | |
| 940 */ | |
| 941 function add_to_group($group_id, $ids) | |
| 942 { | |
| 943 if (!is_array($ids)) { | |
| 944 $ids = explode(self::SEPARATOR, $ids); | |
| 945 } | |
| 946 | |
| 947 $added = 0; | |
| 948 $exists = array(); | |
| 949 | |
| 950 // get existing assignments ... | |
| 951 $sql_result = $this->db->query( | |
| 952 "SELECT `contact_id` FROM " . $this->db->table_name($this->db_groupmembers, true). | |
| 953 " WHERE `contactgroup_id` = ?". | |
| 954 " AND `contact_id` IN (".$this->db->array2list($ids, 'integer').")", | |
| 955 $group_id | |
| 956 ); | |
| 957 | |
| 958 while ($sql_result && ($sql_arr = $this->db->fetch_assoc($sql_result))) { | |
| 959 $exists[] = $sql_arr['contact_id']; | |
| 960 } | |
| 961 | |
| 962 // ... and remove them from the list | |
| 963 $ids = array_diff($ids, $exists); | |
| 964 | |
| 965 foreach ($ids as $contact_id) { | |
| 966 $this->db->query( | |
| 967 "INSERT INTO " . $this->db->table_name($this->db_groupmembers, true). | |
| 968 " (`contactgroup_id`, `contact_id`, `created`)". | |
| 969 " VALUES (?, ?, ".$this->db->now().")", | |
| 970 $group_id, | |
| 971 $contact_id | |
| 972 ); | |
| 973 | |
| 974 if ($error = $this->db->is_error()) { | |
| 975 $this->set_error(self::ERROR_SAVING, $error); | |
| 976 } | |
| 977 else { | |
| 978 $added++; | |
| 979 } | |
| 980 } | |
| 981 | |
| 982 return $added; | |
| 983 } | |
| 984 | |
| 985 /** | |
| 986 * Remove the given contact records from a certain group | |
| 987 * | |
| 988 * @param string $group_id Group identifier | |
| 989 * @param array|string $ids List of contact identifiers to be removed | |
| 990 * | |
| 991 * @return int Number of deleted group members | |
| 992 */ | |
| 993 function remove_from_group($group_id, $ids) | |
| 994 { | |
| 995 if (!is_array($ids)) | |
| 996 $ids = explode(self::SEPARATOR, $ids); | |
| 997 | |
| 998 $ids = $this->db->array2list($ids, 'integer'); | |
| 999 | |
| 1000 $sql_result = $this->db->query( | |
| 1001 "DELETE FROM " . $this->db->table_name($this->db_groupmembers, true). | |
| 1002 " WHERE `contactgroup_id` = ?". | |
| 1003 " AND `contact_id` IN ($ids)", | |
| 1004 $group_id | |
| 1005 ); | |
| 1006 | |
| 1007 return $this->db->affected_rows($sql_result); | |
| 1008 } | |
| 1009 | |
| 1010 /** | |
| 1011 * Check for existing groups with the same name | |
| 1012 * | |
| 1013 * @param string $name Name to check | |
| 1014 * | |
| 1015 * @return string A group name which is unique for the current use | |
| 1016 */ | |
| 1017 private function unique_groupname($name) | |
| 1018 { | |
| 1019 $checkname = $name; | |
| 1020 $num = 2; | |
| 1021 $hit = false; | |
| 1022 | |
| 1023 do { | |
| 1024 $sql_result = $this->db->query( | |
| 1025 "SELECT 1 FROM " . $this->db->table_name($this->db_groups, true). | |
| 1026 " WHERE `del` <> 1". | |
| 1027 " AND `user_id` = ?". | |
| 1028 " AND `name` = ?", | |
| 1029 $this->user_id, | |
| 1030 $checkname); | |
| 1031 | |
| 1032 // append number to make name unique | |
| 1033 if ($hit = $this->db->fetch_array($sql_result)) { | |
| 1034 $checkname = $name . ' ' . $num++; | |
| 1035 } | |
| 1036 } | |
| 1037 while ($hit); | |
| 1038 | |
| 1039 return $checkname; | |
| 1040 } | |
| 1041 } |