tasklist_database_driver.php
No OneTemporary
Actions

Size

15 KB

Referenced Files

None

Subscribers

None

tasklist_database_driver.php
View Options

	<?php

	/**
	* Database driver for the Tasklist plugin
	*
	* @version @package_version@
	* @author Thomas Bruederli <bruederli@kolabsys.com>
	*
	* Copyright (C) 2012, Kolab Systems AG <contact@kolabsys.com>
	*
	* This program is free software: you can redistribute it and/or modify
	* it under the terms of the GNU Affero General Public License as
	* published by the Free Software Foundation, either version 3 of the
	* License, or (at your option) any later version.
	*
	* This program is distributed in the hope that it will be useful,
	* but WITHOUT ANY WARRANTY; without even the implied warranty of
	* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
	* GNU Affero General Public License for more details.
	*
	* You should have received a copy of the GNU Affero General Public License
	* along with this program. If not, see <http://www.gnu.org/licenses/>.
	*/

	class tasklist_database_driver extends tasklist_driver
	{
	public $undelete = true; // yes, we can
	public $sortable = false;

	private $rc;
	private $plugin;
	private $cache = array();
	private $lists = array();
	private $list_ids = '';

	private $db_tasks = 'tasks';
	private $db_lists = 'tasklists';
	private $sequence_tasks = 'task_ids';
	private $sequence_lists = 'tasklist_ids';


	/**
	* Default constructor
	*/
	public function __construct($plugin)
	{
	$this->rc = $plugin->rc;
	$this->plugin = $plugin;

	// read database config
	$this->db_lists = $this->rc->config->get('db_table_lists', $this->db_lists);
	$this->db_tasks = $this->rc->config->get('db_table_tasks', $this->db_tasks);
	$this->sequence_lists = $this->rc->config->get('db_sequence_lists', $this->sequence_lists);
	$this->sequence_tasks = $this->rc->config->get('db_sequence_tasks', $this->sequence_tasks);

	$this->_read_lists();
	}

	/**
	* Read available calendars for the current user and store them internally
	*/
	private function _read_lists()
	{
	$hidden = array_filter(explode(',', $this->rc->config->get('hidden_tasklists', '')));

	if (!empty($this->rc->user->ID)) {
	$list_ids = array();
	$result = $this->rc->db->query(
	"SELECT *, tasklist_id AS id FROM " . $this->db_lists . "
	WHERE user_id=?
	ORDER BY name",
	$this->rc->user->ID
	);

	while ($result && ($arr = $this->rc->db->fetch_assoc($result))) {
	$arr['showalarms'] = intval($arr['showalarms']);
	$arr['active'] = !in_array($arr['id'], $hidden);
	$arr['editable'] = true;
	$this->lists[$arr['id']] = $arr;
	$list_ids[] = $this->rc->db->quote($arr['id']);
	}
	$this->list_ids = join(',', $list_ids);
	}
	}

	/**
	* Get a list of available tasks lists from this source
	*/
	public function get_lists()
	{
	// attempt to create a default list for this user
	if (empty($this->lists)) {
	if ($this->create_list(array('name' => 'Default', 'color' => '000000')))
	$this->_read_lists();
	}

	return $this->lists;
	}

	/**
	* Create a new list assigned to the current user
	*
	* @param array Hash array with list properties
	* @return mixed ID of the new list on success, False on error
	* @see tasklist_driver::create_list()
	*/
	public function create_list($prop)
	{
	$result = $this->rc->db->query(
	"INSERT INTO " . $this->db_lists . "
	(user_id, name, color, showalarms)
	VALUES (?, ?, ?, ?)",
	$this->rc->user->ID,
	$prop['name'],
	$prop['color'],
	$prop['showalarms']?1:0
	);

	if ($result)
	return $this->rc->db->insert_id($this->sequence_lists);

	return false;
	}

	/**
	* Update properties of an existing tasklist
	*
	* @param array Hash array with list properties
	* @return boolean True on success, Fales on failure
	* @see tasklist_driver::edit_list()
	*/
	public function edit_list($prop)
	{
	$query = $this->rc->db->query(
	"UPDATE " . $this->db_lists . "
	SET name=?, color=?, showalarms=?
	WHERE tasklist_id=?
	AND user_id=?",
	$prop['name'],
	$prop['color'],
	$prop['showalarms']?1:0,
	$prop['id'],
	$this->rc->user->ID
	);

	return $this->rc->db->affected_rows($query);
	}

	/**
	* Set active/subscribed state of a list
	*
	* @param array Hash array with list properties
	* @return boolean True on success, Fales on failure
	* @see tasklist_driver::subscribe_list()
	*/
	public function subscribe_list($prop)
	{
	$hidden = array_flip(explode(',', $this->rc->config->get('hidden_tasklists', '')));

	if ($prop['active'])
	unset($hidden[$prop['id']]);
	else
	$hidden[$prop['id']] = 1;

	return $this->rc->user->save_prefs(array('hidden_tasklists' => join(',', array_keys($hidden))));
	}

	/**
	* Delete the given list with all its contents
	*
	* @param array Hash array with list properties
	* @return boolean True on success, Fales on failure
	* @see tasklist_driver::remove_list()
	*/
	public function remove_list($prop)
	{
	// TODO: implement this
	return false;
	}

	/**
	* Get number of tasks matching the given filter
	*
	* @param array List of lists to count tasks of
	* @return array Hash array with counts grouped by status (all\|flagged\|today\|tomorrow\|overdue\|nodate)
	* @see tasklist_driver::count_tasks()
	*/
	function count_tasks($lists = null)
	{
	if (empty($lists))
	$lists = array_keys($this->lists);
	else if (is_string($lists))
	$lists = explode(',', $lists);

	// only allow to select from lists of this user
	$list_ids = array_map(array($this->rc->db, 'quote'), array_intersect($lists, array_keys($this->lists)));

	$today_date = new DateTime('now', $this->plugin->timezone);
	$today = $today_date->format('Y-m-d');
	$tomorrow_date = new DateTime('now + 1 day', $this->plugin->timezone);
	$tomorrow = $tomorrow_date->format('Y-m-d');

	$result = $this->rc->db->query(sprintf(
	"SELECT task_id, flagged, date FROM " . $this->db_tasks . "
	WHERE tasklist_id IN (%s)
	AND del=0 AND complete<1",
	join(',', $list_ids)
	));

	$counts = array('all' => 0, 'flagged' => 0, 'today' => 0, 'tomorrow' => 0, 'overdue' => 0, 'nodate' => 0);
	while ($result && ($rec = $this->rc->db->fetch_assoc($result))) {
	$counts['all']++;
	if ($rec['flagged'])
	$counts['flagged']++;
	if (empty($rec['date']))
	$counts['nodate']++;
	else if ($rec['date'] == $today)
	$counts['today']++;
	else if ($rec['date'] == $tomorrow)
	$counts['tomorrow']++;
	else if ($rec['date'] < $today)
	$counts['overdue']++;
	}

	return $counts;
	}

	/**
	* Get all taks records matching the given filter
	*
	* @param array Hash array wiht filter criterias
	* @param array List of lists to get tasks from
	* @return array List of tasks records matchin the criteria
	* @see tasklist_driver::list_tasks()
	*/
	function list_tasks($filter, $lists = null)
	{
	if (empty($lists))
	$lists = array_keys($this->lists);
	else if (is_string($lists))
	$lists = explode(',', $lists);

	// only allow to select from lists of this user
	$list_ids = array_map(array($this->rc->db, 'quote'), array_intersect($lists, array_keys($this->lists)));
	$sql_add = '';

	// add filter criteria
	if ($filter['from'] \|\| ($filter['mask'] & tasklist::FILTER_MASK_TODAY)) {
	$sql_add .= ' AND (date IS NULL OR date >= ?)';
	$datefrom = $filter['from'];
	}
	if ($filter['to']) {
	if ($filter['mask'] & tasklist::FILTER_MASK_OVERDUE)
	$sql_add .= ' AND (date IS NOT NULL AND date <= ' . $this->rc->db->quote($filter['to']) . ')';
	else
	$sql_add .= ' AND (date IS NULL OR date <= ' . $this->rc->db->quote($filter['to']) . ')';
	}

	// special case 'today': also show all events with date before today
	if ($filter['mask'] & tasklist::FILTER_MASK_TODAY) {
	$datefrom = date('Y-m-d', 0);
	}

	if ($filter['mask'] & tasklist::FILTER_MASK_NODATE)
	$sql_add = ' AND date IS NULL';

	if ($filter['mask'] & tasklist::FILTER_MASK_COMPLETE)
	$sql_add .= ' AND complete=1';
	else // don't show complete tasks by default
	$sql_add .= ' AND complete<1';

	if ($filter['mask'] & tasklist::FILTER_MASK_FLAGGED)
	$sql_add .= ' AND flagged=1';

	// compose (slow) SQL query for searching
	// FIXME: improve searching using a dedicated col and normalized values
	if ($filter['search']) {
	$sql_query = array();
	foreach (array('title','description','organizer','attendees') as $col)
	$sql_query[] = $this->rc->db->ilike($col, '%'.$filter['search'].'%');
	$sql_add = 'AND (' . join(' OR ', $sql_query) . ')';
	}

	$tasks = array();
	if (!empty($list_ids)) {
	$datecol = $this->rc->db->quote_identifier('date');
	$timecol = $this->rc->db->quote_identifier('time');
	$result = $this->rc->db->query(sprintf(
	"SELECT * FROM " . $this->db_tasks . "
	WHERE tasklist_id IN (%s)
	AND del=0
	%s
	ORDER BY parent_id, task_id ASC",
	join(',', $list_ids),
	$sql_add
	),
	$datefrom
	);

	while ($result && ($rec = $this->rc->db->fetch_assoc($result))) {
	$tasks[] = $this->_read_postprocess($rec);
	}
	}

	return $tasks;
	}

	/**
	* Return data of a specific task
	*
	* @param mixed Hash array with task properties or task UID
	* @return array Hash array with task properties or false if not found
	*/
	public function get_task($prop)
	{
	if (is_string($prop))
	$prop['uid'] = $prop;

	$query_col = $prop['id'] ? 'task_id' : 'uid';

	$result = $this->rc->db->query(sprintf(
	"SELECT * FROM " . $this->db_tasks . "
	WHERE tasklist_id IN (%s)
	AND %s=?
	AND del=0",
	$this->list_ids,
	$query_col
	),
	$prop['id'] ? $prop['id'] : $prop['uid']
	);

	if ($result && ($rec = $this->rc->db->fetch_assoc($result))) {
	return $this->_read_postprocess($rec);
	}

	return false;
	}

	/**
	* Map some internal database values to match the generic "API"
	*/
	private function _read_postprocess($rec)
	{
	$rec['id'] = $rec['task_id'];
	$rec['list'] = $rec['tasklist_id'];
	$rec['changed'] = new DateTime($rec['changed']);
	$rec['tags'] = array_filter(explode(',', $rec['tags']));

	if (!$rec['parent_id'])
	unset($rec['parent_id']);

	unset($rec['task_id'], $rec['tasklist_id'], $rec['created']);
	return $rec;
	}

	/**
	* Add a single task to the database
	*
	* @param array Hash array with task properties (see header of this file)
	* @return mixed New event ID on success, False on error
	* @see tasklist_driver::create_task()
	*/
	public function create_task($prop)
	{
	// check list permissions
	$list_id = $prop['list'] ? $prop['list'] : reset(array_keys($this->lists));
	if (!$this->lists[$list_id] \|\| $this->lists[$list_id]['readonly'])
	return false;

	foreach (array('parent_id', 'date', 'time') as $col) {
	if (empty($prop[$col]))
	$prop[$col] = null;
	}

	$result = $this->rc->db->query(sprintf(
	"INSERT INTO " . $this->db_tasks . "
	(tasklist_id, uid, parent_id, created, changed, title, date, time, description, tags)
	VALUES (?, ?, ?, %s, %s, ?, ?, ?, ?, ?)",
	$this->rc->db->now(),
	$this->rc->db->now()
	),
	$list_id,
	$prop['uid'],
	$prop['parent_id'],
	$prop['title'],
	$prop['date'],
	$prop['time'],
	strval($prop['description']),
	join(',', (array)$prop['tags'])
	);

	if ($result)
	return $this->rc->db->insert_id($this->sequence_tasks);

	return false;
	}

	/**
	* Update an task entry with the given data
	*
	* @param array Hash array with task properties
	* @return boolean True on success, False on error
	* @see tasklist_driver::edit_task()
	*/
	public function edit_task($prop)
	{
	$sql_set = array();
	foreach (array('title', 'description', 'flagged', 'complete') as $col) {
	if (isset($prop[$col]))
	$sql_set[] = $this->rc->db->quote_identifier($col) . '=' . $this->rc->db->quote($prop[$col]);
	}
	foreach (array('parent_id', 'date', 'time', 'startdate', 'starttime') as $col) {
	if (isset($prop[$col]))
	$sql_set[] = $this->rc->db->quote_identifier($col) . '=' . (empty($prop[$col]) ? 'NULL' : $this->rc->db->quote($prop[$col]));
	}
	if (isset($prop['tags']))
	$sql_set[] = $this->rc->db->quote_identifier('tags') . '=' . $this->rc->db->quote(join(',', (array)$prop['tags']));

	// moved from another list
	if ($prop['_fromlist'] && ($newlist = $prop['list'])) {
	$sql_set[] = 'tasklist_id=' . $this->rc->db->quote($newlist);
	}

	$query = $this->rc->db->query(sprintf(
	"UPDATE " . $this->db_tasks . "
	SET changed=%s %s
	WHERE task_id=?
	AND tasklist_id IN (%s)",
	$this->rc->db->now(),
	($sql_set ? ', ' . join(', ', $sql_set) : ''),
	$this->list_ids
	),
	$prop['id']
	);

	return $this->rc->db->affected_rows($query);
	}

	/**
	* Remove a single task from the database
	*
	* @param array Hash array with task properties
	* @param boolean Remove record irreversible
	* @return boolean True on success, False on error
	* @see tasklist_driver::delete_task()
	*/
	public function delete_task($prop, $force = true)
	{
	$task_id = $prop['id'];

	if ($task_id && $force) {
	$query = $this->rc->db->query(
	"DELETE FROM " . $this->db_tasks . "
	WHERE task_id=?
	AND tasklist_id IN (" . $this->list_ids . ")",
	$task_id
	);
	}
	else if ($task_id) {
	$query = $this->rc->db->query(sprintf(
	"UPDATE " . $this->db_tasks . "
	SET changed=%s, del=1
	WHERE task_id=?
	AND tasklist_id IN (%s)",
	$this->rc->db->now(),
	$this->list_ids
	),
	$task_id
	);
	}

	return $this->rc->db->affected_rows($query);
	}

	/**
	* Restores a single deleted task (if supported)
	*
	* @param array Hash array with task properties
	* @return boolean True on success, False on error
	* @see tasklist_driver::undelete_task()
	*/
	public function undelete_task($prop)
	{
	$query = $this->rc->db->query(sprintf(
	"UPDATE " . $this->db_tasks . "
	SET changed=%s, del=0
	WHERE task_id=?
	AND tasklist_id IN (%s)",
	$this->rc->db->now(),
	$this->list_ids
	),
	$prop['id']
	);

	return $this->rc->db->affected_rows($query);
	}

	}

File Metadata

Mime Type: text/x-php
Expires: Fri, May 1, 2:11 PM (1 d, 21 h)
Storage Engine: blob
Storage Format: Raw Data
Storage Handle: 661367
Default Alt Text: tasklist_database_driver.php (15 KB)

tasklist_database_driver.phpNo OneTemporaryActions

tasklist_database_driver.phpView Options

File Metadata

Event Timeline

tasklist_database_driver.php
No OneTemporary
Actions

tasklist_database_driver.php
View Options