1<?php 2/** 3 * EGroupware API: Contacts - SQL storage 4 * 5 * @link http://www.egroupware.org 6 * @author Ralf Becker <RalfBecker-AT-outdoor-training.de> 7 * @package api 8 * @subpackage contacts 9 * @copyright (c) 2006-16 by Ralf Becker <RalfBecker-AT-outdoor-training.de> 10 * @license http://opensource.org/licenses/gpl-license.php GPL - GNU General Public License 11 * @version $Id$ 12 */ 13 14namespace EGroupware\Api\Contacts; 15 16use EGroupware\Api; 17 18/** 19 * Contacts - SQL storage 20 */ 21class Sql extends Api\Storage 22{ 23 /** 24 * name of custom fields table 25 * 26 * @var string 27 */ 28 var $account_repository = 'sql'; 29 var $contact_repository = 'sql'; 30 var $grants; 31 32 /** 33 * join to show only active account (and not already expired ones) 34 */ 35 const ACCOUNT_ACTIVE_JOIN = ' LEFT JOIN egw_accounts ON egw_addressbook.account_id=egw_accounts.account_id '; 36 /** 37 * filter to show only active account (and not already expired or deactived ones) 38 * UNIX_TIMESTAMP(NOW()) gets replaced with value of time() in the code! 39 */ 40 const ACOUNT_ACTIVE_FILTER = "(account_expires IS NULL OR account_expires = -1 OR account_expires > UNIX_TIMESTAMP(NOW())) AND (account_type IS NULL OR account_type!='u' OR account_status='A')"; 41 42 /** 43 * internal name of the id, gets mapped to uid 44 * 45 * @var string 46 */ 47 var $contacts_id='id'; 48 49 /** 50 * Name of the table for distribution lists 51 * 52 * @var string 53 */ 54 var $lists_table = 'egw_addressbook_lists'; 55 /** 56 * Name of the table with the members (contacts) of the distribution lists 57 * 58 * @var string 59 */ 60 var $ab2list_table = 'egw_addressbook2list'; 61 62 const EXTRA_TABLE = 'egw_addressbook_extra'; 63 const EXTRA_VALUE = 'contact_value'; 64 65 const SHARED_TABLE = 'egw_addressbook_shared'; 66 67 /** 68 * Constructor 69 * 70 * @param Api\Db $db =null 71 */ 72 function __construct(Api\Db $db=null) 73 { 74 parent::__construct('api', 'egw_addressbook', self::EXTRA_TABLE, 75 'contact_', '_name', '_value', '_id', $db); 76 77 // Get custom fields from addressbook instead of api 78 $this->customfields = Api\Storage\Customfields::get('addressbook'); 79 80 if ($GLOBALS['egw_info']['server']['account_repository']) 81 { 82 $this->account_repository = $GLOBALS['egw_info']['server']['account_repository']; 83 } 84 elseif ($GLOBALS['egw_info']['server']['auth_type']) 85 { 86 $this->account_repository = $GLOBALS['egw_info']['server']['auth_type']; 87 } 88 if ($GLOBALS['egw_info']['server']['contact_repository']) 89 { 90 $this->contact_repository = $GLOBALS['egw_info']['server']['contact_repository']; 91 } 92 } 93 94 /** 95 * Query organisations by given parameters 96 * 97 * @var array $param 98 * @var string $param[org_view] 'org_name', 'org_name,adr_one_location', 'org_name,org_unit' how to group 99 * @var int $param[owner] addressbook to search 100 * @var string $param[search] search pattern for org_name 101 * @var string $param[searchletter] letter the org_name need to start with 102 * @var array $param[col_filter] filter 103 * @var string $param[search] or'ed search pattern 104 * @var array $param[advanced_search] indicator that advanced search is active 105 * @var string $param[op] (operator like AND or OR; will be passed when advanced search is active) 106 * @var string $param[wildcard] (wildcard like % or empty or not set (for no wildcard); will be passed when advanced search is active) 107 * @var int $param[start] 108 * @var int $param[num_rows] 109 * @var string $param[sort] ASC or DESC 110 * @return array or arrays with keys org_name,count and evtl. adr_one_location or org_unit 111 */ 112 function organisations($param) 113 { 114 $filter = is_array($param['col_filter']) ? $param['col_filter'] : array(); 115 $join = ''; 116 $op = 'OR'; 117 if (isset($param['op']) && !empty($param['op'])) $op = $param['op']; 118 $advanced_search = false; 119 if (isset($param['advanced_search']) && !empty($param['advanced_search'])) $advanced_search = true; 120 $wildcard ='%'; 121 if ($advanced_search || (isset($param['wildcard']) && !empty($param['wildcard']))) $wildcard = ($param['wildcard']?$param['wildcard']:''); 122 123 // fix cat_id filter to search in comma-separated multiple cats and return subcats 124 if ($filter['cat_id']) 125 { 126 $filter[] = $this->_cat_filter($filter['cat_id']); 127 unset($filter['cat_id']); 128 } 129 // add filter for read ACL in sql, if user is NOT the owner of the addressbook 130 if ($param['owner'] && $param['owner'] == $GLOBALS['egw_info']['user']['account_id']) 131 { 132 $filter['owner'] = $param['owner']; 133 } 134 else 135 { 136 // we have no private grants in addressbook at the moment, they have then to be added here too 137 if ($param['owner']) 138 { 139 if (!$this->grants[(int) $filter['owner']]) return false; // we have no access to that addressbook 140 141 $filter['owner'] = $param['owner']; 142 $filter['private'] = 0; 143 } 144 else // search all addressbooks, incl. accounts 145 { 146 if ($this->account_repository != 'sql' && $this->contact_repository != 'sql-ldap') 147 { 148 $filter[] = $this->table_name.'.contact_owner != 0'; // in case there have been accounts in sql previously 149 } 150 $filter[] = "(".$this->table_name.".contact_owner=".(int)$GLOBALS['egw_info']['user']['account_id']. 151 (!$this->grants ? ')' : 152 " OR contact_private=0 AND ".$this->table_name.".contact_owner IN (". 153 implode(',',array_keys($this->grants))."))"); 154 } 155 if ($GLOBALS['egw_info']['user']['preferences']['addressbook']['hide_accounts'] !== 'none') 156 { 157 $join .= self::ACCOUNT_ACTIVE_JOIN; 158 if ($GLOBALS['egw_info']['user']['preferences']['addressbook']['hide_accounts'] === '0') 159 { 160 $filter[] = str_replace('UNIX_TIMESTAMP(NOW())',time(),self::ACOUNT_ACTIVE_FILTER); 161 } 162 else 163 { 164 $filter[] = 'egw_accounts.account_id IS NULL'; 165 } 166 } 167 } 168 if ($param['searchletter']) 169 { 170 $filter[] = 'org_name '.$this->db->capabilities[Api\Db::CAPABILITY_CASE_INSENSITIV_LIKE].' '.$this->db->quote($param['searchletter'].'%'); 171 } 172 else 173 { 174 $filter[] = "org_name != ''";// AND org_name IS NOT NULL"; 175 } 176 if (isset($filter['list'])) 177 { 178 if ($filter['list'] < 0) 179 { 180 $join .= " JOIN egw_acl ON $this->table_name.account_id=acl_account AND acl_appname='phpgw_group' AND ". 181 $this->db->expression('egw_acl', array('acl_location' => $filter['list'])); 182 } 183 else 184 { 185 $join .= " JOIN $this->ab2list_table ON $this->table_name.contact_id=$this->ab2list_table.contact_id AND ". 186 $this->db->expression($this->ab2list_table, array('list_id' => $filter['list'])); 187 } 188 unset($filter['list']); 189 } 190 $sort = $param['sort'] == 'DESC' ? 'DESC' : 'ASC'; 191 192 list(,$by) = explode(',',$param['org_view']); 193 if (!$by) 194 { 195 $extra = array( 196 'COUNT(DISTINCT egw_addressbook.contact_id) AS org_count', 197 "COUNT(DISTINCT CASE WHEN org_unit IS NULL THEN '' ELSE org_unit END) AS org_unit_count", 198 "COUNT(DISTINCT CASE WHEN adr_one_locality IS NULL THEN '' ELSE adr_one_locality END) AS adr_one_locality_count", 199 ); 200 $append = "GROUP BY org_name ORDER BY org_name $sort"; 201 } 202 else // by adr_one_location or org_unit 203 { 204 // org total for more then one $by 205 $by_expr = $by == 'org_unit_count' ? "COUNT(DISTINCT CASE WHEN org_unit IS NULL THEN '' ELSE org_unit END)" : 206 "COUNT(DISTINCT CASE WHEN adr_one_locality IS NULL THEN '' ELSE adr_one_locality END)"; 207 parent::search($param['search'],array('org_name'), 208 "GROUP BY org_name HAVING $by_expr > 1 ORDER BY org_name $sort", array( 209 "NULL AS $by", 210 '1 AS is_main', 211 'COUNT(DISTINCT egw_addressbook.contact_id) AS org_count', 212 "COUNT(DISTINCT CASE WHEN org_unit IS NULL THEN '' ELSE org_unit END) AS org_unit_count", 213 "COUNT(DISTINCT CASE WHEN adr_one_locality IS NULL THEN '' ELSE adr_one_locality END) AS adr_one_locality_count", 214 ),$wildcard,false,$op/*'OR'*/,'UNION',$filter,$join); 215 // org by location 216 parent::search($param['search'],array('org_name'), 217 "GROUP BY org_name,$by ORDER BY org_name $sort,$by $sort", array( 218 "CASE WHEN $by IS NULL THEN '' ELSE $by END AS $by", 219 '0 AS is_main', 220 'COUNT(DISTINCT egw_addressbook.contact_id) AS org_count', 221 "COUNT(DISTINCT CASE WHEN org_unit IS NULL THEN '' ELSE org_unit END) AS org_unit_count", 222 "COUNT(DISTINCT CASE WHEN adr_one_locality IS NULL THEN '' ELSE adr_one_locality END) AS adr_one_locality_count", 223 ),$wildcard,false,$op/*'OR'*/,'UNION',$filter,$join); 224 $append = "ORDER BY org_name $sort,is_main DESC,$by $sort"; 225 } 226 $rows = parent::search($param['search'],array('org_name'),$append,$extra,$wildcard,false,$op/*'OR'*/, 227 array($param['start'],$param['num_rows']),$filter,$join); 228 229 if (!$rows) return false; 230 231 // query the values for *_count == 1, to display them instead 232 $filter['org_name'] = $orgs = array(); 233 foreach($rows as $row) 234 { 235 if ($row['org_unit_count'] == 1 || $row['adr_one_locality_count'] == 1) 236 { 237 $filter['org_name'][$row['org_name']] = $row['org_name']; // use as key too to have every org only once 238 } 239 $org_key = $row['org_name'].($by ? '|||'.($row[$by] || $row[$by.'_count']==1 ? $row[$by] : '|||') : ''); 240 $row['group_count'] = $row['org_count']; 241 $orgs[$org_key] = $row; 242 } 243 unset($rows); 244 245 if (count($filter['org_name'])) 246 { 247 foreach((array) parent::search(null, array('org_name','org_unit','adr_one_locality'), 248 'GROUP BY org_name,org_unit,adr_one_locality', 249 '',$wildcard,false,$op/*'AND'*/,false,$filter,$join) as $row) 250 { 251 $org_key = $row['org_name'].($by ? '|||'.$row[$by] : ''); 252 if ($orgs[$org_key]['org_unit_count'] == 1) 253 { 254 $orgs[$org_key]['org_unit'] = $row['org_unit']; 255 } 256 if ($orgs[$org_key]['adr_one_locality_count'] == 1) 257 { 258 $orgs[$org_key]['adr_one_locality'] = $row['adr_one_locality']; 259 } 260 if ($by && isset($orgs[$org_key = $row['org_name'].'||||||'])) 261 { 262 if ($orgs[$org_key]['org_unit_count'] == 1) 263 { 264 $orgs[$org_key]['org_unit'] = $row['org_unit']; 265 } 266 if ($orgs[$org_key]['adr_one_locality_count'] == 1) 267 { 268 $orgs[$org_key]['adr_one_locality'] = $row['adr_one_locality']; 269 } 270 } 271 } 272 } 273 return array_values($orgs); 274 } 275 276 277 /** 278 * Query for duplicate contacts according to given parameters 279 * 280 * We join egw_addressbook to itself, and count how many fields match. If 281 * enough of the fields we care about match, we count those two records as 282 * duplicates. 283 * 284 * @var array $param 285 * @var string $param[grouped_view] 'duplicate', 'duplicate,adr_one_location', 'duplicate,org_name' how to group 286 * @var int $param[owner] addressbook to search 287 * @var string $param[search] search pattern for org_name 288 * @var string $param[searchletter] letter the name need to start with 289 * @var array $param[col_filter] filter 290 * @var string $param[search] or'ed search pattern 291 * @var array $param[advanced_search] indicator that advanced search is active 292 * @var string $param[op] (operator like AND or OR; will be passed when advanced search is active) 293 * @var string $param[wildcard] (wildcard like % or empty or not set (for no wildcard); will be passed when advanced search is active) 294 * @var int $param[start] 295 * @var int $param[num_rows] 296 * @var string $param[sort] ASC or DESC 297 * @return array or arrays with keys org_name,count and evtl. adr_one_location or org_unit 298 */ 299 function duplicates($param) 300 { 301 $join = 'JOIN ' . $this->table_name . ' AS a2 ON '; 302 $filter = $param['col_filter']; 303 $op = 'OR'; 304 if (isset($param['op']) && !empty($param['op'])) $op = $param['op']; 305 $advanced_search = false; 306 if (isset($param['advanced_search']) && !empty($param['advanced_search'])) $advanced_search = true; 307 $wildcard ='%'; 308 if ($advanced_search || (isset($param['wildcard']) && !empty($param['wildcard']))) $wildcard = ($param['wildcard']?$param['wildcard']:''); 309 310 // fix cat_id filter to search in comma-separated multiple cats and return subcats 311 if ($param['cat_id']) 312 { 313 $cat_filter = $this->_cat_filter($filter['cat_id']); 314 $filter[] = str_replace('cat_id', $this->table_name . '.cat_id', $cat_filter); 315 $join .= str_replace('cat_id', 'a2.cat_id', $cat_filter) . ' AND '; 316 unset($filter['cat_id']); 317 } 318 if ($filter['tid']) 319 { 320 $filter[$this->table_name . '.contact_tid'] = $param['col_filter']['tid']; 321 $join .= 'a2.contact_tid = ' . $this->db->quote($filter['tid']) . ' AND '; 322 unset($filter['tid']); 323 } 324 else 325 { 326 $join .= 'a2.contact_tid != \'D\' AND '; 327 } 328 // add filter for read ACL in sql, if user is NOT the owner of the addressbook 329 if (array_key_exists('owner',$param) && $param['owner'] == $GLOBALS['egw_info']['user']['account_id']) 330 { 331 $filter[$this->table_name.'.contact_owner'] = $param['owner']; 332 $join .= 'a2.contact_owner = ' . $this->db->quote($param['owner']) . ' AND '; 333 } 334 else 335 { 336 // we have no private grants in addressbook at the moment, they have then to be added here too 337 if (array_key_exists('owner', $param)) 338 { 339 if (!$this->grants[(int) $param['owner']]) return false; // we have no access to that addressbook 340 341 $filter[$this->table_name.'.contact_owner'] = $param['owner']; 342 $filter[$this->table_name.'.private'] = 0; 343 $join .= 'a2.contact_owner = ' . $this->db->quote($param['owner']) . ' AND '; 344 $join .= 'a2.contact_private = ' . $this->db->quote($filter['private']) . ' AND '; 345 } 346 else // search all addressbooks, incl. accounts 347 { 348 if ($this->account_repository != 'sql' && $this->contact_repository != 'sql-ldap') 349 { 350 $filter[] = $this->table_name.'.contact_owner != 0'; // in case there have been accounts in sql previously 351 } 352 $filter[] = $access = "(".$this->table_name.".contact_owner=".(int)$GLOBALS['egw_info']['user']['account_id']. 353 " OR {$this->table_name}.contact_private=0 AND ".$this->table_name.".contact_owner IN (". 354 implode(',',array_keys($this->grants))."))"; 355 $join .= str_replace($this->table_name, 'a2', $access) . ' AND '; 356 } 357 } 358 if ($param['searchletter']) 359 { 360 $filter[] = $this->table_name.'.n_fn '.$this->db->capabilities[Api\Db::CAPABILITY_CASE_INSENSITIV_LIKE].' '.$this->db->quote($param['searchletter'].'%'); 361 } 362 $sort = $param['sort'] == 'DESC' ? 'DESC' : 'ASC'; 363 $group = $GLOBALS['egw_info']['user']['preferences']['addressbook']['duplicate_fields'] ? 364 explode(',',$GLOBALS['egw_info']['user']['preferences']['addressbook']['duplicate_fields']): 365 array('n_family', 'org_name', 'contact_email'); 366 $match_count = $GLOBALS['egw_info']['user']['preferences']['addressbook']['duplicate_threshold'] ? 367 $GLOBALS['egw_info']['user']['preferences']['addressbook']['duplicate_threshold'] : 3; 368 369 $columns = Array(); 370 $extra = Array(); 371 $order = in_array($param['order'], $group) ? $param['order'] : $group[0]; 372 $join .= $this->table_name .'.contact_id != a2.contact_id AND ('; 373 $join_fields = Array(); 374 foreach($group as $field) 375 { 376 $extra[] = "IF({$this->table_name}.$field = a2.$field, 1, 0)"; 377 $join_fields[] = $this->table_name . ".$field = a2.$field"; 378 $columns[] = "IF({$this->table_name}.$field = a2.$field, {$this->table_name}.$field, '') AS $field"; 379 } 380 $extra = Array( 381 implode('+', $extra) . ' AS match_count' 382 ); 383 $join .= $this->db->column_data_implode(' OR ',$join_fields) . ')'; 384 if ($GLOBALS['egw_info']['user']['preferences']['addressbook']['hide_accounts'] !== 'none') 385 { 386 if ($GLOBALS['egw_info']['user']['preferences']['addressbook']['hide_accounts'] === '0') 387 { 388 $join .=' LEFT JOIN egw_accounts AS account_1 ON egw_addressbook.account_id=account_1.account_id '; 389 $join .=' LEFT JOIN egw_accounts AS account_2 ON egw_addressbook.account_id=account_2.account_id '; 390 $filter[] = str_replace(array('UNIX_TIMESTAMP(NOW())', 'account_'),array(time(),'account_1.account_'),self::ACOUNT_ACTIVE_FILTER); 391 $filter[] = str_replace(array('UNIX_TIMESTAMP(NOW())', 'account_'),array(time(),'account_2.account_'),self::ACOUNT_ACTIVE_FILTER); 392 } 393 else 394 { 395 $filter[] = 'egw_addressbook.account_id IS NULL and a2.account_id IS NULL'; 396 } 397 } 398 $append = " HAVING match_count >= $match_count ORDER BY {$order} $sort, $this->table_name.contact_id"; 399 $columns[] = $this->table_name.'.contact_id AS contact_id'; 400 401 $criteria = array(); 402 if ($param['search'] && !is_array($param['search'])) 403 { 404 $search_cols = array(); 405 foreach($group as $col) 406 { 407 $search_cols[] = $this->table_name . '.' . $col; 408 } 409 $search = $this->search2criteria($param['search'],$wildcard,$op, null, $search_cols); 410 $criteria = array($search); 411 } 412 $query = $this->parse_search(array_merge($criteria, $filter), $wildcard, false, ' AND '); 413 414 $sub_query = $this->db->select($this->table_name, 415 'DISTINCT ' . implode(', ',array_merge($columns, $extra)), 416 $query, 417 False, False, 0, $append, False, -1, 418 $join 419 ); 420 421 $columns = implode(', ', $group); 422 if ($this->db->Type == 'mysql' && $this->db->ServerInfo['version'] >= 4.0) 423 { 424 $mysql_calc_rows = 'SQL_CALC_FOUND_ROWS '; 425 } 426 427 $rows = $this->db->query( 428 "SELECT $mysql_calc_rows " . $columns. ', COUNT(contact_id) AS group_count' . 429 ' FROM (' . $sub_query . ') AS matches GROUP BY ' . implode(',',$group) . 430 ' HAVING group_count > 1 ORDER BY ' . $order, 431 __LINE__, __FILE__, (int)$param['start'],$mysql_calc_rows ? (int)$param['num_rows'] : -1 432 ); 433 434 // Go through rows and only return one for each pair/triplet/etc. of matches 435 $dupes = array(); 436 foreach($rows as $key => $row) 437 { 438 $row['email'] = $row['contact_email']; 439 $row['email_home'] = $row['contact_email_home']; 440 $dupes[] = $this->db2data($row); 441 } 442 443 if ($mysql_calc_rows) 444 { 445 $this->total = $this->db->query('SELECT FOUND_ROWS()')->fetchColumn(); 446 } 447 else 448 { 449 $this->total = $rows->NumRows(); 450 } 451 return $dupes; 452 } 453 454 /** 455 * searches db for rows matching searchcriteria 456 * 457 * '*' and '?' are replaced with sql-wildcards '%' and '_' 458 * 459 * For a union-query you call search for each query with $start=='UNION' and one more with only $order_by and $start set to run the union-query. 460 * 461 * @param array|string $criteria array of key and data cols, OR a SQL query (content for WHERE), fully quoted (!) 462 * @param boolean|string|array $only_keys =true True returns only keys, False returns all cols. or 463 * comma seperated list or array of columns to return 464 * @param string $order_by ='' fieldnames + {ASC|DESC} separated by colons ',', can also contain a GROUP BY (if it contains ORDER BY) 465 * @param string|array $extra_cols ='' string or array of strings to be added to the SELECT, eg. "count(*) as num" 466 * @param string $wildcard ='' appended befor and after each criteria 467 * @param boolean $empty =false False=empty criteria are ignored in query, True=empty have to be empty in row 468 * @param string $op ='AND' defaults to 'AND', can be set to 'OR' too, then criteria's are OR'ed together 469 * @param mixed $start =false if != false, return only maxmatch rows begining with start, or array($start,$num), or 'UNION' for a part of a union query 470 * @param array $filter =null if set (!=null) col-data pairs, to be and-ed (!) into the query without wildcards 471 * @param string $join ='' sql to do a join, added as is after the table-name, eg. ", table2 WHERE x=y" or 472 * "LEFT JOIN table2 ON (x=y)", Note: there's no quoting done on $join! 473 * @param boolean $need_full_no_count =false If true an unlimited query is run to determine the total number of rows, default false 474 * @param boolean $ignore_acl =false true: no acl check 475 * @return boolean/array of matching rows (the row is an array of the cols) or False 476 */ 477 function &search($criteria,$only_keys=True,$order_by='',$extra_cols='',$wildcard='',$empty=False,$op='AND',$start=false,$filter=null,$join='',$need_full_no_count=false, $ignore_acl=false) 478 { 479 if ((int) $this->debug >= 4) echo '<p>'.__METHOD__.'('.array2string($criteria).','.array2string($only_keys).",'$order_by','$extra_cols','$wildcard','$empty','$op',$start,".array2string($filter).",'$join')</p>\n"; 480 //error_log(__METHOD__.'('.array2string($criteria,true).','.array2string($only_keys).",'$order_by', ".array2string($extra_cols).",'$wildcard','$empty','$op',$start,".array2string($filter).",'$join')"); 481 482 $owner = isset($filter['owner']) ? $filter['owner'] : (isset($criteria['owner']) ? $criteria['owner'] : null); 483 484 // fix cat_id criteria to search in comma-separated multiple cats and return subcats 485 if (is_array($criteria) && ($cats = $criteria['cat_id'])) 486 { 487 $criteria = array_merge($criteria, $this->_cat_search($criteria['cat_id'])); 488 unset($criteria['cat_id']); 489 } 490 // fix cat_id filter to search in comma-separated multiple cats and return subcats 491 if (($cats = $filter['cat_id'])) 492 { 493 if ($filter['cat_id'][0] == '!') 494 { 495 $filter['cat_id'] = substr($filter['cat_id'],1); 496 $not = 'NOT'; 497 } 498 $filter[] = $this->_cat_filter($filter['cat_id'],$not); 499 unset($filter['cat_id']); 500 } 501 502 if (!empty($filter['shared_by'])) 503 { 504 $filter[] = $this->table_name.'.contact_id IN (SELECT DISTINCT contact_id FROM '.self::SHARED_TABLE.' WHERE '. 505 'shared_deleted IS NULL AND shared_by='.(int)$filter['shared_by']. 506 ((string)$filter['owner'] !== '' ? ' AND shared_with='.(int)$filter['owner'] : '').')'; 507 unset($filter['shared_by']); 508 $shared_sql = '1=1'; // can not be empty and must be true 509 } 510 else 511 { 512 // SQL to get all shared contacts to be OR-ed into ACL filter 513 $shared_sql = $this->table_name.'.contact_id IN (SELECT contact_id FROM '.self::SHARED_TABLE.' WHERE '. 514 // $filter[tid] === null is used by sync-collection report, in which case we need to return deleted shares, to remove them from devices 515 (array_key_exists('tid', $filter) && !isset($filter['tid']) ? '' : 'shared_deleted IS NULL AND '). 516 $this->db->expression(self::SHARED_TABLE, ['shared_with' => $filter['owner'] ?? array_keys($this->grants)]).')'; 517 } 518 519 // add filter for read ACL in sql, if user is NOT the owner of the addressbook 520 if (isset($this->grants) && !$ignore_acl) 521 { 522 // add read ACL for groupmembers (they have no 523 if ($GLOBALS['egw_info']['user']['preferences']['common']['account_selection'] == 'groupmembers' && 524 (!isset($filter['owner']) || in_array('0',(array)$filter['owner']))) 525 { 526 $groupmembers = array(); 527 foreach($GLOBALS['egw']->accounts->memberships($GLOBALS['egw_info']['user']['account_id'],true) as $group_id) 528 { 529 if (($members = $GLOBALS['egw']->accounts->members($group_id,true))) 530 { 531 $groupmembers = array_merge($groupmembers,$members); 532 } 533 } 534 $groupmember_sql = $this->db->expression($this->table_name, ' OR '.$this->table_name.'.',array( 535 'account_id' => array_unique($groupmembers), 536 )); 537 } 538 // we have no private grants in addressbook at the moment, they have then to be added here too 539 if (isset($filter['owner'])) 540 { 541 // no grants for selected owner/addressbook 542 if (!array_intersect((array)$filter['owner'],array_keys($this->grants))) 543 { 544 if (!isset($groupmember_sql)) return false; 545 $filter[] = '('.substr($groupmember_sql,4)." OR $shared_sql)"; 546 unset($filter['owner']); 547 } 548 // for an owner filter, which does NOT include current user, filter out private entries 549 elseif (!in_array($GLOBALS['egw_info']['user']['account_id'], (array)$filter['owner'])) 550 { 551 $filter[] = '('.$this->db->expression($this->table_name, $this->table_name.'.', ['contact_owner' => $filter['owner'], 'contact_private' => 0]). 552 " OR $shared_sql)"; 553 unset($filter['owner']); 554 } 555 // if multiple addressbooks (incl. current owner) are searched, we need full acl filter 556 elseif(is_array($filter['owner']) && count($filter['owner']) > 1) 557 { 558 $filter[] = "($this->table_name.contact_owner=".(int)$GLOBALS['egw_info']['user']['account_id']. 559 " OR $shared_sql". 560 " OR contact_private=0 AND $this->table_name.contact_owner IN (". 561 implode(',',array_keys($this->grants)).") $groupmember_sql OR $this->table_name.contact_owner IS NULL)"; 562 } 563 else 564 { 565 $filter[] = '('.$this->db->expression($this->table_name, $this->table_name.'.', ['contact_owner' => $filter['owner']]). 566 " OR $shared_sql)"; 567 unset($filter['owner']); 568 } 569 } 570 else // search all addressbooks, incl. accounts 571 { 572 if ($this->account_repository != 'sql' && $this->contact_repository != 'sql-ldap') 573 { 574 $filter[] = $this->table_name.'.contact_owner != 0'; // in case there have been accounts in sql previously 575 } 576 $filter[] = "($this->table_name.contact_owner=".(int)$GLOBALS['egw_info']['user']['account_id']. 577 " OR $shared_sql". 578 ($this->grants ? " OR contact_private=0 AND $this->table_name.contact_owner IN (". 579 implode(',',array_keys($this->grants)).")" : ''). 580 $groupmember_sql." OR $this->table_name.contact_owner IS NULL)"; 581 } 582 } 583 if (isset($filter['list'])) 584 { 585 if ($filter['list'] < 0) 586 { 587 $join .= " JOIN egw_acl ON $this->table_name.account_id=acl_account AND acl_appname='phpgw_group' AND ". 588 $this->db->expression('egw_acl', array('acl_location' => $filter['list'])); 589 } 590 else 591 { 592 $join .= " JOIN $this->ab2list_table ON $this->table_name.contact_id=$this->ab2list_table.contact_id AND ". 593 $this->db->expression($this->ab2list_table, array('list_id' => $filter['list'])); 594 } 595 unset($filter['list']); 596 } 597 // add join to show only active accounts (only if accounts are shown and in sql and we not already join the accounts table, eg. used by admin) 598 if ((is_array($owner) ? in_array(0, $owner) : !$owner) && substr($this->account_repository,0,3) == 'sql' && 599 strpos($join,$GLOBALS['egw']->accounts->backend->table) === false && !array_key_exists('account_id',$filter)) 600 { 601 $join .= self::ACCOUNT_ACTIVE_JOIN; 602 if ($GLOBALS['egw_info']['user']['preferences']['addressbook']['hide_accounts'] === '0') 603 { 604 $filter[] = str_replace('UNIX_TIMESTAMP(NOW())',time(),self::ACOUNT_ACTIVE_FILTER); 605 } 606 } 607 if ($join || ($criteria && is_string($criteria)) || ($criteria && is_array($criteria) && $order_by)) // search also adds a join for custom fields! 608 { 609 switch(gettype($only_keys)) 610 { 611 case 'boolean': 612 // Correctly handled by parent class 613 break; 614 case 'string': 615 $only_keys = explode(',',$only_keys); 616 // fall through 617 } 618 // postgres requires that expressions in order by appear in the columns of a distinct select 619 $all_matches = null; 620 if ($this->db->Type != 'mysql' && preg_match_all("/(#?[a-zA-Z_.]+) *(<> *''|IS NULL|IS NOT NULL)? *(ASC|DESC)?(,|$)/ui", 621 $order_by, $all_matches, PREG_SET_ORDER)) 622 { 623 if (!is_array($extra_cols)) $extra_cols = $extra_cols ? explode(',',$extra_cols) : array(); 624 foreach($all_matches as $matches) 625 { 626 $table = ''; 627 $column = $matches[1]; 628 if ($column[0] == '#') continue; // order by custom field is handeled in so_sql_cf anyway 629 if (($key = array_search($column, $this->db_cols)) !== false) $column = $key; 630 if (strpos($column,'.') === false) 631 { 632 $table = $column == $this->extra_value ? $this->extra_table : $this->table_name; 633 if (isset($this->db_cols[$column])) 634 { 635 $table .= '.'; 636 } 637 else 638 { 639 $table = ''; 640 } 641 } 642 $extra_cols[] = $table.$column.' '.$matches[2]; 643 //_debug_array($matches); 644 if (!empty($order_by) && $table) // postgres requires explizit order by 645 { 646 $order_by = str_replace($matches[0],$table.$column.' '.$matches[2].' '.$matches[3].$matches[4],$order_by); 647 } 648 } 649 //_debug_array($order_by); _debug_array($extra_cols); 650 } 651 652 // Understand search by date with wildcard (????.10.??) according to user date preference 653 if(is_string($criteria) && strpos($criteria, '?') !== false) 654 { 655 // First, check for a 'date', with wildcards, in the user's format 656 $date_regex = str_replace('Q','d', 657 str_replace(array('Y','m','d','.','-'), 658 array('(?P<Y>(?:\?|\Q){4})','(?P<m>(?:\?|\Q){2})','(?P<d>(?:\?|\Q){2})','\.','\-'), 659 $GLOBALS['egw_info']['user']['preferences']['common']['dateformat'])); 660 661 if(preg_match_all('$'.$date_regex.'$', $criteria, $matches)) 662 { 663 foreach($matches[0] as $m_id => $match) 664 { 665 // Birthday is Y-m-d 666 $criteria = str_replace($match, "*{$matches['Y'][$m_id]}-{$matches['m'][$m_id]}-{$matches['d'][$m_id]}*",$criteria); 667 } 668 } 669 } 670 } 671 // shared with column and filter 672 if (!is_array($extra_cols)) $extra_cols = $extra_cols ? explode(',',$extra_cols) : array(); 673 $shared_with = '(SELECT '.$this->db->group_concat('DISTINCT shared_with').' FROM '.self::SHARED_TABLE. 674 ' WHERE '.self::SHARED_TABLE.'.contact_id='.$this->table_name.'.contact_id AND shared_deleted IS NULL)'; 675 if (($key = array_search('shared_with', $extra_cols)) !== false) 676 { 677 $extra_cols[$key] = "$shared_with AS shared_with"; 678 } 679 switch ((string)$filter['shared_with']) 680 { 681 case '': // filter not set 682 break; 683 case 'not': 684 $filter[] = $shared_with.' IS NULL'; 685 break; 686 case 'shared': 687 $filter[] = $shared_with.' IS NOT NULL'; 688 break; 689 default: 690 $join .= ' JOIN '.self::SHARED_TABLE.' sw ON '.$this->table_name.'.contact_id=sw.contact_id AND sw.'. 691 $this->db->expression(self::SHARED_TABLE, ['shared_with' => $filter['shared_with']]). 692 ' AND sw.shared_deleted IS NULL'; 693 break; 694 } 695 unset($filter['shared_with']); 696 697 $rows =& parent::search($criteria,$only_keys,$order_by,$extra_cols,$wildcard,$empty,$op,$start,$filter,$join,$need_full_no_count); 698 699 if ($start === false) $this->total = is_array($rows) ? count($rows) : 0; // so_sql sets total only for $start !== false! 700 701 return $rows; 702 } 703 704 /** 705 * fix cat_id filter to search in comma-separated multiple cats and return subcats 706 * 707 * @internal 708 * @param int|array $cat_id 709 * @return string sql to filter by given cat 710 */ 711 function _cat_filter($cat_id, $not='') 712 { 713 if (!is_object($GLOBALS['egw']->categories)) 714 { 715 $GLOBALS['egw']->categories = new Api\Categories; 716 } 717 foreach($GLOBALS['egw']->categories->return_all_children($cat_id) as $cat) 718 { 719 $cat_filter[] = $this->db->concat("','", 'cat_id', "','")." $not LIKE '%,$cat,%'"; 720 } 721 $cfilter = '('.implode(' OR ',$cat_filter).')'; 722 if(!empty($not)) 723 { 724 $cfilter = "( $cfilter OR cat_id IS NULL )"; 725 } 726 return $cfilter; 727 } 728 729 /** 730 * fix cat_id criteria to search in comma-separated multiple cats 731 * 732 * @internal 733 * @param int|array|string $cats 734 * @return array of sql-strings to be OR'ed or AND'ed together 735 */ 736 function _cat_search($cats) 737 { 738 $cat_filter = array(); 739 foreach(is_array($cats) ? $cats : (is_numeric($cats) ? array($cats) : explode(',',$cats)) as $cat) 740 { 741 if (is_numeric($cat)) $cat_filter[] = $this->db->concat("','",cat_id,"','")." LIKE '%,$cat,%'"; 742 } 743 return $cat_filter; 744 } 745 746 /** 747 * Change the ownership of contacts and distribution-lists owned by a given account 748 * 749 * @param int $account_id account-id of the old owner 750 * @param int $new_owner account-id of the new owner 751 */ 752 function change_owner($account_id,$new_owner) 753 { 754 if (!$new_owner) // otherwise we would create an account (contact_owner==0) 755 { 756 throw Api\Exception\WrongParameter(__METHOD__."($account_id, $new_owner) new owner must not be 0!"); 757 } 758 // contacts 759 $this->db->update($this->table_name,array( 760 'contact_owner' => $new_owner, 761 ),array( 762 'contact_owner' => $account_id, 763 ),__LINE__,__FILE__); 764 765 // cfs 766 $this->db->update(self::EXTRA_TABLE, array( 767 'contact_owner' => $new_owner 768 ),array( 769 'contact_owner' => $account_id 770 ), __LINE__, __FILE__); 771 772 // lists 773 $this->db->update($this->lists_table, array( 774 'list_owner' => $new_owner, 775 ),array( 776 'list_owner' => $account_id, 777 ),__LINE__,__FILE__); 778 } 779 780 /** 781 * Get the availible distribution lists for givens users and groups 782 * 783 * @param array $uids array of user or group id's for $uid_column='list_owners', or values for $uid_column, 784 * or whole where array: column-name => value(s) pairs 785 * @param string $uid_column ='list_owner' column-name or null to use $uids as where array 786 * @param string $member_attr =null null: no members, 'contact_uid', 'contact_id', 'caldav_name' return members as that attribute 787 * @param boolean|int|array $limit_in_ab =false if true only return members from the same owners addressbook, 788 * if int|array only return members from the given owners addressbook(s) 789 * @return array with list_id => array(list_id,list_name,list_owner,...) pairs 790 */ 791 function get_lists($uids,$uid_column='list_owner',$member_attr=null,$limit_in_ab=false) 792 { 793 if (is_array($uids) && array_key_exists('list_id', $uids)) 794 { 795 $uids[] = $this->db->expression($this->lists_table, $this->lists_table.'.',array('list_id' => $uids['list_id'])); 796 unset($uids['list_id']); 797 } 798 $lists = array(); 799 foreach($this->db->select($this->lists_table,'*',$uid_column?array($uid_column=>$uids):$uids,__LINE__,__FILE__, 800 false,'ORDER BY list_owner<>'.(int)$GLOBALS['egw_info']['user']['account_id'].',list_name') as $row) 801 { 802 if ($member_attr) $row['members'] = array(); 803 $lists[$row['list_id']] = $row; 804 } 805 if ($lists && $member_attr && in_array($member_attr,array('contact_id','contact_uid','caldav_name'))) 806 { 807 if ($limit_in_ab) 808 { 809 $in_ab_join = " JOIN $this->lists_table ON $this->lists_table.list_id=$this->ab2list_table.list_id AND $this->lists_table."; 810 if (!is_bool($limit_in_ab)) 811 { 812 $in_ab_join .= $this->db->expression($this->lists_table, array('list_owner'=>$limit_in_ab)); 813 } 814 else 815 { 816 $in_ab_join .= "list_owner=$this->table_name.contact_owner"; 817 } 818 } 819 foreach($this->db->select($this->ab2list_table,"$this->ab2list_table.list_id,$this->table_name.$member_attr", 820 $this->db->expression($this->ab2list_table, $this->ab2list_table.'.', array('list_id'=>array_keys($lists))), 821 __LINE__,__FILE__,false,$member_attr=='contact_id' ? '' : 822 '',false,0,"JOIN $this->table_name ON $this->ab2list_table.contact_id=$this->table_name.contact_id".$in_ab_join) as $row) 823 { 824 $lists[$row['list_id']]['members'][] = $row[$member_attr]; 825 } 826 } 827 /* groups as list are implemented currently in Contacts\Storage::get_lists() for all backends 828 if ($uid_column == 'list_owner' && in_array(0, (array)$uids) && (!$limit_in_ab || in_array(0, (array)$limit_in_ab))) 829 { 830 foreach($GLOBALS['egw']->accounts->search(array( 831 'type' => 'groups' 832 )) as $account_id => $group) 833 { 834 $list = array( 835 'list_id' => $account_id, 836 'list_name' => Api\Accounts::format_username($group['account_lid'], '', '', $account_id), 837 'list_owner' => 0, 838 'list_uid' => 'group'.$account_id, 839 'list_carddav_name' => 'group'.$account_id.'.vcf', 840 'list_etag' => md5(json_encode($GLOBALS['egw']->accounts->members($account_id, true))) 841 ); 842 if ($member_attr) 843 { 844 $list['members'] = array(); // ToDo 845 } 846 $lists[(string)$account_id] = $list; 847 } 848 }*/ 849 //error_log(__METHOD__.'('.array2string($uids).", '$uid_column', '$member_attr') returning ".array2string($lists)); 850 return $lists; 851 } 852 853 /** 854 * Adds / updates a distribution list 855 * 856 * @param string|array $keys list-name or array with column-name => value pairs to specify the list 857 * @param int $owner user- or group-id 858 * @param array $contacts =array() contacts to add (only for not yet existing lists!) 859 * @param array &$data=array() values for keys 'list_uid', 'list_carddav_name', 'list_name' 860 * @return int|boolean integer list_id or false on error 861 */ 862 function add_list($keys,$owner,$contacts=array(),array &$data=array()) 863 { 864 //error_log(__METHOD__.'('.array2string($keys).", $owner, ".array2string($contacts).', '.array2string($data).') '.function_backtrace()); 865 if (!$keys && !$data || !(int)$owner) return false; 866 867 if ($keys && !is_array($keys)) $keys = array('list_name' => $keys); 868 if ($keys) 869 { 870 $keys['list_owner'] = $owner; 871 } 872 else 873 { 874 $data['list_owner'] = $owner; 875 } 876 if (!$keys || !($list_id = $this->db->select($this->lists_table,'list_id',$keys,__LINE__,__FILE__)->fetchColumn())) 877 { 878 $data['list_created'] = time(); 879 $data['list_creator'] = $GLOBALS['egw_info']['user']['account_id']; 880 } 881 else 882 { 883 $data[] = 'list_etag=list_etag+1'; 884 } 885 $data['list_modified'] = time(); 886 $data['list_modifier'] = $GLOBALS['egw_info']['user']['account_id']; 887 if (!$data['list_id']) unset($data['list_id']); 888 889 if (!$this->db->insert($this->lists_table,$data,$keys,__LINE__,__FILE__)) return false; 890 891 if (!$list_id && ($list_id = $this->db->get_last_insert_id($this->lists_table,'list_id')) && 892 (!isset($data['list_uid']) || !isset($data['list_carddav_name']))) 893 { 894 $update = array(); 895 if (!isset($data['list_uid'])) 896 { 897 $update['list_uid'] = $data['list_uid'] = Api\CalDAV::generate_uid('addresbook-lists', $list_id); 898 } 899 if (!isset($data['list_carddav_name'])) 900 { 901 $update['list_carddav_name'] = $data['list_carddav_name'] = $data['list_uid'].'.vcf'; 902 } 903 $this->db->update($this->lists_table,$update,array('list_id'=>$list_id),__LINE__,__FILE__); 904 905 $this->add2list($list_id,$contacts,array()); 906 } 907 if ($keys) $data += $keys; 908 //error_log(__METHOD__.'('.array2string($keys).", $owner, ...) data=".array2string($data).' returning '.array2string($list_id)); 909 return $list_id; 910 } 911 912 /** 913 * Adds contact(s) to a distribution list 914 * 915 * @param int|array $contact contact_id(s) 916 * @param int $list list-id 917 * @param array $existing =null array of existing contact-id(s) of list, to not reread it, eg. array() 918 * @return false on error 919 */ 920 function add2list($contact,$list,array $existing=null) 921 { 922 if (!(int)$list || !is_array($contact) && !(int)$contact) return false; 923 924 if (!is_array($existing)) 925 { 926 $existing = array(); 927 foreach($this->db->select($this->ab2list_table,'contact_id',array('list_id'=>$list),__LINE__,__FILE__) as $row) 928 { 929 $existing[] = $row['contact_id']; 930 } 931 } 932 if (!($to_add = array_diff((array)$contact,$existing))) 933 { 934 return true; // no need to insert it, would give sql error 935 } 936 foreach($to_add as $contact) 937 { 938 $this->db->insert($this->ab2list_table,array( 939 'contact_id' => $contact, 940 'list_id' => $list, 941 'list_added' => time(), 942 'list_added_by' => $GLOBALS['egw_info']['user']['account_id'], 943 ),array(),__LINE__,__FILE__); 944 } 945 // update etag 946 return $this->db->update($this->lists_table,array( 947 'list_etag=list_etag+1', 948 'list_modified' => time(), 949 'list_modifier' => $GLOBALS['egw_info']['user']['account_id'], 950 ),array( 951 'list_id' => $list, 952 ),__LINE__,__FILE__); 953 } 954 955 /** 956 * Removes one contact from distribution list(s) 957 * 958 * @param int|array $contact contact_id(s) 959 * @param int $list =null list-id or null to remove from all lists 960 * @return false on error 961 */ 962 function remove_from_list($contact,$list=null) 963 { 964 if (!(int)$list && !is_null($list) || !is_array($contact) && !(int)$contact) return false; 965 966 $where = array( 967 'contact_id' => $contact, 968 ); 969 if (!is_null($list)) 970 { 971 $where['list_id'] = $list; 972 } 973 else 974 { 975 $list = array(); 976 foreach($this->db->select($this->ab2list_table,'list_id',$where,__LINE__,__FILE__) as $row) 977 { 978 $list[] = $row['list_id']; 979 } 980 } 981 if (!$this->db->delete($this->ab2list_table,$where,__LINE__,__FILE__)) 982 { 983 return false; 984 } 985 foreach((array)$list as $list_id) 986 { 987 $this->db->update($this->lists_table,array( 988 'list_etag=list_etag+1', 989 'list_modified' => time(), 990 'list_modifier' => $GLOBALS['egw_info']['user']['account_id'], 991 ),array( 992 'list_id' => $list_id, 993 ),__LINE__,__FILE__); 994 } 995 return true; 996 } 997 998 /** 999 * Deletes a distribution list (incl. it's members) 1000 * 1001 * @param int|array $list list_id(s) 1002 * @return number of members deleted or false if list does not exist 1003 */ 1004 function delete_list($list) 1005 { 1006 if (!$this->db->delete($this->lists_table,array('list_id' => $list),__LINE__,__FILE__)) return false; 1007 1008 $this->db->delete($this->ab2list_table,array('list_id' => $list),__LINE__,__FILE__); 1009 1010 return $this->db->affected_rows(); 1011 } 1012 1013 /** 1014 * Get ctag (max list_modified as timestamp) for lists 1015 * 1016 * @param int|array $owner =null null for all lists user has access too 1017 * @return int 1018 */ 1019 function lists_ctag($owner=null) 1020 { 1021 if (is_null($owner)) $owner = array_keys($this->grants); 1022 1023 if (!($modified = $this->db->select($this->lists_table,'MAX(list_modified)',array('list_owner'=>$owner), 1024 __LINE__,__FILE__)->fetchColumn())) 1025 { 1026 return 0; 1027 } 1028 return $this->db->from_timestamp($modified); 1029 } 1030 1031 /** 1032 * Reads a contact, reimplemented to use the uid, if a non-numeric key is given 1033 * 1034 * @param int|string|array $keys 1035 * @param string|array $extra_cols 1036 * @param string $join 1037 * @return array|boolean 1038 */ 1039 function read($keys,$extra_cols='',$join='') 1040 { 1041 if (isset($GLOBALS['egw_info']['user']['preferences']['syncml']['minimum_uid_length'])) { 1042 $minimum_uid_length = $GLOBALS['egw_info']['user']['preferences']['syncml']['minimum_uid_length']; 1043 } else { 1044 $minimum_uid_length = 8; 1045 } 1046 1047 if (!is_array($keys) && !is_numeric($keys)) 1048 { 1049 $keys = array('uid' => $keys); 1050 } 1051 try { 1052 $contact = parent::read($keys,$extra_cols,$join); 1053 } 1054 // catch Illegal mix of collations (ascii_general_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '=' (1267) 1055 // caused by non-ascii chars compared with ascii field uid 1056 catch(Api\Db\Exception $e) { 1057 _egw_log_exception($e); 1058 return false; 1059 } 1060 1061 // enforce a minium uid strength 1062 if (is_array($contact) && (!isset($contact['uid']) 1063 || strlen($contact['uid']) < $minimum_uid_length)) { 1064 parent::update(array('uid' => Api\CalDAV::generate_uid('addressbook',$contact['id']))); 1065 } 1066 if (is_array($contact)) 1067 { 1068 $contact['shared'] = $this->read_shared($contact['id']); 1069 } 1070 return $contact; 1071 } 1072 1073 /** 1074 * Read sharing information of a contact 1075 * 1076 * @param int $id contact_id to read 1077 * @param ?boolean $deleted =false false: ignore deleted, true: only deleted, null: both 1078 * @return array of array with values for keys "shared_(with|writable|by|at|id|deleted)" 1079 */ 1080 function read_shared($id, $deleted=false) 1081 { 1082 $shared = []; 1083 $where = ['contact_id' => $id]; 1084 if (isset($deleted)) $where[] = $deleted ? 'shared_deleted IS NOT NULL' : 'shared_deleted IS NULL'; 1085 foreach($this->db->select(self::SHARED_TABLE, '*', $where, __LINE__, __FILE__, false) as $row) 1086 { 1087 $row['shared_at'] = Api\DateTime::server2user($row['shared_at'], 'object'); 1088 $shared[] = $row; 1089 } 1090 return $shared; 1091 } 1092 1093 /** 1094 * Save sharing information of a contact 1095 * 1096 * @param int $id 1097 * @param array $shared array of array with values for keys "shared_(with|writable|by|at|id)" 1098 * @return array of array with values for keys "shared_(with|writable|by|at|id)" 1099 */ 1100 function save_shared($id, array $shared) 1101 { 1102 $ids = []; 1103 foreach($shared as $key => &$data) 1104 { 1105 if (empty($data['shared_id'])) 1106 { 1107 unset($data['shared_id']); 1108 $data['contact_id'] = $id; 1109 $data['shared_at'] = Api\DateTime::user2server($data['shared_at'] ?: 'now'); 1110 $data['shared_by'] = $data['shared_by'] ?: $GLOBALS['egw_info']['user']['account_id']; 1111 $data['shared_deleted'] = null; 1112 foreach($shared as $ckey => $check) 1113 { 1114 if (!empty($check['shared_id']) && 1115 $data['shared_with'] == $check['shared_with'] && 1116 $data['shared_by'] == $check['shared_by']) 1117 { 1118 if ($data['shared_writable'] == $check['shared_writable']) 1119 { 1120 unset($shared[$key]); 1121 continue 2; // no need to save identical entry 1122 } 1123 // remove 1124 unset($shared[$ckey]); 1125 break; 1126 } 1127 } 1128 $this->db->insert(self::SHARED_TABLE, $data, 1129 $where = array_intersect_key($data, array_flip(['shared_by','shared_with','contact_id','share_id'])), __LINE__, __FILE__); 1130 // if we resurect a previous deleted share, we dont get the shared_id back, need to query it 1131 $data['shared_id'] = $this->db->select(self::SHARED_TABLE, 'shared_id', $where, __LINE__, __FILE__)->fetchColumn(); 1132 } 1133 $ids[] = (int)$data['shared_id']; 1134 } 1135 $delete = ['contact_id' => $id, 'shared_deleted IS NULL']; 1136 if ($ids) $delete[] = 'shared_id NOT IN ('.implode(',', $ids).')'; 1137 $this->db->update(self::SHARED_TABLE, ['shared_deleted' => new Api\DateTime('now')], $delete, __LINE__, __FILE__); 1138 foreach($shared as &$data) 1139 { 1140 $data['shared_at'] = Api\DateTime::server2user($data['shared_at'], 'object'); 1141 } 1142 return $shared; 1143 } 1144 1145 /** 1146 * deletes row representing keys in internal data or the supplied $keys if != null 1147 * 1148 * reimplented to also delete sharing info 1149 * 1150 * @param array|int $keys =null if given array with col => value pairs to characterise the rows to delete, or integer autoinc id 1151 * @param boolean $only_return_ids =false return $ids of delete call to db object, but not run it (can be used by extending classes!) 1152 * @return int|array affected rows, should be 1 if ok, 0 if an error or array with id's if $only_return_ids 1153 */ 1154 function delete($keys=null,$only_return_ids=false) 1155 { 1156 if (!$only_return_ids) 1157 { 1158 if (is_scalar($keys)) 1159 { 1160 $query = ['contact_id' => $keys]; 1161 } 1162 elseif (!isset($keys['contact_id'])) 1163 { 1164 $query = parent::delete($keys,true); 1165 } 1166 $this->db->delete(self::SHARED_TABLE, $query ?? $keys, __LINE__, __FILE__); 1167 } 1168 return parent::delete($keys, $only_return_ids); 1169 } 1170 1171 /** 1172 * Saves a contact, reimplemented to check a given etag and set a uid 1173 * 1174 * @param array $keys if given $keys are copied to data before saveing => allows a save as 1175 * @param string|array $extra_where =null extra where clause, eg. to check the etag, returns 'nothing_affected' if not affected rows 1176 * @return int 0 on success and errno != 0 else 1177 */ 1178 function save($keys = NULL, $extra_where = NULL) 1179 { 1180 unset($extra_where); // not used, but required by function signature 1181 1182 if (isset($GLOBALS['egw_info']['user']['preferences']['syncml']['minimum_uid_length'])) { 1183 $minimum_uid_length = $GLOBALS['egw_info']['user']['preferences']['syncml']['minimum_uid_length']; 1184 } else { 1185 $minimum_uid_length = 8; 1186 } 1187 1188 if (is_array($keys) && count($keys)) $this->data_merge($keys); 1189 1190 $new_entry = !$this->data['id']; 1191 1192 if (isset($this->data['etag'])) // do we have an etag in the data to write 1193 { 1194 $etag = $this->data['etag']; 1195 unset($this->data['etag']); 1196 if (!($err = parent::save(array('contact_etag=contact_etag+1'),array('contact_etag' => $etag)))) 1197 { 1198 $this->data['etag'] = $etag+1; 1199 } 1200 else 1201 { 1202 $this->data['etag'] = $etag; 1203 } 1204 } 1205 else 1206 { 1207 unset($this->data['etag']); 1208 if (!($err = parent::save(array('contact_etag=contact_etag+1'))) && $new_entry) 1209 { 1210 $this->data['etag'] = 0; 1211 } 1212 } 1213 1214 $update = array(); 1215 // enforce a minium uid strength 1216 if (!isset($this->data['uid']) || strlen($this->data['uid']) < $minimum_uid_length) 1217 { 1218 $update['uid'] = Api\CalDAV::generate_uid('addressbook',$this->data['id']); 1219 //echo "<p>set uid={$this->data['uid']}, etag={$this->data['etag']}</p>"; 1220 } 1221 // set carddav_name, if not given by caller 1222 if (empty($this->data['carddav_name'])) 1223 { 1224 $update['carddav_name'] = $this->data['id'].'.vcf'; 1225 } 1226 // update photo in entry-directory, unless hinted it is unchanged 1227 if (!$err && $this->data['photo_unchanged'] !== true) 1228 { 1229 // in case files bit-field is not available read it from DB 1230 if (!isset($this->data['files'])) 1231 { 1232 $this->data['files'] = (int)$this->db->select($this->table_name, 'contact_files', array( 1233 'contact_id' => $this->data['id'], 1234 ), __LINE__, __FILE__)->fetchColumn(); 1235 } 1236 $path = Api\Link::vfs_path('addressbook', $this->data['id'], Api\Contacts::FILES_PHOTO); 1237 $backup = Api\Vfs::$is_root; Api\Vfs::$is_root = true; 1238 if (empty($this->data['jpegphoto'])) 1239 { 1240 unlink($path); 1241 $update['files'] = $this->data['files'] & ~Api\Contacts::FILES_BIT_PHOTO; 1242 } 1243 else 1244 { 1245 file_put_contents($path, $this->data['jpegphoto']); 1246 $update['files'] = $this->data['files'] | Api\Contacts::FILES_BIT_PHOTO; 1247 } 1248 Api\Vfs::$is_root = $backup; 1249 } 1250 if (!$err && $update) 1251 { 1252 parent::update($update); 1253 } 1254 // save sharing information, if given, eg. not the case for CardDAV 1255 if (!$err && isset($this->data['shared'])) 1256 { 1257 $this->data['shared'] = $this->save_shared($this->data['id'], $this->data['shared']); 1258 } 1259 return $err; 1260 } 1261 1262 1263 /** 1264 * Read data of a distribution list 1265 * 1266 * @param int $list list_id 1267 * @return array of data or false if list does not exist 1268 */ 1269 function read_list($list) 1270 { 1271 if (!$list) return false; 1272 1273 return $this->db->select($this->lists_table,'*',array('list_id'=>$list),__LINE__,__FILE__)->fetch(); 1274 } 1275 1276 /** 1277 * saves custom field data 1278 * Re-implemented to deal with extra contact_owner column 1279 * 1280 * @param array $data data to save (cf's have to be prefixed with self::CF_PREFIX = #) 1281 * @param array $extra_cols =array() extra-data to be saved 1282 * @return bool false on success, errornumber on failure 1283 */ 1284 function save_customfields(&$data, array $extra_cols=array()) 1285 { 1286 return parent::save_customfields($data, array('contact_owner' => $data['owner'])+$extra_cols); 1287 } 1288 1289 /** 1290 * Deletes custom field data 1291 * Implemented to deal with LDAP backend, which saves CFs in SQL, but the account record is in LDAP 1292 */ 1293 function delete_customfields($data) 1294 { 1295 $this->db->delete($this->extra_table,$data,__LINE__,__FILE__); 1296 } 1297} 1298