<?php

namespace App\MainBundle\Model;

/**
 * Description of ThWordsModel
 *
 */
class ThThemesModel extends MainBundleModel {
    private $id;

    private $user_id;

    private $name;

    private $description;

    private $time_create;
    //Ключевые слова для темы
    private $keywords;
    //стоп слова для темы
    private $stopwords;
    //Сообщения по темам
    private $messages = array();

    private $countMessages = 0;

    private $countAuthors = 0;
    //Доступные хабы для темы
    private $allow_hubs = array();

    private $type_visible;

    private $allow_message;

    private $allow_comments;

    private $theme_enabled;

    private $group_id;

    private $author_filter = 0;

    private $tag_filter = 0;

    public function SetAuthor($a){
        $this->author_filter =$a;
    }

    public function SetTag($tag){
        $this->tag_filter = $tag;
    }

    public function GetThemeEnabled(){
        return $this->theme_enabled;
    }

    public function GetGroupId(){
        return $this->group_id;
    }

    public function GetTypeVisible(){
        return $this->type_visible;
    }

    public function GetAllowMessage(){
        return $this->allow_message;
    }

    public function GetTypeMessage(){
        $tm = array();
        if($this->allow_message == 1){
            $tm[] = 1;
        }

        if($this->allow_comments == 1){
            $tm[] = 2;
        }

        return $tm;
    }

    public function GetAllowComments(){
        return $this->allow_comments;
    }

    public function GetId(){
        return $this->id;
    }

    public function GetUserID(){
        return $this->user_id;
    }

    public function GetAllowHubs(){
        return $this->allow_hubs;
    }

    public function SetAllowHubs(array $hubs){
        $this->allow_hubs = $hubs;
    }

    public function GetName(){
        return $this->name;
    }

    public function GetDescription(){
        return $this->description;
    }

    public function GetTimeCreate(){
        return $this->time_create;
    }

    public function GetKeyWords(){
        return $this->keywords;
    }

    public function GetStopWords(){
        return $this->stopwords;
    }

    //TODO подумать о кэше в коснтрукторе
    public function __construct($id = 0, $arFilter = null) {
        if($id!=0){
            $sql = 'select user_id, name, description, time_create, type_visible,
                    allow_message, allow_comments, group_id, theme_enabled
                                        from th_themes
                            where id = :id';

            $statement = $this->getConnection()->prepare($sql);
            $statement->bindParam(':id', $id, \PDO::PARAM_INT);

            if(($statement->execute()) AND ($statement->rowCount()>0)){
                $row = $statement->fetch();
                //основные поля
                $this->id               = $id;
                $this->user_id          = $row['user_id'];
                $this->name             = $row['name'];
                $this->description      = $row['description'];
                $this->time_create      = $row['time_create'];
                $this->type_visible     = $row['type_visible'];
                $this->allow_message    = $row['allow_message'];
                $this->allow_comments   = $row['allow_comments'];
                $this->group_id         = $row['group_id'];
                $this->theme_enabled    = $row['theme_enabled'];

                //счиаем количество авторов и сообщений
                $this->MakeCountMA($arFilter);

                //Получаем доступные хабы для данной темы
                $this->MakeThemeHubs();

                //Инициализация ключевых и стоп слов
                //$this->MakeWords();
            }
        }
        /*
        else{
            return $this;
            //throw new \Exception('Такой темы не существует');
        }  */
    }

    public function MakeThemeHubs(){
        $sql = 'select hub_id from th_themes_allowhubs where theme_id = ?';
        $statement = $this->getConnection()->prepare($sql);
        $statement->bindParam(1, $this->id, \PDO::PARAM_INT);
        if(($statement->execute()) AND ($statement->rowCount()>0)){
            $rows = $statement->fetchAll();
            foreach($rows as $row){
                $this->allow_hubs[] = (int) $row['hub_id'];
            }
        }


    }
   // public function GetTopAuthor
    //Собирает количество слов и авторов по темме
    private function MakeCountMA($arFilter = null) {
    $szWhereTime = '';
        $ret = \ECSNData\MemC::get(MCConstants::ThThemeCOUNT_MA . $this->id.'_tf_'.$arFilter['created-from'].'_tt_'.$arFilter['created-upto']);
        if (!$ret) {
                    if ($arFilter) {
                        $szWhereTime = ' and time_create >= ? and time_create <= ? ';
                    }
                    $sql = <<<SQL
                            select count(*) as c_m,
                                count(distinct author_id)  as c_a
                            from th_themes_messages where theme_id = ? and exclude =0
                $szWhereTime
SQL;
                    $statement = $this->getConnection()->prepare($sql);
                    $statement->bindParam(1, $this->id, \PDO::PARAM_INT);
                    if ($arFilter) {
            $statement->bindParam(2, $arFilter['created-from'], \PDO::PARAM_INT);
            $statement->bindParam(3, $arFilter['created-upto'], \PDO::PARAM_INT);
                    }
                    $statement->execute();
                    $row = $statement->fetch();
                    $this->countMessages = $row['c_m'];
                    $this->countAuthors = $row['c_a'];
                    \ECSNData\MemC::set(MCConstants::ThThemeCOUNT_MA . $this->id.'_tf_'.$arFilter['created-from'].'_tt_'.$arFilter['created-upto'], $row);

                }
        else{
            $this->countMessages = $ret['c_m'];
            $this->countAuthors = $ret['c_a'];
        }
    }

    //Возвращает общее количество сообщений для данной темы
    public function GetCountMessages(){
        return $this->countMessages;
    }
    public function SetCountMessages($count){
        return $this->countMessages = $count;
    }
    //Возвращает общее количество авторов для данной темы
    public function GetCountAuthors(){
        return $this->countAuthors;
    }

    //Создание списка тем по данному пользователю
    public function GetThemesArrayByUser($user_id = 0){
        if($user_id!=0){
            $list = array();
            $sql = 'select id, name from th_themes where user_id=? order by name';
            $statement = $this->getConnection()->prepare($sql);
            $statement->bindValue(1, $user_id, \PDO::PARAM_INT);
            $statement->execute();
            $rows = $statement->fetchAll();
            foreach($rows as $row){
                $list[$row['id']]['id'] = $row['id'];
                $list[$row['id']]['name'] = $row['name'];
            }
            return $list;
        }
    }


    public function updateTheme(\App\MainBundle\Entity\ThemeForm $theme){
        $sql ='update th_themes set
                    name=?, type_visible=?, allow_message=?, allow_comments=?
               where id=?';
        $statement = $this->getConnection()->prepare($sql);
        $allow_message  = $theme->getAllowTypeMessage(1);
        $allow_comments = $theme->getAllowTypeMessage(2);
        $statement->bindValue(1, $theme->getName(), \PDO::PARAM_STR);
        $statement->bindValue(2, $theme->getPublicvisible(), \PDO::PARAM_INT);
        $statement->bindValue(3, $allow_message, \PDO::PARAM_INT);
        $statement->bindValue(4, $allow_comments, \PDO::PARAM_INT);
        $statement->bindValue(5, $this->id, \PDO::PARAM_INT);
        $statement->execute();

        $this->name           = $theme->getName();
        $this->allow_message  = $allow_message;
        $this->allow_comments = $allow_comments;
        $this->type_visible   = $theme->getPublicvisible();

        $id = $this->id;

        $sql = 'delete from th_themes_allowhubs where theme_id=?';
        $statement = $this->getConnection()->prepare($sql);
        $statement->bindValue(1, $this->id, \PDO::PARAM_INT);
        $statement->execute();


        //Записываем доступные хабы
        $this->allow_hubs = $theme->getHubs();
        $sql = 'insert into th_themes_allowhubs (theme_id, hub_id) values ';
        foreach($this->allow_hubs as $v){
            $sql.='('.$id.','.$v.'),';
        }
        //Убираем последнюю запятую.
        $sql = substr($sql, 0, strlen($sql)-1);
        //Сохраняем хабы
        $statement = $this->getConnection()->prepare($sql);
        $statement->execute();


        $word = new ThWordsModel(0);
        //Удаляем ключевые слова
        $sql = 'delete from th_themes_keywords where theme_id=?';
        $statement = $this->getConnection()->prepare($sql);
        $statement->bindValue(1, $this->id, \PDO::PARAM_INT);
        $statement->execute();
        //Удаляем стоп слова
        $sql = 'delete from th_themes_stopwords where theme_id=?';
        $statement = $this->getConnection()->prepare($sql);
        $statement->bindValue(1, $this->id, \PDO::PARAM_INT);
        $statement->execute();

        //сохраняем ключевые слова
        $keywords = explode(',', $theme->getKeywords());
        $keywords_id = array();

        foreach ($keywords as $v){
           $keywords_id[] = $word->setWord($v);
        }

        $sql = 'insert into th_themes_keywords (theme_id, word_id) values ';
        foreach($keywords_id as $v){
           $sql.='('.$id.','.$v.'),';
        }
        //Убираем последнюю запятую.
        $sql = substr($sql, 0, strlen($sql)-1);

        $statement = $this->getConnection()->prepare($sql);
        $statement->execute();

        //сохраняем стоп слова
        if(strlen($theme->getStopwords())){
            //сохраняем ключевые слова
            $stopwords = explode(',', $theme->getStopwords());
            $keywords_id = array();

            foreach ($stopwords as $v){
                $keywords_id[] = $word->setWord($v);
            }

            $sql = 'insert into th_themes_stopwords (theme_id, word_id) values ';
            foreach($keywords_id as $v){
                $sql.='('.$id.','.$v.'),';
            }
            //Убираем последнюю запятую.
            $sql = substr($sql, 0, strlen($sql)-1);
            $statement = $this->getConnection()->prepare($sql);
            $statement->execute();
        }
        return true;
    }


    //Создание новой темы -
    public function createNewTheme(\App\MainBundle\Entity\ThemeForm $theme, $user_id){
       $curDate = new \DateTime('now', new \DateTimeZone('Europe/Moscow'));
       $sql = 'insert into th_themes (user_id, name, time_create,
           type_visible, allow_message, allow_comments)
           values (?,?,?,?,?,?)';

       $statement = $this->getConnection()->prepare($sql);
       //Общие параметры для темы
       $allow_message  = $theme->getAllowTypeMessage(1);
       $allow_comments = $theme->getAllowTypeMessage(2);

       $statement->bindValue(1, $user_id, \PDO::PARAM_INT);
       $statement->bindValue(2, $theme->getName(), \PDO::PARAM_STR);
       $statement->bindValue(3, $curDate->format("Y-m-d h:i:s"), \PDO::PARAM_STR);
       $statement->bindValue(4, $theme->getPublicvisible(), \PDO::PARAM_INT);
       $statement->bindValue(5, $allow_message, \PDO::PARAM_INT);
       $statement->bindValue(6, $allow_comments, \PDO::PARAM_INT);

       $statement->execute();
       //заполняем поля модели
       $this->id = $this->getConnection()->lastInsertId();
       $this->user_id = $user_id;
       $this->name  = $theme->getName();
       $this->time_create = $curDate->format("Y-m-d h:i:s");
       $id = $this->id;

       //Записываем доступные хабы
       $this->allow_hubs = $theme->getHubs();
       $sql = 'insert into th_themes_allowhubs (theme_id, hub_id) values ';
       foreach($this->allow_hubs as $v){
           $sql.='('.$id.','.$v.'),';
       }
       //Убираем последнюю запятую.
       $sql = substr($sql, 0, strlen($sql)-1);
       //Сохраняем хабы
       $statement = $this->getConnection()->prepare($sql);
       $statement->execute();

       $word = new ThWordsModel(0);

       //сохраняем ключевые слова
       $keywords = explode(',', $theme->getKeywords());
       $keywords_id = array();

       foreach ($keywords as $v){
           $keywords_id[] = $word->setWord($v);
       }

       $sql = 'insert into th_themes_keywords (theme_id, word_id) values ';
       foreach($keywords_id as $v){
           $sql.='('.$id.','.$v.'),';
       }
       //Убираем последнюю запятую.
       $sql = substr($sql, 0, strlen($sql)-1);

       $statement = $this->getConnection()->prepare($sql);
       $statement->execute();

       //сохраняем стоп слова
       if(strlen($theme->getStopwords())){
            //сохраняем ключевые слова
            $stopwords = explode(',', $theme->getStopwords());
            $keywords_id = array();

            foreach ($stopwords as $v){
                $keywords_id[] = $word->setWord($v);
            }

            $sql = 'insert into th_themes_stopwords (theme_id, word_id) values ';
            foreach($keywords_id as $v){
                $sql.='('.$id.','.$v.'),';
            }
            //Убираем последнюю запятую.
            $sql = substr($sql, 0, strlen($sql)-1);
            $statement = $this->getConnection()->prepare($sql);
            $statement->execute();
       }
       return true;
    }


    //Метод получения Top хабов по теме
    public function GetTopHubs($offset=0,$limit =10, $arFilter = null){
    $szCacheKey = MCConstants::ThThemeTopHubs . $this->id . '_offset_' . $offset . '_limit_' . $limit.'_from_'.$arFilter['created-from'].'_to_'.$arFilter['created-upto'];
        $ret = \ECSNData\MemC::get($szCacheKey);
        if(!$ret) {
        $szWhereTime = '';
        if ($arFilter) {
                    $szWhereTime = ' and time_create >= :from and time_create <= :upto ';
        }
                if (count($this->allow_hubs)) {
                    $szWhereCond = ' and `hub_id` in (' . join(',', $this->allow_hubs) . ') ';
        }
        else {
            $szWhereCond = '';
        }
            $sql =<<<SQL
                                select hub_id, COUNT(*) as c_hub
                                    from th_themes_messages
                                    where theme_id = :id and exclude = 0
                                            $szWhereCond
                                            $szWhereTime
                                    group by hub_id order by c_hub desc
                                    limit :offset, :limit
SQL;
            $statement = $this->getConnection()->prepare($sql);
            $statement->bindParam(':id', $this->id, \PDO::PARAM_INT);
            if (!empty($szWhereTime)) {
                $statement->bindParam(':from', $arFilter['created-from'], \PDO::PARAM_INT);
                $statement->bindParam(':upto', $arFilter['created-upto'], \PDO::PARAM_INT);
            }
//$statement->bindParam(2, $date_from, \PDO::PARAM_INT);
            //$statement->bindParam(3, $date_to, \PDO::PARAM_INT);
            $statement->bindParam(':offset', $offset, \PDO::PARAM_INT);
            $statement->bindParam(':limit', $limit, \PDO::PARAM_INT);

            if (($statement->execute()) AND ($statement->rowCount() > 0)) {
                $rows = $statement->fetchAll();
                $list = array();
                $hub_title_ar = array(
                    1 => 'Твиттер',
                    2 => 'LiveJournal',
                    3 => 'ВКонтакте ',
                    8 => 'Новости'
                );
                //Для процентного соотношения для правого графика
                $p1 = 0;
                foreach ($rows as $row) {
                    //Расчитываем процентное соотношение по количеству сообщений данной темы
                    if ($p1 == 0) {
                        $p1 = $row['c_hub'];
                    }
                    $hub_id = (int) $row['hub_id'];
                    $list[$hub_id]['procent'] = $row['c_hub'] / $p1 * 100;
                    $list[$hub_id]['c_hub'] = (int) $row['c_hub'];
                    $list[$hub_id]['hub_id'] = $hub_id;
                    $list[$hub_id]['hub_name'] = $hub_title_ar[$hub_id];
                    //echo 'hubid '.$row['hub_id'].'</br>';
                }
                //Возвращаем массив топ авторов - ключ - ид автора, и поля count и author
                \ECSNData\MemC::set($szCacheKey, $list);
                return $list;
            } else {
                return FALSE;
            }
        } else {
            //Возвращаем кэшированный список
            //echo "Возвращено из кэша";
            return $ret;
        }
    }

    /**
     *
     * @return array
     */
    function getStatsData($arFilter) {
        $arRes = array();

        $timeFrom = \DateTime::createFromFormat('U', $arFilter['created-from']);
        $timeUpto = \DateTime::createFromFormat('U', $arFilter['created-upto']);
        $szFrom =  $timeFrom->format('Y-m-d');
        $szUpto =  $timeUpto->format('Y-m-d');
        $sql = <<<SQL
                            select date, count_messages
                            from `th_themes_stats_daily`
                            where theme_id = :id and date > :from and date <= :upto
SQL;
        $stmt = $this->getConnection()->prepare($sql);
        $stmt->bindParam(':id', $this->id, \PDO::PARAM_INT);
        $stmt->bindParam(':from', $szFrom, \PDO::PARAM_STR);
        $stmt->bindParam(':upto', $szUpto, \PDO::PARAM_STR);
        if (($stmt->execute()) && ($stmt->rowCount() > 0)) {
            $arRows = $stmt->fetchAll();
            foreach ($arRows as $row) {
                $arRes[] = array(
                    '1' => (int) $row['count_messages'],
                    'date' => (int) strtotime($row['date'] . ' 00:00:00')
                );
            }
        }
        return $arRes;
    }

    public function GetMonthStatistics() {

        $today = \DateTime::createFromFormat("U", time())->setTimezone(new \DateTimeZone('Europe/Moscow'));
        $month = clone $today;
        $month->modify('-31 day');
        $sql = 'select date, count_messages from th_themes_stats_daily
                    where theme_id=? and (date <= ? and date >= ?)
                order by date limit 31';

        $dateStart = $month->format('Y-m-d');
        $dateEnd = $today->format('Y-m-d');
        $statement = $this->getConnection()->prepare($sql);
        $statement->bindParam(1, $this->id, \PDO::PARAM_INT);
        $statement->bindParam(2, $dateEnd, \PDO::PARAM_STR);
        $statement->bindParam(3, $dateStart, \PDO::PARAM_STR);
        //var_dump($statement);
        if (($statement->execute()) AND ($statement->rowCount() > 0)) {
            $rows = $statement->fetchAll();
            $list = array();
            //var_dump($rows);
            foreach ($rows as $row) {
                $list[] = array(
                    '1' => (int) $row['count_messages'],
                    'date' => (int) strtotime($row['date'] . ' 00:00:00')
                );
            }
            return json_encode($list);
        }
    }

        public function getCountByHub($hub_id, $arFilter = null){
            $szWhereTime = '';
            if (!is_null($arFilter)) {
                $szWhereTime = ' and time_create >= :from and time_create <= :upto ';
            }
            $sql = 'select count(*) as c_m from th_themes_messages
                        where theme_id = :theme_id
                    and hub_id = :hub_id and exclude = 0
                    ' .$szWhereTime;
            $stmt = $this->getConnection()->prepare($sql);
            $stmt->bindParam(':theme_id', $this->id, \PDO::PARAM_INT);
            $stmt->bindParam(':hub_id', $hub_id, \PDO::PARAM_INT);
            if (!empty($szWhereTime)) {
                $stmt->bindParam(':from', $arFilter['created-from'], \PDO::PARAM_INT);
        $stmt->bindParam(':upto', $arFilter['created-upto'], \PDO::PARAM_INT);
            }
            if (($stmt->execute()) && ($stmt->rowCount() > 0)) {
                $row = $stmt->fetch();
                $count = (int) $row['c_m'];
                return $count;
            }
            else return 0;

        }

    //Метод вытаскивания TOP авторов для конкретной темы
    public function GetTopAuthors($offset = 0, $limit = 10, $arFilter = null) {
        $szCacheKey = MCConstants::ThThemeTopAuthor . $this->id . '_offset_' . $offset . '_limit_' . $limit . '_date_'.$arFilter['created-from'].$arFilter['created-upto'];
        $ret = \ECSNData\MemC::get($szCacheKey);
        if (!$ret) {
            $szWhereTime = '';
            if (!is_null($arFilter)) {
                $szWhereTime = ' and time_create >= :from and time_create <= :upto ';
            }
            $sql = <<<SQL
                                select author_id, COUNT(*) as c_mess
                                from th_themes_messages
                                where theme_id = :id and exclude = 0
                                $szWhereTime
                                group by author_id
                                order by c_mess desc
                                limit :offset, :limit
SQL;
            $stmt = $this->getConnection()->prepare($sql);
            $stmt->bindParam(':id', $this->id, \PDO::PARAM_INT);
            if (!empty($szWhereTime)) {
                $stmt->bindParam(':from', $arFilter['created-from'], \PDO::PARAM_INT);
                $stmt->bindParam(':upto', $arFilter['created-upto'], \PDO::PARAM_INT);
            }
            $stmt->bindParam(':offset', $offset, \PDO::PARAM_INT);
            $stmt->bindParam(':limit', $limit, \PDO::PARAM_INT);

            if (($stmt->execute()) AND ($stmt->rowCount() > 0)) {
                $rows = $stmt->fetchAll();
                $list = array();
                //Для процентного соотношения для графика
                $p1 = 0;

                foreach ($rows as $row) {
                    //Расчитываем процентное соотношение по количеству сообщений данной темы
                    if ($p1 == 0) {
                        $p1 = $row['c_mess'];
                    }
                    $list[$row['author_id']]['procent'] = $row['c_mess'] / $p1 * 100;
                    $list[$row['author_id']]['c_mess'] = $row['c_mess'];
                    $list[$row['author_id']]['author_id'] = $row['author_id'];
                    //Для имплода второго запроса выборки по никам и именам авторов
                    $sql_in[] = $row['author_id'];
                }

                $sql_author = 'select id, fullname, nickname
                                        from th_authors where id in (' . implode(',', $sql_in) . ')';
                $statement = $this->getConnection()->prepare($sql_author);
                if (($statement->execute()) AND ($statement->rowCount() > 0)) {
                    $a_rows = $statement->fetchAll();
                    foreach ($a_rows as $row) {
                        $list[$row['id']]['fullname'] = $row['fullname'];
                        $list[$row['id']]['nickname'] = $row['nickname'];
                    }
                }
                //Возвращаем массив топ авторов - ключ - ид автора, и поля count и author
                \ECSNData\MemC::set($szCacheKey, $list);

                return $list;
            } else {
                return FALSE;
            }
        } else {
            //Возвращаем кэшированный список
            //echo "Возвращено из кэша";
            return $ret;
        }
    }

    //Вытаскиваем сообщения из БД для этой темы
    public function GetMessages($offset, $limit, $arFilter = null) {
    $hub_str = implode('-', $this->allow_hubs);
    $memkey = MCConstants::ThThemesModel_Message . $this->id . '_OF_' . $offset . '_LIM_' . $limit . '_tf_' .$arFilter['created-from'].'_tt_'.$arFilter['created-upto'].'_hub_'.$hub_str;

    if($this->author_filter!=0) $memkey=$memkey.'_a_id_'.$this->author_filter;
    if($this->tag_filter) $memkey=$memkey.'_t_id_'.$this->tag_filter;

    $ret = \ECSNData\MemC::get($memkey);
            if (!$ret) {
                if (count($this->allow_hubs) AND ($this->author_filter!=0)) {
                    $szWhereHub = ' and b.hub_id in (' . join(',', $this->allow_hubs) . ')
                        and b.author_id = '.$this->author_filter.' ';

                }
                else if(count($this->allow_hubs)){
                    $szWhereHub = ' and b.hub_id in (' . join(',', $this->allow_hubs) . ') ';
                }
                else {
                    $szWhereHub = '';
                }
        $szWhereTime = ' and b.time_create >= ? and b.time_create <= ? ';
                if ($this->tag_filter) $szWhereTag = "and f.tag like '".$this->tag_filter."'";
                    else $szWhereTag = '';
        $sql = <<<SQL
                        select a.id, a.id_mongo, a.id_external, a.hash, a.hub_id, a.author_id, a.title,a.text,
                       a.url, a.time_create, a.time_modified, a.time_store, a.time_store_ecsn, a.time_updated,
                       b.message_rule, b.format_text, b.format_flag,
                        (
                        select GROUP_CONCAT(d.tag SEPARATOR ':|:') from th_messages_tags c
                        left join th_tags d on c.tag_id = d.id and c.theme_id = ?
                        where c.message_id = a.id
                        limit 1
                        ) as tags
                        from th_messages a
                    join th_themes_messages b on a.id = b.message_id
                    left join th_messages_tags e on a.id = e.message_id and e.theme_id = ?
                    left join th_tags f on e.tag_id = f.id
                        where b.theme_id = ? AND b.exclude = 0
                            $szWhereTime
                           $szWhereHub
                            $szWhereTag
                    order by b.time_create DESC
                  limit ?, ?
SQL;

        $statement = $this->getConnection()->prepare($sql);
        $statement->bindParam(1, $this->id, \PDO::PARAM_INT);
                $statement->bindParam(2, $this->id, \PDO::PARAM_INT);
                $statement->bindParam(3, $this->id, \PDO::PARAM_INT);
        $statement->bindParam(4, $arFilter['created-from'], \PDO::PARAM_INT);
        $statement->bindParam(5, $arFilter['created-upto'], \PDO::PARAM_INT);
        $statement->bindParam(6, $offset, \PDO::PARAM_INT);
        $statement->bindParam(7, $limit, \PDO::PARAM_INT);

        if (($statement->execute()) AND ($statement->rowCount() > 0)) {
            $rows = $statement->fetchAll();
            $this->messages = $this->prepareMessages($rows);
            \ECSNData\MemC::set($memkey, $this->messages);
            return $this->messages;
        }
    } else {
        $this->messages = $ret;
        return $ret;
    }
    }

    protected function prepareMessages(array $messages)
    {
        $commentsSearch = array();
        $commentsCached = array();
        foreach($messages as $message)
        {
            $comentsCount = \ECSNData\MemC::get(MCConstants::ThThemeElasticComCount . $message['id']);
            if($comentsCount)
                $commentsCached[$message['id']] = $comentsCount;
            else
                $commentsSearch[$message['id_mongo']] = $message['id'];
        }

        if(count($commentsSearch) > 0)
        {
            $commentsQuery = array(
                "filter" => array(
                    "terms" => array(
                        "docId" => array_keys($commentsSearch)
                    )
                ),
                "size" => count($commentsSearch),
                "fields" => array("comments_count", "docId")
            );
            $elastic = new \ECSNData\client();
            $comments = $elastic->ESearch($commentsQuery);
            $commentsRows = $comments['hits']['hits'];
            foreach ($commentsRows as $eC) {
                $messageId = $commentsSearch[$eC['fields']['docId']];
                $comentsCount = (int) $eC['fields']['comments_count'];
                \ECSNData\MemC::set(MCConstants::ThThemeElasticComCount . $messageId, $comentsCount, 300);
                $commentsCached[$messageId] = $comentsCount;
            }
        }

        $list = array();
        //Creating messaging for template
        foreach ($messages as $message) {
            $list[$message['id']] = new ThMessagesModel(0, $message);
            $list[$message['id']]->SetCommentsCount($comentsCount[$message['id']]);


            if (strlen($message['message_rule']) > 0) {
                //echo $row['message_rule'].'</br>';
                $rule = json_decode($message['message_rule'], TRUE);
                $list[$message['id']]->parseText($rule);
            } else {
                $list[$message['id']]->parseWithoutRule();
            }
            //var_dump($list);


            /* Вариант на боевом сервере
             *
              if($row['format_flag']==1) {
              echo "1 <br>";
              $list[$row['id']]->setSmallText($row['format_text']);
              } elseif ((strlen($row['message_rule']) > 0) and is_null($row['format_flag'])) {
              echo "2 <br>";
              $rule = json_decode($row['message_rule'], TRUE);
              $list[$row['id']]->parseText($rule);
              } else {
              echo "3 <br>";
              $list[$row['id']]->parseWithoutRule();
              }
              /**
             */
        }
        return $list;
    }


    public function makePaging($limit, $offset) {
        $i = ceil($this->countMessages / $limit) - 1;
        $j = ($offset >= 5) ? $offset - 3 : 1;
        $ret = array();
        if ($i >= 7) {
            if ($j + 7 < $i) {
                for ($k = $j; $k <= $j + 6; $k++) {
                    if ($offset > 4) {
                        $ret[1] = "";
                        $ret[2] = "skip";
                    }
                    if ($k == $offset) {
                        $ret[$k] = "current";
                    } else {
                        if ($k == ($j + 7)) {
                            $ret[$k] = "skip";
                            $ret[$i] = "";
                        } else {
                            $ret[$k] = "";
                        }
                    }
                    if (!isset($ret[$i - 1])) {
                        $ret[$i - 1] = "skip";
                    }
                    if (!isset($ret[$i])) {
                        $ret[$i] = "";
                    }
                }
            } else {
                for ($k = $j; $k <= $i; $k++) {
                    if ($k == $offset) {
                        $ret[$k] = "current";
                    } else {
                        $ret[$k] = "";
                    }
                }
                $ret[1] = "";
                $ret[2] = "skip";
            }
        } else {
            for ($k = $j; $k <= $i; $k++) {
                if ($k == $j) {
                    $ret[$k] = "current";
                } else {
                    if ($k == ($j + 7)) {
                        $ret[$k] = "skip";
                    } else {
                        $ret[$k] = "";
                    }
                }
            }
        }
        ksort($ret);

        return $ret;
        }

    public function MakeWords() {
    $id = $this->id;
        $sql = 'select word from th_words where id in(
                    select word_id from th_themes_keywords where theme_id=?)';
    $statement = $this->getConnection()->prepare($sql);
    $statement->bindParam(1, $id, \PDO::PARAM_INT);
    if (($statement->execute()) AND ($statement->rowCount() > 0)) {
            $rows = $statement->fetchAll();
            $keyword = array();
            foreach ($rows as $row) {
        $keyword[]=$row['word'];
            }
            $this->keywords = implode(', ', $keyword);
    }
        else{
            $this->keywords = '';
        }

        $sql = 'select word from th_words where id in(
                    select word_id from th_themes_stopwords where theme_id=?)';
    $statement = $this->getConnection()->prepare($sql);
    $statement->bindParam(1, $id, \PDO::PARAM_INT);
    if (($statement->execute()) AND ($statement->rowCount() > 0)) {
            $rows = $statement->fetchAll();
            $keyword = array();
            foreach ($rows as $row) {
        $keyword[]=$row['word'];
            }
            $this->stopwords = implode(', ', $keyword);
    }
        else{
            $this->stopwords = '';
        }

    }

    public function getMessagesByIds(array $ids)
    {
        if(count($ids) < 1)
            return array();

        $db = $this->getConnection();


        $sql = 'SELECT
                       m.id, m.id_mongo, m.id_external, m.hash, m.hub_id, m.author_id, m.title,m.text,
                       m.url, m.time_create, m.time_modified, m.time_store, m.time_store_ecsn, m.time_updated,
                       "" as message_rule, tm.format_text, tm.format_flag
                  FROM
                        th_messages m
                  INNER JOIN
                        th_themes_messages tm on m.id = tm.message_id
                  WHERE
                        tm.theme_id = ?
                        AND tm.exclude = 0
                        AND m.id IN ( '.str_repeat('?, ', count($ids) - 1).' ? )
                  ORDER BY tm.time_create DESC';


        $binds = $ids;
        array_unshift($binds, $this->id);
        $messages = $db->executeQuery($sql, $binds)->fetchAll(\PDO::FETCH_ASSOC);
        return $this->prepareMessages($messages);
    }

    //Создание списка тем по данному пользователю
    public function GetThemesArrayByGroup($group_id = 1){
        if($group_id!=0){
            $list = array();
            $sql = 'select id, name from th_themes where group_id=? order by name';
            $statement = $this->getConnection()->prepare($sql);
            $statement->bindValue(1, $group_id, \PDO::PARAM_INT);
            $statement->execute();
            $rows = $statement->fetchAll();
            foreach($rows as $row){
                $list[$row['id']]['id'] = $row['id'];
                $list[$row['id']]['name'] = $row['name'];
            }
            return $list;
        }
    }

    public static function getIDbyGroup($group_name = 'default'){
        $sql = 'select id from th_themes where group_id = (
                    select group_id from th_themes_groups where group_tname=?
                ) order by name limit 1';
        $statement = \App\MainBundle\Entity\ConnectionDB::getInstance()->getConnection()->prepare($sql);
        $statement->bindParam(1, $group_name, \PDO::PARAM_STR);
        if (($statement->execute()) AND ($statement->rowCount() > 0)) {
            $row = $statement->fetch();
            return $row['id'];
        }
        else{
            return false;
        }
    }


    public function GetCountMessagesByAuthor($arFilter = null) {
    $sql = 'select count(*) as c_a from th_themes_messages b
                        where b.theme_id = ? AND b.exclude = 0
                         and b.time_create >= ? and b.time_create <= ?
                         and b.author_id = '.$this->author_filter.'
                 and b.hub_id in (' . join(',', $this->allow_hubs) . ')';

        $statement = $this->getConnection()->prepare($sql);
    $statement->bindParam(1, $this->id, \PDO::PARAM_INT);
    $statement->bindParam(2, $arFilter['created-from'], \PDO::PARAM_INT);
    $statement->bindParam(3, $arFilter['created-upto'], \PDO::PARAM_INT);

    if (($statement->execute()) AND ($statement->rowCount() > 0)) {
            $row = $statement->fetch();
            $this->countMessages = $row['c_a'];
            return $this->countMessages;
    }
    }

    public function GetCountTags($tag, $arFilter = null) {
    $sql = 'select count(*) as c_a from th_themes_messages b
                        left join th_messages_tags e on b.message_id = e.message_id
                        left join th_tags f on e.tag_id = f.id
                        where b.theme_id = ? AND b.exclude = 0
                        and f.tag = ?
                        and b.time_create >= ? and b.time_create <= ?
                 and b.hub_id in (' . join(',', $this->allow_hubs) . ')';

        $statement = $this->getConnection()->prepare($sql);
    $statement->bindParam(1, $this->id, \PDO::PARAM_INT);
        $statement->bindParam(2, $tag, \PDO::PARAM_STR);
    $statement->bindParam(3, $arFilter['created-from'], \PDO::PARAM_INT);
    $statement->bindParam(4, $arFilter['created-upto'], \PDO::PARAM_INT);

    if (($statement->execute()) AND ($statement->rowCount() > 0)) {
            $row = $statement->fetch();
            $this->countMessages = $row['c_a'];
            return $this->countMessages;
    }
    }

    // Добавление тегов
    public function AddTags($message_id, $tags) {
        $tags_arr = explode(',', $this->_strtolower(strtolower($tags)));
        foreach ($tags_arr as $key => $tag)
        {
            $tag = trim($tag);
            if (!$tag) continue;
            $sql = 'INSERT INTO th_tags SET tag = ? ON DUPLICATE KEY UPDATE id = LAST_INSERT_ID(id)';
            $statement = $this->getConnection()->prepare($sql);
            $statement->bindValue(1, trim($tag), \PDO::PARAM_STR);
            $statement->execute();
            $tag = htmlspecialchars($tag);
            $tags_arr[$key] = "<a href=\"/events/".$this->id."/tags/$tag\">$tag</a>";
            $lastInsertedID = $this->getConnection()->lastInsertId();

            $sql = 'INSERT INTO th_messages_tags SET theme_id = ?, message_id = ?, tag_id = ?';
            $statement = $this->getConnection()->prepare($sql);
            $statement->bindValue(1, $this->id, \PDO::PARAM_INT);
            $statement->bindValue(2, $message_id, \PDO::PARAM_INT);
            $statement->bindValue(3, $lastInsertedID, \PDO::PARAM_INT);
            $statement->execute();
        }
        return implode(' ', $tags_arr);
    }

    // тэги темы
    public function GetTags()
    {
        $sql = 'SELECT DISTINCT t.tag FROM th_tags t
            LEFT JOIN th_messages_tags mt ON t.id = mt.tag_id
            WHERE mt.theme_id = ?
            GROUP BY t.tag
            ORDER BY COUNT(t.id) DESC
            LIMIT 15';
        $statement = \App\MainBundle\Entity\ConnectionDB::getInstance()->getConnection()->prepare($sql);
        $statement->bindParam(1, $this->id, \PDO::PARAM_STR);
        if (($statement->execute()) AND ($statement->rowCount() > 0)) {
            $rows = $statement->fetchAll();
            $result = array();
            foreach($rows as $key => $row){
                $rows[$key][0] = htmlspecialchars($row[0]);
                $result[$key] = "<a href=\"javascript:void(0)\">$row[0]</a>";
            }
            return implode(' ', $result);
        }
        else{
            return false;
        }
    }

    private function _strtolower($string)
    {
        $small = array('а','б','в','г','д','е','ё','ж','з','и','й',
                       'к','л','м','н','о','п','р','с','т','у','ф',
                       'х','ч','ц','ш','щ','э','ю','я','ы','ъ','ь',
                       'э', 'ю', 'я');
        $large = array('А','Б','В','Г','Д','Е','Ё','Ж','З','И','Й',
                       'К','Л','М','Н','О','П','Р','С','Т','У','Ф',
                       'Х','Ч','Ц','Ш','Щ','Э','Ю','Я','Ы','Ъ','Ь',
                       'Э', 'Ю', 'Я');
        return str_replace($large, $small, $string);
    }

    //Метод вытаскивания TOP тегов для конкретной темы
    public function GetTopTags($offset = 0, $limit = 10) {
        $ret = \ECSNData\MemC::get(MCConstants::ThThemeTopTags . $this->id . '_offset_' . $offset . '_limit_' . $limit);
        if (!$ret) {
            $sql = 'SELECT mt.tag_id, t.tag, mt.c_mess
                    FROM (
                    SELECT tag_id, COUNT(*) AS c_mess FROM th_messages_tags
                    WHERE theme_id = ?
                    GROUP BY tag_id ORDER BY c_mess DESC
                    LIMIT ?, ? ) AS mt
                    INNER JOIN th_tags t ON t.id = mt.tag_id';

            $statement = $this->getConnection()->prepare($sql);
            $statement->bindParam(1, $this->id, \PDO::PARAM_INT);
            $statement->bindParam(2, $offset, \PDO::PARAM_INT);
            $statement->bindParam(3, $limit, \PDO::PARAM_INT);

            if (($statement->execute()) AND ($statement->rowCount() > 0)) {
                $rows = $statement->fetchAll();
                $list = array();
                //Для процентного соотношения для графика
                $p1 = 0;

                foreach ($rows as $row) {
                    //Расчитываем процентное соотношение по количеству сообщений данной темы
                    if ($p1 == 0) {
                        $p1 = $row['c_mess'];
                    }
                    $list[$row['tag_id']]['percent'] = $row['c_mess'] / $p1 * 100;
                    $list[$row['tag_id']]['c_mess'] = $row['c_mess'];
                    $list[$row['tag_id']]['name'] = $row['tag'];
                }
                //Возвращаем массив топ тегов - ключ - ид тега, и поля count и teg
                \ECSNData\MemC::set(MCConstants::ThThemeTopTags . $this->id . '_offset_' . $offset . '_limit_' . $limit, $list);
                return $list;
            } else {
                return FALSE;
            }
        } else {
            //Возвращаем кэшированный список
            echo "Возвращено из кэша";
            return $ret;
        }
    }

}