<?php

class api_command_portlet extends picok_command {
    /**
     * Prefix for all via the proxyCall() callable methods
     */
    const METHOD_PREFIX = 'execute';

    /**
     * Exception Error Codes
     */
    const PORTLET_MISSING = -1;
    const PORTLET_METHOD_MISSING = -2;
    const USER_PORTLET_ID_MISSING = -3;
    const PORTLET_SETTINGS_NOT_FOUND = -4;
    const PORTLET_PARAM_MISSING = -5;
    const USER_PORTLET_MISSING = -6;
    const WRONG_EXTENSION = -7;
    const USER_TAB_MISSING = -8;
    const ALREADY_EXISTS = -9;
    const TAB_MISSING = -10;
    const PORTLET_DEACTIVATED = -11;

    protected function getIndexData($admin) {
        $data = array(
            'mode' => empty($admin) ? false : $admin,
            'is_admin' => $this->isAdmin,
            'tabs' => array(),
            'backgroundImagePath' => $this->config->backgroundImagePath,
            'laterRefreshRate' => $this->config->laterRefreshRate,
            'inactiveTabRemoveInterval' => $this->config->inactiveTabRemoveInterval,
            'languages' => $this->config->lang['languages'],
            'languageLabels' => $this->config->lang['languageLabels'],
            'themes' => $this->config->themes,
            'fileChainerActive' => $this->config->fileChainerActive,
            'jsFiles' => $this->config->jsFiles,
            'cssFiles' => $this->config->cssFiles,
            'contactLang' => (in_array($this->lang, $this->config->lang['contactLanguages'])) ? $this->lang : $this->config->lang['default'],
        );
       return $data;
    }

    /**
     * Generate the HTML interface
     *
     * @return bool
     */
    public function index($adminMode = false) {

        $_SESSION[$this->config->appname]['admin_mode'] = $adminMode;

        try {
            if ($this->extension !== 'html') {
                $msg = 'Index view is only available as HTML';
                throw new api_exception(api_exception::THROW_NONE, array(), api_command_portlet::WRONG_EXTENSION, $msg);
            }

            $this->pruneUserSetup();

            $data = $this->getIndexData($adminMode);
            if (!empty($_SESSION[$this->config->appname]['recommendMessage'])) {
                if ($_SESSION[$this->config->appname]['recommendMessage'] == 'error') {
                    $data['msg'] = array('type' => 'error', 'msg' => $this->trans['recommendation_error']);
                } else {
                    $data['msg'] = array(
                        'msg' => str_replace('PORTLETNAME', $_SESSION[$this->config->appname]['recommendMessage'], $this->trans['recommendation_success']),
                        'type' => 'success',
                    );
                }
                unset($_SESSION[$this->config->appname]['recommendMessage']);
            }

            $sql = 'SELECT id AS arraykey, id, label FROM user_tabs
                WHERE is_removed = 0 AND user_id = :user_id ORDER BY pos';
            $stmt = $this->db->prepare($sql);
            $sqlparams = array('user_id' => $this->getUserId());
            $stmt->execute($sqlparams);
            $tabs = $stmt->fetchAll(PDO::FETCH_ASSOC|PDO::FETCH_UNIQUE);
            if (empty($tabs)) {
                $tabs = api_helpers_portletHelper::writeDefaultTabs($this->db, $this->getUserId(), $this->config->crud['mandatoryColumns'], $this->user);
            }
            if (empty($tabs)) {
                $msg = 'No Tabs found';
                throw new api_exception(api_exception::THROW_NONE, array(), api_command_portlet::USER_TAB_MISSING, $msg);
            }

            foreach ($tabs as $key => $tab) {
                $tab['content'] = '';
                $tabs[$key]['content'] = $this->renderTemplate('tabContainer', $tab, 'html');;
            }
            $data['tabs'] = $tabs;

            if (!empty($_SESSION[$this->config->appname]['lastActiveTab']) && $data['mode'] === false
                && isset($data['tabs'][(int)$_SESSION[$this->config->appname]['lastActiveTab']])
            ) {
                $data['activeTabID'] = (int)$_SESSION[$this->config->appname]['lastActiveTab'];
            } else {
                $keys = array_keys($data['tabs']);
                $data['activeTabID'] = (int)$keys[0];
            }
            $this->data = $this->renderTemplate('viewport', $data, 'html');
        } catch (Exception $e) {
            api_log::log(api_log::ERR, $e->getMessage());
            $this->data = $this->trans['error'];
        }

        return true;
    }

    /**
     * Generate the HTML interface, after switching admin mode
     *
     * @return bool
     */
    public function admin() {
        $collection = $this->request->getParam('collection', false);
        if ($collection) {
            return $this->index($collection);
        }
        return $this->index('admin');
    }

    /**
     * Adds a new collection as first tab, used for links
     *
     * @return bool
     */
    public function collectionLink() {
        $collection = $this->request->getParam('collection', false);
        try {
            if (empty($collection)) {
                throw new api_exception(api_exception::THROW_NONE, array(), api_command_portlet::WRONG_EXTENSION, 'No collection name passed');
            }
            list($tabID, $label) = $this->copyCollection(array('type' => $collection), true);
            $this->storeActiveTab($tabID);
            $host = $this->config->public_url[strtolower($this->lang)];
            $response = api_response::getInstance();
            $response->redirect($host);
        } catch (api_exception $e) {
            api_log::log(api_log::ERR, $e->getMessage());
            echo $e->getMessage();
            $this->route['view']['ignore'] = true;
        }
    }

    /**
     * Render the the portlets contained in a tab
     *
     * @return array HTML for the tab and portlets as well as the configuration of the tab/portlets
     */
    public function renderTab($id = null) {
        try {
            if ($this->extension !== 'json' && $this->extension !== 'html') {
                $msg = 'renderTab is only callable with the JSON or HTML view';
                throw new api_exception(api_exception::THROW_NONE, array(), api_command_portlet::WRONG_EXTENSION, $msg);
            }

            $id = (int)$this->request->getParam('id');
            $template = $this->renderTabInternal($id);
            $data = array('status' => 'success', 'data' => $template);

        } catch (api_exception $e) {
            $data = array('status' => 'error', 'data' => $this->trans['error']);
            api_log::log(api_log::ERR, $e->getMessage());
        } catch (Exception $e) {
            $data = array('status' => 'error', 'data' => $e->getMessage());
        }

        $this->data = $data;
        return true;
    }

    protected function renderTabInternal($id) {
        $sql = 'SELECT t.id, t.label, c.proportion FROM user_tabs t
            LEFT JOIN collections c ON t.collection_id = c.id
            WHERE is_removed = 0 AND t.user_id = :user_id AND t.id = :id';
        $stmt = $this->db->prepare($sql);
        $sqlparams = array('id' => (int)$id, 'user_id' => $this->getUserId());
        $stmt->execute($sqlparams);
        $tab = $stmt->fetch(PDO::FETCH_ASSOC);

        if (empty($tab)) {
            $msg = 'Tab ID does not match a tab ID for the current user: '.$id;
            throw new api_exception(api_exception::THROW_NONE, array(), api_command_portlet::TAB_MISSING, $msg);
        }

        $sql = 'SELECT utp.tab_id, p.sandbox, utp.id, utp.col, utp.is_open, utp.config, utp.type, utp.subtype, utp.theme,
            COALESCE(utp.label, pt.label) AS label, p.url, p.is_config_open, p.auto_refresh, p.is_label_editable, p.is_deactivated, p.size
            FROM user_tab_portlets utp
            INNER JOIN portlets p ON (utp.type = p.type AND utp.subtype = p.subtype)
            INNER JOIN portlets_trans pt ON (p.type = pt.type AND p.subtype = pt.subtype AND pt.lang = :lang)
            WHERE utp.is_removed = 0 AND utp.pos >= 0
                AND utp.tab_id = :tab_id
            ORDER BY utp.col, utp.pos';
        $stmt = $this->db->prepare($sql);
        $stmt->execute(array('lang' => $this->lang, 'tab_id' => (int)$tab['id']));
        $portlets = $stmt->fetchAll(PDO::FETCH_ASSOC);

        foreach ($portlets as $key => $portlet) {
            $portlets[$key]['config'] = (array)@unserialize($portlet['config']);
        }

        $data = array(
            'tab' => $tab,
            'portlets' => array(),
        );

        if (!empty($portlets)) {
            $params = array(
                'method' => 'index',
            );

            foreach ($portlets as $portlet) {
                try {
                    $portlet_data = $this->proxyCall($params, $portlet, $portlet['sandbox']);
                } catch (api_exception $e) {
                    $portlet_data['data'] = $this->trans['error'];
                    api_log::log(api_log::ERR, $e->getMessage());
                } catch (Exception $e) {
                    $portlet_data['data'] = $e->getMessage();
                }
                $portlet['portlet'] = $portlet_data['data'];
                $data['portlets'][] = $portlet;
            }
        }

        return $this->renderTemplate('tab', $data, 'html');
    }

    /**
     * Get the basic translations
     *
     * @return bool
     */
    public function getTrans() {
        try {
            if ($this->extension !== 'json' && $this->extension !== 'html') {
                $msg = 'Translations are only callable with the JSON or HTML view';
                throw new api_exception(api_exception::THROW_NONE, array(), api_command_portlet::WRONG_EXTENSION, $msg);
            }
            $data = array('status' => 'success', 'data' => $this->trans);
        } catch (api_exception $e) {
            api_log::log(api_log::ERR, $e->getMessage());
            $data = array(
                'status' => 'error',
                'data'   => $this->trans['error'],
                'code'   => $e->getCode(),
            );
        } catch (Exception $e) {
            $data = array(
                'status' => 'error',
                'data'   => $e->getMessage(),
                'code'   => $e->getCode(),
            );
        }
        $this->data = $data;
        return true;
    }

    /**
     * Execute a method call against a portlet
     *
     * @return bool
     */
    public function call() {
        try {
            if ($this->extension !== 'json' && $this->extension !== 'html') {
                $msg = 'Methods are only callable with the JSON or HTML view';
                throw new api_exception(api_exception::THROW_NONE, array(), api_command_portlet::WRONG_EXTENSION, $msg);
            }

            $portlet = $this->request->getParam('portlet');
            if (isset($portlet)) {
                $id = $this->request->getParam('id');
                if (empty($id)) {
                    $msg = 'Portlet ID missing';
                    throw new api_exception(api_exception::THROW_NONE, array(), api_command_portlet::USER_PORTLET_ID_MISSING, $msg);
                }

                $sql = 'SELECT utp.tab_id, utp.id, utp.col, utp.config, utp.theme, COALESCE(utp.label, pt.label) AS label,
                    utp.type, utp.subtype, p.url, p.is_config_open, p.auto_refresh, p.is_label_editable, p.is_deactivated, p.size
                    FROM user_tab_portlets utp
                    INNER JOIN portlets p ON (utp.type = p.type AND utp.subtype = p.subtype)
                    INNER JOIN portlets_trans pt ON (p.type = pt.type AND p.subtype = pt.subtype AND pt.lang = :lang)
                    INNER JOIN user_tabs ut ON (utp.tab_id = ut.id)
                    WHERE utp.is_removed = 0 AND ut.is_removed = 0
                        AND ut.user_id = :user_id AND utp.id = :id
                    ORDER BY utp.col, utp.pos';
                $stmt = $this->db->prepare($sql);
                $sqlparams = array('lang' => $this->lang, 'user_id' => $this->getUserId(), 'id' => (int)$id);
                $stmt->execute($sqlparams);
                $portlet = $stmt->fetch(PDO::FETCH_ASSOC);
                if (empty($portlet)) {
                    $msg = '<p class="errorblock">'.$this->trans['PORTLET_SETTINGS_NOT_FOUND'].'</p>';
                    throw new Exception($msg, api_command_portlet::PORTLET_SETTINGS_NOT_FOUND);
                }
                $portlet['config'] = (array)@unserialize($portlet['config']);
            }

            $data = $this->proxyCall((array)$this->request->getParameters(), $portlet);
            $data['status'] = 'success';
        } catch (api_exception $e) {
            api_log::log(api_log::ERR, $e->getMessage());
            $data = array(
                'status' => 'error',
                'data'   => $this->trans['error'],
                'code'   => $e->getCode(),
            );
        } catch (Exception $e) {
            $data = array(
                'status' => 'error',
                'data'   => $e->getMessage(),
                'code'   => $e->getCode(),
            );
        }

        if (!empty($data['data']['doNotRender']) && $data['data']['doNotRender'] === true) {
            unset($data['data']['doNotRender']);
            $data = $data['data'];
        } else if ($this->extension == 'html') {
            $tmp = $data['data'];
            $data = $this->getIndexData(false);
            $data['data'] = $tmp;
            $data['openPrintWindow'] = $this->request->getParam('openPrintWindow', false);
            $data = $this->renderTemplate('portletpopup', $data, 'html');
        }
        $this->data = $data;
        return true;
    }

    public function storeActiveTab($id = false) {
        if (!$id) {
            $id = $this->request->getParam('id');
        }
        if (empty($id)) {
            return false;
        }
        $_SESSION[$this->config->appname]['lastActiveTab'] = $id;
        return true;
    }

    /**
     * Create an instance of a portlet class
     *
     * @param string portlet type
     *
     * @return object portlet instance
     */
    protected function createPortletInstance($type) {
        $portlet_name = 'portlets_'.$type.'_core';
        if (!class_exists($portlet_name)) {
            $msg = $this->trans['PORTLET_MISSING'].' '.$type;
            throw new Exception($msg, api_command_portlet::PORTLET_MISSING);
        }

        return new $portlet_name($this->user, $this->db, $this->env);
    }

    /**
     * Proxy a call to a portlet or alternatively to a portlet method in this class
     *
     * @param array
     * @param array
     * @return mixed
     */
    protected function proxyCall($params, $portlet = null, $sandbox = false) {
        if (isset($portlet['type'])) {
            $obj = $this->createPortletInstance($portlet['type']);
        } else {
            $obj = $this;
        }

        if (!isset($params['method'])) {
            $msg = 'Portlet method missing';
            throw new api_exception(api_exception::THROW_NONE, array(), api_command_portlet::PORTLET_METHOD_MISSING, $msg);
        }

        $method_name = api_command_portlet::METHOD_PREFIX.ucfirst($params['method']);
        if (!method_exists($obj, $method_name)) {
            $msg = 'Portlet method does not exist: '.get_class($obj).'::'.$method_name;
            throw new api_exception(api_exception::THROW_NONE, array(), api_command_portlet::PORTLET_METHOD_MISSING, $msg);
        }

        if (is_array($portlet)) {
            $data = $portlet;
            if (empty($portlet['is_deactivated'])) {
                if ($sandbox) {
                    $data['data'] = array('body' => $this->renderTemplate('sandbox', $data, 'html'));
                } else {
                    $data['data'] = $obj->$method_name($params, $portlet);
                }
            } else {
                if ($params['method'] === 'index') {
                    $data['auto_refresh'] = $this->config->deactivatedRefreshRate;
                    $data['data'] = array('body' => $this->renderTemplate('deactivated', $data, 'html'));
                } else {
                    $msg = $this->renderTemplate('deactivated', $data, 'html');
                    throw new Exception($msg, api_command_portlet::PORTLET_DEACTIVATED);
                }
            }
            switch ($params['method']) {
                case 'index':
                    if (isset($params['addPortlet'])) {
                        $data['addPortlet'] = true;
                    }
                    if (!empty($params['contentOnlyCall'])) {
                        $this->renderPortletContent($data);
                    } else {
                        $data = $this->renderPortlet($data);
                    }
                    break;
                case 'saveConfig':
                    if (isset($data['data']['theme'])) {
                        $portlet['theme'] = $data['theme'] = $data['data']['theme'];
                        unset($data['data']['theme']);
                    }
                    $this->saveConfig($data);
                    $params = array('method' => 'index', 'contentOnlyCall' => !empty($params['contentOnlyCall']));
                    $portlet['config'] = $data['data'];
                    $data = $this->proxyCall($params, $portlet);
                    break;
                default:
                    if (!empty($data['data']['doNotRender'])) {
                        return $data;
                    }
                    if ($this->extension == 'json') {
                        $data = $this->renderPortletContent($data);
                    } else {
                        $data['data'] = $data['data']['body'];
                    }
                    break;
            }
        } else {
            $data['data'] = $obj->$method_name($params);
        }

        return $data;
    }

    /**
     * Render portlet
     *
     * @param array $data
     * @param array $portlet
     *
     * @return array HTML for the given portlet as well as the portlet configuration
     */
    protected function renderPortlet($data) {
        $data = $this->renderPortletContent($data);
        $data['themes'] = $this->config->themes;
        $data['data'] = $this->renderTemplate('widget', $data, 'html');
        return $data;
    }

    /**
     * Render portlet content
     *
     * @param array $data
     * @param array $portlet
     *
     * @return array HTML for the given portlet content area as well as the portlet configuration
     */
    protected function renderPortletContent($data) {
        if (!isset($data['data']['config'])) {
            $data['data']['config'] = '';
        }
        $jsPath = '../localinc/portlets/'.$data['type'].'/www/js/'.$data['type'].'.js';
        if (file_exists($jsPath)) {
            $data['jsPath'] = $jsPath;
        }
        $data['themes'] = $this->config->themes;
        $data['data'] = $this->renderTemplate('content', $data, 'html');
        return $data;
    }

    /**
     * Save config for a portlet
     *
     * @param array $data
     * @param array $portlet
     *
     * @return bool
     */
    protected function saveConfig($data) {
        if (!isset($data['id']) || !isset($data['data']) || !isset($data['theme'])) {
            $msg = 'Parameter "id", "theme" and/or "config" missing';
            throw new api_exception(api_exception::THROW_NONE, array(), api_command_portlet::PORTLET_PARAM_MISSING, $msg);
        }

        $config_id = '';
        if (isset($data['data']['__config_id__'])) {
            $config_id = (string)$data['data']['__config_id__'];
            unset($data['data']['__config_id__']);
        }

        $sql = 'SELECT utp.id
                FROM user_tabs ut
                    INNER JOIN user_tab_portlets utp ON (ut.id = utp.tab_id)
                WHERE ut.user_id = :user_id AND utp.id = :id';
        $stmt = $this->db->prepare($sql);
        $sqlparams = array(
            'user_id' => $this->getUserId(),
            'id' => (int)$data['id']
        );
        $stmt->execute($sqlparams);
        $id = $stmt->fetch(PDO::FETCH_ASSOC);
        if (empty($id)) {
            return false;
        }

        $sql = 'UPDATE user_tab_portlets SET config_id = :config_id, config = :config, theme = :theme
            WHERE id = :id';
        $stmt = $this->db->prepare($sql);
        $sqlparams = array(
            'config_id' => $config_id,
            'config' => serialize($data['data']),
            'theme' => $data['theme'],
            'id' => (int)$data['id']
        );
        $stmt->execute($sqlparams);
        return (bool)$stmt->rowCount();
    }

    /**
     * Prune anything marked as to be removed for the current
     *
     * @return bool
     */
    protected function pruneUserSetup() {
        $sql = 'SELECT DISTINCT ut.id
            FROM user_tabs ut
                LEFT JOIN user_tab_portlets utp ON (utp.tab_id = ut.id)
            WHERE ut.user_id = :user_id
                AND (utp.is_removed = 1 OR ut.is_removed = 1)';

        $stmt = $this->db->prepare($sql);
        $sqlparams = array('user_id' => $this->getUserId());
        $stmt->execute($sqlparams);
        $tabs = $stmt->fetchAll(PDO::FETCH_COLUMN);

        if (!empty($tabs)) {
            $sql = 'DELETE utp FROM user_tab_portlets utp, user_tabs ut
                WHERE utp.tab_id = ut.id AND ut.user_id = :user_id
                    AND (utp.is_removed = 1 OR ut.is_removed = 1)';
            $stmt = $this->db->prepare($sql);
            $sqlparams = array('user_id' => $this->getUserId());
            $stmt->execute($sqlparams);

            if ($stmt->rowCount() > 0) {
                foreach ($tabs as $tab) {
                    $sql = 'SET @poscolleft := -1, @poscolmiddle := -1, @poscolright := -1';
                    $this->db->exec($sql);

                    $sql = 'UPDATE user_tab_portlets SET pos =
                        (
                        CASE
                            WHEN col = :colleft THEN (@poscolleft := @poscolleft + 1)
                            WHEN col = :colmiddle THEN (@poscolmiddle := @poscolmiddle + 1)
                            ELSE (@poscolright := @poscolright + 1) END
                        )
                        WHERE tab_id = :tab_id ORDER BY pos';
                    $stmt = $this->db->prepare($sql);
                    $sqlparams = array('tab_id' => $tab, 'colleft' => 'left', 'colmiddle' => 'middle');
                    $stmt->execute($sqlparams);
                }
            }

            $sql = 'DELETE FROM user_tabs WHERE is_removed = 1 AND user_id = :user_id';
            $stmt = $this->db->prepare($sql);
            $sqlparams = array('user_id' => $this->getUserId());
            $stmt->execute($sqlparams);

            if ($stmt->rowCount() > 0) {
                $sql = 'SET @pos := -1';
                $this->db->exec($sql);

                $sql = 'UPDATE user_tabs SET pos = (@pos := @pos + 1)
                    WHERE user_id = :user_id ORDER BY pos';
                $stmt = $this->db->prepare($sql);
                $sqlparams = array('user_id' => $this->getUserId());
                $stmt->execute($sqlparams);
            }
        }

        return true;
    }

    /**
     * Prune anything that has been marked as removed
     *
     * @param array $params array
     *
     * @return bool
     */
    protected function executePrune($params) {
        $this->pruneUserSetup();

        return true;
    }

    /**
     * Add a tab at the end of the tab list
     *
     * @param array $params array
     *
     * @return array meta data and HTML for new tab
     */
    protected function executeAddTab($params) {
        if (!isset($params['label'])) {
            $msg = 'Parameter "label" missing';
            throw new api_exception(api_exception::THROW_NONE, array(), api_command_portlet::PORTLET_PARAM_MISSING, $msg);
        }

        $sql = 'INSERT INTO user_tabs (user_id, label, pos)
            (SELECT :user_id, :label, COALESCE(MAX(pos)+1, 0)
                FROM user_tabs
                WHERE user_id = :user_id GROUP BY user_id
            )';
        $stmt = $this->db->prepare($sql);
        $sqlparams = array('user_id' => $this->getUserId(), 'label' => $params['label']);
        $stmt->execute($sqlparams);

        $id = $this->db->lastInsertID('user_tabs');

        $tab = array(
            'id' => (int)$id,
            'label' => $params['label'],
        );

        $data = array(
            'tab' => $tab,
            'portlets' => array(),
        );

        $tab['content'] = $this->renderTemplate('tab', $data, 'html');
        $data['data'] = $this->renderTemplate('tabContainer', $tab, 'html');
        return $data;
    }

    protected function executeAddCollection($params, $existingTabID = false) {
        list($tabID, $label) = $this->copyCollection($params, false, $existingTabID);
        $this->storeActiveTab($tabID);
        $tab = array(
            'id' => (int)$tabID,
            'label' => $label,
        );

        $tab['content'] = $this->renderTabInternal($tabID);
        $data = $this->renderTemplate('tabContainer', $tab, 'html');

        return $data;
    }

    protected function executeResetCollection($params) {
        $sql = "DELETE FROM user_tab_portlets WHERE tab_id = :tab_id";
        $stmt = $this->db->prepare($sql);
        $stmt->execute(array('tab_id' => $params['tab_id']));

        return $this->executeAddCollection($params, $params['tab_id']);
    }

    protected function copyCollection($params, $asFirstTab = false, $existingTabID = false) {
        if (!isset($params['type'])) {
            $msg = 'Collection parameter "type" missing';
            throw new api_exception(api_exception::THROW_NONE, array(), api_command_portlet::PORTLET_PARAM_MISSING, $msg);
        }

        $query = $this->db->select();
        $query->from(array('c' => 'collections'), array('t.id AS tab_id', 'ct.label', 'c.id AS collection_id'))
            ->join(array('t' => 'user_tabs'), 'c.default_user_id = t.user_id')
            ->join(array('ct' => 'collections_trans'), 'c.id = ct.collection_id')
            ->where("c.type = :type AND ct.lang = :lang")
            ->limit(1);
        $sql = $query->__toString();

        $stmt = $this->db->prepare($sql);

        $sqlParams = array(
            'lang' => $this->lang,
            'type' => $params['type'],
        );

        $stmt->execute($sqlParams);
        $tab = $stmt->fetch(PDO::FETCH_ASSOC);
        if (empty($tab)) {
            throw new api_exception(api_exception::THROW_NONE, array(), api_command_portlet::PORTLET_PARAM_MISSING, 'Collection not found in the database');
        }

        $foo = $this->getUsersTabs(); // This checks if the user already has tabs and if not he creates everything necessary

        if (!$existingTabID) {
            if ($asFirstTab) {
                $sql = "UPDATE user_tabs SET pos = pos+1 WHERE user_id = :user_id";
                $stmt = $this->db->prepare($sql);
                $stmt->execute(array('user_id' => $this->getUserId()));
            }
            $sql = 'INSERT INTO user_tabs (user_id, label, pos, collection_id)';
            if ($asFirstTab) {
                $sql .= ' VALUES (:user_id, :label, 0, :collection_id)';
            } else {
                $sql .= ' (SELECT :user_id, :label, COALESCE(MAX(pos)+1, 0), :collection_id
                    FROM user_tabs
                    WHERE user_id = :user_id GROUP BY user_id
                )';
            }

            $stmt = $this->db->prepare($sql);
            $sqlparams = array('user_id' => $this->getUserId(), 'label' => $tab['label'], 'collection_id' => $tab['collection_id']);
            $stmt->execute($sqlparams);

            $newTabID = $this->db->lastInsertID('user_tabs');
        } else {
            $newTabID = $existingTabID;
        }

        $sql = "INSERT INTO user_tab_portlets (tab_id, type, subtype, pos, col, label, is_open, config, theme, is_removed, config_id)
                (SELECT :tab_id, type, subtype, pos, col, label, is_open, config, theme, is_removed, config_id FROM user_tab_portlets WHERE tab_id = :defaultTabID)";

        $stmt = $this->db->prepare($sql);
        $stmt->execute(array('tab_id' => $newTabID, 'defaultTabID' => $tab['tab_id']));

        return array($newTabID, $tab['label']);
    }

    /**
     * Marked a tab as to be removed
     *
     * @param array $params array
     *
     * @return bool
     */
    protected function executeRemoveTab($params) {
        $this->pruneUserSetup();

        if (!isset($params['id'])) {
            $msg = 'Parameter "id" missing';
            throw new api_exception(api_exception::THROW_NONE, array(), api_command_portlet::PORTLET_PARAM_MISSING, $msg);
        }

        // mark as removed
        $sql = 'UPDATE user_tabs SET is_removed = 1 WHERE user_id = :user_id AND id = :id';
        $stmt = $this->db->prepare($sql);
        $sqlparams = array('user_id' => $this->getUserId(), 'id' => (int)$params['id']);
        $stmt->execute($sqlparams);
        return (bool)$stmt->rowCount();
    }

    /**
     * Restore a tab that was marked to be removed
     *
     * @param array $params array
     *
     * @return bool
     */
    protected function executeRestoreTab($params) {
        if (!isset($params['id'])) {
            $msg = 'Parameter "id" missing';
            throw new api_exception(api_exception::THROW_NONE, array(), api_command_portlet::PORTLET_PARAM_MISSING, $msg);
        }

        // remove mark as removed
        $sql = 'UPDATE user_tabs SET is_removed = 0 WHERE user_id = :user_id AND id = :id';
        $stmt = $this->db->prepare($sql);
        $sqlparams = array('user_id' => $this->getUserId(), 'id' => (int)$params['id']);
        $stmt->execute($sqlparams);
        return (bool)$stmt->rowCount();
    }

    /**
     * Rename the tab
     *
     * @param array $params array
     *
     * @return bool
     */
    protected function executeRenameTab($params) {
        if (!isset($params['id']) || !isset($params['label']) || $params['label'] === '') {
            $msg = 'Parameter "id" and/or "label" missing';
            throw new api_exception(api_exception::THROW_NONE, array(), 0,$msg, api_command_portlet::PORTLET_PARAM_MISSING);
        }

        $sql = 'UPDATE user_tabs SET label = :label WHERE user_id = :user_id AND id = :id';
        $stmt = $this->db->prepare($sql);
        $sqlparams = array(
            'label' => $params['label'],
            'user_id' => $this->getUserId(),
            'id' => (int)$params['id']
        );
        $stmt->execute($sqlparams);
        return (bool)$stmt->rowCount();
    }

    /**
     * Move tab to another pos in the tab list
     *
     * @param array $params array
     *
     * @return bool
     */
    protected function executeMoveTab($params) {
        if (!isset($params['id']) || !isset($params['pos'])) {
            $msg = 'Parameter "id" and/or "pos" missing';
            throw new api_exception(api_exception::THROW_NONE, array(), api_command_portlet::PORTLET_PARAM_MISSING, $msg);
        }

        $id = intval(substr($params['id'], 3)); //Cut the tab from the string

        $sql = 'SELECT id, pos FROM user_tabs WHERE user_id = :user_id ORDER BY pos';
        $stmt = $this->db->prepare($sql);
        $sqlparams = array('user_id' => $this->getUserId());
        $stmt->execute($sqlparams);
        $tabs = $stmt->fetchAll(PDO::FETCH_UNIQUE|PDO::FETCH_COLUMN);
        if (current($tabs) !== 0) {
            // Fix wrong ordering
            $parts = array();
            $i=0;
            $sql = '';
            foreach ($tabs as $tabID => $tab) {
                $sql .= " UPDATE user_tabs SET pos = {$i} WHERE id = {$tabID};";
                $i++;
            }
            $this->db->exec($sql);
        }

        if (!isset($tabs[$id])) {
            $msg = 'Portlet "id" does not exist';
            throw new api_exception(api_exception::THROW_NONE, array(), api_command_portlet::USER_TAB_MISSING, $msg);
        }
        $pos = $tabs[$id];

        if ($pos !== $params['pos']) {
            if ($pos > $params['pos']) {
                // move tab to the left
                $sql = 'UPDATE user_tabs
                    SET pos = CASE WHEN id = :id THEN :newpos ELSE (pos + 1) END
                    WHERE user_id = :user_id AND pos BETWEEN :newpos AND :oldpos';
                $stmt = $this->db->prepare($sql);
                $sqlparams = array(
                    'id' => $id,
                    'newpos' => $params['pos'],
                    'user_id' => $this->getUserId(),
                    'oldpos' => $pos
                );
                $stmt->execute($sqlparams);
            } else {
                // move tab to the right
                $sql = 'UPDATE user_tabs
                    SET pos = CASE WHEN id = :id THEN :newpos ELSE (pos - 1) END
                    WHERE user_id = :user_id AND pos BETWEEN :oldpos AND :newpos';
                $stmt = $this->db->prepare($sql);
                $sqlparams = array(
                    'id' => $id,
                    'newpos' => $params['pos'],
                    'user_id' => $this->getUserId(),
                    'oldpos' => $pos
                );
                $stmt->execute($sqlparams);
            }
        }

        return true;
    }

    protected function getUsersTabs() {
        $query = $this->db->select();
        $query->from('user_tabs', array('id', 'label'));
        $query->where('user_id = :user_id AND is_removed = 0');
        $query->order('pos');
        $query->limit(1);
        $sql = $query->__toString();
        $stmt = $this->db->prepare($sql);
        $stmt->execute(array('user_id' => $this->getUserId()));
        $tabs = $stmt->fetchAll(PDO::FETCH_COLUMN|PDO::FETCH_UNIQUE);
        if (empty($tabs)) {
            $tabs = api_helpers_portletHelper::writeDefaultTabs($this->db, $this->getUserId(), $this->user,$this->config->crud['mandatoryColumns']);
        }
        return $tabs;
    }

    /**
     * Add a portlet to a tab
     *
     * @param array $params array, elements type and subtype are mandatory, tab_id is optional
     *
     * @return array meta data and HTML for new portlet
     */
    protected function executeAddPortlet($params, $render = true) {
        if (!isset($params['type']) || !isset($params['subtype'])) {
            $msg = 'Parameter "type" and/or "subtype" missing';
            throw new api_exception(api_exception::THROW_NONE, array(), api_command_portlet::PORTLET_PARAM_MISSING, $msg);
        }

        // get first tab id of user or create default tabs and get first id if none given
        // this mainly happens if a user tries adding a portlet from an external link without having visited before
        if (!isset($params['tab_id'])) {
            $tabs = $this->getUsersTabs();
            $tabIds = array_keys($tabs);
            $params['tab_id'] = reset($tabIds);
        }

        if (empty($params['tab_id'])) {
            $msg = 'Parameter "tab_id" missing' . $params['tab_id'];
            throw new api_exception(api_exception::THROW_NONE, array(), api_command_portlet::PORTLET_PARAM_MISSING, $msg);
        }

        // from this point on we're sure the user has tabs and we have his best tab_id to add the new portlet
        if (empty($params['portlet'])) {
            $sql = 'SELECT p.type, p.subtype, COALESCE(p.config_id, p.subtype) AS config_id, pt.label,
                    p.url, p.is_config_open, p.auto_refresh, p.is_label_editable, p.is_deactivated, p.size
                FROM portlets p
                    INNER JOIN portlets_trans pt ON (p.type = pt.type AND p.subtype = pt.subtype AND pt.lang = :lang)
                WHERE p.type = :type AND p.subtype = :subtype';
            $stmt = $this->db->prepare($sql);
            $sqlparams = array('lang' => $this->lang, 'type' => $params['type'], 'subtype' => $params['subtype']);
            $stmt->execute($sqlparams);
            $portlet = $stmt->fetch(PDO::FETCH_ASSOC);

            if (empty($portlet)) {
                $msg = 'Portlet type/subtype combination does not exist';
                throw new api_exception(api_exception::THROW_NONE, array(), api_command_portlet::USER_PORTLET_MISSING, $msg);
            }

            $obj = $this->createPortletInstance($portlet['type']);
            $portlet['config'] = $obj->getDefaultConfig($portlet['subtype']);
            $portlet['config_id'] = $obj->getConfigId($portlet);
            $portlet['theme'] = 'Grey';
        } else {
            $portlet = $params['portlet'];
        }

        $portlet['is_open'] = true;
        $portlet['tab_id'] = $params['tab_id'];
        $portlet['col'] = 'left';

        $sql = 'INSERT INTO user_tab_portlets (tab_id, type, subtype, config_id, pos, col, is_open, config, theme)
            (SELECT :tab_id, :type, :subtype, :config_id, COALESCE(MIN(utp.pos)-1, -1), :col, :is_open, :config, :theme
                FROM user_tabs ut
                    LEFT JOIN user_tab_portlets utp ON (ut.id = utp.tab_id AND utp.col = :col)
                WHERE ut.id = :tab_id
                GROUP BY utp.tab_id, utp.col)';
        $stmt = $this->db->prepare($sql);

        $sqlparams = array(
            'type' => $params['type'],
            'subtype' => $params['subtype'],
            'config_id' => $portlet['config_id'],
            'config' => serialize($portlet['config']),
            'theme' => $portlet['theme'],
            'tab_id' => (int)$portlet['tab_id'],
            'col' => $portlet['col'],
            'is_open' => $portlet['is_open'],
        );

        $stmt->execute($sqlparams);

        $portlet['id'] = $this->db->lastInsertID('user_tab_portlets');
        $portlet['type'] = $params['type'];
        $portlet['subtype'] = $params['subtype'];
        if (empty($portlet['id'])) {
            $msg = $this->trans['USER_TAB_MISSING']; //'Tab "id" does not exist';
            throw new Exception($msg, api_command_portlet::USER_TAB_MISSING);
        }

        // move things to 0 as the start again
        $sql = 'UPDATE user_tab_portlets SET pos = pos + 1 WHERE tab_id = :tab_id AND col = :col';
        $stmt = $this->db->prepare($sql);
        $sqlparams = array('tab_id' => (int)$params['tab_id'], 'col' => 'left');
        $stmt->execute($sqlparams);

        if ($render) {
            $params = array(
                'method' => 'index',
                'addPortlet' => true
            );
            return $this->proxyCall($params, $portlet);
        }

        return $portlet;
    }

    /**
     * Marked a portlet as to be removed
     *
     * @param array $params array
     *
     * @return bool
     */
    protected function executeRemovePortlet($params) {
        $this->pruneUserSetup();

        if (!isset($params['id'])) {
            $msg = 'Parameter "id" missing';
            throw new api_exception(api_exception::THROW_NONE, array(), api_command_portlet::PORTLET_PARAM_MISSING, $msg);
        }

        // mark as removed
        $sql = 'UPDATE user_tab_portlets
                SET is_removed = 1 WHERE id = :id';
        $stmt = $this->db->prepare($sql);
        $sqlparams = array('id' => (int)$params['id']);
        $stmt->execute($sqlparams);
        return (bool)$stmt->rowCount();
    }

    /**
     * Restore a portlet that was marked to be removed
     *
     * @param array $params array
     *
     * @return bool
     */
    protected function executeRestorePortlet($params) {
        if (!isset($params['id'])) {
            $msg = 'Parameter "id" missing';
            throw new api_exception(api_exception::THROW_NONE, array(), api_command_portlet::PORTLET_PARAM_MISSING, $msg);
        }

        // remove mark as removed
        $sql = 'UPDATE user_tab_portlets
                SET is_removed = 0 WHERE id = :id';
        $stmt = $this->db->prepare($sql);
        $sqlparams = array('id' => (int)$params['id']);
        $stmt->execute($sqlparams);
        return (bool)$stmt->rowCount();
    }

    /**
     * Rename a portlet
     *
     * @param array $params array
     *
     * @return bool
     */
    protected function executeRenamePortlet($params) {
        if (!isset($params['id']) || !isset($params['label']) || $params['label'] === '') {
            $msg = 'Parameter "id" and/or "label" missing';
            throw new api_exception(api_exception::THROW_NONE, array(), api_command_portlet::PORTLET_PARAM_MISSING, $msg);
        }

        $sql = 'SELECT utp.id
            FROM user_tabs ut
                INNER JOIN user_tab_portlets utp ON (ut.id = utp.tab_id)
            WHERE ut.user_id = :user_id AND utp.id = :id';
        $stmt = $this->db->prepare($sql);
        $sqlparams = array(
            'user_id' => $this->getUserId(),
            'id' => (int)$params['id']
        );
        $stmt->execute($sqlparams);
        $id = $stmt->fetch(PDO::FETCH_ASSOC);
        if (empty($id)) {
            return false;
        }

        $sql = 'UPDATE user_tab_portlets SET label = :label
            WHERE id = :id';
        $stmt = $this->db->prepare($sql);
        $sqlparams = array(
            'label' => $params['label'],
            'id' => (int)$params['id']
        );
        $stmt->execute($sqlparams);
        return (bool)$stmt->rowCount();
    }

    /**
     * Save if portlet is closed or open
     *
     * @param array $params array
     *
     * @return bool
     */
    protected function executeSavePortletState($params) {
        if (!isset($params['id']) || !isset($params['is_open'])) {
            $msg = 'Parameter "id" and/or "is_open" missing';
            throw new api_exception(api_exception::THROW_NONE, array(), api_command_portlet::PORTLET_PARAM_MISSING, $msg);
        }

        $sql = 'SELECT upt.tab_id, upt.is_open FROM user_tab_portlets upt
            INNER JOIN user_tabs ut ON (upt.tab_id = ut.id) WHERE upt.id = :id';
        $stmt = $this->db->prepare($sql);
        $sqlparams = array('id' => (int)$params['id']);
        $stmt->execute($sqlparams);
        $portlet = $stmt->fetch(PDO::FETCH_ASSOC);

        if (empty($portlet)) {
            $msg = $this->trans['PORTLET_MISSING'];
            throw new Exception($msg, api_command_portlet::USER_PORTLET_MISSING);
        }

        if ($params['is_open'] != $portlet['is_open']) {
            $sql = 'UPDATE user_tab_portlets SET is_open = :is_open WHERE id = :id';
            $stmt = $this->db->prepare($sql);
            $sqlparams = array('is_open' => (bool)$params['is_open'], 'id' =>(int)$params['id']);
            $stmt->execute($sqlparams);
        } else {
            $msg = 'Portlet is already in the requested state';
            throw new api_exception(api_exception::THROW_NONE, array(), api_command_portlet::ALREADY_EXISTS, $msg);
        }

        return true;
    }

    /**
     * Move the portlet to another the tab
     *
     * @param array $params array
     *
     * @return bool
     */
    protected function executeMovePortletToTab($params) {
        if (!isset($params['id']) || !isset($params['tab_id'])) {
            $msg = 'Parameter "id" and/or missing';
            throw new api_exception(api_exception::THROW_NONE, array(), api_command_portlet::PORTLET_PARAM_MISSING, $msg);
        }

        $sql = 'SELECT upt.tab_id, upt.col, upt.pos FROM user_tab_portlets upt
            INNER JOIN user_tabs ut ON (upt.tab_id = ut.id) WHERE upt.id = :id';
        $stmt = $this->db->prepare($sql);
        $sqlparams = array('id' => (int)$params['id']);
        $stmt->execute($sqlparams);
        $portlet = $stmt->fetch(PDO::FETCH_ASSOC);

        if (empty($portlet)) {
            $msg = $this->trans['PORTLET_MISSING'];
            throw new Exception($msg, api_command_portlet::USER_PORTLET_MISSING);
        }

        if ($portlet['tab_id'] === $params['tab_id']) {
            $msg = 'Portlet already exists in target tab';
            throw new api_exception(api_exception::THROW_NONE, array(), api_command_portlet::ALREADY_EXISTS, $msg);
        }

        $sql = 'UPDATE user_tab_portlets SET
            tab_id = (CASE WHEN id = :id THEN :newtab_id ELSE tab_id END),
            pos = (CASE WHEN id = :id THEN 0
                ELSE (CASE WHEN tab_id = :newtab_id THEN pos + 1 ELSE pos - 1 END) END),
            col = (CASE WHEN id = :id THEN :newcol ELSE col END)
            WHERE (tab_id = :oldtab_id AND pos >= :oldpos AND col = :oldcol)
                OR (tab_id = :newtab_id AND col = :newcol)';
        $stmt = $this->db->prepare($sql);
        $sqlparams = array(
            'id' => (int)$params['id'],
            'newtab_id' => (int)$params['tab_id'],
            'newcol' => 'left',
            'oldtab_id' => (int)$portlet['tab_id'],
            'oldpos' => $portlet['pos'],
            'oldcol' => $portlet['col'],
        );
        $stmt->execute($sqlparams);

        return true;
    }

    /**
     * Save pos in the tab
     *
     * @param array $params array
     *
     * @return bool
     */
    protected function executeMovePortlet($params) {
        if (!isset($params['id']) || !isset($params['pos']) || !isset($params['col'])) {
            $msg = 'Parameter "id", "pos" and/or "col" missing';
            throw new api_exception(api_exception::THROW_NONE, array(), api_command_portlet::PORTLET_PARAM_MISSING, $msg);
        }

        $sql = 'SELECT upt.tab_id, upt.col, upt.pos FROM user_tab_portlets upt
            INNER JOIN user_tabs ut ON (upt.tab_id = ut.id) WHERE upt.id = :id';
        $stmt = $this->db->prepare($sql);
        $sqlparams = array('id' => (int)$params['id']);
        $stmt->execute($sqlparams);
        $portlet = $stmt->fetch(PDO::FETCH_ASSOC);

        if (empty($portlet)) {
            $msg = $this->trans['PORTLET_MISSING'];
            throw new Exception($msg, api_command_portlet::USER_PORTLET_MISSING);
        }

        if ($params['col'] != $portlet['col']) {
            $sql = 'UPDATE user_tab_portlets SET
                pos = (CASE WHEN id = :id THEN :newpos
                    ELSE (CASE WHEN tab_id = :tab_id THEN pos + 1 ELSE pos - 1 END) END),
                col = (CASE WHEN id = :id THEN :newcol ELSE col END)
                WHERE tab_id = :tab_id AND ((pos >= :newpos AND col = :newcol)
                    OR (pos >= :oldpos AND col = :oldcol))';
            $stmt = $this->db->prepare($sql);
            $sqlparams = array(
                'id' => (int)$params['id'],
                'tab_id' => (int)$portlet['tab_id'],
                'newpos' => $params['pos'],
                'newcol' => $params['col'],
                'oldpos' => $portlet['pos'],
                'oldcol' => $portlet['col'],
            );

            $stmt->execute($sqlparams);
        } else if ($params['pos'] != $portlet['pos']) {
            if ($portlet['pos'] > $params['pos']) {
                // move portlet up
                $sql = 'UPDATE user_tab_portlets
                    SET pos = CASE WHEN id = :id THEN :pos ELSE (pos + 1) END
                    WHERE pos >= :pos AND col = :col AND tab_id = :tab_id';
                $stmt = $this->db->prepare($sql);
                $sqlparams = array(
                    'id' => (int)$params['id'],
                    'pos' => $params['pos'],
                    'col' => $params['col'],
                    'tab_id' => (int)$portlet['tab_id']
                );
                $stmt->execute($sqlparams);
            } else {
                // move portlet down
                $sql = 'UPDATE user_tab_portlets
                    SET pos = CASE WHEN id = :id THEN :newpos ELSE (pos - 1) END
                    WHERE pos BETWEEN :oldpos AND :newpos AND col = :col AND tab_id = :tab_id';
                $stmt = $this->db->prepare($sql);
                $sqlparams = array(
                    'id' => (int)$params['id'],
                    'newpos' => $params['pos'],
                    'oldpos' => $portlet['pos'],
                    'col' => $params['col'],
                    'tab_id' => (int)$portlet['tab_id']
                );
                $stmt->execute($sqlparams);
            }
        }

        return true;
    }

    /**
     * Get all available portlets
     *
     * @param array $params array
     *
     * @return array
     */
    protected function executeGetPortlets($params) {

        //Fetch Portlets
        $binds = array('lang' => $this->lang);
        $where = array();
        if (! empty($params['category'])) {
            $where[] = 'c.category_id = :cat_id';
            $binds['cat_id'] = intval($params['category']);
        }
        if (! empty($params['search'])) {
            $where[] = '(label LIKE :search OR description LIKE :search)';
            $binds['search'] = '%' . $params['search'] . '%';
        }

        $sql = 'SELECT pt.label as label, COALESCE(p.icon, \'portlet\') AS icon, p.type, p.subtype, pt.description as description
            FROM portlets p
            INNER JOIN portlets_trans pt ON (p.type = pt.type AND p.subtype = pt.subtype AND pt.lang = :lang)';
        if (! empty($params['category'])) {
            $sql .= 'LEFT JOIN categories_portlets  c ON (c.type = pt.type AND c.subtype = pt.subtype)';
        }
        $sql .= 'WHERE p.pos >= 0 AND (is_deactivated = 0 OR is_deactivated IS NULL)';
        if (! empty($where)) {
            $sql .= ' AND '. implode(' AND ', $where);
        }
        $sql .= ' ORDER BY p.pos';


        $stmt = $this->db->prepare($sql);
        $stmt->execute($binds);
        $portlets = $stmt->fetchAll(PDO::FETCH_ASSOC);

        // //Fetch collections
        $sql = "SELECT clt.label as label, COALESCE(cl.icon, 'collection') AS icon, cl.type, clt.description as description
            FROM collections cl
            INNER JOIN collections_trans clt ON (cl.id = clt.collection_id AND clt.lang = :lang)
            LEFT JOIN categories_portlets c ON (c.type = cl.type AND c.subtype = 'collection')";
        $where[] = '(cl.pos >= 0 OR cl.pos IS NULL)';
        if (! empty($where)) {
            $sql .= 'WHERE '. implode(' AND ', $where);
        }
        $sql .= ' ORDER BY cl.pos';
        $stmt = $this->db->prepare($sql);
        $stmt->execute($binds);
        $collections = $stmt->fetchAll(PDO::FETCH_ASSOC);

        //Fetch Categories and counts
        $sql = "SELECT '' as id, :all as label, count(*) as count
            FROM portlets p
            WHERE p.pos >= 0 AND (is_deactivated = 0 OR is_deactivated IS NULL)
            UNION
            SELECT c.category_id as id, MIN(ct.label) AS label, count(*) as count
            FROM categories_portlets c
            INNER JOIN categories_trans ct ON (c.category_id = ct.category_id AND ct.lang = :lang)
            RIGHT JOIN portlets p ON (c.type = p.type AND c.subtype = p.subtype)
            WHERE p.pos >= 0 AND (is_deactivated = 0 OR is_deactivated IS NULL)
            GROUP BY c.category_id";
        $stmt = $this->db->prepare($sql);
        $stmt->execute(array('lang' => $this->lang, 'all' => $this->trans['all']));

        $data = array(
            'portlets' => $portlets,
            'collections' => $collections,
            'categories' => $stmt->fetchAll(PDO::FETCH_ASSOC),
            'portletIconsPath' => $this->config->portletIconsPath,
            'themes' => $this->config->themes,
            'showTitle' => $this->config->showTitle,
        );
        return $this->renderTemplate('portletlist', $data, 'html');
    }

    protected function generateRandomCode($length) {
        $id = '';
        for ($i = 0; $i < $length; ++$i) {
            $ascii = rand(0, 1) ? rand(65, 90): rand(97, 122);
            $id.= chr($ascii);
        }
        return $id;
    }

    public function sendRecommendation() {
        try {
            $portlet_id = (int)$this->request->getParam('id');
            $users = $this->request->getParam('users');
            if (empty($portlet_id) || empty($users) || !is_array($users)) {
                throw new api_exception(api_exception::THROW_NONE, array(), 0, "Parameter 'id' and/or 'users' missing");
            }

            $sql = 'SELECT upt.type, upt.subtype, upt.label, upt.theme, upt.config, upt.config_id
                FROM user_tab_portlets upt
                    INNER JOIN user_tabs ut ON (upt.tab_id = ut.id)
                WHERE upt.id = :id AND ut.user_id = :user_id';
            $stmt = $this->db->prepare($sql);
            $stmt->execute(array('id' => $portlet_id, 'user_id' => $this->getUserId()));
            $portletConfig = $stmt->fetch(PDO::FETCH_ASSOC);
            if (empty($portletConfig)) {
                throw new api_exception(api_exception::THROW_NONE, array(), 0, "No portlet exist with the provided 'id': $recommendation_id");
            }

            $max = 10;
            $count = 0;
            $length = 8;
            do {
                $portletConfig['id'] = $this->generateRandomCode($length);

                $sql = "INSERT INTO portlet_recommendations (id, type, subtype, label, theme, config, config_id)
                    VALUES (:id, :type, :subtype, :label, :theme, :config, :config_id)";
                $stmt = $this->db->prepare($sql);
                $stmt->execute($portletConfig);
                ++$count;
            } while (!$stmt->rowCount() || $count > $max);

            foreach ($users as $user) {
                $data = array(
                    'ToEmail' => $user['email'],
                    'ToPrename' => $user['prename'],
                    'ToName' => $user['lastname'],
                    'FromEmail' => $this->user['email'],
                    'FromPrename' => $this->user['firstname'],
                    'FromName' => $this->user['lastname'],
                    'allTrans' => $this->config->trans,
                    'link' => 'http://'.$_SERVER['HTTP_HOST'].'/portlet/importRecommendation.html?id='.$portletConfig['id'],
                );

                $params = array(
                    'ToEmail' => $user['email'],
                    'ToPrename' => $user['prename'],
                    'ToName' => $user['lastname'],
                    'FromEmail' => $this->user['email'],
                    'FromPrename' => $this->user['firstname'],
                    'FromName' => $this->user['lastname'],
                    'MailSubject' => $this->trans['recommendationEmailSubject'],
                    'Body' => $this->renderTemplate('mailMessage', $data, 'html'),
                );
                $mail = new picok_emailHelper();
                $mail->buildMail($params);

                if (!$mail->sendMail()) {
                    throw new api_exception(api_exception::THROW_NONE, array(), 0,'Could not send Email');
                }
            }

            $data = array('status' => 'success', 'data' => $portletConfig['id']);
        } catch (api_exception $e) {
            api_log::log(api_log::ERR, $e->getMessage());
            $data = array(
                'status' => 'error',
                'data'   => $this->trans['error'],
                'code'   => $e->getCode(),
            );
        } catch (Exception $e) {
            $data = array(
                'status' => 'error',
                'data'   => $e->getMessage(),
                'code'   => $e->getCode(),
            );
        }

        $this->data = $data;

        return true;
    }

    public function importRecommendation() {
        try {
            $recommendation_id = $this->request->getParam('id');
            if (empty($recommendation_id) || strlen($recommendation_id) != 8) {
                throw new api_exception(api_exception::THROW_NONE, array(), 0, "Parameter 'id' missing");
            }

            $sql = "SELECT pr.type, pr.subtype, pr.label, pr.theme, pr.config, pr.config_id, pt.label AS name
                FROM portlet_recommendations pr
                    INNER JOIN portlets_trans pt ON (pr.type = pt.type AND pr.subtype = pt.subtype)
                WHERE pr.id = :id AND pt.lang = :lang";
            $stmt = $this->db->prepare($sql);
            $stmt->execute(array('lang' => $this->lang, 'id' => $recommendation_id));
            $portletConfig = $stmt->fetch(PDO::FETCH_ASSOC);
            if (empty($portletConfig)) {
                throw new api_exception(api_exception::THROW_NONE, array(), 0, "No recommendation exist with the provided 'id': $recommendation_id");
            }

            $portlet['type'] = $portletConfig['type'];
            $portlet['subtype'] = $portletConfig['subtype'];

            $portletConfig['config'] = unserialize($portletConfig['config']);
            $portlet['portlet'] = $portletConfig;

            $this->executeAddPortlet($portlet, false);
            $msg = $portletConfig['name'];
        } catch (Exception $e) {
            $msg = $this->trans['recommendation_error'];
        }
        $_SESSION[$this->config->appname]['recommendMessage'] = $msg;
        $host = $this->config->public_url[strtolower($this->lang)];
        $response = api_response::getInstance();
        $response->redirect($host);
        return true;
    }

    /**
     * Create portlet from an external site
     *
     * @return boolean
     */
    public function addFeed() {
        try {
            $portlet = $this->createPortletInstance('feedreader');
            if ($portlet instanceof portlets_feedreader_core) {
                $portletData = $portlet->addFeed($this->request->getParameters());
            } else {
                throw new api_exception(api_exception::THROW_NONE, array(), 0, "Cannot create instance of the feedreader portlet");
            }

            $this->executeAddPortlet($portletData, false);

            $hosts = $this->config->hosts;
            $app_url = empty($_SERVER['HTTPS']) ? 'http' : 'https';
            $app_url.= '://'.key($hosts);

            $data = array(
                'msg' => str_replace(
                    array('FEEDLABEL', 'APPURL', 'APPNAME'),
                    array($portletData['label'], $app_url, $this->trans['app_name']),
                    $this->trans['feed_added']
                )
            );

        } catch (api_exception $e) {
            $data['msg'] = $this->trans['error'];
            api_log::log(api_log::ERR, $e->getMessage());
        } catch (Exception $e) {
            $data['msg'] = $e->getMessage();
        }

        $this->data = $portlet->addFeedConfirmation($data);
        return true;
    }
}