path: root/public/app/controllers/CmsAdmin.php

<?php 

namespace app\controllers;

use Registry;
use Render;
use app\models\Cms_model;
use app\extends\Cache_service;

class CmsAdmin {


    ## -------------------------------------------------------------------------
    ##
    ## COURSE (category) METHODS
    ##
    ## -------------------------------------------------------------------------


    /** breadcrumbs
     * ---
     * responds to ajax GET: /admin/api/categories
     */
    public static function breadcrumbs()
    {
        Render::json( Cache_service::courses_struct()['breadcrumbs'] );
    }

    /** add course
     * ---
     * @param void (get data from POST)
     */
    public static function add_course()
    {
        // insert new category; id = new category id
        $id = Registry::use('database')->query(
		    "INSERT INTO course (parent_id, label) VALUES (:parent, :label)",
            [
                ':parent' => Registry::get('REQUEST')->POST['parent_id'],
                ':label' => Registry::get('REQUEST')->POST['label']
            ]
        )->lastInsertID();
        
        $cache = self::update_courses_cache();  // update category caches
        return $cache['breadcrumbs'];           // return
    }


    /** update course
     * ---
     * @param void (get data from POST)
     */
    public static function update_course()
    {
        Registry::use('database')->query(
		    "UPDATE course SET parent_id = :parent, label = :label 
            WHERE id = :id",
            [
                ':id' => Registry::get('REQUEST')->POST['id'],
                ':parent' => Registry::get('REQUEST')->POST['parent_id'],
                ':label' => Registry::get('REQUEST')->POST['label']
            ]
        );
        $cache = self::update_courses_cache();
        return $cache['breadcrumbs'];
    }


    /** update courses cache
     * --- -- -- - - -
     * Forces re-caching of courses tree
     * Shall run if anything changes to courses
     * 
     * @param void
     * @return (array): ['tree' => ..., 'breadcrumbs' => ... ]
     */
    public static function update_courses_cache()
    {
        return Cache_service::courses_struct(PROXY_IGNORE_CACHE);
    }


    ## -------------------------------------------------------------------------
    ##
    ## LESSON METHODS
    ##
    ## -------------------------------------------------------------------------


    /** all lessons
     * 
     */
    public static function all_lessons()
    {
        Render::json(Registry::use('database')->runQuery(
            "SELECT lesson.*, lesson_privilege.privilege_id FROM lesson
            LEFT JOIN lesson_privilege ON lesson_privilege.lesson_id = lesson.id",
            []
        ));
    }


    /** get_lesson
     * 
     * @param $id (int) : lesson's id
     */
    public static function get_lesson($id)
    {
        // get (first) lesson with id = $id; render as json
        Render::json(Registry::use('database')->query(
            "SELECT lesson.*, 
            ( SELECT
                CONCAT('[', GROUP_CONCAT(JSON_OBJECT(
                    'id', media.id,
                    'label', media.label,
                    'type', media.type,
                    'path', media.path )),
                ']')
                FROM media
                LEFT JOIN lesson_media ON lesson_media.media_id = media.id
                WHERE lesson_media.lesson_id = :id
            ) AS medias_json,
            lesson_privilege.privilege_id
            FROM lesson
            LEFT JOIN lesson_privilege ON lesson_privilege.lesson_id = lesson.id
            WHERE lesson.id = :id",
            [ ':id' => $id]
        )->getFirst());
    }


    /** add_lesson
     * insert a new lesson
     * 
     * all parametres are passed via $_POST array
     * 
     * POST @param title
     * POST @param course_id
     * POST @param intro
     * POST @param body
     * POST @param published
     */
    public static function add_lesson()
    {
        $post = Registry::get('REQUEST')->POST;

        // insert lesson content into daabase
        $id = Registry::use('database')->query(
            "INSERT INTO lesson (title, course_id, intro, `body`, `status`)
            VALUES (:title, :courseid, :intro, :body, :status)",
            [
                ':title' => $post['title'],
                ':courseid' => $post['course_id'],
                ':intro' => $post['intro'],
                ':body' => $post['body'],
                'status' => $post['status']
            ]
        )->lastInsertID();

        // set lesson privileges to database
        Registry::use('database')->runQuery(
            "INSERT INTO lesson_privilege (lesson_id, privilege_id)
            VALUES (:lesson_id, :privilege_id)",
            [
                ':lesson_id' => $id,
                ':privilege_id' => $post['privilege_id']
            ]
        );

        if (isset($post['media'])) {
            // update media's privileges; NOTE: media table
            self::update_medias_privileges($post['media'], $post['privilege_id']);

            // set lesson's media files; NOTE: lesson_media table
            // ERROR: self::create_medias_for_lesson($post['media'], $id, $post['privilege_id']);
            self::create_medias_for_lesson($post['media'], $id);

            // recache media
            Cache_service::files_attributes(PROXY_IGNORE_CACHE);
        }

        return true;
    }


    public static function update_lesson()
    {
        $post = Registry::get('REQUEST')->POST;

        // print_r($post); die();

        // update lesson's content
        Registry::use('database')->query(
            "UPDATE lesson
            SET title = :title,
                course_id = :courseid,
                intro = :intro, `body` = :body,
                `status` = :status
            WHERE id = :id",
            [
                ':id' => $post['id'],
                ':title' => $post['title'],
                ':courseid' => $post['course_id'],
                ':intro' => $post['intro'],
                ':body' => $post['body'],
                ':status' => $post['status']
            ]
        );

        // update lesson's privileges
        Registry::use('database')->runQuery(
            "UPDATE lesson_privilege SET privilege_id = :privilege_id
            WHERE lesson_id = :lesson_id",
            [
                ':lesson_id' => $post['id'],
                ':privilege_id' => $post['privilege_id']
            ]
        );

        if (isset($post['media'])) {
            // update media's privileges
            self::update_medias_privileges($post['media'], $post['privilege_id']);
        }

        // remove all old lesson's links to media files
        Registry::use('database')->runQuery(
            "DELETE from lesson_media WHERE lesson_id = :lesson",
            [ ':lesson' => $post['id'] ]
        );

        if (isset($post['media'])) {
            // update lesson's media files
            // ERROR: self::create_medias_for_lesson($post['media'], $post['id'], $post['privilege_id']);
            self::create_medias_for_lesson($post['media'], $post['id']);

            // recache media
            Cache_service::files_attributes(PROXY_IGNORE_CACHE);
        }

        return true;
    }


    ## -------------------------------------------------------------------------
    ##
    ## FILE METHODS
    ##
    ## -------------------------------------------------------------------------


    /** files
     * echo all files
     * 
     * @return (array)
     */
    public static function files()
    {
        return  Cache_service::files_attributes();
    }


    /** upload_file
     * upload the file to the file system
     * 
     * the method reads the POST and FILES array
     * to retrieve all needed parametres
     *
     * FILES @param file
     * POST @param folder : lesson's ID or somthing random
     * POST @param reference : reference type
     * POST @param 
     */
    public static function upload_file()
    {
        $request = Registry::get('REQUEST');

        $uploaded = self::upload_to_fs();    // upload file to file-system

        if ($uploaded['success']) {

            $media_id = self::define_media([    // define media in database; get id
                'title' => $request->POST['title'],
                'type' => $uploaded['type'],
                'path' => $uploaded['path']
            ]);

            Render::json([      // render results as json
                'success' => true,
                'id' => $media_id,
                'title' => $request->POST['title'],
                'path' => $uploaded['path'],
                'type' => $uploaded['type']
            ]);

        } else {
            Render::json(['success' => false ]);
        }
    }


    /** upload to fs
     * upload file to File-System
     * 
     * POST @param folder
     * FILES @param file
     */
    private static function upload_to_fs()
    {
        $post = Registry::get('REQUEST')->POST;
        $files = Registry::get('REQUEST')->FILES;


        // Checks before uploading the file
        ////////////////////////////////////////////////////////////////////////

        // ** 1: file is upladed to temporary folder ---------------------------
        if (! is_uploaded_file($files['file']['tmp_name'])) {
            return ['success' => 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 ['success' => 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 ['success' => false];       // bye!
        }


        // READY to finaly save/upload the file to CDN /////////////////////////

        $folder = MEDIA_STORAGE_ROOT . $post['folder'];
        if (!file_exists($folder)) {    // create folder if not exists
            mkdir($folder, 0757, true);
        }

        // print_r([ 
        //     'dir' => $folder,
        //     'file' => $bare_name,
        //     'ext' => $file_ext,
        //     'type' => $file_type
        // ]); die();

        $relative_filename = $post['folder']
            .'/'
            . strtolower(self::clear_file_name($bare_name) .'.'. $file_ext);
        $store_filename = MEDIA_STORAGE_ROOT . $relative_filename;

        if (move_uploaded_file($files["file"]["tmp_name"], $store_filename)) {
            return [
                'success' => true,
                'path' => $relative_filename,
                'type' => $mime_type
            ];

        } else { return ['success' => false]; }

    }


    /** clear_file_name
     * replace greek characters and strip symbols
     */
    private static function clear_file_name($str)
    {
        $el = mb_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 (array): [title => , path => , type => mime-type]
     * @return id (int): id of created media record
     */
    private static function define_media($data)
    {
        $request = Registry::get('REQUEST');

        $media_id = Registry::use('database')->query(
            "INSERT INTO media (label, `type`, `path`)
            VALUES (:label, :mimetype, :filepath)",
            [
                'label' => $data['title'],
                'mimetype' => $data['type'],
                'filepath' => $data['path']
            ]
        )->lastInsertID();
		return $media_id;
    }


    /** create media for lesson
     * 
     * links lesson to each media-file of the media `id`s array
     * 
     * @param $media (array): a list of media-file `id`s
     * @param $lesson_id (int)
     */
    private static function create_medias_for_lesson($medias, $lesson_id)
    {
        foreach($medias as $key => $medi) {
            self::link_media_to_lesson($medi, $lesson_id);   // link to lesson
        }
        return true;
    }


    /** create media for page
     * 
     * links page to each media-file of the media `id`s array
     * 
     * @param $media (array): a list of media-file `id`s
     * @param $page_id (int)
     */
    private static function create_medias_for_page($medias, $page_id)
    {
        foreach($medias as $key => $medi) {
            self::link_media_to_page($medi, $page_id);   // link to page
        }
        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 $lesson_id (int)
     */
    private static function link_media_to_lesson( $media_id, $lesson_id )
    {
        Registry::use('database')->runQuery(
            "INSERT INTO lesson_media (lesson_id, media_id)
            VALUES (:lesson, :media)",
            [
                'lesson' => $lesson_id,
                'media' => $media_id,
            ]
        );
        return true;
    }

    /** link one media-file to a specific page
     *
     * NOTE:
     * the method does not check if media is linked already
     * so be sure that the pair of (media_id, page_id) not exist
     * 
     * @param $media_id (int)
     * @param $page_id (int)
     */
    private static function link_media_to_page( $media_id, $page_id )
    {
        Registry::use('database')->runQuery(
            "INSERT INTO page_media (page_id, media_id)
            VALUES (:page, :media)",
            [
                'page' => $page_id,
                'media' => $media_id,
            ]
        );
        return true;
    }


    /** update medias privileges
     * 
     * UPDATE media SET privige WHERE IN {list}
     * 
     * @param $medias (array) : array of media id(s)
     * @param $privilege (int) : privilege id
     * @return true (always)
     */
    private static function update_medias_privileges($medias, $privilege)
    {
        // create WHERE IN (LIST) holders and params for prepare statement
        $params = [ ':privilege' => $privilege];
        $holders = [];
        foreach($medias as $key => $media) {
            $params[':id'.$key] = $media;
            $holders[] = ':id'.$key;
        }
        $list = '('. implode(',', $holders) .')';

        // print_r(['params' => $params, 'list' => $list]); die();

        Registry::use('database')->runQuery(
            "UPDATE media SET privilege_id = :privilege
            WHERE id IN {$list}",
            $params
        );

        return true;
    }






    ## -------------------------------------------------------------------------
    ##
    ## PAGE METHODS
    ##
    ## -------------------------------------------------------------------------


    /** all pages
     * 
     */
    public static function all_pages()
    {
        Render::json(Registry::use('database')->runQuery(
            "SELECT * FROM page", []
        ));
    }


    /** get_page
     * 
     * @param $id (int) : page's id
     */
    public static function get_page($id)
    {
        // get (first) page with id = $id; render as json
        Render::json(Registry::use('database')->query(
            "SELECT page.*, 
            ( SELECT
                CONCAT('[', GROUP_CONCAT(JSON_OBJECT(
                    'id', media.id,
                    'label', media.label,
                    'type', media.type,
                    'path', media.path )),
                ']')
                FROM media
                LEFT JOIN page_media ON page_media.media_id = media.id
                WHERE page_media.page_id = :id
            ) AS medias_json
            FROM page
            WHERE page.id = :id",
            [ ':id' => $id]
        )->getFirst());
    }


    /** add_page
     * insert a new page
     * 
     * all parametres are passed via $_POST array
     * 
     * POST @param title
     * POST @param body
     * POST @param status (int): 0 = draft , 1 = published
     */
    public static function add_page()
    {
        $post = Registry::get('REQUEST')->POST;

        // insert page content into daabase
        $id = Registry::use('database')->query(
            "INSERT INTO page (title, `body`, `status`)
            VALUES (:title, :body, :status)",
            [
                ':title' => $post['title'],
                ':body' => $post['body'],
                'status' => $post['status']
            ]
        )->lastInsertID();


        if (isset($post['media'])) {
            // update media's privileges;
            // NOTE: media table; pages have public files (privilege_id=0)
            self::update_medias_privileges($post['media'], 0);

            // set page's media files
            // NOTE: page_media table
            self::create_medias_for_page($post['media'], $id);
        }

        self::update_pages_cache();     // update pages cache

        return true;
    }


    /** update_page
     * 
     * POST @param id (int)
     * POST @param title
     * POST @param body
     * POST @param status (int): 0 = draft , 1 = published
     */
    public static function update_page()
    {
        $post = Registry::get('REQUEST')->POST;

        // print_r($post); die();

        // update page's content
        Registry::use('database')->query(
            "UPDATE page
                SET title = :title, `body` = :body, `status` = :status
            WHERE id = :id",
            [
                ':id' => $post['id'],
                ':title' => $post['title'],
                ':body' => $post['body'],
                ':status' => $post['status']
            ]
        );

        if (isset($post['media'])) {
            // update media's privileges
            self::update_medias_privileges($post['media'], 0);
        }

        // remove all old page's links to media files
        Registry::use('database')->runQuery(
            "DELETE from page_media WHERE page_id = :page",
            [ ':page' => $post['id'] ]
        );

        if (isset($post['media'])) {
            // update page's media files
            self::create_medias_for_page($post['media'], $post['id']);
        }

        self::update_pages_cache();     // update pages cache

        return true;
    }


    /** update pages cache
     * --- -- -- - - -
     * Forces re-caching of pages tree
     * Shall run if anything changes to courses
     * 
     * @param void
     * @return (array): ['tree' => ..., 'breadcrumbs' => ... ]
     */
    public static function update_pages_cache()
    {
        return Cache_service::pages_list(PROXY_IGNORE_CACHE);
    }

}