Pagination refactoring

7 files changed, 191 insertions, 330 deletions

diff --git a/app/Model/Project.php b/app/Model/Project.php
index f9c5c39c..a9693fbb 100644
--- a/app/Model/Project.php
+++ b/app/Model/Project.php
@@ -95,7 +95,7 @@ class Project extends Base
     }
 
     /**
-     * Get all projects, optionaly fetch stats for each project and can check users permissions
+     * Get all projects
      *
      * @access public
      * @param  bool       $filter_permissions    If true, remove projects not allowed for the current user
@@ -188,7 +188,7 @@ class Project extends Base
      * @param  integer    $project_id    Project id
      * @return array
      */
-    public function getStats($project_id)
+    public function getTaskStats($project_id)
     {
         $stats = array();
         $stats['nb_active_tasks'] = 0;
@@ -208,6 +208,60 @@ class Project extends Base
     }
 
     /**
+     * Get stats for each column of a project
+     *
+     * @access public
+     * @param  array    $project
+     * @return array
+     */
+    public function getColumnStats(array &$project)
+    {
+        $project['columns'] = $this->board->getColumns($project['id']);
+        $stats = $this->board->getColumnStats($project['id']);
+
+        foreach ($project['columns'] as &$column) {
+            $column['nb_tasks'] = isset($stats[$column['id']]) ? $stats[$column['id']] : 0;
+        }
+
+        return $project;
+    }
+
+    /**
+     * Apply column stats to a collection of projects (filter callback)
+     *
+     * @access public
+     * @param  array    $projects
+     * @return array
+     */
+    public function applyColumnStats(array $projects)
+    {
+        foreach ($projects as &$project) {
+            $this->getColumnStats($project);
+        }
+
+        return $projects;
+    }
+
+    /**
+     * Get project summary for a list of project
+     *
+     * @access public
+     * @param  array      $project_ids     List of project id
+     * @return \PicoDb\Table
+     */
+    public function getQueryColumnStats(array $project_ids)
+    {
+        if (empty($project_ids)) {
+            return $this->db->table(Project::TABLE)->limit(0);
+        }
+
+        return $this->db
+                    ->table(Project::TABLE)
+                    ->in('id', $project_ids)
+                    ->filter(array($this, 'applyColumnStats'));
+    }
+
+    /**
      * Create a project from another one.
      *
      * @author Antonio Rabelo
diff --git a/app/Model/ProjectPaginator.php b/app/Model/ProjectPaginator.php
deleted file mode 100644
index 68b216b1..00000000
--- a/app/Model/ProjectPaginator.php
+++ /dev/null
@@ -1,50 +0,0 @@
-<?php
-
-namespace Model;
-
-/**
- * Project Paginator
- *
- * @package  model
- * @author   Frederic Guillot
- */
-class ProjectPaginator extends Base
-{
-    /**
-     * Get project summary for a list of project (number of tasks for each column)
-     *
-     * @access public
-     * @param  array      $project_ids     List of project id
-     * @param  integer    $offset          Offset
-     * @param  integer    $limit           Limit
-     * @param  string     $column          Sorting column
-     * @param  string     $direction       Sorting direction
-     * @return array
-     */
-    public function projectSummaries(array $project_ids, $offset = 0, $limit = 25, $column = 'name', $direction = 'asc')
-    {
-        if (empty($project_ids)) {
-            return array();
-        }
-
-        $projects = $this->db
-                         ->table(Project::TABLE)
-                         ->in('id', $project_ids)
-                         ->offset($offset)
-                         ->limit($limit)
-                         ->orderBy($column, $direction)
-                         ->findAll();
-
-        foreach ($projects as &$project) {
-
-            $project['columns'] = $this->board->getColumns($project['id']);
-            $stats = $this->board->getColumnStats($project['id']);
-
-            foreach ($project['columns'] as &$column) {
-                $column['nb_tasks'] = isset($stats[$column['id']]) ? $stats[$column['id']] : 0;
-            }
-        }
-
-        return $projects;
-    }
-}
diff --git a/app/Model/SubTask.php b/app/Model/SubTask.php
index 1c5d1bf0..e5b03ab0 100644
--- a/app/Model/SubTask.php
+++ b/app/Model/SubTask.php
@@ -66,21 +66,15 @@ class SubTask extends Base
     }
 
     /**
-     * Get all subtasks for a given task
+     * Add subtask status status to the resultset
      *
      * @access public
-     * @param  integer   $task_id    Task id
+     * @param  array    $subtasks   Subtasks
      * @return array
      */
-    public function getAll($task_id)
+    public function addStatusName(array $subtasks)
     {
         $status = $this->getStatusList();
-        $subtasks = $this->db->table(self::TABLE)
-                             ->eq('task_id', $task_id)
-                             ->columns(self::TABLE.'.*', User::TABLE.'.username', User::TABLE.'.name')
-                             ->join(User::TABLE, 'id', 'user_id')
-                             ->asc(self::TABLE.'.id')
-                             ->findAll();
 
         foreach ($subtasks as &$subtask) {
             $subtask['status_name'] = $status[$subtask['status']];
@@ -90,6 +84,49 @@ class SubTask extends Base
     }
 
     /**
+     * Get the query to fetch subtasks assigned to a user
+     *
+     * @access public
+     * @param  integer    $user_id         User id
+     * @param  array      $status          List of status
+     * @return \PicoDb\Table
+     */
+    public function getUserQuery($user_id, array $status)
+    {
+        return $this->db->table(SubTask::TABLE)
+            ->columns(
+                SubTask::TABLE.'.*',
+                Task::TABLE.'.project_id',
+                Task::TABLE.'.color_id',
+                Project::TABLE.'.name AS project_name'
+            )
+            ->eq('user_id', $user_id)
+            ->in(SubTask::TABLE.'.status', $status)
+            ->join(Task::TABLE, 'id', 'task_id')
+            ->join(Project::TABLE, 'id', 'project_id', Task::TABLE)
+            ->filter(array($this, 'addStatusName'));
+    }
+
+    /**
+     * Get all subtasks for a given task
+     *
+     * @access public
+     * @param  integer   $task_id    Task id
+     * @return array
+     */
+    public function getAll($task_id)
+    {
+        return $this->db
+                    ->table(self::TABLE)
+                    ->eq('task_id', $task_id)
+                    ->columns(self::TABLE.'.*', User::TABLE.'.username', User::TABLE.'.name')
+                    ->join(User::TABLE, 'id', 'user_id')
+                    ->asc(self::TABLE.'.id')
+                    ->filter(array($this, 'addStatusName'))
+                    ->findAll();
+    }
+
+    /**
      * Get a subtask by the id
      *
      * @access public
@@ -101,18 +138,13 @@ class SubTask extends Base
     {
         if ($more) {
 
-            $subtask = $this->db->table(self::TABLE)
-                             ->eq(self::TABLE.'.id', $subtask_id)
-                             ->columns(self::TABLE.'.*', User::TABLE.'.username', User::TABLE.'.name')
-                             ->join(User::TABLE, 'id', 'user_id')
-                             ->findOne();
-
-            if ($subtask) {
-                $status = $this->getStatusList();
-                $subtask['status_name'] = $status[$subtask['status']];
-            }
-
-            return $subtask;
+            return $this->db
+                        ->table(self::TABLE)
+                        ->eq(self::TABLE.'.id', $subtask_id)
+                        ->columns(self::TABLE.'.*', User::TABLE.'.username', User::TABLE.'.name')
+                        ->join(User::TABLE, 'id', 'user_id')
+                        ->filter(array($this, 'addStatusName'))
+                        ->findOne();
         }
 
         return $this->db->table(self::TABLE)->eq('id', $subtask_id)->findOne();
diff --git a/app/Model/SubtaskPaginator.php b/app/Model/SubtaskPaginator.php
deleted file mode 100644
index 8ccbd696..00000000
--- a/app/Model/SubtaskPaginator.php
+++ /dev/null
@@ -1,68 +0,0 @@
-<?php
-
-namespace Model;
-
-/**
- * Subtask Paginator
- *
- * @package  model
- * @author   Frederic Guillot
- */
-class SubtaskPaginator extends Base
-{
-    /**
-     * Get all subtasks assigned to a user
-     *
-     * @access public
-     * @param  integer    $user_id         User id
-     * @param  array      $status          List of status
-     * @param  integer    $offset          Offset
-     * @param  integer    $limit           Limit
-     * @param  string     $column          Sorting column
-     * @param  string     $direction       Sorting direction
-     * @return array
-     */
-    public function userSubtasks($user_id, array $status, $offset = 0, $limit = 25, $column = 'tasks.id', $direction = 'asc')
-    {
-        $status_list = $this->subTask->getStatusList();
-
-        $subtasks = $this->db->table(SubTask::TABLE)
-                             ->columns(
-                                SubTask::TABLE.'.*',
-                                Task::TABLE.'.project_id',
-                                Task::TABLE.'.color_id',
-                                Project::TABLE.'.name AS project_name'
-                             )
-                             ->eq('user_id', $user_id)
-                             ->in(SubTask::TABLE.'.status', $status)
-                             ->join(Task::TABLE, 'id', 'task_id')
-                             ->join(Project::TABLE, 'id', 'project_id', Task::TABLE)
-                             ->offset($offset)
-                             ->limit($limit)
-                             ->orderBy($column, $direction)
-                             ->findAll();
-
-        foreach ($subtasks as &$subtask) {
-            $subtask['status_name'] = $status_list[$subtask['status']];
-        }
-
-        return $subtasks;
-    }
-
-    /**
-     * Count all subtasks assigned to the user
-     *
-     * @access public
-     * @param  integer    $user_id         User id
-     * @param  array      $status          List of status
-     * @return integer
-     */
-    public function countUserSubtasks($user_id, array $status)
-    {
-        return $this->db
-                    ->table(SubTask::TABLE)
-                    ->eq('user_id', $user_id)
-                    ->in('status', $status)
-                    ->count();
-    }
-}
diff --git a/app/Model/TaskFinder.php b/app/Model/TaskFinder.php
index eb86fe3e..9d517ac5 100644
--- a/app/Model/TaskFinder.php
+++ b/app/Model/TaskFinder.php
@@ -13,12 +13,66 @@ use PDO;
 class TaskFinder extends Base
 {
     /**
-     * Common request to fetch a list of tasks
+     * Get query for closed tasks
      *
      * @access public
+     * @param  integer    $project_id    Project id
      * @return \PicoDb\Table
      */
-    public function getQuery()
+    public function getClosedTaskQuery($project_id)
+    {
+        return $this->getExtendedQuery()
+                    ->eq('project_id', $project_id)
+                    ->eq('is_active', Task::STATUS_CLOSED);
+    }
+
+    /**
+     * Get query for task search
+     *
+     * @access public
+     * @param  integer    $project_id    Project id
+     * @param  string     $search        Search terms
+     * @return \PicoDb\Table
+     */
+    public function getSearchQuery($project_id, $search)
+    {
+        return $this->getExtendedQuery()
+                    ->eq('project_id', $project_id)
+                    ->ilike('title', '%'.$search.'%');
+    }
+
+    /**
+     * Get query for assigned user tasks
+     *
+     * @access public
+     * @param  integer    $user_id       User id
+     * @return \PicoDb\Table
+     */
+    public function getUserQuery($user_id)
+    {
+        return $this->db
+                    ->table(Task::TABLE)
+                    ->columns(
+                        'tasks.id',
+                        'tasks.title',
+                        'tasks.date_due',
+                        'tasks.date_creation',
+                        'tasks.project_id',
+                        'tasks.color_id',
+                        'projects.name AS project_name'
+                    )
+                    ->join(Project::TABLE, 'id', 'project_id')
+                    ->eq('tasks.owner_id', $user_id)
+                    ->eq('tasks.is_active', Task::STATUS_OPEN);
+    }
+
+    /**
+     * Extended query
+     *
+     * @access public
+     * @return \PicoDb\Table
+     */
+    public function getExtendedQuery()
     {
         return $this->db
             ->table(Task::TABLE)
@@ -62,7 +116,7 @@ class TaskFinder extends Base
      */
     public function getTasksByColumnAndSwimlane($project_id, $column_id, $swimlane_id = 0)
     {
-        return $this->getQuery()
+        return $this->getExtendedQuery()
                     ->eq('project_id', $project_id)
                     ->eq('column_id', $column_id)
                     ->eq('swimlane_id', $swimlane_id)
diff --git a/app/Model/TaskPaginator.php b/app/Model/TaskPaginator.php
deleted file mode 100644
index e8109229..00000000
--- a/app/Model/TaskPaginator.php
+++ /dev/null
@@ -1,138 +0,0 @@
-<?php
-
-namespace Model;
-
-/**
- * Task Paginator model
- *
- * @package  model
- * @author   Frederic Guillot
- */
-class TaskPaginator extends Base
-{
-    /**
-     * Task search with pagination
-     *
-     * @access public
-     * @param  integer    $project_id    Project id
-     * @param  string     $search        Search terms
-     * @param  integer    $offset        Offset
-     * @param  integer    $limit         Limit
-     * @param  string     $column        Sorting column
-     * @param  string     $direction     Sorting direction
-     * @return array
-     */
-    public function searchTasks($project_id, $search, $offset = 0, $limit = 25, $column = 'tasks.id', $direction = 'DESC')
-    {
-        return $this->taskFinder->getQuery()
-                    ->eq('project_id', $project_id)
-                    ->ilike('title', '%'.$search.'%')
-                    ->offset($offset)
-                    ->limit($limit)
-                    ->orderBy($column, $direction)
-                    ->findAll();
-    }
-
-    /**
-     * Count the number of tasks for a custom search
-     *
-     * @access public
-     * @param  integer   $project_id   Project id
-     * @param  string    $search       Search terms
-     * @return integer
-     */
-    public function countSearchTasks($project_id, $search)
-    {
-        return $this->db->table(Task::TABLE)
-                        ->eq('project_id', $project_id)
-                        ->ilike('title', '%'.$search.'%')
-                        ->count();
-    }
-
-    /**
-     * Get all completed tasks with pagination
-     *
-     * @access public
-     * @param  integer    $project_id    Project id
-     * @param  integer    $offset        Offset
-     * @param  integer    $limit         Limit
-     * @param  string     $column        Sorting column
-     * @param  string     $direction     Sorting direction
-     * @return array
-     */
-    public function closedTasks($project_id, $offset = 0, $limit = 25, $column = 'tasks.date_completed', $direction = 'DESC')
-    {
-        return $this->taskFinder->getQuery()
-                    ->eq('project_id', $project_id)
-                    ->eq('is_active', Task::STATUS_CLOSED)
-                    ->offset($offset)
-                    ->limit($limit)
-                    ->orderBy($column, $direction)
-                    ->findAll();
-    }
-
-    /**
-     * Count all closed tasks
-     *
-     * @access public
-     * @param  integer   $project_id   Project id
-     * @return integer
-     */
-    public function countClosedTasks($project_id)
-    {
-        return $this->db
-                    ->table(Task::TABLE)
-                    ->eq('project_id', $project_id)
-                    ->eq('is_active', Task::STATUS_CLOSED)
-                    ->count();
-    }
-
-    /**
-     * Get all open tasks for a given user
-     *
-     * @access public
-     * @param  integer    $user_id       User id
-     * @param  integer    $offset        Offset
-     * @param  integer    $limit         Limit
-     * @param  string     $column        Sorting column
-     * @param  string     $direction     Sorting direction
-     * @return array
-     */
-    public function userTasks($user_id, $offset = 0, $limit = 25, $column = 'tasks.id', $direction = 'ASC')
-    {
-        return $this->db
-                    ->table(Task::TABLE)
-                    ->columns(
-                        'tasks.id',
-                        'tasks.title',
-                        'tasks.date_due',
-                        'tasks.date_creation',
-                        'tasks.project_id',
-                        'tasks.color_id',
-                        'projects.name AS project_name'
-                    )
-                    ->join(Project::TABLE, 'id', 'project_id')
-                    ->eq('tasks.owner_id', $user_id)
-                    ->eq('tasks.is_active', Task::STATUS_OPEN)
-                    ->offset($offset)
-                    ->limit($limit)
-                    ->orderBy($column, $direction)
-                    ->findAll();
-    }
-
-    /**
-     * Count all tasks assigned to the user
-     *
-     * @access public
-     * @param  integer    $user_id    User id
-     * @return integer
-     */
-    public function countUserTasks($user_id)
-    {
-        return $this->db
-                    ->table(Task::TABLE)
-                    ->eq('owner_id', $user_id)
-                    ->eq('is_active', Task::STATUS_OPEN)
-                    ->count();
-    }
-}
diff --git a/app/Model/User.php b/app/Model/User.php
index 1bcc82b5..01be8597 100644
--- a/app/Model/User.php
+++ b/app/Model/User.php
@@ -29,6 +29,30 @@ class User extends Base
     const EVERYBODY_ID = -1;
 
     /**
+     * Get query to fetch all users
+     *
+     * @access public
+     * @return \PicoDb\Table
+     */
+    public function getQuery()
+    {
+        return $this->db
+                    ->table(self::TABLE)
+                    ->columns(
+                        'id',
+                        'username',
+                        'name',
+                        'email',
+                        'is_admin',
+                        'default_project_id',
+                        'is_ldap_user',
+                        'notifications_enabled',
+                        'google_id',
+                        'github_id'
+                    );
+    }
+
+    /**
      * Return the full name
      *
      * @param  array    $user   User properties
@@ -112,54 +136,7 @@ class User extends Base
      */
     public function getAll()
     {
-        return $this->db
-                    ->table(self::TABLE)
-                    ->asc('username')
-                    ->columns(
-                        'id',
-                        'username',
-                        'name',
-                        'email',
-                        'is_admin',
-                        'default_project_id',
-                        'is_ldap_user',
-                        'notifications_enabled',
-                        'google_id',
-                        'github_id'
-                    )
-                    ->findAll();
-    }
-
-    /**
-     * Get all users with pagination
-     *
-     * @access public
-     * @param  integer    $offset        Offset
-     * @param  integer    $limit         Limit
-     * @param  string     $column        Sorting column
-     * @param  string     $direction     Sorting direction
-     * @return array
-     */
-    public function paginate($offset = 0, $limit = 25, $column = 'username', $direction = 'ASC')
-    {
-        return $this->db
-                    ->table(self::TABLE)
-                    ->columns(
-                        'id',
-                        'username',
-                        'name',
-                        'email',
-                        'is_admin',
-                        'default_project_id',
-                        'is_ldap_user',
-                        'notifications_enabled',
-                        'google_id',
-                        'github_id'
-                    )
-                    ->offset($offset)
-                    ->limit($limit)
-                    ->orderBy($column, $direction)
-                    ->findAll();
+        return $this->getQuery()->asc('username')->findAll();
     }
 
     /**