/modules/admin/classes/model/adminpages.php

<?php

defined('SYSPATH') OR die('No direct access allowed.');

class Model_AdminPages extends Model
{
    
    static private $_childpages = array();


    public function page_types()
    {
        $query = DB::select()
                        ->from('pagetypes')
                        ->order_by('id')
                        ->cached(60);

        $result = $query->execute(); //->cached(10)

        if ($result->count() == 0)
            return array();
        else
		{			
			$return = array();
			foreach( $result->as_array() as $item )
			{
				$item['name'] = i18n::get($item['name']);
				$return[] = $item;
			}
		}
		return $return;
		
    }

    function find_childrens($param_id, &$param_rows, $depth=0)
    {
        $p_return = array();

        foreach ($param_rows as $p_k => $p_v)
        {
            if ($p_v['parent_id'] == $param_id)
            {
                $p_v['depth'] = $depth; // save object depth
                $p_v['page_title'] = str_repeat('-', $depth) . $p_v['page_title'];
                $p_return[] = $p_v;

                $p_current_key = count($p_return) - 1;
                foreach ($this->find_childrens($p_v['id'], $param_rows, $depth + 1) AS $value)
                {
                    $p_return[] = $value;
                }
            }
        }

        return $p_return;
    }

// function

    public function page_folders($lang_id = 1)
    {

        $return = array('0' => array('name' => I18n::get('Site Root'), 'url' => '/'));

        $query = DB::select('page_url', 'id', 'page_title', 'parent_url', 'parent_id')
                        ->from('content')
                        ->where('pagetypes_id', '=', '2') // Folder
                        ->where('languages_id', '=', $lang_id)
                        ->order_by('parent_id')
                        ->cached(10);

        $result = $query->execute(); //->cached(10)

        if ($result->count() == 0)
            return $return;
        else
        {
            $rows = $result->as_array();

            $rows = $this->find_childrens(0, $rows);
            foreach ($rows as $key => $value)
            {
                $value['parent_url'] = $value['parent_url'] == '/' ? '' : $value['parent_url'];
                $return[$value['id']] = array('name' => $value['page_title']
                    , 'url' => $value['parent_url'] . '/' . $value['page_url']);
            }

            return $return;
        }
    }

    public function get_page_templates()
    {
        $template_list = array();

        $template_info = Model_AdminTemplates::get_info(
                        Model_AdminDomains::domain_template($_SERVER['HTTP_HOST'])
        );

        foreach ($template_info->templates->filename as $key => $item)
        {
            $item = $item . '';
            if ($template_info->default != $item)
                $template_list[$item] = $item;
        }

        /*
          $tdir = TEMPLATEPATH . 'consol';
          if (!file_exists($tdir) or !is_dir($tdir) or is_link($tdir)) return $template_list;

          foreach (scandir($tdir) as $item)
          {
          if(!is_dir("$tdir/$item")) $template_list[] = $item;
          }

          echo Kohana::debug( (string)$template_info->default);
          echo Kohana::debug($template_list);
          die();
         */

        $template_list = Arr::unshift($template_list, (string) $template_info->default, (string) $template_info->default);

        return $template_list;
    }

    public function page_visible_to()
    {
        $return = $enum_type = array('all' => I18n::get('All'));
        $query = DB::query(Database::SELECT, "SHOW COLUMNS FROM content LIKE 'visible_to' ;")
                        ->cached(60);

        $result = $query->execute(); //->cached(10)

        if ($result->count() == 0)
            return $enum_type;
        else
        {
            $columns = $result->current();
            preg_match_all("|'([^']+)'|i", $columns['Type'], $enum_type);

            foreach ($enum_type[1] as $value)
            {
                $return[$value] = I18n::get(ucfirst($value));
            }

            return $return;
        }
    }

    public function page_show($languages_id = NULL, $parent_id = 0)
    {
        $return = array();

        $query = DB::select('content.*', array('pagetypes.name', 'page_type_name'))
                        ->from('content')
                        ->join('pagetypes')->on('pagetypes.id', '=', 'content.pagetypes_id')
                        ->order_by('page_order');

        if (is_numeric($languages_id))
            $query->where('content.languages_id', '=', $languages_id);

        if ($parent_id)
            $query->and_where('content.parent_id', '!=', '0');
        else
            $query->and_where('content.parent_id', '=', '0');

        $result = $query->execute();

        if ($result->count() == 0)
            return array();
        else
        {
            if (!$parent_id)
                return $result->as_array();
            else
            {
                // Recurse function
                function find_childrens($param_id, &$param_rows, $depth=1)
                {
                    $p_return = array();

                    foreach ($param_rows as $p_k => $p_v)
                    {
                        if ($p_v['parent_id'] == $param_id)
                        {
                            $p_v['depth'] = $depth; // save object depth
                            $p_return[] = $p_v;

                            if ($p_v['pagetypes_id'] == 2) // Folder
                            {
                                $p_current_key = count($p_return) - 1;

                                $p_return[$p_current_key]['childs'] = find_childrens($p_v['id'], $param_rows, $depth + 1);
                            }
                        }
                    }

                    return $p_return;
                }

// function

                $rows = $result->as_array();

                $return = find_childrens($parent_id, $rows);

                return $return;
            }
        }
    }

    public function page_load($id = NULL, $where = NULL)
    {
        $query = DB::select()->from('content')->limit(1);

        if ((int) $id || $where)
        {
            if ((int) $id)
                $query->where('id', '=', $id);

            if ($where)
            {
                foreach ($where as $key => $item)
                {
                    $query->and_where($key, '=', $item);
                }
            }

            $result = $query->execute();
            if ($result->count() == 0)
                return array();
            else
            {
                $return = $result->current();
                $return['page_blocks'] = self::getPageBlocks(NULL, $id);
                return $return;
            }
        }
        else
        {
            return array();
        }
    }

    public function page_save($lang, $id = NULL)
    {
        if ($post = Model_AdminPages::page_validate())
        {
            $post['parent_url'] .= '/';
            $parent_page = Model_AdminPages::page_load($post['parent_id']);
            if (!$parent_page)
                $parent_page = array('parent_url'=>'', 'page_url'=>'');
            $post['parent_url'] = str_replace('//', '/', $parent_page['parent_url'].'/'.$parent_page['page_url']);

            if ((int) $id)
            { // update
                $id = (int) $id;

                // Load current page info
                $result = DB::select()->from('content')
                                ->limit(1)
                                ->where('id', '=', $id)
                                ->execute()
                                ->current();

                DB::update('content')
                        ->set(
                                array(
                                    'parent_id' => $post['parent_id'],
                                    'parent_url' => $post['parent_url'],
                                    'languages_id' => $lang,
                                    'pagetypes_id' => $post['pagetypes_id'],
                                    'page_url' => Controller_Admin::to_url(str_replace('//', '/', $post['page_url'])),
                                    'page_title' => $post['page_title'],
                                    'page_name' => $post['page_name'],
                                    'page_content' => $post['page_content'],
                                    'page_keywords' => $post['page_keywords'],
                                    'page_description' => $post['page_description'],
                                    'is_default' => $post['is_default'],
                                    'is_cached' => $post['is_cached'],
                                    'hide_from_map' => $post['is_on_map'],
                                    'page_associated_words' => $post['page_associated_words'],
                                    'page_disabled_words' => $post['page_disabled_words'],
                                    'visible_to' => $post['visible_to'] ? $post['visible_to'] : 'all',
                                    'page_template' => $post['page_template'],
                                    'status' => $post['status'],
                                )
                        )
                        ->where('id', '=', $id)
                        ->execute()
                ;

                if ($post['pagetypes_id'] == 2) // Folder
                    Model_AdminPages::updateFolderChilds($id, $result, $post);
            }
            else
            { // create
                $id = $total_rows = 0;

                list($id, $total_rows) = DB::insert('content',
                                        array(
                                            'parent_id', 'parent_url', 'languages_id',
                                            'pagetypes_id', 'page_url', 'page_title',
                                            'page_name', 'page_content', 'page_keywords',
                                            'page_description', 'is_default', 'is_cached',
                                            'hide_from_map', 'page_associated_words', 'page_disabled_words',
                                            'visible_to', 'page_template', 'status'
                                        )
                                )
                                ->values(
                                        array(
                                            $post['parent_id'], ($post['parent_url'] ? $post['parent_url'] : '/'), $lang,
                                            $post['pagetypes_id'], Controller_Admin::to_url(str_replace('//', '/', $post['page_url']))
                                            , $post['page_title'],
                                            $post['page_name'], $post['page_content'], $post['page_keywords'],
                                            $post['page_description'], $post['is_default'], $post['is_cached'],
                                            $post['is_on_map'], $post['page_associated_words'], $post['page_disabled_words'],
                                            'all', $post['page_template'], $post['status'],
                                        )
                                )
                                ->execute();

                $result = DB::query(Database::SELECT,
                                "SELECT COUNT(*) AS cnt FROM content WHERE parent_id={$post['parent_id']}")->execute();
                $pages_count = $result->current();

                $page_order = $pages_count['cnt'];

                DB::query(Database::UPDATE,
                        " UPDATE content SET date_create = NOW(), page_order = $page_order"
                        . " WHERE id = $id")->execute();
            }

            // Set IS_DEFAULT status
            if ($post['is_default'])
            {
                DB::query(Database::UPDATE, " UPDATE content SET is_default = 0 "
                        . " WHERE languages_id = $lang  and is_default = 1 and id <> $id")->execute();
            }

            Model_AdminPages::deletePageBlocks($id);

            if ($post['page_block_name'])
                Model_AdminPages::savePageBlocks($id, $post, $lang);

            return $id;
        }
        else
        {
            return false;
        }
    }

    public function page_move_new_diz($data = NULL)
    {
        parse_str( substr($data,0,-1), $data );

        foreach($data['pages'] as $key => $item)
        {
            DB::update('content')->set(array('page_order'=>($key+1)))->where('id','=',$item)->execute();
        }

    }

    public function page_move($id = NULL, $new_parent_id = 0, $new_order = 0)
    {
//        echo (Kohana::debug($id, $new_parent_id , $new_order));
        try
        {
            $newpage = DB::select()
                            ->from('content')
                            ->where('parent_id', '=', $new_parent_id)
                            ->and_where_open()
                            ->and_where('page_order', '>=', $new_order)
                            ->and_where('page_order', '<', '65535')
                            ->and_where_close()
                            ->execute()
            ;
            $newpage = $newpage->current();

            // готовим место для cтраницы которую мы меняем
            DB::query(Database::UPDATE,
                    " UPDATE content SET page_order = page_order+1 "
                    . " WHERE parent_id=$new_parent_id AND ( page_order >= $new_order AND page_order < 65535 )")->execute();

            // cтраница которую мы меняем
            DB::update('content')
                    ->set(array(
                        'parent_id' => $new_parent_id,
                        'parent_url' => $newpage['parent_url'],
                        'page_order' => $new_order
                    ))
                    ->where('id','=', $id)
                    ->execute()
            ;
            // меняем номера пейдж ордер всей старой ветки.
            $table_hash = 't'.uniqid();
            DB::query(Database::DELETE, "DROP TABLE IF EXISTS $table_hash")->execute();
            $sql = "CREATE TEMPORARY TABLE $table_hash (i INT AUTO_INCREMENT, id INT UNSIGNED DEFAULT 0,
                    PRIMARY KEY (`i`)
                    )
                    SELECT id
                    FROM content
                    WHERE parent_id = ".$newpage['parent_id']."
                    ORDER BY page_order
            ";

             DB::query(Database::INSERT, $sql)->execute();
             DB::query(Database::UPDATE, "UPDATE content,$table_hash SET content.page_order = ($table_hash.i-1) WHERE $table_hash.id = content.id")->execute();


//            DB::query(Database::UPDATE,
//                    " UPDATE content SET parent_id=$new_parent_id, page_order = $new_order "
//                    . " WHERE id = $id")->execute();
            return 1;
        }
        catch (Database_Exception $e)
        {
            echo $e->getMessage();
        }
    }

    public function page_delete($id = NULL)
    {
        $query = DB::select()->from('content');

        if (is_numeric($id))
        {
            $query->where('id', '=', $id); // ->limit(1)
            $row = $query->execute()->current();
            
//            $query = DB::delete('content');
//            $query->where('id', '=', $id); // ->limit(1)
//            $total_rows = $query->execute();
            
//            if( $row['pagetypes_id'] == 2 )
//            {
                self::getChildrenPagesRecurse($id);
                
                $query = DB::delete('content');
                
                $query->where('id', 'IN', DB::expr( '('.implode(',', self::$_childpages).')' ) );
                $total_rows = $query->execute();
//            }
        }
        return true;
    }

    public function page_delete_list($array)
    {
        $query = DB::delete('content');
        
        if( count($array) )
        {
            foreach( $array as $item )
            {
                self::getChildrenPagesRecurse($item);
            }
            
            $query->where('id', 'IN', DB::expr( '('.implode(',', self::$_childpages).')' ) );
            $total_rows = $query->execute();
        }
        return true;
    }
    
    /**
     * Function return chilren of cur page
     * put data in self::$_childpages
     * 
     * @param int $id 
     * @return array
     */
    static function getChildrenPagesRecurse( $id )
    {
        $query = DB::select()
                    ->from('content')
                ;
        array_push(self::$_childpages, (int)$id);
        
        if( is_numeric($id) )
        {
            $query->where('parent_id','=', $id);
            $query = $query->execute();
            
            if( $query->count() > 0 )
            {
                
                foreach( $query->as_array('id', 'parent_id') as $key => $item )
                {
                    self::getChildrenPagesRecurse($key);
                }
            }
        }
            
        return;
    }

    public function page_active($id = NULL)
    {
        if (is_numeric($id))
        {
            DB::query(Database::UPDATE, "UPDATE content SET status = !status WHERE id =$id LIMIT 1")
                    ->execute();
        }
        return true;
    }

    public function page_copy_new_diz($id = NULL)
    {

    }

    public function page_copy($id = NULL)
    {
        if (is_numeric($id))
        {
            $resut = DB::select('page_url')->from('content')->limit(1)->where('id', '=', $id)->execute()->as_array();
            $query = DB::query(Database::INSERT,
                                    ' INSERT INTO content ( page_url, parent_id, parent_url, languages_id,
					pagetypes_id, page_name, page_title,
					page_content, page_keywords,
					page_description, is_default, is_cached,
					hide_from_map, page_associated_words, page_disabled_words,
					date_create, visible_to, page_template )' .
                                    '  SELECT CONCAT_WS("_", t2.page_url, t3.s_sum ) AS page_url, t2.parent_id, t2.parent_url, t2.languages_id,
					t2.pagetypes_id, page_name, CONCAT(t2.page_title," ' . (I18n::get('CopyAdd')) . '") As page_title,
					t2.page_content, t2.page_keywords,
					t2.page_description, t2.is_default, t2.is_cached,
					t2.hide_from_map, t2.page_associated_words, t2.page_disabled_words,
					NOW(), t2.visible_to, t2.page_template ' .
                                    '  FROM content AS t2 ' .
                                    '  LEFT JOIN ( ' .
                                    '     SELECT COUNT(*)+1 AS s_sum, :id AS id FROM content AS tc WHERE page_url LIKE "' . $resut[0]['page_url'] . '%" ' .
                                    '  ) AS t3 ON (t3.id = t2.id) ' .
                                    '  WHERE t2.id=:id')
                            ->bind(':id', $id);

            $result = $query->execute();
        }
        return true;
    }

    public function page_validate()
    {
        $keys = array(
            'parent_id', 'parent_url',
            'pagetypes_id', 'page_url', 'page_title',
            'page_name', 'page_content', 'page_keywords',
            'page_description', 'is_default', 'is_cached',
            'is_on_map', 'page_associated_words', 'page_disabled_words',
            'visible_to', 'page_template', 'status', 'page_block_name', 'page_block_text', 'page_block_id'
        );
        $params = Arr::extract($_POST, $keys, 0);

        $post = Validate::factory($params)
                        ->rule('pagetypes_id', 'not_empty')
                        ->rule('pagetypes_id', 'digit')
                        ->rule('parent_id', 'not_empty')
                        ->rule('parent_id', 'digit')
                        ->rule('page_url', 'not_empty')
                        ->rule('page_url', 'max_length', array(255))
                        ->rule('page_keywords', 'max_length', array(255))
                        ->rule('page_description', 'max_length', array(255))
                        ->rule('page_template', 'not_empty')
        ;

        if ($post->check())
        {
            return $params;
        }
        else
        {

            Messages::add($post->errors('validate'));
        }
    }

    public function updateFolderChilds($id = NULL, $prev_info = array(), $new_info = array())
    {
        // If Folder URL or Folder Parent was changed then fix childrens params
        if ($prev_info['page_url'] != $new_info['page_url'] or $prev_info['parent_url'] != $new_info['parent_url'])
        {
            $old_childs_url = ($prev_info['parent_url'] != '/' ? $prev_info['parent_url'] : '') . '/' . $prev_info['page_url'];
            $new_childs_url = ($new_info['parent_url'] != '/' ? $new_info['parent_url'] : '') . '/' . $new_info['page_url'];

            DB::query(Database::UPDATE,
                    " UPDATE content SET parent_url = CONCAT( '$new_childs_url', SUBSTRING(parent_url, LENGTH('$old_childs_url')+1) ) "
                    . " WHERE parent_url LIKE '$old_childs_url%'")->execute();
        }
    }

    function getSearch($queryOrig)
    {
        $queryOrig = mb_strtolower($queryOrig, 'UTF-8');
        $fields = array('page_url', 'page_title', 'page_name', 'page_associated_words', 'page_content');
        $query = explode(' ', $queryOrig);

        $found = array();

        $str = "SELECT * FROM content WHERE ";
        $like = '';
        foreach ($fields as $item)
        {
            $like .= '(';
            foreach ($query as $word)
            {
                if( mb_strlen( $word, 'UTF-8' ) > Kohana::config('search.min_count_for_search') )
                    $like .= " LCASE(content.$item) LIKE '%".mysql_escape_string($word)."%' AND ";
            }
            if( trim($like) != '(' )
                $like = rtrim($like,'AND ').') OR ';
        }

        $select = $str . '('.rtrim($like,' OR').') AND content.status = 1';        
        $queryDB = DB::query(Database::SELECT, $select.' ORDER BY date_update DESC');
        $result = $queryDB->execute();

        if ($result->count() == 0)
            return array();

        $found = $result->as_array();
        $return = array();
        foreach ($found as $field)
        {
            $text = '';
            foreach ($fields as $item)
            {
                foreach($query as $word)
                    if( strstr(mb_strtolower(strip_tags($field[$item]), 'UTF-8'), $word) ) $text .= $field[$item].' ';
            }
            $return[] = array(
                'text' => $text,
                'url' => str_replace('//', '/', $field['parent_url'] .'/'. $field['page_url']),
                'date' => $field['date_update']
                );
        }

        return $return;
    }

    function savePageBlocks($id, $post, $lang)
    {
        $page_blocks = self::getPageBlocks(NULL, $id);
        foreach ($post['page_block_name'] as $key => $item)
        {
            if ($post['page_block_text'][$key])
            {
                if ($page_blocks[$item])
                {
                    DB::update('page_blocks')
                            ->set(array(
                                'page_block_name' => $item,
                                'page_block_text' => $post['page_block_text'][$key],
                                'date' => DB::expr('NOW()'),
                                'language_id' => $lang
                            ))
                            ->where('id', '=', $post['page_block_id'][$key])
                            ->execute()
                    ;
                }
                else
                {

                    DB::insert('page_blocks', array(
                                'page_block_name',
                                'page_block_text',
                                'page_id',
                                'date',
                                'language_id',
                            ))
                            ->values(array(
                                $item,
                                $post['page_block_text'][$key],
                                $id,
                                DB::expr('NOW()'),
                                $lang
                            ))
                            ->execute()
                    ;
                }
            }
        }
    }

    static public function getPageBlocks($id = NULL, $page_id = NULL)
    {
        $query = DB::select()
                        ->from('page_blocks')
        ;

        if ($id)
            $query->and_where('id', '=', $id);
        if ($page_id)
            $query->and_where('page_id', '=', $page_id);

        $result = $query->execute();

        if ($result->count() == 0)
            return array();
        else
        {
            if ($id)
                return $result->current();
            else
                return $result->as_array('page_block_name');
        }
    }

    function deletePageBlocks($page_id)
    {
        DB::delete('page_blocks')->where('page_id', '=', $page_id)->execute();
        return 1;
    }

    function siteFoldersOtherLang($lang_id = 1)
    {
        $langs = Model_Content::get_languages_array();
        $return = array();
        foreach ($langs as $item)
        {
            if ($item['id'] != $lang_id)
                $return[$item['id']] = $this->page_folders($item['id']);
        }

        return $return;
    }

    function copy2otherlang($forLang = NULL, $id, $lang_id = 1)
    {
        $page = $this->page_load($id);
        unset($page['id']);
        unset($page['page_blocks']);

//        "copy_for_lang"
//        "toFolderLang" => string(1) "/"

        if ($forLang)
        {
            foreach ($forLang as $key => $item)
            {
                $page['languages_id'] = $key;
                $temp_page = $this->page_load(NULL, array('parent_url' => $_POST['toFolderLang'], 'page_url' => $page['page_url'], 'languages_id' => $key));

                if (count($temp_page) === 0 && $item == 1)
                {
                    $page['parent_url'] = $_POST['toFolderLang'];
                    $page['parent_id'] = $_POST['toParent_id'][$key];
                    DB::insert('content',
                                    array_keys($page)
                            )
                            ->values(
                                    $page
                            )
                            ->execute()
                    ;
                }
            }
        }
        return 1;
    }

}