path: root/html/app/views/simple/Model.php

<?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;
        
    }

}