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