From 9383a15af699ede77142d040b65118e15754a2ca Mon Sep 17 00:00:00 2001 From: Frédéric Guillot Date: Sat, 25 Jan 2014 14:56:02 -0500 Subject: First commit --- models/.htaccess | 1 + models/base.php | 48 ++++++++++++++ models/board.php | 166 ++++++++++++++++++++++++++++++++++++++++++++++++ models/config.php | 88 ++++++++++++++++++++++++++ models/project.php | 162 +++++++++++++++++++++++++++++++++++++++++++++++ models/schema.php | 71 +++++++++++++++++++++ models/task.php | 180 +++++++++++++++++++++++++++++++++++++++++++++++++++++ models/user.php | 154 +++++++++++++++++++++++++++++++++++++++++++++ 8 files changed, 870 insertions(+) create mode 100644 models/.htaccess create mode 100644 models/base.php create mode 100644 models/board.php create mode 100644 models/config.php create mode 100644 models/project.php create mode 100644 models/schema.php create mode 100644 models/task.php create mode 100644 models/user.php (limited to 'models') diff --git a/models/.htaccess b/models/.htaccess new file mode 100644 index 00000000..14249c50 --- /dev/null +++ b/models/.htaccess @@ -0,0 +1 @@ +Deny from all \ No newline at end of file diff --git a/models/base.php b/models/base.php new file mode 100644 index 00000000..85a8f252 --- /dev/null +++ b/models/base.php @@ -0,0 +1,48 @@ +getDatabaseInstance(); + } + + $this->db = self::$dbInstance; + } + + public function getDatabaseInstance() + { + $db = new \PicoDb\Database(array( + 'driver' => 'sqlite', + 'filename' => self::DB_FILENAME + )); + + if ($db->schema()->check(self::DB_VERSION)) { + return $db; + } + else { + die('Unable to migrate database schema!'); + } + } +} diff --git a/models/board.php b/models/board.php new file mode 100644 index 00000000..4a46d6c5 --- /dev/null +++ b/models/board.php @@ -0,0 +1,166 @@ +db->startTransaction(); + + $taskModel = new \Model\Task; + $results = array(); + + foreach ($values as $value) { + $results[] = $taskModel->move( + $value['task_id'], + $value['column_id'], + $value['position'] + ); + } + + $this->db->closeTransaction(); + + return ! in_array(false, $results, true); + } + + // Create board with default columns => must executed inside a transaction + public function create($project_id, array $columns) + { + $position = 0; + + foreach ($columns as $title) { + + $values = array( + 'title' => $title, + 'position' => ++$i, + 'project_id' => $project_id, + ); + + $this->db->table(self::TABLE)->save($values); + } + + return true; + } + + // Add a new column to the board + public function add(array $values) + { + $values['position'] = $this->getLastColumnPosition($values['project_id']) + 1; + return $this->db->table(self::TABLE)->save($values); + } + + // Update columns + public function update(array $values) + { + $this->db->startTransaction(); + + foreach ($values as $column_id => $column_title) { + $this->db->table(self::TABLE)->eq('id', $column_id)->update(array('title' => $column_title)); + } + + $this->db->closeTransaction(); + + return true; + } + + // Get columns and tasks for each column + public function get($project_id) + { + $taskModel = new \Model\Task; + + $this->db->startTransaction(); + + $columns = $this->getColumns($project_id); + + foreach ($columns as &$column) { + $column['tasks'] = $taskModel->getAllByColumnId($project_id, $column['id'], array(1)); + } + + $this->db->closeTransaction(); + + return $columns; + } + + // Get list of columns + public function getColumnsList($project_id) + { + return $this->db->table(self::TABLE)->eq('project_id', $project_id)->asc('position')->listing('id', 'title'); + } + + // Get all columns information for a project + public function getColumns($project_id) + { + return $this->db->table(self::TABLE)->eq('project_id', $project_id)->asc('position')->findAll(); + } + + // Get the number of columns for a project + public function countColumns($project_id) + { + return $this->db->table(self::TABLE)->eq('project_id', $project_id)->count(); + } + + // Get just one column + public function getColumn($column_id) + { + return $this->db->table(self::TABLE)->eq('id', $column_id)->findOne(); + } + + // Get the position of the last column for a project + public function getLastColumnPosition($project_id) + { + return (int) $this->db + ->table(self::TABLE) + ->eq('project_id', $project_id) + ->desc('position') + ->findOneColumn('position'); + } + + // Remove a column and all tasks associated to this column + public function removeColumn($column_id) + { + return $this->db->table(self::TABLE)->eq('id', $column_id)->remove(); + } + + // Validate columns update + public function validateModification(array $columns, array $values) + { + $rules = array(); + + foreach ($columns as $column_id => $column_title) { + $rules[] = new Validators\Required('title['.$column_id.']', t('The title is required')); + $rules[] = new Validators\MaxLength('title['.$column_id.']', t('The maximum length is %d characters', 50), 50); + } + + $v = new Validator($values, $rules); + + return array( + $v->execute(), + $v->getErrors() + ); + } + + // Validate column creation + public function validateCreation(array $values) + { + $rules = array(); + + $v = new Validator($values, array( + new Validators\Required('project_id', t('The project id is required')), + new Validators\Integer('project_id', t('This value must be an integer')), + new Validators\Required('title', t('The title is required')), + new Validators\MaxLength('title', t('The maximum length is %d characters', 50), 50), + )); + + return array( + $v->execute(), + $v->getErrors() + ); + } +} \ No newline at end of file diff --git a/models/config.php b/models/config.php new file mode 100644 index 00000000..d854047d --- /dev/null +++ b/models/config.php @@ -0,0 +1,88 @@ + t('English'), + 'fr_FR' => t('French'), + ); + } + + public function get($name, $default_value = '') + { + if (! isset($_SESSION['config'][$name])) { + $_SESSION['config'] = $this->getAll(); + } + + if (isset($_SESSION['config'][$name])) { + return $_SESSION['config'][$name]; + } + + return $default_value; + } + + public function getAll() + { + return $this->db->table(self::TABLE)->findOne(); + } + + public function save(array $values) + { + $_SESSION['config'] = $values; + return $this->db->table(self::TABLE)->update($values); + } + + public function reload() + { + $_SESSION['config'] = $this->getAll(); + + $language = $this->get('language', 'en_US'); + if ($language !== 'en_US') \Translator\load($language); + } + + public function validateModification(array $values) + { + $v = new Validator($values, array( + new Validators\Required('language', t('The language is required')), + )); + + return array( + $v->execute(), + $v->getErrors() + ); + } + + public static function generateToken() + { + if (ini_get('open_basedir') === '') { + return substr(base64_encode(file_get_contents('/dev/urandom', false, null, 0, 20)), 0, 15); + } + else { + return substr(base64_encode(uniqid(mt_rand(), true)), 0, 20); + } + } + + public function optimizeDatabase() + { + $this->db->getconnection()->exec("VACUUM"); + } + + public function downloadDatabase() + { + return gzencode(file_get_contents(self::DB_FILENAME)); + } + + public function getDatabaseSize() + { + return filesize(self::DB_FILENAME); + } +} diff --git a/models/project.php b/models/project.php new file mode 100644 index 00000000..7a0fb2b1 --- /dev/null +++ b/models/project.php @@ -0,0 +1,162 @@ +db->table(self::TABLE)->eq('id', $project_id)->findOne(); + } + + public function getAll($fetch_stats = false) + { + if (! $fetch_stats) { + return $this->db->table(self::TABLE)->asc('name')->findAll(); + } + + $this->db->startTransaction(); + + $projects = $this->db + ->table(self::TABLE) + ->asc('name') + ->findAll(); + + $taskModel = new \Model\Task; + $boardModel = new \Model\Board; + + foreach ($projects as &$project) { + + $columns = $boardModel->getcolumns($project['id']); + $project['nb_active_tasks'] = 0; + + foreach ($columns as &$column) { + $column['nb_active_tasks'] = $taskModel->countByColumnId($project['id'], $column['id']); + $project['nb_active_tasks'] += $column['nb_active_tasks']; + } + + $project['columns'] = $columns; + $project['nb_tasks'] = $taskModel->countByProjectId($project['id']); + } + + $this->db->closeTransaction(); + + return $projects; + } + + public function getList() + { + return array(t('None')) + $this->db->table(self::TABLE)->asc('name')->listing('id', 'name'); + } + + public function getAllByStatus($status) + { + return $this->db + ->table(self::TABLE) + ->asc('name') + ->eq('is_active', $status) + ->findAll(); + } + + public function getListByStatus($status) + { + return $this->db + ->table(self::TABLE) + ->asc('name') + ->eq('is_active', $status) + ->listing('id', 'name'); + } + + public function countByStatus($status) + { + return $this->db + ->table(self::TABLE) + ->eq('is_active', $status) + ->count(); + } + + public function create(array $values) + { + $this->db->startTransaction(); + + $this->db->table(self::TABLE)->save($values); + + $project_id = $this->db->getConnection()->getLastId(); + + $boardModel = new \Model\Board; + + $boardModel->create($project_id, array( + t('Backlog'), + t('Ready'), + t('Work in progress'), + t('Done'), + )); + + $this->db->closeTransaction(); + + return $project_id; + } + + public function update(array $values) + { + return $this->db->table(self::TABLE)->eq('id', $values['id'])->save($values); + } + + public function remove($project_id) + { + return $this->db->table(self::TABLE)->eq('id', $project_id)->remove(); + } + + public function enable($project_id) + { + return $this->db + ->table(self::TABLE) + ->eq('id', $project_id) + ->save(array('is_active' => 1)); + } + + public function disable($project_id) + { + return $this->db + ->table(self::TABLE) + ->eq('id', $project_id) + ->save(array('is_active' => 0)); + } + + public function validateCreation(array $values) + { + $v = new Validator($values, array( + new Validators\Required('name', t('The project name is required')), + new Validators\MaxLength('name', t('The maximum length is %d characters', 50), 50), + new Validators\Unique('name', t('This project must be unique'), $this->db->getConnection(), self::TABLE) + )); + + return array( + $v->execute(), + $v->getErrors() + ); + } + + public function validateModification(array $values) + { + $v = new Validator($values, array( + new Validators\Required('id', t('The project id is required')), + new Validators\Integer('id', t('This value must be an integer')), + new Validators\Required('name', t('The project name is required')), + new Validators\MaxLength('name', t('The maximum length is %d characters', 50), 50), + new Validators\Unique('name', t('This project must be unique'), $this->db->getConnection(), self::TABLE) + )); + + return array( + $v->execute(), + $v->getErrors() + ); + } +} diff --git a/models/schema.php b/models/schema.php new file mode 100644 index 00000000..3217663a --- /dev/null +++ b/models/schema.php @@ -0,0 +1,71 @@ +exec(" + CREATE TABLE config ( + language TEXT, + webhooks_token TEXT + ) + "); + + $pdo->exec(" + CREATE TABLE users ( + id INTEGER PRIMARY KEY, + username TEXT, + password TEXT, + is_admin INTEGER DEFAULT 0, + default_project_id DEFAULT 0 + ) + "); + + $pdo->exec(" + CREATE TABLE projects ( + id INTEGER PRIMARY KEY, + name TEXT NOCASE UNIQUE, + is_active INTEGER DEFAULT 1 + ) + "); + + $pdo->exec(" + CREATE TABLE columns ( + id INTEGER PRIMARY KEY, + title TEXT, + position INTEGER, + project_id INTEGER, + FOREIGN KEY(project_id) REFERENCES projects(id) ON DELETE CASCADE, + UNIQUE (title, project_id) + ) + "); + + $pdo->exec(" + CREATE TABLE tasks ( + id INTEGER PRIMARY KEY, + title TEXT, + description TEXT, + date_creation INTEGER, + color_id TEXT, + project_id INTEGER, + column_id INTEGER, + owner_id INTEGER DEFAULT '0', + position INTEGER, + is_active INTEGER DEFAULT 1, + FOREIGN KEY(project_id) REFERENCES projects(id) ON DELETE CASCADE, + FOREIGN KEY(column_id) REFERENCES columns(id) ON DELETE CASCADE + ) + "); + + $pdo->exec(" + INSERT INTO users + (username, password, is_admin) + VALUES ('admin', '".\password_hash('admin', PASSWORD_BCRYPT)."', '1') + "); + + $pdo->exec(" + INSERT INTO config + (language, webhooks_token) + VALUES ('en_US', '".\Model\Config::generateToken()."') + "); +} diff --git a/models/task.php b/models/task.php new file mode 100644 index 00000000..db27c650 --- /dev/null +++ b/models/task.php @@ -0,0 +1,180 @@ + t('Yellow'), + 'blue' => t('Blue'), + 'green' => t('Green'), + 'purple' => t('Purple'), + 'red' => t('Red'), + 'orange' => t('Orange'), + 'grey' => t('Grey'), + ); + } + + public function getById($task_id, $more = false) + { + if ($more) { + + return $this->db + ->table(self::TABLE) + ->columns( + self::TABLE.'.id', + self::TABLE.'.title', + self::TABLE.'.description', + self::TABLE.'.date_creation', + self::TABLE.'.color_id', + self::TABLE.'.project_id', + self::TABLE.'.column_id', + self::TABLE.'.owner_id', + self::TABLE.'.position', + self::TABLE.'.is_active', + \Model\Project::TABLE.'.name AS project_name', + \Model\Board::TABLE.'.title AS column_title', + \Model\User::TABLE.'.username' + ) + ->join(\Model\Project::TABLE, 'id', 'project_id') + ->join(\Model\Board::TABLE, 'id', 'column_id') + ->join(\Model\User::TABLE, 'id', 'owner_id') + ->eq(self::TABLE.'.id', $task_id) + ->findOne(); + } + else { + + return $this->db->table(self::TABLE)->eq('id', $task_id)->findOne(); + } + } + + public function getAllByProjectId($project_id) + { + return $this->db->table(self::TABLE)->eq('project_id', $project_id)->findAll(); + } + + public function countByProjectId($project_id, $status = array(1, 0)) + { + return $this->db + ->table(self::TABLE) + ->eq('project_id', $project_id) + ->in('is_active', $status) + ->count(); + } + + public function getAllByColumnId($project_id, $column_id, $status = array(1)) + { + return $this->db + ->table(self::TABLE) + ->columns('tasks.id', 'title', 'color_id', 'project_id', 'owner_id', 'column_id', 'position', 'users.username') + ->join('users', 'id', 'owner_id') + ->eq('project_id', $project_id) + ->eq('column_id', $column_id) + ->in('is_active', $status) + ->asc('position') + ->findAll(); + } + + public function countByColumnId($project_id, $column_id, $status = array(1)) + { + return $this->db + ->table(self::TABLE) + ->eq('project_id', $project_id) + ->eq('column_id', $column_id) + ->in('is_active', $status) + ->count(); + } + + public function create(array $values) + { + $this->db->startTransaction(); + + unset($values['another_task']); + + $values['date_creation'] = time(); + $values['position'] = $this->countByColumnId($values['project_id'], $values['column_id']); + + $this->db->table(self::TABLE)->save($values); + + $task_id = $this->db->getConnection()->getLastId(); + + $this->db->closeTransaction(); + + return $task_id; + } + + public function update(array $values) + { + return $this->db->table(self::TABLE)->eq('id', $values['id'])->update($values); + } + + public function close($task_id) + { + return $this->db->table(self::TABLE)->eq('id', $task_id)->update(array('is_active' => 0)); + } + + public function open($task_id) + { + return $this->db->table(self::TABLE)->eq('id', $task_id)->update(array('is_active' => 1)); + } + + public function remove($task_id) + { + return $this->db->table(self::TABLE)->eq('id', $task_id)->remove(); + } + + public function move($task_id, $column_id, $position) + { + return (bool) $this->db + ->table(self::TABLE) + ->eq('id', $task_id) + ->update(array('column_id' => $column_id, 'position' => $position)); + } + + public function validateCreation(array $values) + { + $v = new Validator($values, array( + new Validators\Required('color_id', t('The color is required')), + new Validators\Required('project_id', t('The project is required')), + new Validators\Integer('project_id', t('This value must be an integer')), + new Validators\Required('column_id', t('The column is required')), + new Validators\Integer('column_id', t('This value must be an integer')), + new Validators\Integer('owner_id', t('This value must be an integer')), + new Validators\Required('title', t('The title is required')), + new Validators\MaxLength('title', t('The maximum length is %d characters', 200), 200), + )); + + return array( + $v->execute(), + $v->getErrors() + ); + } + + public function validateModification(array $values) + { + $v = new Validator($values, array( + new Validators\Required('id', t('The id is required')), + new Validators\Integer('id', t('This value must be an integer')), + new Validators\Required('color_id', t('The color is required')), + new Validators\Required('project_id', t('The project is required')), + new Validators\Integer('project_id', t('This value must be an integer')), + new Validators\Required('column_id', t('The column is required')), + new Validators\Integer('column_id', t('This value must be an integer')), + new Validators\Integer('owner_id', t('This value must be an integer')), + new Validators\Required('title', t('The title is required')), + new Validators\MaxLength('title', t('The maximum length is %d characters', 200), 200), + )); + + return array( + $v->execute(), + $v->getErrors() + ); + } +} diff --git a/models/user.php b/models/user.php new file mode 100644 index 00000000..50e02fef --- /dev/null +++ b/models/user.php @@ -0,0 +1,154 @@ +db->table(self::TABLE)->eq('id', $user_id)->findOne(); + } + + public function getByUsername($username) + { + return $this->db->table(self::TABLE)->eq('username', $username)->findOne(); + } + + public function getAll() + { + return $this->db + ->table(self::TABLE) + ->asc('username') + ->columns('id', 'username', 'is_admin', 'default_project_id') + ->findAll(); + } + + public function getList() + { + return array(t('Unassigned')) + $this->db->table(self::TABLE)->asc('username')->listing('id', 'username'); + } + + public function create(array $values) + { + unset($values['confirmation']); + $values['password'] = \password_hash($values['password'], PASSWORD_BCRYPT); + + return $this->db->table(self::TABLE)->save($values); + } + + public function update(array $values) + { + if (! empty($values['password'])) { + $values['password'] = \password_hash($values['password'], PASSWORD_BCRYPT); + } + else { + unset($values['password']); + } + + unset($values['confirmation']); + + $this->db->table(self::TABLE)->eq('id', $values['id'])->save($values); + + if ($_SESSION['user']['id'] == $values['id']) { + $this->updateSession(); + } + + return true; + } + + public function remove($user_id) + { + return $this->db->table(self::TABLE)->eq('id', $user_id)->remove(); + } + + public function updateSession(array $user = array()) + { + if (empty($user)) { + $user = $this->getById($_SESSION['user']['id']); + } + + if (isset($user['password'])) unset($user['password']); + + $_SESSION['user'] = $user; + } + + public function validateCreation(array $values) + { + $v = new Validator($values, array( + new Validators\Required('username', t('The username is required')), + new Validators\MaxLength('username', t('The maximum length is %d characters', 50), 50), + new Validators\AlphaNumeric('username', t('The username must be alphanumeric')), + new Validators\Unique('username', t('The username must be unique'), $this->db->getConnection(), self::TABLE, 'id'), + new Validators\Required('password', t('The password is required')), + new Validators\MinLength('password', t('The minimum length is %d characters', 6), 6), + new Validators\Required('confirmation', t('The confirmation is required')), + new Validators\Equals('password', 'confirmation', t('Passwords doesn\'t matches')), + new Validators\Integer('default_project_id', t('The value must be an integer')), + new Validators\Integer('is_admin', t('This value must be an integer')), + )); + + return array( + $v->execute(), + $v->getErrors() + ); + } + + public function validateModification(array $values) + { + if (! empty($values['password'])) { + return $this->validateCreation($values); + } + else { + + $v = new Validator($values, array( + new Validators\Required('id', t('The user id is required')), + new Validators\Required('username', t('The username is required')), + new Validators\MaxLength('username', t('The maximum length is %d characters', 50), 50), + new Validators\AlphaNumeric('username', t('The username must be alphanumeric')), + new Validators\Unique('username', t('The username must be unique'), $this->db->getConnection(), self::TABLE, 'id'), + new Validators\Integer('default_project_id', t('This value must be an integer')), + new Validators\Integer('is_admin', t('This value must be an integer')), + )); + } + + return array( + $v->execute(), + $v->getErrors() + ); + } + + public function validateLogin(array $values) + { + $v = new Validator($values, array( + new Validators\Required('username', t('The username is required')), + new Validators\MaxLength('username', t('The maximum length is %d characters', 50), 50), + new Validators\Required('password', t('The password is required')), + )); + + $result = $v->execute(); + $errors = $v->getErrors(); + + if ($result) { + + $user = $this->getByUsername($values['username']); + + if ($user !== false && \password_verify($values['password'], $user['password'])) { + $this->updateSession($user); + } + else { + $result = false; + $errors['login'] = t('Bad username or password'); + } + } + + return array( + $result, + $errors + ); + } +} -- cgit v1.2.3