githubFork/roundcubemail.git

Fix missing or not up-to-date CATEGORIES entry in vCard export (#1490277)

Aleksander Machniak

2015-02-22 5321cbd4989658576533b6a4a4205269a96db751

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