/modules/shop/classes/model/frontend/categories.php

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

class Model_Frontend_Categories extends Model
{

    protected static $recursion_return;
    static private $_filtableFields = null;
    static private $_categoryId = null;
    
    static public $url_search = array( '%23', '%3F', '%2F', '%26', '.', ',', 'quot','amp', '/','"',"'",'%27',';','%2C','%28','%29','%3B','&','%2B','(',')', );
    static public $url_replace = array( '', '', '', '', '', '','_','_','_', '', '','','','','(',')','','','','','' );

    static public function info( $id = NULL )
    {
        $query = Db::select( 'id', 'name','parent_id', 'page_title', 'display_mode', 'parents_serialized', 'description', 'seo_url' )
                        ->from( 'shop_categories' )
                        ->where( 'id', '=', $id )
                        ->and_where( 'status', '=', 'verified' )
        ;
        $result = $query->execute();

        if( $result->count() == 0 )
            return array( );
        else
        {
            $return = array( );
            $return = $result->current();
            $return['parents_serialized'] = unserialize( $return['parents_serialized'] );

            return $return;
        }
    }

    static public function init()
    {
        Kohana_Controller_Quicky::$intermediate_vars['shop_top_categories'] =
                Model_Frontend_Categories::show_categories( Kohana::config( 'shop' )->root_category_id );

        Kohana_Controller_Quicky::$intermediate_vars['shop_config'] = (array) Kohana::config( 'shop' );
    }

    static public function show_categories( $parent_id = NULL )
    {
        $query = Db::query( Database::SELECT, "SELECT c.`id`, c.`name`, c.`seo_url` AS category_url, c.`display_mode`, c1.parent_id AS `count` "
                                . " FROM `shop_categories` AS c"
                                . " LEFT JOIN (SELECT DISTINCT c2.parent_id FROM shop_categories AS c2 ) AS c1 ON (c.id = c1.parent_id) "
                                . " WHERE c.`languages_id` = " . CURRENT_LANG_ID
                                . " AND c.`parent_id` = $parent_id "
                                . " AND c.`status` = 'verified' "
                                . " ORDER BY c.`order_id` ASC, c.name ASC"
                        )
                        ->cached( 60 );
        $result = $query->execute();

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

            foreach( $result->as_array( 'id' ) as $key => $val )
            {
                $val['category_url'] = $val['category_url'] ? $val['category_url'] : Controller_Admin::to_url( $val['name'] );
                $return[$key] = $val;
            }
            
            return $return;
        }
    }

    static public function show_child_categories( $category_id = NULL )
    {
        $return = array( );
        $result = Model_Frontend_Categories::child_categories( $category_id );

        foreach( $result as $key => $val )
        {
            if( $val['name'] )
                $val['category_url'] = $val['category_url'] ? $val['category_url'] : Controller_Admin::to_url( $val['name'] );
            $return[$key] = $val;
        }
        return $return['childs'];
    }

    static public function show_bestseller( $limit = NULL, $price_category = NULL )
    {
        $query = Db::select( 'g.*'
                                , array( 'c.name', 'category_name' )
                                , array( 'c.seo_url', 'category_url' )
                                , array( 'c.page_title', 'page_title' )
                        )
                        ->from( array( 'shop_goods', 'g' ))
                        ->join( array( 'shop_categories', 'c' ))
                        ->on( 'g.categories_id', '=', 'c.id' )
                        ->join( array( 'shop_prices', 'z' ) )
//                            ->on('z.good_id' , '=',  'c.id')
//                            ->on('z.good_id' , '=',  'g.categories_id')
//                            ->on('c.id', '=',  'z.good_id')
//                            ->on('g.categories_id' , '=',  'z.good_id')
                        ->on( 'g.id', '=', 'z.good_id' )
                        ->where( 'z.price', '>', '0' )
                        ->where( 'z.price_categories_id', '=', $price_category )
                        ->and_where( 'c.languages_id', '=', CURRENT_LANG_ID )
                        
                        
//                        ->or_where( 'g.status', '=', 'from1c' )
                        
                        ->where( 'g.is_bestseller', '>', '0' )
                        ->order_by( 'g.name' )
                        ->cached( 180 )
        ;
        
        $query->and_where_open();
        foreach( Kohana::config('shop.frontendStatuses') as $key => $item )
        {
            $query->or_where( "g.status", '=', $item );
        }
        $query->and_where_close();

        if( is_numeric( $limit ) )
            $query->limit( (int) $limit );
        
        $result = $query->execute();

        if( $result->count() == 0 )
            return array( );
        else
        {
            $return = array( );
            $tmpkeys = $result->as_array( 'id' );
            
            $rows = Model_Frontend_Goods::getPriceCategories( array_keys($tmpkeys), $price_category );
            
            $curr = new Model_Frontend_Currencies();
            foreach( $tmpkeys as $key => $val )
            {
                $prices = array( );

//                foreach ($rows as $value)
//                {
//                    $prices[$value['price_categories_id']][$value['currencies_id']] = $value['price'];
//                }
//
//                $val['prices'] = $prices;
                if( $val['quantity'] == 0 )
                        $val['store_approximate_date'] = Model_Frontend_Goods::getApproximateDate( $val['store_approximate_date'] );
                
                $val['prices'] = $curr->getPrice( $rows[$key] );
                $val['seo_url'] = $val['seo_url'] ? str_replace( self::$url_search, self::$url_replace, urlencode($val['seo_url']) ) : str_replace( self::$url_search, self::$url_replace, Controller_Admin::to_url( $val['name'] ));
                $val['seo_url'] = str_replace( '__', '_', $val['seo_url'] );
                $val['seo_url'] = str_replace( '__', '_', $val['seo_url'] );
                $val['category_url'] = $val['category_url'] ? str_replace( self::$url_search, self::$url_replace, urlencode( $val['category_url'] ) ) : str_replace( self::$url_search, self::$url_replace, Controller_Admin::to_url( $val['category_name'] ));
                $val['category_url'] = str_replace( '__', '_', $val['category_url'] );
                $val['category_url'] = str_replace( '__', '_', $val['category_url'] );
                $return[$key] = $val;
            }
            
            return $return;
        }
    }

    static public function show_last( $limit = NULL, $price_categoty = null )
    {
        $query = Db::select( 'g.*', array( 'c.name', 'category_name' ), array( 'c.seo_url', 'category_url' ) )
                        ->from( array( 'shop_goods', 'g' ) )
                        ->join( array( 'shop_categories', 'c' ) )
                        ->on( 'g.categories_id', '=', 'c.id' )
                        ->join( array( 'shop_categories', 'c1' ) )
                        ->on( 'c.parent_id', '=', 'c1.id' )
                        ->where( 'c.languages_id', '=', CURRENT_LANG_ID )
                        ->where( 'c.status', '=', 'verified' )
                        ->where( 'c1.status', '=', 'verified' )
                        ->order_by( 'g.id', 'desc' )
                        ->cached( 180 )
        ;
        
        $query->and_where_open();
        foreach( Kohana::config('shop.frontendStatuses') as $key => $item )
        {
            $query->or_where( "g.status", '=', $item );
        }
        $query->and_where_close();

        
        if( is_numeric( $limit ) )
            $query->limit( (int) $limit );

        $result = $query->execute();

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

            $curr = new Model_Frontend_Currencies();
            foreach( $result->as_array( 'id' ) as $key => $val )
            {
                $query = Db::select( 'p.price_categories_id', 'p.currencies_id', 'p.price' )
                                ->from( array( 'shop_prices', 'p' ) )
                                ->where( 'p.good_id', '=', $key )
                                ->order_by( 'p.currencies_id' )
                                ->cached( 60 )
                ;

                if( is_numeric( $price_categoty ) )
                    $query->where( 'p.price_categories_id', '=', (int) $price_categoty );

                $prices = array( );
                $rows = $query->execute()->as_array();
                
                if( $val['quantity'] == 0 )
                        $val['store_approximate_date'] = Model_Frontend_Goods::getApproximateDate( $val['store_approximate_date'] );
                
                $val['prices'] = $curr->getPrice( $rows );
                $val['seo_url'] = $val['seo_url'] ? str_replace( self::$url_search, self::$url_replace, urlencode($val['seo_url']) ) : str_replace( self::$url_search, self::$url_replace, Controller_Admin::to_url( $val['name'] ));
                $val['seo_url'] = str_replace( '__', '_', $val['seo_url'] );
                $val['seo_url'] = str_replace( '__', '_', $val['seo_url'] );
                $val['category_url'] = $val['category_url'] ? str_replace( self::$url_search, self::$url_replace, urlencode( $val['category_url'] ) ) : str_replace( self::$url_search, self::$url_replace, Controller_Admin::to_url( $val['category_name'] ));
                $val['category_url'] = str_replace( '__', '_', $val['category_url'] );
                $val['category_url'] = str_replace( '__', '_', $val['category_url'] );
                $return[$key] = $val;
            }

            return $return;
        }
    }

    static public function show_new( $limit = NULL, $price_categoty = NULL )
    {
        $query = Db::select( 'g.*', array( 'c.name', 'category_name' ), array( 'c.seo_url', 'category_url' ) )
                        ->from( array( 'shop_goods', 'g' ) )
                        ->join( array( 'shop_categories', 'c' ) )
                        ->on( 'g.categories_id', '=', 'c.id' )
                        ->join( array( 'shop_categories', 'c1' ) )
                        ->on( 'c.parent_id', '=', 'c1.id' )
                        ->where( 'c.languages_id', '=', CURRENT_LANG_ID )
                        ->where( 'g.is_new', '>', '0' )
                        ->where( 'c.status', '=', 'verified' )
                        ->where( 'c1.status', '=', 'verified' )
                        ->order_by( 'g.name' )
                        ->cached( 180 )
        ;
        $query->and_where_open();
        foreach( Kohana::config('shop.frontendStatuses') as $key => $item )
        {
            $query->or_where( "g.status", '=', $item );
        }
        $query->and_where_close();

        if( is_numeric( $limit ) )
            $query->limit( (int) $limit );

        $result = $query->execute();

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

            $curr = new Model_Frontend_Currencies();
            foreach( $result->as_array( 'id' ) as $key => $val )
            {
                $query = Db::select( 'p.price_categories_id', 'p.currencies_id', 'p.price' )
                                ->from( array( 'shop_prices', 'p' ) )
                                ->where( 'p.good_id', '=', $key )
                                ->order_by( 'p.currencies_id' )
                                ->cached( 60 )
                ;

                if( is_numeric( $price_categoty ) )
                    $query->where( 'p.price_categories_id', '=', (int) $price_categoty );

                $prices = array( );
                $rows = $query->execute()->as_array();

//                foreach ($rows as $value)
//                {
//                    $prices[$value['price_categories_id']][$value['currencies_id']] = $value['price'];
//                }
//
//                $val['prices'] = $prices;
                if( $val['quantity'] == 0 )
                        $val['store_approximate_date'] = Model_Frontend_Goods::getApproximateDate( $val['store_approximate_date'] );
                    
                $val['prices'] = $curr->getPrice( $rows );
                $val['seo_url'] = $val['seo_url'] ? str_replace( self::$url_search, self::$url_replace, urlencode( $val['seo_url'] ) ) : str_replace( self::$url_search, self::$url_replace, Controller_Admin::to_url( $val['name'] ));
                $val['seo_url'] = str_replace( '__', '_', $val['seo_url'] );
                $val['seo_url'] = str_replace( '__', '_', $val['seo_url'] );
                $val['category_url'] = $val['category_url'] ? str_replace( self::$url_search, self::$url_replace, urlencode( $val['category_url'] ) ) : str_replace( self::$url_search, self::$url_replace, Controller_Admin::to_url( $val['category_name'] ));
                $val['category_url'] = str_replace( '__', '_', $val['category_url'] );
                $val['category_url'] = str_replace( '__', '_', $val['category_url'] );
                $return[$key] = $val;
            }

            return $return;
        }
    }

    static public function show_onstartpage( $limit = NULL, $price_categoty = NULL )
    {
        $query = Db::select( 'g.*', array( 'c.name', 'category_name' ), array( 'c.seo_url', 'category_url' ) )
                        ->from( array( 'shop_goods', 'g' ) )
                        ->join( array( 'shop_categories', 'c' ) )
                        ->on( 'g.categories_id', '=', 'c.id' )
                        ->join( array( 'shop_categories', 'c1' ) )
                        ->on( 'c.parent_id', '=', 'c1.id' )
                        ->where( 'c.languages_id', '=', CURRENT_LANG_ID )
                        ->where( 'g.show_on_startpage', '=', '1' )
                        ->where( 'c.status', '=', 'verified' )
                        ->where( 'c1.status', '=', 'verified' )
                        ->order_by( 'g.name' )
                        ->cached( 180 )
        ;
        
        $query->and_where_open();
        foreach( Kohana::config('shop.frontendStatuses') as $key => $item )
        {
            $query->or_where( "g.status", '=', $item );
        }
        $query->and_where_close();

        
        if( is_numeric( $limit ) )
            $query->limit( (int) $limit );

        $result = $query->execute();

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

            $curr = new Model_Frontend_Currencies();
            foreach( $result->as_array( 'id' ) as $key => $val )
            {
                $query = Db::select( 'p.price_categories_id', 'p.currencies_id', 'p.price' )
                                ->from( array( 'shop_prices', 'p' ) )
                                ->where( 'p.good_id', '=', $key )
                                ->order_by( 'p.currencies_id' )
                                ->cached( 60 )
                ;

                if( is_numeric( $price_categoty ) )
                    $query->where( 'p.price_categories_id', '=', (int) $price_categoty );

                $prices = array( );
                $rows = $query->execute()->as_array();

//                foreach ($rows as $value)
//                {
//                    $prices[$value['price_categories_id']][$value['currencies_id']] = $value['price'];
//                }
//
//                $val['prices'] = $prices;
                
                if( $val['quantity'] == 0 )
                        $val['store_approximate_date'] = Model_Frontend_Goods::getApproximateDate( $val['store_approximate_date'] );
                    
                
                $val['prices'] = $curr->getPrice( $rows );
                $val['seo_url'] = $val['seo_url'] ? str_replace( self::$url_search, self::$url_replace, urlencode( $val['seo_url'] ) ) : str_replace( self::$url_search, self::$url_replace, Controller_Admin::to_url( $val['name'] ));
                $val['seo_url'] = str_replace( '__', '_', $val['seo_url'] );
                $val['seo_url'] = str_replace( '__', '_', $val['seo_url'] );
                
                $val['category_url'] = $val['category_url'] ? str_replace( self::$url_search, self::$url_replace, urlencode( $val['category_url'] ) ) : str_replace( self::$url_search, self::$url_replace, Controller_Admin::to_url( $val['category_name'] ));
                $val['category_url'] = str_replace( '__', '_', $val['category_url'] );
                $val['category_url'] = str_replace( '__', '_', $val['category_url'] );
                $return[$key] = $val;
            }

            return $return;
        }
    }

    static protected function find_last_childrens( $param_id, &$param_rows, $parents_info_array=array( ) )
    {
        $p_return = array( );

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

                foreach( Model_Frontend_Categories::find_last_childrens( $p_v['id'], $param_rows, $parents_info_array ) AS $value )
                {
                    $p_return[$param_id][$p_v['id']]['childs'] = $value;
                }

                $p_return[$param_id][$p_v['id']]['parents_info'] = $parents_info_array;
                array_pop( $parents_info_array );
            }
        }

        return $p_return;
    }

    static public function child_categories( $category_id = NULL, $lang_id = 1 )
    {
        $return = array( Kohana::config( 'shop' )
            ->root_category_id => array( 'name' => I18n::get( 'Root Category' ), 'id' => Kohana::config( 'shop' )->root_category_id ) );

        $query = DB::select( 'id', 'name', 'parent_id', 'seo_url' )
                        ->from( 'shop_categories' )
                        ->where( 'languages_id', '=', $lang_id )
                        ->and_where( 'status', '=', 'verified' )
                        ->order_by( 'order_id' )
                        ->order_by( 'name' )
                        ->cached( 10 );

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

        if( $result->count() == 0 )
            return $return;
        else
        {
            $rows = $result->as_array( 'id' );
            
            $rows = self::find_last_childrens( Kohana::config( 'shop' )->root_category_id, $rows, false );                        
            if( isset($category_id) )
                return $rows[Kohana::config( 'shop' )->root_category_id][$category_id];
            else
                return $rows[Kohana::config( 'shop' )->root_category_id];
        }
    }

    static function find_inner_categories( &$param_rows )
    {
        $p_return = array( );

        if( is_array( @$param_rows['childs'] ) )
        {
            foreach( $param_rows['childs'] as $p_k => $p_v )
            {
                $p_return[] = $p_k;

                foreach( Model_Frontend_Categories::find_inner_categories( $p_v['childs'] ) as $value )
                {
                    $p_return[] = $value;
                }
            }
        }

        return $p_return;
    }

    static public function goods( $category_id = NULL, $rows_per_page = 10, $price_categoty = NULL, $filter = array( ), $sortby = null, $sortasc = 'asc', $brandId = null, $lang_id = 1 )
    {
        if( is_numeric( $category_id ) )
        {
            $curr = new Model_Frontend_Currencies();

            $price_categories_id = 1;
            $ids = array( );
            $rows = Model_Frontend_Categories::child_categories( $category_id );
            
            $childs = Model_Frontend_Categories::find_inner_categories( $rows );

            $query_c = DB::select( DB::expr( 'COUNT(*) AS mycount' ) )
                            ->from( array( 'shop_goods', 'g' ) )
                            ->join( array( 'shop_categories', 'c' ) )
                            ->on( 'g.categories_id', '=', 'c.id' )
                            ->join( array( DB::expr( '(select price,good_id  from `shop_prices` where currencies_id = ' . $curr->getDefaultCurrency() . ' and price_categories_id = ' . $price_categories_id . ' group by good_id)' ), 'p' ), 'left outer' )
                            ->on( 'p.good_id', '=', 'g.id' )

//здесь дополнительное условие если цена больше 0
                            ->where( 'p.price', '>', '0' )
//конец
            ;
            $query_c->and_where_open();
            foreach( Kohana::config('shop.frontendStatuses') as $key => $item )
            {
                $query_c->or_where( "g.status", '=', $item );
            }
            $query_c->and_where_close();


            $query = Db::select( 'g.*', array( 'c.name', 'category_name' ), array( 'c.seo_url', 'category_url' ), array( 'p.price', 'price' ), array(DB::expr('IF(g.quantity > 0, 1, 0)'), 'exist') )
                            ->from( array( 'shop_goods', 'g' ) )
                            ->join( array( 'shop_categories', 'c' ) )
                            ->on( 'g.categories_id', '=', 'c.id' )
                            ->join( array( DB::expr( '(select price,good_id  from `shop_prices` where currencies_id = ' . $curr->getDefaultCurrency() . ' and price_categories_id = ' . $price_categories_id . ' group by good_id)' ), 'p' ), 'left outer' )
                            ->on( 'p.good_id', '=', 'g.id' )

//здесь дополнительное условие если цена больше 0
                            ->where( 'p.price', '>', '0' )
//конец                     
                            ->order_by( 'exist', 'desc' )
            ;
            $query->and_where_open();
            foreach( Kohana::config('shop.frontendStatuses') as $key => $item )
            {
                $query->or_where( "g.status", '=', $item );
            }
            $query->and_where_close();

          
            if( $brandId !== null )
            {
                $query_c->and_where( 'brand_id', '=', $brandId );
                $query->and_where( 'brand_id', '=', $brandId );
            }

            if( isset( $filter['add_fields'] ) )
            {
                $ids = self::getFilteredGoods( $filter['add_fields'] );
                if( empty( $ids ) )
                    $ids = array( 0 );
                $query_c->and_where( 'g.id', 'in', DB::expr( "(" . implode( ",", $ids ) . ")" ) );
                $query->and_where( 'g.id', 'in', DB::expr( "(" . implode( ",", $ids ) . ")" ) );
            }

            $query_c->and_where_open()->where( 'g.categories_id', '=', $category_id );
            $query->and_where_open()->where( 'g.categories_id', '=', $category_id );


            if( count( $childs ) )
                foreach( $childs as $value )
                {
                    $query_c->or_where( 'g.categories_id', '=', $value );
                    $query->or_where( 'g.categories_id', '=', $value );
                }

            $query_c->and_where_close();
            $query->and_where_close();

            if( !self::validAsc( $sortasc ) )
                $sortasc = 'asc';
            if( strtolower($sortby) !== null && self::validSortableFields( $sortby ) )
                $query->order_by( $sortby, $sortasc );
            else
                $query->order_by( 'p.price', 'desc' );
//            echo Kohana::debug($query_c->__toString());
            $count = $query_c
                            ->execute()
                            ->get( 'mycount' );

            if( isset( $_POST['page'] ) )
                $_GET['page'] = $_POST['page'];
            
            if( $rows_per_page )
            {
                $pagination = Pagination::factory( array(
                            'total_items' => $count,
                            'items_per_page' => $rows_per_page,
                        ) );
                $query
                    ->cached(180)
                    ->limit( $pagination->items_per_page )
                    ->offset( $pagination->offset )
                    ;
                $page_links_str = $pagination->render( 'pagination/digg' );
            }

            $result = $query->execute();


            if( $result->count() == 0 )
                return array( array( ), '', array( ) );
            else
            {

                $resultArr = $result->as_array( 'id' );
                $return = array( );

                foreach( $resultArr as $key => $val )
                {
                    $query = Db::select( 'p.price_categories_id', 'p.currencies_id', 'p.price' )
                                    ->from( array( 'shop_prices', 'p' ) )
                                    ->where( 'p.good_id', '=', $key )
                                    ->order_by( 'p.currencies_id' )
                                    ->cached( 30 )
                    ;

                    if( is_numeric( $price_categoty ) )
                        $query->where( 'p.price_categories_id', '=', (int) $price_categoty );

                    $prices = array( );
                    $rows = $query->execute()->as_array();
                    if( $val['quantity'] == 0 )
                        $val['store_approximate_date'] = Model_Frontend_Goods::getApproximateDate( $val['store_approximate_date'] );

                    $val['prices'] = $curr->getPrice( $rows );
                    $val['seo_url'] = $val['seo_url'] ? str_replace( self::$url_search, self::$url_replace, urlencode( $val['seo_url'] ) ) : str_replace( self::$url_search, self::$url_replace, Controller_Admin::to_url( $val['name'] ));
                    $val['seo_url'] = str_replace( '__', '_', $val['seo_url'] );
                    $val['seo_url'] = str_replace( '__', '_', $val['seo_url'] );
                    $val['category_url'] = $val['category_url'] ? str_replace( self::$url_search, self::$url_replace, urlencode( $val['category_url'] ) ) : str_replace( self::$url_search, self::$url_replace, Controller_Admin::to_url( $val['category_name'] ));
                    $val['category_url'] = str_replace( '__', '_', $val['category_url'] );
                    $val['category_url'] = str_replace( '__', '_', $val['category_url'] );
                    $return[$key] = $val;
                }


                $retPag = $rows_per_page ? $pagination->total_pages : 1;
                
                return array( $return, $page_links_str, $retPag );
            }
        }
    }

    static public function search( $rows_per_page = 10, $srchWhat, $sortby = null, $sortasc = 'asc', $brandId = null, $filter = array( ), $lang_id = 1 )
    {
        $curr = new Model_Frontend_Currencies();

        $price_categories_id = 1;
        $ids = array( );
        $rows = Model_Frontend_Categories::child_categories();
//        $childs = Model_Frontend_Categories::find_inner_categories($rows);

        $query_c = DB::select( DB::expr( 'COUNT(*) AS mycount' ) )
                        ->from( array( 'shop_goods', 'g' ) )
                        ->join( array( 'shop_categories', 'c' ) )
                        ->on( 'g.categories_id', '=', 'c.id' )
                        ->join( array( DB::expr( '(select price,good_id  from `shop_prices` where currencies_id = ' . $curr->getDefaultCurrency() . ' and price_categories_id = ' . $price_categories_id . ' group by good_id)' ), 'p' ), 'left outer' )
                            ->on( 'p.good_id', '=', 'g.id' )
                        ->where( 'p.price', '>', '0' )
                        ->and_where( 'c.languages_id', '=', $lang_id )
                
                        ->and_where_open()
                        ->or_where( 'g.name', 'like', "%" . $srchWhat . "%" )
                        ->or_where( 'g.alter_name', 'like', "%" . $srchWhat . "%" )
                        ->or_where( 'g.short_description', 'like', "%" . $srchWhat . "%" )
                        ->and_where_close()
        ;
        $query_c->and_where_open();
        
        foreach( Kohana::config('shop.frontendStatuses') as $key => $item )
        {
            $query_c->or_where( "g.status", '=', $item );
        }
        $query_c->and_where_close();

        
        $query = Db::select( 'g.*', array( 'c.name', 'category_name' ), array( 'c.seo_url', 'category_url' ), array( 'p.price', 'price' ), array(DB::expr('IF(g.quantity > 0, 1, 0)'), 'exist') )
                        ->from( array( 'shop_goods', 'g' ) )
                        ->join( array( 'shop_categories', 'c' ) )
                        ->on( 'g.categories_id', '=', 'c.id' )
                        ->join( array( DB::expr( '(select price,good_id  from `shop_prices` where currencies_id = ' . $curr->getDefaultCurrency() . ' and price_categories_id = ' . $price_categories_id . ' group by good_id)' ), 'p' ), 'left outer' )
                            ->on( 'p.good_id', '=', 'g.id' )
                        ->where( 'p.price', '>', '0' )
                        ->and_where( 'c.languages_id', '=', $lang_id )
                
                        ->and_where_open()
                        ->or_where( 'g.name', 'like', "%" . $srchWhat . "%" )
                        ->or_where( 'g.alter_name', 'like', "%" . $srchWhat . "%" )
                        ->or_where( 'g.short_description', 'like', "%" . $srchWhat . "%" )
                        ->and_where_close()
                        
                        ->order_by( 'exist', 'desc' )
                        ->order_by( 'p.price', 'desc' )
        ;
        $query->and_where_open();
        foreach( Kohana::config('shop.frontendStatuses') as $key => $item )
        {
            $query->or_where( "g.status", '=', $item );
        }
        $query->and_where_close();



        if( $brandId !== null )
        {
            $query_c->and_where( 'brand_id', '=', $brandId );
            $query->and_where( 'brand_id', '=', $brandId );
        }

        $ids = self::getFoundGoods( $srchWhat );

        if( !empty( $ids ) )
        {
            $query_c->or_where( 'g.id', 'in', DB::expr( "(" . implode( ",", $ids ) . ")" ) );
            $query->or_where( 'g.id', 'in', DB::expr( "(" . implode( ",", $ids ) . ")" ) );
        }




        if( isset( $filter['add_fields'] ) )
        {
            $ids = self::getFilteredGoods( $filter['add_fields'] );
            if( empty( $ids ) )
                $ids = array( 0 );
            $query_c->and_where( 'g.id', 'in', DB::expr( "(" . implode( ",", $ids ) . ")" ) );
            $query->and_where( 'g.id', 'in', DB::expr( "(" . implode( ",", $ids ) . ")" ) );
        }

//        Не помню за что отвечает этот код
//        if( !self::validAsc( $sortasc ) )
//            $sortasc = 'asc';
//        if( $sortby !== null && self::validSortableFields( $sortby ) )
//            $query->order_by( $sortby, $sortasc );
//        else
//            $query->order_by( 'g.name', $sortasc );

        $count = $query_c
                        ->cached( 20 )
                        ->execute()
                        ->get( 'mycount' );

        if( isset( $_POST['page'] ) )
            $_GET['page'] = $_POST['page'];
        
        $pagination = Pagination::factory( array(
                    'total_items' => $count,
                    'items_per_page' => $rows_per_page,
                ) );

        $result = $query->cached( 30 )
                        ->limit( $pagination->items_per_page )
                        ->offset( $pagination->offset )
                        ->execute();

        $page_links_str = $pagination->render( 'pagination/digg' );

        if( $result->count() == 0 )
            return array( array( ), '', array( ) );
        else
        {

            $resultArr = $result->as_array( 'id' );
            $return = array( );


            foreach( $resultArr as $key => $val )
            {
                $query = Db::select( 'p.price_categories_id', 'p.currencies_id', 'p.price' )
                                ->from( array( 'shop_prices', 'p' ) )
                                ->where( 'p.good_id', '=', $key )
                                ->order_by( 'p.currencies_id' )
                                ->cached( 30 )
                ;

                if( is_numeric( $price_categoty ) )
                    $query->where( 'p.price_categories_id', '=', (int) $price_categoty );

                if( $val['quantity'] == 0 )
                        $val['store_approximate_date'] = Model_Frontend_Goods::getApproximateDate( $val['store_approximate_date'] );
                
                $prices = array( );
                $rows = $query->execute()->as_array();
                $val['prices'] = $curr->getPrice( $rows );
                $val['seo_url'] = $val['seo_url'] ? str_replace( self::$url_search, self::$url_replace, urlencode( $val['seo_url'] ) ) : str_replace( self::$url_search, self::$url_replace, Controller_Admin::to_url( $val['name'] ));
                $val['seo_url'] = str_replace( '__', '_', $val['seo_url'] );
                $val['seo_url'] = str_replace( '__', '_', $val['seo_url'] );
                $val['category_url'] = $val['category_url'] ? str_replace( self::$url_search, self::$url_replace, urlencode( $val['category_url'] ) ) : str_replace( self::$url_search, self::$url_replace, Controller_Admin::to_url( $val['category_name'] ));
                $val['category_url'] = str_replace( '__', '_', $val['category_url'] );
                $val['category_url'] = str_replace( '__', '_', $val['category_url'] );
                $return[$key] = $val;
            }




            return array( $return, $page_links_str, $pagination->total_pages );
        }
    }

    static public function validSortableFields( $field )
    {
        $possFields = array( 'name', 'price', 'rating' );

        if( in_array( $field, $possFields ) )
            return true;

        return false;
    }

    static public function validAsc( $asc )
    {
        $possValues = array( 'asc', 'desc' );

        if( in_array( $asc, $possValues ) )
            return true;

        return false;
    }

    static public function getFilterableFields( $categoryId = 0 )
    {
        if( self::$_filtableFields !== null && $categoryId === self::$_categoryId )
            return self::$_filtableFields;
        self::$_categoryId = $categoryId;
        $res = DB::select( 'p.*' )
                        ->from( array( 'shop_goods_properties', 'p' ) )
                        ->join( array( 'shop_categories', 'c' ) )
                        ->on( 'p.category_id', '=', 'c.id' )
                        ->where( 'c.languages_id', '=', CURRENT_LANG_ID )
                        ->where( 'is_filterable', '=', 1 );
        if( $categoryId )
            $res->where( 'category_id', '=', $categoryId );

        $filtableFields = $res->cached( '60' )
                        ->execute()
                        ->as_array( 'alias' );
        ;

        foreach( $filtableFields as &$field )
        {
            if( $field['type'] == 'select' )
            {
                $field['defaults'] = explode( '|', $field['defaults'] );
            }
        }
        self::$_filtableFields = $filtableFields;
        return $filtableFields;
    }

    static public function getFilteredGoods( $properties )
    {
        $sql = "SELECT vse.* " .
                "FROM (SELECT shop_goods_properties_values.*, UPPER(GROUP_CONCAT(value_text ,'|' )) AS grc
                    FROM shop_goods_properties_values
                    GROUP BY good_id) as vse WHERE ";

        foreach( $properties as $propId => $propData )
        {
            $propId = intval( $propId );
//            $sql .= " (";
//            $sql .= "properties_category_id = $propId AND ";
            $sql .= str_replace( 'value_text', 'vse.grc', self::getMySQLCond( $propData ) ) . " AND";
        }
        $sql = rtrim( $sql, ' AND' );
//Kohana_log::instance()->add('d', Kohana::debug( $sql ));
        $res = DB::query( Database::SELECT, $sql )->cached( 10 )->execute()->as_array();
        $resultArray = array( );
        if( !$res )
            return array( -1 );
        foreach( $res as $param )
        {
            $resultArray[$param['good_id']][] = $param['properties_category_id'];
        }

        $goodIds = array( );

        foreach( $resultArray as $goodId => $goodPropertyIds )
        {
            if( array_search( 0, array_keys( $properties ) ) !== 0 )
            {
                $temp = array_diff( array_keys( $properties ), $goodPropertyIds );
                if( empty( $temp ) )
                {
                    $goodIds[] = $goodId;
                }
            }else
            {
                $goodIds[] = $goodId;
            }
        }

        return $goodIds;
    }

    static public function getFilteredGoodsForCategory( $properties )
    {
        $sql = "SELECT * FROM shop_goods_properties_values WHERE CASE ";

        foreach( $properties as $propId => $propData )
        {
            $propId = intval( $propId );
            $sql .= "when properties_category_id = " . (int) $propId . " then " . self::getMySQLCond( $propData );
        }
        $sql .= " end";

        $res = DB::query( Database::SELECT, $sql )->cached( 10 )->execute()->as_array();
        $resultArray = array( );
        if( !$res )
            return array( -1 );
        foreach( $res as $param )
        {
            $resultArray[$param['good_id']][] = $param['properties_category_id'];
        }

        $goodIds = array( );

        foreach( $resultArray as $goodId => $goodPropertyIds )
        {
//            if( array_search(0, array_keys($properties) ) !== 0 )
//            {
            $temp = array_diff( array_keys( $properties ), $goodPropertyIds );
            if( empty( $temp ) )
            {
                $goodIds[] = $goodId;
            }
//            }else{
//                $goodIds[] = $goodId;
//            }
        }
        return $goodIds;
    }

    static public function getFoundGoods( $srchWhat )
    {
        $sql = "select * from shop_goods_properties_values where " .
                "value_text like " . Database::instance()->escape( "%" . $srchWhat . "%" ) .
                " or value_varchar like " . Database::instance()->escape( "%" . $srchWhat . "%" )
                . "";

        $res = DB::query( Database::SELECT, $sql )->cached( 10 )->execute()->as_array( 'good_id' );
        return array_keys( $res );
    }

    static public function getMySQLCond( $data, $search = false )
    {
        $possTypes = array( 'int', 'float', 'date', 'text', 'varchar' );
        if( $data['type'] == 'select' )
            $data['type'] = 'text';
        if( !in_array( $data['type'], $possTypes ) )
            throw new Exception( 'Incorrect data' );

        if( $search )
            $cond = array( "", "" );
        else
            $cond = array( 'from' => ">", 'to' => "<" );

        if( is_array( $data['data'] ) )
        {
            $where = array( );
            foreach( $data['data'] as $key => $p )
            {
                $where[] = " value_{$data['type']} {$cond[$key]}= " . Database::instance()->escape( $data['data'][0] ) . "";
            }
            return implode( " and ", $where );
        }else
        if( $data['type'] == 'text' || $data['type'] == 'varchar' )
            return " value_{$data['type']} like UPPER(" . Database::instance()->escape( "%" . $data['data'] . '%' ) . ") ";
        else
            return " value_{$data['type']} = " . Database::instance()->escape( $data['data'] ) . " ";
    }

    function getLastNewGoods()
    {
        $catids = Kohana::config('shop.topCatsToNew');
    }
}