diff options
Diffstat (limited to 'public/app/views/simple/Model.php')
| -rw-r--r-- | public/app/views/simple/Model.php | 887 |
1 files changed, 887 insertions, 0 deletions
diff --git a/public/app/views/simple/Model.php b/public/app/views/simple/Model.php new file mode 100644 index 0000000..6ea1e7d --- /dev/null +++ b/public/app/views/simple/Model.php @@ -0,0 +1,887 @@ +<?php +/** Wiki Model Class + * ----------------------------------------------------------------------------- + * + * + connects to the Wiki database; forwards SQL queries and returns data + * + * + forms data to be used in the application + * + * + implements some optimizations (data-caching) + * + * ----------------------------------------------------------------------------- + */ +class Wiki_Model +{ + + protected $pdo; + + protected $pdo_pythia; + + protected $user_id; // connected user-id + + protected $wiki_files_root = 'uploads/wiki/files'; // root path for wiki file attachments + + + /** __construct + * + * Connects to the Wiki database + * + */ + public function __construct() + { + /// // Default + /// $db = new Database(); + /// $this->pdo_pythia= $db->connect(); + + /// // database for wiki + /// $this->pdo = new PDO('mysql:unix_socket=/cloudsql/pythia-251711:europe-west4:pythia-db-eu;dbname=' . DB_DATABASE . ';charset=utf8', DB_USERNAME, DB_PASSWORD, array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC)); + /// $this->user_id = $_SESSION['user_session']; + + $this->pdo = $GLOBALS['DB_con']; + + } + + + /** Close Instance + * --------------------------------------------------- (probably not needed) + */ + public function close() + { + // Default + // $this->pdo=Database::disconnect(); + } + + + + ## ------------------------------------------------------------------------- + ## + ## CATEGORY METHODS + ## + ## ------------------------------------------------------------------------- + + + /** get all categories + * + * raw table data (simplest SELECT) + * + */ + public function get_categories() + { + $sql = "SELECT * FROM wiki_category ORDER BY title"; + $stmt = $this->pdo->prepare($sql); + $stmt->execute(); + + return $stmt->fetchAll(PDO::FETCH_ASSOC); + } + + + /** constuct a category_tree + * + * returns a tree representation of the categories + * + * NOTE: + * category_tree() is an expensive method; + * it calls 2 other methods implementing recursive algorithms + * thus it uses many sources to run (particularly RAM). + * Caching the result is strogly recommended. + * + */ + public function category_tree() + { + $categories = $this->get_categories(); // get all categories + + $tree = $this->to_tree($categories); // format to a tree + + $tree_wParents = $this->tree_parents($tree); // add parents section for each tree-node + + return $tree_wParents; + } + + + /** to_tree + * + * constructs a tree from raw-table data; + * this is a private method and uses a recursive algorithm + * + * @param $dataset (array): flar array of records with id/parent-id pairs + * @return $root (array): id of root category + * + * (**) each node has 2 parts: + * .... .. rec : all record attributes/data as passed into $dataset + * .... .. childs : array of (children) nodes + */ + public function to_tree($dataset, $root = 0) + { + $return = []; + + // loop data ; search for direct children of root + foreach($dataset as $key => $rec) { + + $child = $rec['id']; + $parent = $rec['parent_id']; + + if ($parent == $root) { // a direct child is found + + unset($dataset[$key]); // remove item (no need to traverse again) + + // Append the child into result array ; parse its children + $return[] = [ + 'rec' => [ + 'id' => $rec['id'], + 'title' => $rec['title'], + 'parent' => $rec['parent_id'] + ], + 'childs' => $this->to_tree($dataset, $child) // recursively + ]; + } + } + return empty($return) ? [] : $return; + } + + + /** tree_parents + * + * adds a section to each tree node with all parents of each node + * + * @param $tree (array) : nodes array (each node has `rec` and `childs` sections ) + * @param $parents (array); DO NOT SET IT (takes values automaticaly) + * @return array of nodes with an extra node[parents] section + * + */ + private function tree_parents($tree, $parents = []) + { + $tree_with_parents = []; + + foreach($tree as $key => $node) { + // parents to be pushed for node's children + $push_parents = $parents; // parents so far + $push_parents[] = $node['rec']; // this record will be a new parent + + $tree_with_parents[$key] = [ + 'rec' => $node['rec'], + 'parents' => $parents, + 'childs' => ($node['childs'] == []) + ? [] + : $this->tree_parents($node['childs'], $push_parents) + ]; + } + + return $tree_with_parents; + } + + + /** add_category() + * + * add new category + * + * @param $recuest (array): [ title => , parent_id => ] + * @return true; + * + * TODO: catch database error -> return succes => false + */ + public function add_category($request) + { + $sql = "INSERT INTO wiki_category (title, parent_id) VALUES (:title, :parent_id)"; + $stmt = $this->pdo->prepare($sql); + $stmt->execute([ + 'title' => $request['title'], + 'parent_id' => $request['parent_id'] + ]); + $inserted_id = $this->pdo->lastInsertId(); + + return [ "success" => true, 'id' => $inserted_id ]; + } + + + /** update_category() + * + * update an existing category + * + * @param $recuest (array): [ id => , title => , parent_id => ] + * @return true; + * + * TODO: catch database error -> return false + */ + public function update_category($request) + { + $sql = "UPDATE wiki_category SET title = :title, parent_id = :parent_id WHERE id = :id"; + $stmt = $this->pdo->prepare($sql); + $stmt->execute([ + 'title' => $request['title'], + 'parent_id' => $request['parent_id'], + 'id' => $request['id'] + ]); ; + + return true; + } + + + + ## ------------------------------------------------------------------------- + ## + ## POST (ARTICLE) METHODS + ## + ## ------------------------------------------------------------------------- + + + /** get_post + * + * get a certain post (given the post's `id`) + * + * @param $id (int) + * @param $only_publised (bool): should the post be published? (default=true) + */ + public function get_post($id, $only_published = true) { + + $where_clause = ($only_published) ? ' AND post.status = 1' : ''; + + // depricated: $sql = "SELECT * FROM post WHERE id = :id {$where_clause}"; + // Select post + // + inject tags json + // TODO: + inject media + // --- + $sql = "SELECT wiki_post.*, + ( SELECT CONCAT('[', GROUP_CONCAT(JSON_OBJECT('id', wiki_tag.id, 'name', wiki_tag.name)), ']') + FROM wiki_tag + WHERE wiki_tag.id IN ( SELECT tag_id FROM wiki_post_tags WHERE post_id = :id ) + ) AS tags_json, + ( SELECT + CONCAT('[', GROUP_CONCAT(JSON_OBJECT( + 'id', wiki_media.id, + 'title', wiki_media.title, + 'type', wiki_media.type, + 'path', wiki_media.path, + 'reference', wiki_post_media.reference )), + ']') + FROM wiki_media + LEFT JOIN wiki_post_media ON wiki_post_media.media_id = wiki_media.id + WHERE wiki_post_media.post_id = :id + ) AS medias_json + FROM wiki_post + WHERE wiki_post.deleted IS NULL AND wiki_post.id = :id"; + + $stmt = $this->pdo->prepare($sql); + $stmt->execute(['id' => $id]); + + $result = $stmt->fetchAll(PDO::FETCH_ASSOC); + + return ($result == []) + ? false // no result? false + : $result[0]; // else return 1st record + } + + + /** add post + * + * insert post to database + * + * @param $data (array): an array with all post-properties + * @return: an array with `status` (bool), `id` (int) given to post + */ + public function add_post($data) + { + $post = [ + 'title' => $data['title'], + 'category_id' => $data['category_id'], + 'body' => $data['body'], + 'intro' => $data['intro'], + 'status' => $data['status'], + 'userid' => $this->user_id + ]; + + $sql = "INSERT INTO wiki_post (`title`, category_id, `body`, intro, `status`, user_id) + VALUES (:title, :category_id, :body, :intro, :status, :userid)"; + $stmt = $this->pdo->prepare($sql); + $stmt->execute($post); + + $inserted_id = $this->pdo->lastInsertId(); + + // create + link tags to the post + // --- -- -- - - - + if (isset($data['tags'])) { // existing tags... + $this->set_post_tags($inserted_id, $data['tags']); + } + if (isset($data['new_tags'])) { // new tags... + $this->create_tags_for_post($data['new_tags'], $inserted_id); + } + + // set medias for post + // --- -- -- - - - + if (isset($data['media'])) { // new tags... + $this->create_medias_for_post($data['media'], $inserted_id); + } + + return [ "success" => true, 'id' => $inserted_id ]; + } + + + /** update post + * + * update post record to database + * + * @param $data (array): an array with all post-properties + */ + public function update_post($data) + { + $post = [ + 'id' => $data['id'], + 'title' => $data['title'], + 'category_id' => $data['category_id'], + 'body' => $data['body'], + 'intro' => $data['intro'], + 'status' => $data['status'] + ]; + $sql = "UPDATE wiki_post + SET `title` = :title, category_id = :category_id, + `body` = :body, intro = :intro, + `status` = :status + WHERE id = :id"; + $stmt = $this->pdo->prepare($sql); + $stmt->execute($post); + + // create + link tags to the post + // --- -- -- - - - + if (isset($data['tags'])) { // old tags... + $this->set_post_tags($post['id'], $data['tags']); + } + if (isset($data['new_tags'])) { // new tags... + $this->create_tags_for_post($data['new_tags'], $post['id']); + } + + // set medias for post + // --- -- -- - - - + if (isset($data['media'])) { + $this->remove_media_from_post($post['id']); // remove old + $this->create_medias_for_post($data['media'], $post['id']); // re-create new + } + + return true; + } + + + /** latest_published_posts + * + * latest PUBLISHED posts of a category (by category_id) + * + * TODO: handle sorting + * + * @param $category_id (int) ; if 0 then no category is specified + * @param $limit (int) ; 0 = no limit + * @param $fieldset (string): list of fields; default all = '*' + */ + public function latest_published_posts($category_id, $limit = 10, $fieldset = '*') + { + + $where_clause = ($category_id) + ? " AND category_id = :categoryid " + : " "; + + $limit_clause = ($limit) + ? " LIMIT ". $limit + : ""; + + $sql = "SELECT {$fieldset} FROM wiki_post + WHERE `status` = 1 AND deleted IS NULL ". $where_clause + ." ORDER BY creation_date DESC ". $limit_clause; + + $stmt = $this->pdo->prepare($sql); + + if (!$category_id) { + $stmt->execute(); + + } else { + $stmt->execute(['categoryid' => $category_id]); + } + + $result = $stmt->fetchAll(PDO::FETCH_ASSOC); + + return ($result === null) + ? false // no result? return false + : $result; // else return results + } + + + /** delete post + * + * NOTE: + * post is *marked* as deleted; + * a cron-job will make the actual deletions in a later time + * + * @param $id (int): post id + */ + public function delete_post($id) + { + if (intval($id) == 0) { // no valid id? + return ["success" => false]; // return false + } + + $sql = "UPDATE wiki_post SET deleted = 1 WHERE id = :id"; + $stmt = $this->pdo->prepare($sql); + $stmt->execute(['id' => $id]); + + return ["success" => true]; + } + + + /** all posts + * published + unpublished ; any category ; no limit + * + * @param $filedset (string): list of fields; default all = '*' + */ + public function all_posts($fieldset = '*') + { + + $sql = "SELECT {$fieldset} FROM wiki_post WHERE deleted IS NULL"; + $stmt = $this->pdo->prepare($sql); + $stmt->execute(); + + $result = $stmt->fetchAll(PDO::FETCH_ASSOC); + + return ($result === null) + ? false // no result? return false + : $result; // else return results + } + + + + ## ------------------------------------------------------------------------- + ## + ## MEDIA METHODS + ## + ## ------------------------------------------------------------------------- + + /** create media for post + * + * links post to each media-file of the media `id`s array + * @param $media (array): a list of media-file `id`s + * @param $post_id (int) + */ + private function create_medias_for_post( $medias, $post_id ) + { + foreach($medias as $medi_string) { + $medi = explode(';', $medi_string); // [0]: media_id, [1]: reference + $this->link_media_to_post($medi[0], $post_id, $medi[1]); // link to post + } + return true; + } + + /** remove_media_from_post + * + * remove all media records/attachments from post + * + * @param $post_id (int) + */ + private function remove_media_from_post( $post_id ) + { + $sql = "DELETE FROM wiki_post_media WHERE post_id = :postid"; + $stmt = $this->pdo->prepare($sql); + $stmt->execute(['postid' => $post_id]); + return true; + } + + /** link one media-file to a specific post + * + * NOTE: + * the method does not check if media is linked already + * so be sure that the pair of (media_id,post_id) not exist + * + * @param $media_id (int) + * @param $post_id (int) + * @param $reference (int|bit) + */ + private function link_media_to_post( $media_id, $post_id, $reference ) + { + $sql = "INSERT INTO wiki_post_media (post_id, media_id, reference) VALUES (:postid, :mediaid, :reference)"; + $stmt = $this->pdo->prepare($sql); + $stmt->execute([ + 'postid' => $post_id, + 'mediaid' => $media_id, + 'reference' => $reference + ]); + return true; + } + + + + + + ## ------------------------------------------------------------------------- + ## + ## FILE METHODS + ## + ## ------------------------------------------------------------------------- + + + /** upload_file_to_folder + * + * upload the file to CloudStorage + * in a virtual folder + * + * @param $folder + */ + public function upload_file_to_folder($folder) + { + // Checks before uploading the file + //////////////////////////////////////////////////////////////////////// + + // ** 1: file is upladed to temporary folder --------------------------- + if (! is_uploaded_file($_FILES['file']['tmp_name'])) { + return false; // bye! + } + + // ** 2: File belongs to the allowed MIME types ------------------------ + $allowed_file_types = array( + 'application/pdf', + 'image/png', 'image/jpeg', + 'application/msword', 'application/vnd.openxmlformats-officedocument.wordprocessingml.document', + 'application/vnd.ms-excel', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' + ); + // Recomended MIME type checking via mime_content_type(): + $mime_type = mime_content_type($_FILES['file']['tmp_name']); + if (! in_array($mime_type, $allowed_file_types)) { // File type NOT allowed ... + return false; // bye! + } + + $file_name = $_FILES['file']['name']; + $file_type = $_FILES['file']['type']; // do not take it for granted + $file_size = $_FILES['file']['size']; + $file_tmp = $_FILES['file']['tmp_name']; + + $bare_name = pathinfo($file_name, PATHINFO_FILENAME); + $file_ext = pathinfo($file_name, PATHINFO_EXTENSION); + + + // ** 3: filename or size checks may be added -------------------------- + if ($file_name == "") { + return false; // bye! + } + + // READY to finaly save/upload the file to CDN ///////////////////////// + + $store_filename = $folder .'/'. strtolower($this->clear_file_name($bare_name) .'.'. $file_ext); + + $file_destination = $this->wiki_files_root .'/'. $store_filename; + + // depricated; it is autoloaded include_once CLOUDSTORAGE; + $cloudstorage = new CloudStorage(); + + if ($cloudstorage->upload_object($file_destination, $file_tmp)) { + + return [ + 'path' => $file_destination, + 'type' => $mime_type + ]; + + } else { return false; } + } + + /** uc_split + * + * this is an mb_str_split polyfill because + * pythia uses a php version < 7.4 + * + * CHECK: https://www.php.net/manual/en/function.mb-str-split.php + */ + private function uc_split($string, $length = 1, $encoding = 'UTF-8' ) + { + if(!empty($string)){ + $split = array(); + $mb_strlen = mb_strlen($string,$encoding); + for($pi = 0; $pi < $mb_strlen; $pi += $length){ + $substr = mb_substr($string, $pi,$length,$encoding); + if( !empty($substr)) { + $split[] = $substr; + } + } + } + return $split; + } + + /** clear_file_name + * replace greek characters and strip symbols + */ + private function clear_file_name($str) + { + $el = $this->uc_split("ΑΒΓΔΕΖΗΘΙΚΛΜΝΞΟΠΡΣΤΥΦΧΨΩαβγδεζηθικλμνξοπρστυφχψωάέήίόύώϊϋς "); + $en = str_split( "ABGDEZHUIKLMNJOPRSTYFXCVabgdezhuiklmnjoprstyfxcvaehioyviys-"); + $strip = str_split("!@#$%^&*()+~`[]{};'/<>?=\""); + + return str_replace($strip, '', str_replace($el, $en, $str)); + } + + + /** define_media + * + * create a record in media table + * + * @param $data + * @return id (int): id of created media record + */ + public function define_media($data) + { + $sql = "INSERT INTO wiki_media (`title`, `type`, `path`) + VALUES (:title, :mimetype, :filepath)"; + $stmt = $this->pdo->prepare($sql); + $stmt->execute([ + 'title' => $data['title'], + 'mimetype' => $data['type'], + 'filepath' => $data['path'] + ]); + $inserted_id = $this->pdo->lastInsertId(); + + return $inserted_id; + } + + + ## ------------------------------------------------------------------------- + ## + ## TAG METHODS + ## + ## ------------------------------------------------------------------------- + + /** all tags + * + * @return: (array) all tags + */ + public function all_tags() + { + $sql = "SELECT * FROM wiki_tag"; + $stmt = $this->pdo->prepare($sql); + $stmt->execute(); + + $result = $stmt->fetchAll(PDO::FETCH_ASSOC); + + return ($result === null) + ? false // no result? return false + : $result; // else return results + } + + + /** create_tag + * + * @param $tag (string): the tag label + * @return : id (int) given to tag + */ + public function create_tag($tag) + { + $sql = "INSERT INTO wiki_tag (`name`) VALUES (:label)"; + $stmt = $this->pdo->prepare($sql); + $stmt->execute([ 'label' => $tag ]); + + $tag_id = $this->pdo->lastInsertId(); + + return $tag_id; + } + + /** destroy_tag + * + * @param $tag_id (int) + */ + public function destroy_tag($id) + { + // remove all post-links to this tag + $clean_posts = "DELETE FROM wiki_post_tags WHERE tag_id = :tagid"; + $clean = $this->pdo->prepare($clean_posts); + $clean->execute([ 'tagid' => $id ]); + + // remove tag from tags table + $sql = "DELETE FROM wiki_tag WHERE id = :tagid"; + $stmt = $this->pdo->prepare($sql); + $stmt->execute([ 'tagid' => $id ]); + + return true; + } + + /** posts_of_tag + * + * return posts linked to the specified tag (by tag-id) + * + * @param tag_id + * @param limit + * @param fieldset (string): list of post field-names separated with a comma (,) + * @param only_published (bool) + */ + public function posts_of_tag($tag_id, $limit = 0, $fieldset = '*', $only_published = true) + { + // format post field-names for SELECT SQL + // post_fields will be somethning like... `post.*` + // or... `post.id, post.title, post.into` + // --- -- -- - - - + $fields = explode(',', str_replace(' ', '', $fieldset)); + $fields_arr = []; + foreach($fields as $f) { $fields_arr[] = 'wiki_post.'. $f; } + $post_fields = implode(', ', $fields_arr); + + $limit_clause = ($limit) + ? " LIMIT ". $limit + : ""; + + $filter = ($only_published) + ? " AND post.status = 1 " + : ""; + + $sql = "SELECT {$post_fields} FROM wiki_post + LEFT JOIN wiki_post_tags ON wiki_post_tags.post_id = wiki_post.id + LEFT JOIN wiki_tag ON wiki_tag.id = wiki_post_tags.tag_id + WHERE wiki_post.deleted IS NULL AND wiki_tag.id = :tagid {$filter} + {$limit_clause}"; + + $stmt = $this->pdo->prepare($sql); + $stmt->execute(['tagid' => $tag_id]); + + $result = $stmt->fetchAll(PDO::FETCH_ASSOC); + + return $result; + } + + + /** tag_name + * return tag name (name = label / title / text) + * by tag_id + * + * @param $id (int): given tag-id + * @return (string) + */ + public function tag_name($id) { + $sql = "SELECT `name` FROM wiki_tag WHERE id = :id LIMIT 1"; + $stmt = $this->pdo->prepare($sql); + $stmt->execute(['id' => $id]); + + $result = $stmt->fetch(); + + return $result['name']; + + } + + /** set_tags_to_post() + * + * REMOVE OLD tags from post; + * then SET NEW tags + * + * NOTE: + * make sure that any old tags are removed from post; + * new tags should not be linked in a later cycle + * + */ + private function set_post_tags($post_id, $tags) + { + $this->remove_tags_from_post($post_id); // remobve any old tags + + foreach($tags as $tag) { // set each tag to post + $this->link_tag_to_post($tag, $post_id); + } + } + + /** remove_tags_from_post + * + * @param $post_id (int) + */ + private function remove_tags_from_post( $post_id ) + { + $sql = "DELETE FROM wiki_post_tags WHERE post_id = :postid"; + $stmt = $this->pdo->prepare($sql); + $stmt->execute(['postid' => $post_id]); + return true; + } + + /** set one tag to a specific post + * + * NOTE: + * the method does not check if tag is linked already + * + */ + private function link_tag_to_post( $tag_id, $post_id ) + { + $sql = "INSERT INTO wiki_post_tags (post_id, tag_id) VALUES (:postid, :tagid)"; + $stmt = $this->pdo->prepare($sql); + $stmt->execute([ + 'postid' => $post_id, + 'tagid' => $tag_id + ]); + return true; + } + + /** create tags for post + * + * creates an array of tags; links post to each one; + * @param $tags (array): a list of tags (labels/texts) + * @param $post_id (int) + */ + private function create_tags_for_post( $tags, $post_id ) + { + foreach($tags as $tag) { + $tag_id = $this->create_tag($tag); // create tag + $this->link_tag_to_post($tag_id, $post_id); // link to post + } + return true; + } + + + public function tags_stats() + { + $sql = "SELECT tag.id, tag.name, COUNT(post_tags.post_id) as totals + FROM wiki_tag + LEFT JOIN wiki_post_tags ON wiki_post_tags.tag_id = wiki_tag.id + GROUP BY wiki_tag.id"; + $stmt = $this->pdo->prepare($sql); + $stmt->execute(); + + return $stmt->fetchAll(PDO::FETCH_ASSOC); + } + + + ## ------------------------------------------------------------------------- + ## + ## USER METHODS + ## + ## ------------------------------------------------------------------------- + + /** user_info + * + * no user is passed; user info are exrtacted from Session + * + */ + private function user_info() + { + $user = []; + $keys = [ + 'user_session' => 'user', + 'employee_id' => 'employee', + 'store_id' => 'store', + 'site_id' => 'site', + 'unit_id' => 'unit', + 'employee_rID' => 'rid' + ]; + + foreach($keys as $key => $attribute) { + if (isset($_SESSION[$key]) && ($_SESSION[$key] != '')) { + $user[$attribute] = $_SESSION[$key]; + } + } + return $wiki_user; + } + + + /** update post stats + * + */ + public function update_post_stats($post_id) + { + + $user_id = $_SESSION['user_session']; // get user from session + + $sql = "INSERT INTO wiki_stats + (post_id, user_id, views) VALUES (:post, :user, 1) + ON DUPLICATE KEY UPDATE views = views + 1"; + + $stmt = $this->pdo->prepare($sql); + $stmt->execute([ + 'user' => $user_id, + 'post' => $post_id + ]); + + return true; + + } + +} |