From cafbd1f5a7e60d931e4dbd700b12e5f50319340d Mon Sep 17 00:00:00 2001 From: Frederic Guillot Date: Wed, 8 Nov 2017 14:50:02 -0800 Subject: Improve iCalendar feed to include tasks with start/end date and due date with a time --- app/Controller/ICalendarController.php | 97 +++++++++++++++++------------ app/Core/Filter/QueryBuilder.php | 12 ++++ app/Core/Helper.php | 1 - app/Filter/BaseFilter.php | 4 +- app/Filter/TaskDueDateRangeFilter.php | 5 ++ app/Formatter/BaseTaskCalendarFormatter.php | 43 ------------- app/Formatter/TaskICalFormatter.php | 40 +++++++----- app/Helper/ICalHelper.php | 37 ----------- app/ServiceProvider/HelperProvider.php | 1 - 9 files changed, 100 insertions(+), 140 deletions(-) delete mode 100644 app/Formatter/BaseTaskCalendarFormatter.php delete mode 100644 app/Helper/ICalHelper.php (limited to 'app') diff --git a/app/Controller/ICalendarController.php b/app/Controller/ICalendarController.php index 4fe8b78a..20e2a9d7 100644 --- a/app/Controller/ICalendarController.php +++ b/app/Controller/ICalendarController.php @@ -5,6 +5,7 @@ namespace Kanboard\Controller; use Kanboard\Core\Controller\AccessForbiddenException; use Kanboard\Core\Filter\QueryBuilder; use Kanboard\Filter\TaskAssigneeFilter; +use Kanboard\Filter\TaskDueDateRangeFilter; use Kanboard\Filter\TaskProjectFilter; use Kanboard\Filter\TaskStatusFilter; use Kanboard\Model\TaskModel; @@ -18,81 +19,97 @@ use Eluceo\iCal\Component\Calendar as iCalendar; */ class ICalendarController extends BaseController { - /** - * Get user iCalendar - * - * @access public - */ public function user() { $token = $this->request->getStringParam('token'); $user = $this->userModel->getByToken($token); - // Token verification if (empty($user)) { throw AccessForbiddenException::getInstance()->withoutLayout(); } - // Common filter - $queryBuilder = new QueryBuilder(); - $queryBuilder - ->withQuery($this->taskFinderModel->getICalQuery()) - ->withFilter(new TaskStatusFilter(TaskModel::STATUS_OPEN)) - ->withFilter(new TaskAssigneeFilter($user['id'])); + $startRange = $this->request->getStringParam('start', strtotime('-2 months')); + $endRange = $this->request->getStringParam('end', strtotime('+6 months')); + + $startColumn = $this->configModel->get('calendar_project_tasks', 'date_started'); - // Calendar properties $calendar = new iCalendar('Kanboard'); $calendar->setName($user['name'] ?: $user['username']); $calendar->setDescription($user['name'] ?: $user['username']); $calendar->setPublishedTTL('PT1H'); - $this->renderCalendar($queryBuilder, $calendar); + $queryDueDateOnly = QueryBuilder::create() + ->withQuery($this->taskFinderModel->getICalQuery()) + ->withFilter(new TaskStatusFilter(TaskModel::STATUS_OPEN)) + ->withFilter(new TaskDueDateRangeFilter(array($startRange, $endRange))) + ->withFilter(new TaskAssigneeFilter($user['id'])) + ->getQuery(); + + $queryStartAndDueDate = QueryBuilder::create() + ->withQuery($this->taskFinderModel->getICalQuery()) + ->withFilter(new TaskStatusFilter(TaskModel::STATUS_OPEN)) + ->withFilter(new TaskAssigneeFilter($user['id'])) + ->getQuery() + ->addCondition($this->getConditionForTasksWithStartAndDueDate($startRange, $endRange, $startColumn, 'date_due')); + + $this->response->ical($this->taskICalFormatter + ->setCalendar($calendar) + ->addTasksWithDueDateOnly($queryDueDateOnly) + ->addTasksWithStartAndDueDate($queryStartAndDueDate, $startColumn, 'date_due') + ->format()); } - /** - * Get project iCalendar - * - * @access public - */ public function project() { $token = $this->request->getStringParam('token'); $project = $this->projectModel->getByToken($token); - // Token verification if (empty($project)) { throw AccessForbiddenException::getInstance()->withoutLayout(); } - // Common filter - $queryBuilder = new QueryBuilder(); - $queryBuilder - ->withQuery($this->taskFinderModel->getICalQuery()) - ->withFilter(new TaskStatusFilter(TaskModel::STATUS_OPEN)) - ->withFilter(new TaskProjectFilter($project['id'])); + $startRange = $this->request->getStringParam('start', strtotime('-2 months')); + $endRange = $this->request->getStringParam('end', strtotime('+6 months')); + + $startColumn = $this->configModel->get('calendar_project_tasks', 'date_started'); - // Calendar properties $calendar = new iCalendar('Kanboard'); $calendar->setName($project['name']); $calendar->setDescription($project['name']); $calendar->setPublishedTTL('PT1H'); - $this->renderCalendar($queryBuilder, $calendar); + $queryDueDateOnly = QueryBuilder::create() + ->withQuery($this->taskFinderModel->getICalQuery()) + ->withFilter(new TaskStatusFilter(TaskModel::STATUS_OPEN)) + ->withFilter(new TaskProjectFilter($project['id'])) + ->withFilter(new TaskDueDateRangeFilter(array($startRange, $endRange))) + ->getQuery(); + + $queryStartAndDueDate = QueryBuilder::create() + ->withQuery($this->taskFinderModel->getICalQuery()) + ->withFilter(new TaskStatusFilter(TaskModel::STATUS_OPEN)) + ->withFilter(new TaskProjectFilter($project['id'])) + ->getQuery() + ->addCondition($this->getConditionForTasksWithStartAndDueDate($startRange, $endRange, $startColumn, 'date_due')); + + $this->response->ical($this->taskICalFormatter + ->setCalendar($calendar) + ->addTasksWithDueDateOnly($queryDueDateOnly) + ->addTasksWithStartAndDueDate($queryStartAndDueDate, $startColumn, 'date_due') + ->format()); } - /** - * Common method to render iCal events - * - * @access private - * @param QueryBuilder $queryBuilder - * @param iCalendar $calendar - */ - private function renderCalendar(QueryBuilder $queryBuilder, iCalendar $calendar) + protected function getConditionForTasksWithStartAndDueDate($start_time, $end_time, $start_column, $end_column) { - $start = $this->request->getStringParam('start', strtotime('-2 month')); - $end = $this->request->getStringParam('end', strtotime('+6 months')); + $start_column = $this->db->escapeIdentifier($start_column); + $end_column = $this->db->escapeIdentifier($end_column); + + $conditions = array( + "($start_column >= '$start_time' AND $start_column <= '$end_time')", + "($start_column <= '$start_time' AND $end_column >= '$start_time')", + "($start_column <= '$start_time' AND ($end_column = '0' OR $end_column IS NULL))", + ); - $this->helper->ical->addTaskDateDueEvents($queryBuilder, $calendar, $start, $end); - $this->response->ical($this->taskICalFormatter->setCalendar($calendar)->format()); + return $start_column.' IS NOT NULL AND '.$start_column.' > 0 AND ('.implode(' OR ', $conditions).')'; } } diff --git a/app/Core/Filter/QueryBuilder.php b/app/Core/Filter/QueryBuilder.php index 3de82b63..bdd6b940 100644 --- a/app/Core/Filter/QueryBuilder.php +++ b/app/Core/Filter/QueryBuilder.php @@ -20,6 +20,18 @@ class QueryBuilder */ protected $query; + /** + * Create a new class instance + * + * @static + * @access public + * @return static + */ + public static function create() + { + return new static(); + } + /** * Set the query * diff --git a/app/Core/Helper.php b/app/Core/Helper.php index cb9c50f6..5f4772d5 100644 --- a/app/Core/Helper.php +++ b/app/Core/Helper.php @@ -19,7 +19,6 @@ use Pimple\Container; * @property \Kanboard\Helper\FileHelper $file * @property \Kanboard\Helper\FormHelper $form * @property \Kanboard\Helper\HookHelper $hook - * @property \Kanboard\Helper\ICalHelper $ical * @property \Kanboard\Helper\ModalHelper $modal * @property \Kanboard\Helper\ModelHelper $model * @property \Kanboard\Helper\SubtaskHelper $subtask diff --git a/app/Filter/BaseFilter.php b/app/Filter/BaseFilter.php index e029f4e1..2687d903 100644 --- a/app/Filter/BaseFilter.php +++ b/app/Filter/BaseFilter.php @@ -51,7 +51,7 @@ abstract class BaseFilter * * @access public * @param Table $query - * @return \Kanboard\Core\Filter\FilterInterface + * @return $this */ public function withQuery(Table $query) { @@ -64,7 +64,7 @@ abstract class BaseFilter * * @access public * @param string $value - * @return \Kanboard\Core\Filter\FilterInterface + * @return $this */ public function withValue($value) { diff --git a/app/Filter/TaskDueDateRangeFilter.php b/app/Filter/TaskDueDateRangeFilter.php index a6aefbe2..a4250f80 100644 --- a/app/Filter/TaskDueDateRangeFilter.php +++ b/app/Filter/TaskDueDateRangeFilter.php @@ -32,6 +32,11 @@ class TaskDueDateRangeFilter extends BaseFilter implements FilterInterface */ public function apply() { + $this->query->beginOr(); + $this->query->isNull(TaskModel::TABLE.'.date_started'); + $this->query->eq(TaskModel::TABLE.'.date_started', 0); + $this->query->closeOr(); + $this->query->gte(TaskModel::TABLE.'.date_due', is_numeric($this->value[0]) ? $this->value[0] : strtotime($this->value[0])); $this->query->lte(TaskModel::TABLE.'.date_due', is_numeric($this->value[1]) ? $this->value[1] : strtotime($this->value[1])); return $this; diff --git a/app/Formatter/BaseTaskCalendarFormatter.php b/app/Formatter/BaseTaskCalendarFormatter.php deleted file mode 100644 index 3d9ead4d..00000000 --- a/app/Formatter/BaseTaskCalendarFormatter.php +++ /dev/null @@ -1,43 +0,0 @@ -startColumn = $start_column; - $this->endColumn = $end_column ?: $start_column; - return $this; - } -} diff --git a/app/Formatter/TaskICalFormatter.php b/app/Formatter/TaskICalFormatter.php index ad2a4449..387074e5 100644 --- a/app/Formatter/TaskICalFormatter.php +++ b/app/Formatter/TaskICalFormatter.php @@ -8,6 +8,7 @@ use Eluceo\iCal\Component\Event; use Eluceo\iCal\Property\Event\Attendees; use Eluceo\iCal\Property\Event\Organizer; use Kanboard\Core\Filter\FormatterInterface; +use PicoDb\Table; /** * iCal event formatter for tasks @@ -15,15 +16,15 @@ use Kanboard\Core\Filter\FormatterInterface; * @package formatter * @author Frederic Guillot */ -class TaskICalFormatter extends BaseTaskCalendarFormatter implements FormatterInterface +class TaskICalFormatter extends BaseFormatter implements FormatterInterface { /** * Calendar object * - * @access private + * @access protected * @var \Eluceo\iCal\Component\Calendar */ - private $vCalendar; + protected $vCalendar; /** * Get Ical events @@ -41,7 +42,7 @@ class TaskICalFormatter extends BaseTaskCalendarFormatter implements FormatterIn * * @access public * @param \Eluceo\iCal\Component\Calendar $vCalendar - * @return FormatterInterface + * @return $this */ public function setCalendar(Calendar $vCalendar) { @@ -53,18 +54,21 @@ class TaskICalFormatter extends BaseTaskCalendarFormatter implements FormatterIn * Transform results to iCal events * * @access public - * @return FormatterInterface + * @param Table $query + * @param string $startColumn + * @param string $endColumn + * @return $this */ - public function addDateTimeEvents() + public function addTasksWithStartAndDueDate(Table $query, $startColumn, $endColumn) { - foreach ($this->query->findAll() as $task) { + foreach ($query->findAll() as $task) { $start = new DateTime; - $start->setTimestamp($task[$this->startColumn]); + $start->setTimestamp($task[$startColumn]); $end = new DateTime; - $end->setTimestamp($task[$this->endColumn] ?: time()); + $end->setTimestamp($task[$endColumn] ?: time()); - $vEvent = $this->getTaskIcalEvent($task, 'task-#'.$task['id'].'-'.$this->startColumn.'-'.$this->endColumn); + $vEvent = $this->getTaskIcalEvent($task, 'task-#'.$task['id'].'-'.$startColumn.'-'.$endColumn); $vEvent->setDtStart($start); $vEvent->setDtEnd($end); @@ -78,18 +82,22 @@ class TaskICalFormatter extends BaseTaskCalendarFormatter implements FormatterIn * Transform results to all day iCal events * * @access public - * @return FormatterInterface + * @param Table $query + * @return $this */ - public function addFullDayEvents() + public function addTasksWithDueDateOnly(Table $query) { - foreach ($this->query->findAll() as $task) { + foreach ($query->findAll() as $task) { $date = new DateTime; - $date->setTimestamp($task[$this->startColumn]); + $date->setTimestamp($task['date_due']); - $vEvent = $this->getTaskIcalEvent($task, 'task-#'.$task['id'].'-'.$this->startColumn); + $vEvent = $this->getTaskIcalEvent($task, 'task-#'.$task['id'].'-date_due'); $vEvent->setDtStart($date); $vEvent->setDtEnd($date); - $vEvent->setNoTime(true); + + if ($date->format('Hi') === '0000') { + $vEvent->setNoTime(true); + } $this->vCalendar->addComponent($vEvent); } diff --git a/app/Helper/ICalHelper.php b/app/Helper/ICalHelper.php deleted file mode 100644 index 95723417..00000000 --- a/app/Helper/ICalHelper.php +++ /dev/null @@ -1,37 +0,0 @@ -withFilter(new TaskDueDateRangeFilter(array($start, $end))); - - $this->taskICalFormatter - ->setColumns('date_due') - ->setCalendar($calendar) - ->withQuery($queryBuilder->getQuery()) - ->addFullDayEvents(); - } -} diff --git a/app/ServiceProvider/HelperProvider.php b/app/ServiceProvider/HelperProvider.php index 054c4009..f0f7ac07 100644 --- a/app/ServiceProvider/HelperProvider.php +++ b/app/ServiceProvider/HelperProvider.php @@ -26,7 +26,6 @@ class HelperProvider implements ServiceProviderInterface $container['helper']->register('file', '\Kanboard\Helper\FileHelper'); $container['helper']->register('form', '\Kanboard\Helper\FormHelper'); $container['helper']->register('hook', '\Kanboard\Helper\HookHelper'); - $container['helper']->register('ical', '\Kanboard\Helper\ICalHelper'); $container['helper']->register('layout', '\Kanboard\Helper\LayoutHelper'); $container['helper']->register('model', '\Kanboard\Helper\ModelHelper'); $container['helper']->register('subtask', '\Kanboard\Helper\SubtaskHelper'); -- cgit v1.2.3