githubFork/roundcubemail.git

alecpl

2010-09-25 e019f2d0f2dc2fbfa345ab5d7ae85e67bfdd76b8

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 \|
A	8	\| Copyright (C) 2006-2010, Roundcube Dev. - Switzerland \|
f11541	9	\| Licensed under the GNU GPL \|
T	10	\| \|
	11	\| PURPOSE: \|
	12	\| Interface to the local address book database \|
	13	\| \|
	14	+-----------------------------------------------------------------------+
	15	\| Author: Thomas Bruederli <roundcube@gmail.com> \|
	16	+-----------------------------------------------------------------------+
	17
638fb8	18	$Id$
f11541	19
T	20	*/
	21
6d969b	22
T	23	/**
	24	* Model class for the local address book database
	25	*
	26	* @package Addressbook
	27	*/
cc97ea	28	class rcube_contacts extends rcube_addressbook
f11541	29	{
495c0e	30	// protected for backward compat. with some plugins
982e0b	31	protected $db_name = 'contacts';
A	32	protected $db_groups = 'contactgroups';
	33	protected $db_groupmembers = 'contactgroupmembers';
	34
3d6c04	35	private $db = null;
A	36	private $user_id = 0;
	37	private $filter = null;
	38	private $result = null;
	39	private $search_fields;
	40	private $search_string;
566b14	41	private $cache;
3d6c04	42	private $table_cols = array('name', 'email', 'firstname', 'surname', 'vcard');
25fdec	43
982e0b	44	// public properties
3d6c04	45	var $primary_key = 'contact_id';
A	46	var $readonly = false;
	47	var $groups = true;
	48	var $list_page = 1;
	49	var $page_size = 10;
	50	var $group_id = 0;
	51	var $ready = false;
f11541	52
25fdec	53
3d6c04	54	/**
A	55	* Object constructor
	56	*
	57	* @param object Instance of the rcube_db class
	58	* @param integer User-ID
	59	*/
	60	function __construct($dbconn, $user)
f11541	61	{
3d6c04	62	$this->db = $dbconn;
A	63	$this->user_id = $user;
	64	$this->ready = $this->db && !$this->db->is_error();
f11541	65	}
T	66
	67
3d6c04	68	/**
A	69	* Save a search string for future listings
	70	*
	71	* @param string SQL params to use in listing method
	72	*/
	73	function set_search_set($filter)
f11541	74	{
3d6c04	75	$this->filter = $filter;
566b14	76	$this->cache = null;
3d6c04	77	}
A	78
25fdec	79
3d6c04	80	/**
A	81	* Getter for saved search properties
	82	*
	83	* @return mixed Search properties used by this class
	84	*/
	85	function get_search_set()
	86	{
	87	return $this->filter;
	88	}
	89
	90
	91	/**
	92	* Setter for the current group
	93	* (empty, has to be re-implemented by extending class)
	94	*/
	95	function set_group($gid)
	96	{
	97	$this->group_id = $gid;
566b14	98	$this->cache = null;
3d6c04	99	}
A	100
	101
	102	/**
	103	* Reset all saved results and search parameters
	104	*/
	105	function reset()
	106	{
	107	$this->result = null;
	108	$this->filter = null;
	109	$this->search_fields = null;
	110	$this->search_string = null;
566b14	111	$this->cache = null;
3d6c04	112	}
25fdec	113
3d6c04	114
A	115	/**
	116	* List all active contact groups of this source
	117	*
	118	* @param string Search string to match group name
	119	* @return array Indexed list of contact groups, each a hash array
	120	*/
	121	function list_groups($search = null)
	122	{
	123	$results = array();
25fdec	124
3d6c04	125	if (!$this->groups)
A	126	return $results;
d17a7f	127
631967	128	$sql_filter = $search ? " AND " . $this->db->ilike('name', '%'.$search.'%') : '';
3d6c04	129
A	130	$sql_result = $this->db->query(
982e0b	131	"SELECT * FROM ".get_table_name($this->db_groups).
3d6c04	132	" WHERE del<>1".
A	133	" AND user_id=?".
	134	$sql_filter.
	135	" ORDER BY name",
	136	$this->user_id);
	137
	138	while ($sql_result && ($sql_arr = $this->db->fetch_assoc($sql_result))) {
	139	$sql_arr['ID'] = $sql_arr['contactgroup_id'];
	140	$results[] = $sql_arr;
	141	}
	142
	143	return $results;
	144	}
	145
	146
	147	/**
	148	* List the current set of contact records
	149	*
	150	* @param array List of cols to show
	151	* @param int Only return this number of records, use negative values for tail
	152	* @param boolean True to skip the count query (select only)
	153	* @return array Indexed list of contact records, each a hash array
	154	*/
	155	function list_records($cols=null, $subset=0, $nocount=false)
	156	{
	157	if ($nocount \|\| $this->list_page <= 1) {
	158	// create dummy result, we don't need a count now
	159	$this->result = new rcube_result_set();
	160	} else {
	161	// count all records
	162	$this->result = $this->count();
	163	}
	164
	165	$start_row = $subset < 0 ? $this->result->first + $this->page_size + $subset : $this->result->first;
	166	$length = $subset != 0 ? abs($subset) : $this->page_size;
	167
	168	if ($this->group_id)
982e0b	169	$join = " LEFT JOIN ".get_table_name($this->db_groupmembers)." AS m".
3d6c04	170	" ON (m.contact_id = c.".$this->primary_key.")";
A	171
	172	$sql_result = $this->db->limitquery(
982e0b	173	"SELECT * FROM ".get_table_name($this->db_name)." AS c" .
821a56	174	$join .
3d6c04	175	" WHERE c.del<>1" .
566b14	176	" AND c.user_id=?" .
A	177	($this->group_id ? " AND m.contactgroup_id=?" : "").
	178	($this->filter ? " AND (".$this->filter.")" : "") .
3d6c04	179	" ORDER BY c.name",
A	180	$start_row,
	181	$length,
	182	$this->user_id,
	183	$this->group_id);
	184
	185	while ($sql_result && ($sql_arr = $this->db->fetch_assoc($sql_result))) {
	186	$sql_arr['ID'] = $sql_arr[$this->primary_key];
	187	// make sure we have a name to display
	188	if (empty($sql_arr['name']))
	189	$sql_arr['name'] = $sql_arr['email'];
	190	$this->result->add($sql_arr);
	191	}
25fdec	192
3d6c04	193	$cnt = count($this->result->records);
A	194
	195	// update counter
	196	if ($nocount)
	197	$this->result->count = $cnt;
	198	else if ($this->list_page <= 1) {
	199	if ($cnt < $this->page_size && $subset == 0)
	200	$this->result->count = $cnt;
821a56	201	else if (isset($this->cache['count']))
A	202	$this->result->count = $this->cache['count'];
3d6c04	203	else
A	204	$this->result->count = $this->_count();
	205	}
25fdec	206
3d6c04	207	return $this->result;
A	208	}
	209
	210
	211	/**
	212	* Search contacts
	213	*
	214	* @param array List of fields to search in
	215	* @param string Search value
	216	* @param boolean True for strict (=), False for partial (LIKE) matching
	217	* @param boolean True if results are requested, False if count only
	218	* @param boolean True to skip the count query (select only)
25fdec	219	* @param array List of fields that cannot be empty
3d6c04	220	* @return Indexed list of contact records and 'count' value
A	221	*/
25fdec	222	function search($fields, $value, $strict=false, $select=true, $nocount=false, $required=array())
3d6c04	223	{
A	224	if (!is_array($fields))
	225	$fields = array($fields);
25fdec	226	if (!is_array($required) && !empty($required))
A	227	$required = array($required);
566b14	228
25fdec	229	$where = $and_where = array();
A	230
3d6c04	231	foreach ($fields as $col) {
A	232	if ($col == 'ID' \|\| $col == $this->primary_key) {
25fdec	233	$ids = !is_array($value) ? explode(',', $value) : $value;
A	234	$ids = $this->db->array2list($ids, 'integer');
	235	$where[] = 'c.' . $this->primary_key.' IN ('.$ids.')';
3d6c04	236	}
A	237	else if ($strict)
25fdec	238	$where[] = $this->db->quoteIdentifier($col).' = '.$this->db->quote($value);
3d6c04	239	else
25fdec	240	$where[] = $this->db->ilike($col, '%'.$value.'%');
3d6c04	241	}
25fdec	242
A	243	foreach ($required as $col) {
	244	$and_where[] = $this->db->quoteIdentifier($col).' <> '.$this->db->quote('');
	245	}
	246
	247	if (!empty($where))
	248	$where = join(' OR ', $where);
	249
	250	if (!empty($and_where))
	251	$where = ($where ? "($where) AND " : '') . join(' AND ', $and_where);
	252
	253	if (!empty($where)) {
	254	$this->set_search_set($where);
3d6c04	255	if ($select)
A	256	$this->list_records(null, 0, $nocount);
	257	else
	258	$this->result = $this->count();
	259	}
	260
	261	return $this->result;
	262	}
	263
	264
	265	/**
	266	* Count number of available contacts in database
	267	*
	268	* @return rcube_result_set Result object
	269	*/
	270	function count()
	271	{
566b14	272	$count = isset($this->cache['count']) ? $this->cache['count'] : $this->_count();
25fdec	273
566b14	274	return new rcube_result_set($count, ($this->list_page-1) * $this->page_size);
3d6c04	275	}
A	276
	277
	278	/**
	279	* Count number of available contacts in database
	280	*
	281	* @return int Contacts count
	282	*/
	283	private function _count()
	284	{
	285	if ($this->group_id)
982e0b	286	$join = " LEFT JOIN ".get_table_name($this->db_groupmembers)." AS m".
3d6c04	287	" ON (m.contact_id=c.".$this->primary_key.")";
25fdec	288
3d6c04	289	// count contacts for this user
A	290	$sql_result = $this->db->query(
	291	"SELECT COUNT(c.contact_id) AS rows".
982e0b	292	" FROM ".get_table_name($this->db_name)." AS c".
821a56	293	$join.
A	294	" WHERE c.del<>1".
3d6c04	295	" AND c.user_id=?".
A	296	($this->group_id ? " AND m.contactgroup_id=?" : "").
	297	($this->filter ? " AND (".$this->filter.")" : ""),
	298	$this->user_id,
	299	$this->group_id
4307cc	300	);
3d6c04	301
A	302	$sql_arr = $this->db->fetch_assoc($sql_result);
566b14	303
A	304	$this->cache['count'] = (int) $sql_arr['rows'];
	305
	306	return $this->cache['count'];
f11541	307	}
T	308
	309
3d6c04	310	/**
A	311	* Return the last result set
	312	*
	313	* @return Result array or NULL if nothing selected yet
	314	*/
	315	function get_result()
f11541	316	{
3d6c04	317	return $this->result;
f11541	318	}
25fdec	319
A	320
3d6c04	321	/**
A	322	* Get a specific contact record
	323	*
	324	* @param mixed record identifier(s)
	325	* @return Result object with all record fields or False if not found
	326	*/
	327	function get_record($id, $assoc=false)
f11541	328	{
3d6c04	329	// return cached result
A	330	if ($this->result && ($first = $this->result->first()) && $first[$this->primary_key] == $id)
	331	return $assoc ? $first : $this->result;
25fdec	332
a61bbb	333	$this->db->query(
982e0b	334	"SELECT * FROM ".get_table_name($this->db_name).
3d6c04	335	" WHERE contact_id=?".
A	336	" AND user_id=?".
	337	" AND del<>1",
	338	$id,
	339	$this->user_id
a61bbb	340	);
3d6c04	341
A	342	if ($sql_arr = $this->db->fetch_assoc()) {
	343	$sql_arr['ID'] = $sql_arr[$this->primary_key];
	344	$this->result = new rcube_result_set(1);
	345	$this->result->add($sql_arr);
	346	}
	347
	348	return $assoc && $sql_arr ? $sql_arr : $this->result;
a61bbb	349	}
3d6c04	350
A	351
	352	/**
	353	* Create a new contact record
	354	*
	355	* @param array Assoziative array with save data
	356	* @return The created record ID on success, False on error
	357	*/
	358	function insert($save_data, $check=false)
	359	{
	360	if (is_object($save_data) && is_a($save_data, rcube_result_set))
	361	return $this->insert_recset($save_data, $check);
	362
	363	$insert_id = $existing = false;
	364
	365	if ($check)
	366	$existing = $this->search('email', $save_data['email'], true, false);
	367
	368	$a_insert_cols = $a_insert_values = array();
	369
	370	foreach ($this->table_cols as $col)
	371	if (isset($save_data[$col])) {
	372	$a_insert_cols[] = $this->db->quoteIdentifier($col);
	373	$a_insert_values[] = $this->db->quote($save_data[$col]);
	374	}
	375
	376	if (!$existing->count && !empty($a_insert_cols)) {
	377	$this->db->query(
982e0b	378	"INSERT INTO ".get_table_name($this->db_name).
3d6c04	379	" (user_id, changed, del, ".join(', ', $a_insert_cols).")".
A	380	" VALUES (".intval($this->user_id).", ".$this->db->now().", 0, ".join(', ', $a_insert_values).")"
	381	);
	382
982e0b	383	$insert_id = $this->db->insert_id($this->db_name);
3d6c04	384	}
A	385
	386	// also add the newly created contact to the active group
	387	if ($insert_id && $this->group_id)
	388	$this->add_to_group($this->group_id, $insert_id);
	389
566b14	390	$this->cache = null;
A	391
3d6c04	392	return $insert_id;
A	393	}
	394
	395
	396	/**
	397	* Insert new contacts for each row in set
	398	*/
	399	function insert_recset($result, $check=false)
	400	{
	401	$ids = array();
	402	while ($row = $result->next()) {
	403	if ($insert = $this->insert($row, $check))
	404	$ids[] = $insert;
	405	}
	406	return $ids;
	407	}
	408
	409
	410	/**
	411	* Update a specific contact record
	412	*
	413	* @param mixed Record identifier
	414	* @param array Assoziative array with save data
	415	* @return True on success, False on error
	416	*/
	417	function update($id, $save_cols)
	418	{
	419	$updated = false;
	420	$write_sql = array();
	421
	422	foreach ($this->table_cols as $col)
	423	if (isset($save_cols[$col]))
	424	$write_sql[] = sprintf("%s=%s", $this->db->quoteIdentifier($col),
	425	$this->db->quote($save_cols[$col]));
	426
	427	if (!empty($write_sql)) {
	428	$this->db->query(
982e0b	429	"UPDATE ".get_table_name($this->db_name).
3d6c04	430	" SET changed=".$this->db->now().", ".join(', ', $write_sql).
A	431	" WHERE contact_id=?".
	432	" AND user_id=?".
	433	" AND del<>1",
	434	$id,
	435	$this->user_id
	436	);
	437
	438	$updated = $this->db->affected_rows();
	439	}
25fdec	440
3d6c04	441	return $updated;
A	442	}
	443
	444
	445	/**
	446	* Mark one or more contact records as deleted
	447	*
	448	* @param array Record identifiers
	449	*/
	450	function delete($ids)
	451	{
566b14	452	if (!is_array($ids))
A	453	$ids = explode(',', $ids);
3d6c04	454
a004bb	455	$ids = $this->db->array2list($ids, 'integer');
3d6c04	456
A	457	// flag record as deleted
	458	$this->db->query(
982e0b	459	"UPDATE ".get_table_name($this->db_name).
3d6c04	460	" SET del=1, changed=".$this->db->now().
A	461	" WHERE user_id=?".
	462	" AND contact_id IN ($ids)",
	463	$this->user_id
	464	);
	465
566b14	466	$this->cache = null;
A	467
3d6c04	468	return $this->db->affected_rows();
A	469	}
	470
	471
	472	/**
	473	* Remove all records from the database
	474	*/
	475	function delete_all()
	476	{
982e0b	477	$this->db->query("DELETE FROM ".get_table_name($this->db_name)." WHERE user_id = ?", $this->user_id);
566b14	478	$this->cache = null;
3d6c04	479	return $this->db->affected_rows();
A	480	}
	481
	482
	483	/**
	484	* Create a contact group with the given name
	485	*
	486	* @param string The group name
	487	* @return False on error, array with record props in success
	488	*/
	489	function create_group($name)
	490	{
	491	$result = false;
	492
	493	// make sure we have a unique name
	494	$name = $this->unique_groupname($name);
25fdec	495
3d6c04	496	$this->db->query(
982e0b	497	"INSERT INTO ".get_table_name($this->db_groups).
3d6c04	498	" (user_id, changed, name)".
A	499	" VALUES (".intval($this->user_id).", ".$this->db->now().", ".$this->db->quote($name).")"
	500	);
25fdec	501
982e0b	502	if ($insert_id = $this->db->insert_id($this->db_groups))
3d6c04	503	$result = array('id' => $insert_id, 'name' => $name);
25fdec	504
3d6c04	505	return $result;
A	506	}
	507
	508
	509	/**
	510	* Delete the given group (and all linked group members)
	511	*
	512	* @param string Group identifier
	513	* @return boolean True on success, false if no data was changed
	514	*/
	515	function delete_group($gid)
	516	{
	517	// flag group record as deleted
	518	$sql_result = $this->db->query(
982e0b	519	"UPDATE ".get_table_name($this->db_groups).
3d6c04	520	" SET del=1, changed=".$this->db->now().
A	521	" WHERE contactgroup_id=?",
	522	$gid
	523	);
	524
566b14	525	$this->cache = null;
A	526
3d6c04	527	return $this->db->affected_rows();
A	528	}
	529
	530
	531	/**
	532	* Rename a specific contact group
	533	*
	534	* @param string Group identifier
	535	* @param string New name to set for this group
	536	* @return boolean New name on success, false if no data was changed
	537	*/
	538	function rename_group($gid, $newname)
	539	{
	540	// make sure we have a unique name
	541	$name = $this->unique_groupname($newname);
25fdec	542
3d6c04	543	$sql_result = $this->db->query(
982e0b	544	"UPDATE ".get_table_name($this->db_groups).
3d6c04	545	" SET name=?, changed=".$this->db->now().
A	546	" WHERE contactgroup_id=?",
	547	$name, $gid
	548	);
	549
	550	return $this->db->affected_rows() ? $name : false;
	551	}
	552
	553
	554	/**
	555	* Add the given contact records the a certain group
	556	*
	557	* @param string Group identifier
	558	* @param array List of contact identifiers to be added
	559	* @return int Number of contacts added
	560	*/
	561	function add_to_group($group_id, $ids)
	562	{
	563	if (!is_array($ids))
	564	$ids = explode(',', $ids);
25fdec	565
3d6c04	566	$added = 0;
1126fc	567	$exists = array();
A	568
	569	// get existing assignments ...
	570	$sql_result = $this->db->query(
	571	"SELECT contact_id FROM ".get_table_name($this->db_groupmembers).
	572	" WHERE contactgroup_id=?".
	573	" AND contact_id IN (".$this->db->array2list($ids, 'integer').")",
	574	$group_id
	575	);
	576	while ($sql_result && ($sql_arr = $this->db->fetch_assoc($sql_result))) {
	577	$exists[] = $sql_arr['contact_id'];
	578	}
	579	// ... and remove them from the list
	580	$ids = array_diff($ids, $exists);
25fdec	581
3d6c04	582	foreach ($ids as $contact_id) {
1126fc	583	$this->db->query(
A	584	"INSERT INTO ".get_table_name($this->db_groupmembers).
	585	" (contactgroup_id, contact_id, created)".
	586	" VALUES (?, ?, ".$this->db->now().")",
3d6c04	587	$group_id,
A	588	$contact_id
	589	);
	590
1126fc	591	if (!$this->db->db_error)
A	592	$added++;
3d6c04	593	}
A	594
	595	return $added;
	596	}
	597
	598
	599	/**
	600	* Remove the given contact records from a certain group
	601	*
	602	* @param string Group identifier
	603	* @param array List of contact identifiers to be removed
	604	* @return int Number of deleted group members
	605	*/
	606	function remove_from_group($group_id, $ids)
	607	{
	608	if (!is_array($ids))
	609	$ids = explode(',', $ids);
	610
a004bb	611	$ids = $this->db->array2list($ids, 'integer');
A	612
3d6c04	613	$sql_result = $this->db->query(
982e0b	614	"DELETE FROM ".get_table_name($this->db_groupmembers).
3d6c04	615	" WHERE contactgroup_id=?".
A	616	" AND contact_id IN ($ids)",
	617	$group_id
	618	);
	619
	620	return $this->db->affected_rows();
	621	}
	622
	623
	624	/**
	625	* Check for existing groups with the same name
	626	*
	627	* @param string Name to check
	628	* @return string A group name which is unique for the current use
	629	*/
	630	private function unique_groupname($name)
	631	{
	632	$checkname = $name;
	633	$num = 2; $hit = false;
25fdec	634
3d6c04	635	do {
A	636	$sql_result = $this->db->query(
982e0b	637	"SELECT 1 FROM ".get_table_name($this->db_groups).
3d6c04	638	" WHERE del<>1".
A	639	" AND user_id=?".
3e696d	640	" AND name=?",
3d6c04	641	$this->user_id,
A	642	$checkname);
25fdec	643
3d6c04	644	// append number to make name unique
A	645	if ($hit = $this->db->num_rows($sql_result))
	646	$checkname = $name . ' ' . $num++;
	647	} while ($hit > 0);
25fdec	648
3d6c04	649	return $checkname;
3b67e3	650	}
T	651
f11541	652	}