<?php
/**
 * @package     JLex Review
 * @version     4.2.3
 * @copyright   Copyright (c) 2013-2018 JLexArt. All rights reserved
 * @license     GNU General Public License version 2 or later;
 * @author      www.jlexart.com
 */

defined ( "_JEXEC" ) or die;

class JLexReviewModelReviews extends JModelLegacy
{
    public      $item_id        = 0;

    public      $itemConfig     = null;

    protected   $item           = null;

    protected   $fields_type    = array();

    public function getItem()
    {
        $config  = JLexReviewHelperAdmin::getConfig();
        $query   = $this->_db->getQuery(true);

        $query->clear()
              ->select('*')
              ->from('#__jlexreview_entry');

        if($this->item_id>0)
        {
            $query->where('id='.$this->_db->quote($this->item_id));
        } else {
            $query->where(array(
                    'object='.$this->_db->quote($this->itemConfig->object),
                    'object_id='.$this->_db->quote($this->itemConfig->object_id),
                ));
        }

        $row = $this->_db->setQuery($query)->loadObject();
        if($row)
        {
            switch(intval($config->get('link_type',0)))
            {
                case 0:
                    // auto update link by visible page
                    if(!empty($this->itemConfig) && $config->get('embed_page',0)==0 && ($config->get('jdisplay', 'both')=='both' || $config->get('jdisplay', 'both')!='review'))
                    {
                        $fields = array();

                        if(!empty($this->itemConfig->name) && $this->itemConfig->name!=$row->object_name)
                        {
                            $row->object_name = $this->itemConfig->name;
                            $fields[] = $this->_db->quoteName('object_name').'='.$this->_db->quote($this->itemConfig->name);
                        }

                        if(!empty($this->itemConfig->url) && $this->itemConfig->url!=$row->url)
                        {
                            // remote query review_id parameter
                            if(preg_match('/\?/', $this->itemConfig->url))
                            {
                                list($baseUrl, $queries) = explode('?', $this->itemConfig->url);
                                parse_str($queries, $output);
                                unset($output['review_id']);
                                $queries = http_build_query($output);

                                $this->itemConfig->url = $baseUrl . ($queries!=''?'?'.$queries:'');
                            }

                            $row->url = $this->itemConfig->url;
                            $fields[] = $this->_db->quoteName('url').'='.$this->_db->quote($this->itemConfig->url);
                        }

                        if(count($fields)>0)
                        {
                            $conditions = array(
                                $this->_db->quoteName('id') . '=' . $this->_db->quote($row->id),
                            );

                            $query->clear()->update($this->_db->quoteName('#__jlexreview_entry'))->set($fields)->where($conditions);
                            $this->_db->setQuery($query)->execute();
                        }
                    }
                    break;

                case 1:
                    // from router file
                    $route = JLexReviewHelperAdmin::getItemDetail(
                                    $row->object,
                                    $row->object_id,
                                    $row->object_name,
                                    $row->url);

                    $row->url = $route->url;
                    $row->object_name = $route->title;
                    break;

                case 2:
                    // sync
                    $sync = JLexReviewHelperAdmin::getSync($row->object, $row->object_id);
                    $sync->action("entry_details");
                    break;
            }

            if(!empty($row->schema))
            {
                $config->set('schema', $row->schema);
            }
        } elseif(!empty($this->itemConfig)) {
            // add new row
            $row = $this->getTable("Object", "TableJR");
            $section_file = $config->get("jsection",1);

            $query->clear()
                  ->select('section_file')
                  ->from('#__jlexreview_section')
                  ->where(array(
                        $this->_db->quoteName('object') . '=' . $this->_db->quote($this->itemConfig->object),
                    ))
                  ->order('section_id ASC');

            if($this->itemConfig->section_id>0)
            {
                $query->where('(section_id=0 OR section_id='.$this->_db->quote($this->itemConfig->section_id).')');
            } else {
                $query->where('section_id=0');
            }

            $list_form_ids = $this->_db->setQuery($query)->loadObjectList();
            if (count($list_form_ids)>1)
            {
                $last_item    = end($list_form_ids);
                $section_file = $last_item->section_file;
            } elseif (count($list_form_ids)>0) {
                $section_file = $list_form_ids[0]->section_file;
            }

            $row->bind(array(
                "object"        => $this->itemConfig->object,
                "object_name"   => $this->itemConfig->name,
                "object_id"     => $this->itemConfig->object_id,
                "section_id"    => $this->itemConfig->section_id,
                "section_file"  => $section_file,
                "url"           => $this->itemConfig->url,
                "editor_content" => "0",
                "extra_content"  => ""
            ));

            // extra some params
            $row->rating = number_format(0,1);

            if (!$row->check() || !$row->store())
            {
                return null;
            }

            // sync - author subscription
            $sync = JLexReviewHelperAdmin::getSync($this->itemConfig->object, $this->itemConfig->object_id);
            $sync->action("author_follow", $row->id);
        } else {
            return null;
        }

        $this->item_id = $row->id;

        // get form
        $query->clear()
              ->select('params')
              ->from('#__jlexreview_form')
              ->where('id='.$this->_db->quote($row->section_file));

        $form = $this->_db->setQuery($query)->loadResult();
        if($form)
        {
            $row->form = json_decode($form);
            if(count($row->form->fs_data))
            {
                foreach($row->form->fs_data as $field)
                {
                    $this->fields_type[$field->id] = $field->type;
                    if (isset($field->rating) && $field->rating==1)
                    {
                        $this->fields_type[$field->rating_data->id] = "rating";
                    }
                }
            }

            unset($form);
        }

        // restriction
        $limit_post = $config->get('review_limit_user',1)*1;
        if($limit_post>0)
        {
            $user       = JFactory::getUser();
            $ip_address = JLexReviewHelperAdmin::ip_address();

            $query->clear()
                  ->select('COUNT(id)')
                  ->from('#__jlexreview')
                  ->where('entry_id='.$this->_db->quote($row->id));

            if($user->guest)
            {
                $query->where('ip_address='.$this->_db->quote($ip_address));
            } else {
                $query->where('created_by='.$this->_db->quote($user->id));
            }

            $reviewCount = $this->_db->setQuery($query)->loadResult();
            if($reviewCount>=$limit_post)
            {
                $config->set('u_post_review', false);
                $config->set('too_many_review', true);
            }
        }

        // process url format
        $row->url = str_replace(JUri::root(true), '', urldecode($row->url));
        $row->url = trim($row->url, '/');
        $row->url = preg_match("/^https?:\/\//", $row->url) ? $row->url : JUri::root().$row->url;

        if(empty($row->url2post))
        {
            $row->url2post = JRoute::_('index.php?option=com_jlexreview&view=form&id='.$row->id,false);
        } else {
            $row->url2post = preg_match('/^http/', $row->url2post)?$row->url2post:(JUri::root(true).'/'.$row->url2post);
        }

        // assign to use for another functions
        // #Jan 6, 2019
        if($row->rating==5) $row->rating=5;

        $this->item = $row;
        return $row;
    }

    public function getForm()
    {
        $this->addIncludePath( JREVIEW_ADMIN . "/models" );
        $ratingModel = $this->getInstance('Rating', 'JLexReviewModel');
        $ratingModel->set('form_id', $this->item->section_file);

        $form = $ratingModel->getForm();

        if(!$form)
        {
            $ratingModel->set('form_id', 1);
            $query = $this->_db->getQuery(true);
            $query->update('#__jlexreview_entry')
                  ->set('section_file=1')
                  ->where('id='.$this->_db->quote($this->item->id));

            $this->_db->setQuery($query)->execute();

            $form = $ratingModel->getForm();
        }

        $form->bind(array(
                "entry_id" => $this->item_id,
            ));

        return $form;
    }


    public $timestamp = 0;

    public $timestamp_offset = 0;

    public $sort    = "best";

    public $offset  = 0;

    public $limit   = 10;

    public $total   = 0;

    public $page    = 1;

    public $rid     = 0;

    public $cmid    = 0;

    public $filters = [];

    public function getReviews()
    {
        $app        = JFactory::getApplication();
        $user       = JFactory::getUser();
        $now        = JFactory::getDate()->toSql();
        $session    = JFactory::getSession();
        $config     = JLexReviewHelperAdmin::getConfig();
        $ip_address = JLexReviewHelperAdmin::ip_address();
        $passed_id  = $session->get('jreview_id_passed',0); // id of review just written

        // $passed_id: Normally use for review is marked unpublished.
        if($passed_id>0)
        {
            $this->rid=0;
            $session->clear('jreview_id_passed');
        }

        $wC = array(
                'r.entry_id='.$this->_db->quote($this->item_id),
            );

        $userConditions = $user->guest ? "ip_address=".$this->_db->quote($ip_address) : "created_by=" . $this->_db->quote($user->id);

        $query  = $this->_db->getQuery(true);
        $sQuery = $this->_db->getQuery(true);

        $query->select("SQL_CALC_FOUND_ROWS r.*")
              ->from("#__jlexreview AS r");

        // language query
        $langQuery = $this->_db->getQuery(true);
        $langQuery->select("r.language")
                  ->from("#__jlexreview AS r");

        // user table
        $query->select("r.author_name")
              ->select("IF(r.created_by=0,r.author_email,u.email) author_email")
              ->leftJoin("#__users AS u ON u.id=r.created_by");

        // helpful
        if( $config->get('u_helpful_vote',false) == true )
        {
            $query->select('IF(helpful.rate_match>-1, helpful.rate_match, NULL) is_helpful')
                  ->leftJoin ('#__jlexreview_helpful AS helpful ON (helpful.type_id=r.id AND helpful.type_ob=1 AND helpful.' .$userConditions. ')');
        }

        // reporting
        if($config->get("u_reporting",false)==true)
        {
            $query->select('IF(report.id>0,1,0) reported')
                  ->leftJoin ('#__jlexreview_report AS report ON (report.type_id=r.id AND report.type_ob=1 AND report.' .$userConditions. ')');
        }

        // subscriber
        if($config->get("u_subscribe",false)==true)
        {
            $query->select('IF(sub.id>0,1,0) subscribed')
                  ->leftJoin ('#__jlexreview_subscribe AS sub ON (sub.sub_value=r.id AND sub.sub_type=1 AND sub.userid='.$user->id.')');
        }

        if($config->get("u_edit_any_review",false)==false)
        {
            $owner = null;
            if($user->guest)
            {
                $owner = "(r.created_by=0 AND r.ip_address=" . $this->_db->quote($ip_address). ")";
            } else {
                $owner = "r.created_by=" . $this->_db->quote($user->id);
            }
            
            $wC[]=$owner!=null?'(r.published=1 OR '.$owner.')':'r.published=1';
        }

        // NEXT

        if($config->get('vote_as_review','0')=='0') $wC[]='r.voting=0';

        if($this->timestamp>0)
        {
            $wC[]='r.created<='.$this->_db->quote(JFactory::getDate($this->timestamp)->toSql());
        } else {
            $wC[]='r.created<='.$this->_db->quote($now);
        }

        if($this->timestamp_offset>0)
        {
            $wC[]='r.created>='.$this->_db->quote(JFactory::getDate($this->timestamp_offset)->toSql());
        }

        $this->limit = $config->get('review_each_load',10)>0?intval($config->get('review_each_load')):10;

        /* link to special review - need to remove all filter */
        if ($this->rid>0||$passed_id>0)
        {
            // remove all filter/sorting by cookie
            /**
            if (array_key_exists("jreview_filter", $_COOKIE))
            {
                unset( $_COOKIE['jreview_filter'] );
                setcookie('jreview_filter', null, -1, '/');
            }
            **/

            if (array_key_exists("jreview_sort", $_COOKIE))
            {
                unset( $_COOKIE['jreview_sort'] );
                setcookie('jreview_sort', null, -1, '/');
            }
        } else {
            /** FILTER VARIABLES **/
            if(is_array($this->filters) && count($this->filters))
            {
                // star level
                if(array_key_exists("star", $this->filters) && is_array($this->filters["star"]))
                {
                    $orClauses = array();
                    foreach ($this->filters["star"] as $star)
                    {
                        if ($star<1) continue;

                        if ($config->get("half_star",0)==1)
                        {
                            $min = intval($star) - 1;
                            $max = intval($star);
                            $orClauses[] = "(r.rating<=".$this->_db->quote($max)." AND r.rating>=".$this->_db->quote($min).")";
                        } else {
                            $orClauses[] = "r.rating=" . $this->_db->quote(intval($star));
                        }
                    }

                    if (count($orClauses))
                    {
                        $wC[]='(' . implode(' OR ', $orClauses) . ')';
                    }
                }

                // search query
                if (array_key_exists("q", $this->filters) && !preg_match("/^\s*$/", $this->filters["q"]))
                {
                    $keywords = $this->_db->quote('%' . $this->filters["q"] . '%');
                    $sQuery->clear()
                           ->select('review_id AS id, GROUP_CONCAT(field_value, "") AS pretext')
                           ->from('#__jlexreview_fields')
                           ->where('(field_type='.$this->_db->quote('textarea').' OR field_type='.$this->_db->quote('text').')')
                           ->group('review_id');

                    $query->leftJoin('('.$sQuery->__toString().') AS tf ON tf.id=r.id');

                    $wC[]="(tf.pretext LIKE ".$keywords." OR r.title LIKE ".$keywords." )";

                    $langQuery->leftJoin('('.$sQuery->__toString().') AS tf ON tf.id=r.id')
                              ->where("(tf.pretext LIKE ".$keywords." OR r.title LIKE ".$keywords.")");
                }

                // field content
                /*if ($this->entry_id>0)*/
                {
                    $orClauses = array();
                    foreach($this->filters as $k=>$v)
                    {
                        if (!array_key_exists($k, $this->fields_type) || !is_array($v) || !count($v))
                        {
                            continue;
                        }

                        if ($this->fields_type[$k] == "checkbox")
                        {
                            $whereClausesSmall = array(
                                    "field_id={$k}",
                                    "field_type=" . $this->_db->quote("checkbox")
                                );
                            foreach ($v as $f1=>$v1)
                            {
                                if (!preg_match('/^[A-z0-9\_\-]+$/', $v1))
                                {
                                    continue;
                                }
                                $regexSql = "'(\\\||^)" .$v1. "(\\\||$)'";
                                $whereClausesSmall[] = "field_value REGEXP ". $regexSql;
                            }

                            if (count($whereClausesSmall)>2)
                            {
                                $orClauses[] = "(" . implode(" AND ", $whereClausesSmall) . ")";
                            }
                        } elseif (in_array($this->fields_type[$k], array("select","radio"))) {
                            foreach ($v as $f1=>$v1)
                            {
                                $orClauses[] = "(field_id={$k} AND field_value=". $this->_db->quote($v1) .")";
                            }
                        }
                    }

                    $fieldConditions = count($orClauses);
                    if ($fieldConditions > 0)
                    {
                        $sQuery->clear()
                               ->select('review_id AS id')
                               ->from('#__jlexreview_fields')
                               ->where(array(
                                    'entry_id='.$this->_db->quote($this->item_id),
                                    '('.implode(" OR ", $orClauses).')',
                                ))
                               ->group('review_id');
                        
                        $query->leftJoin('('.$sQuery->__toString().') AS tf1 ON tf1.id=r.id');
                        $wC[]="tf1.id IS NOT NULL";

                        $langQuery->leftJoin('('.$sQuery->__toString().') AS tf1 ON tf1.id=r.id')
                                  ->where('tf1.id IS NOT NULL');
                    }
                }

                // language
                if (array_key_exists("lang", $this->filters) && is_string($this->filters["lang"]) && $this->filters["lang"]!="*")
                {
                    $langCondition="LOWER(r.language)=" . $this->_db->quote($this->filters["lang"]);
                }
            }
            /** END FILTER **/
        }

        if($passed_id>0)
        {
            $wClause = '(r.id='.$this->_db->quote($passed_id).' OR ('.implode(' AND ', $wC).'))';
            $query->order('FIELD(r.id,'.$this->_db->quote($passed_id).') DESC');
            //$query->order('CASE r.id WHEN '.$this->_db->quote($passed_id).' THEN 1 ELSE 0 END DESC');
        } else {
            $wClause = $wC;
        }
        
        $query->where($wClause);
        $langQuery->where($wClause);

        if($config->get("toolbar_language_filter",0)==1)
        {
            if(isset($langCondition)) $query->where($langCondition);
            
            $langQuery->select("COUNT(r.id) count_item")
                      ->group("LOWER(r.language)");

            $this->langTags = $this->_db->setQuery($langQuery)->loadObjectList();
            $langfilter = $config->get('flang',0)>0?true:false;

            if($langfilter==true)
            {
                $langCurrent = JFactory::getLanguage()->getTag();
                $langExist   = false;

                $jl = new stdClass();
                $jl->language = $langCurrent;
                $jl->count_item = "0";

                if(!empty($this->langTags))
                {
                    foreach($this->langTags as $langTag)
                    {
                        if($langCurrent==$langTag->language)
                        {
                            $langExist = true;
                            break;
                        }
                    }

                    if(!$langExist) $this->langTags[]= $jl;
                } else {
                    $this->langTags=[$jl];
                }

                if(!isset($langCondition) && (empty($this->filters) || !isset($this->filters["lang"])))
                {
                    $langCondition="LOWER(r.language)=" . $this->_db->quote($langCurrent);
                    $query->where($langCondition);

                    /*
                    if(!isset($filter)){
                        $newfilter=["lang"=>$langCurrent];
                    } else {
                        $newfilter=$filter;
                        $newfilter["lang"]=$langCurrent;
                    }

                    setcookie('jreview_filter', json_encode($newfilter), time()+3600, '/');
                    */
                }
            }
        }

        // sorting clause
        $query->order('r.featured DESC');
        switch ( $this->sort ) {
            case 'best':
                $query->order ('(r.count_help-r.count_unhelp) DESC, r.count_help DESC, r.id DESC');
                break;

            case 'popular':
                $query->order ('r.count_cm DESC, (r.count_help+r.count_unhelp) DESC, r.created DESC');
                break;

            case 'desc':
                $query->order ('r.created DESC');
                break;

            case 'modified':
                $query->order ('r.modified DESC, r.created DESC');
                break;

            case 'asc':
                $query->order ('r.created ASC');
                break;

            default:
                // sort by field
                $sort_by_field = false;
                if (!$this->rid && preg_match("/^([1-9][0-9]*)\s(ASC|DESC)$/i", $this->sort, $matches))
                {
                    if (array_key_exists($matches[1], $this->fields_type) && $this->fields_type[$matches[1]]=="rating")
                    {
                        $sQuery->clear()
                               ->select('review_id, field_value AS sortCol')
                               ->from('#__jlexreview_fields')
                               ->where(array(
                                    'field_id='.$this->_db->quote($matches[1]),
                                    'entry_id='.$this->_db->quote($this->item_id),
                                ))
                               ->group('review_id');

                        $query->leftJoin('('.$sQuery->__toString().') sotable ON sotable.review_id=r.id')
                              ->order('sotable.sortCol ' . $matches[2]);

                        $sort_by_field = true;
                    }
                }

                if(!$sort_by_field) $this->sort = 'best';
                break;
        }

        
        if($this->rid>0)
        {
            // find page if have highlight review
            $this->page = $this->_findId($this->rid,$this->limit,$wC);
            if($this->page<1) $this->page = 1;
        }

        $this->offset = ($this->page-1)*$this->limit;
        $this->offset = $this->offset>0?$this->offset:0;

        $reviews = $this->_db->setQuery($query, $this->offset, $this->limit)->loadObjectList();
        $this->total = $this->_db->setQuery("SELECT FOUND_ROWS()")->loadResult();

        if (!$reviews)
        {
            // not found any review!
            return null;
        }

        // Comment model class
        $commentModel = $this->getInstance('Comments', 'JLexReviewModel');
        $userProfile  = JLexReviewHelperSite::getProfile();
        $this->_getReplacer();

        foreach ($reviews as $k=>$review)
        {
            $reviews[$k]->params = json_decode($review->params);
            $reviews[$k]->author = $userProfile->getUser($review->created_by, $review->author_email, $review->author_name); // author profile

            // date format
            $format_date = $config->get("review_date_format","default")=="default"?null:$config->get("review_date_format");
            $reviews[$k]->created_format = JLexReviewHelperSite::formatTime($review->created, null, $format_date);

            // editing permission
            $reviews[$k]->can_edit = false;
            if($config->get("u_edit_any_review",false)==true)
            {
                $reviews[$k]->can_edit = true;
            } else {
                if ($review->created_by>0 && $review->created_by==$user->id && $config->get("u_edit_own_review"))
                {
                    $reviews[$k]->can_edit = true;
                }
            }

            // url to modify
            $reviews[$k]->url = JRoute::_("index.php?option=com_jlexreview&view=review&id=" . $review->id,false);

            // delete permission
            $reviews[$k]->can_delete = false;
            if($config->get("u_del_any_review",false)==true)
            {
                $reviews[$k]->can_delete = true;
            } else {
                if ($review->created_by>0 && $review->created_by==$user->id && $config->get("u_del_own_review"))
                {
                    $reviews[$k]->can_delete = true;
                }
            }

            // google map location
            $reviews[$k]->map = false;
            if(isset($review->params->map_address) && !preg_match("/^\s*$/", $review->params->map_address))
            {
                $reviews[$k]->map = true;
            }

            // load fields
            $fields = $this->_getFields($review->id);

            if ($fields)
            {
                // prepare field content
                foreach ($fields as $j=>$field)
                {
                    if(!array_key_exists($j, $this->fields_type)) continue;
                    
                    if($this->fields_type[$j]=="textarea" || $this->fields_type[$j]=="text")
                    {
                        if($this->replaceClause)
                        {
                            foreach($this->replaceClause as $rClause)
                            {
                                $fields[$j]->field_value = preg_replace ($rClause->regexClause, $rClause->replaceClause, $field->field_value);
                            }
                        }

                        JLexReviewHelperSite::lowWords($fields[$j]->field_value);
                    }
                }
            }

            $reviews[$k]->fields = $fields;

            // get roles
            if($config->get('enable_roles',1)==1) $reviews[$k]->roles = JLexReviewHelperSite::roles($review->created_by);

            // comments
            $reviews[$k]->cmOpen = false;
            if($config->get("comment_enable",1)==1)
            {
                if($config->get('comment_preview_count',3)>0 || $this->cmid>0)
                {
                    $commentModel->set("rid", $review->id);
                    if ($review->id==$this->rid && $this->cmid>0)
                    {
                        $commentModel->set("hl", $this->cmid);
                    } else {
                        $commentModel->set("hl", 0);
                        $commentModel->set("limitstart", 0);
                    }
                    $ct = $commentModel->getComments();
                    
                    if ($ct->total>0)
                    {
                        $reviews[$k]->cmOpen = true; 
                    }
                } else {
                    $ct = new stdClass();
                    $ct->comments = null;
                    $ct->total    = $review->count_cm;
                }
                $reviews[$k]->comments = $ct;
            }

            // get media
            $reviews[$k]->media  = $this->_getMedia($review->id);

            // title
            JLexReviewHelperSite::lowWords($reviews[$k]->title);
        }

        return $reviews;
    }

    public function getPagination()
    {
        $page = new stdClass();
        $page->page  = $this->page;
        $page->total = $this->total%$this->limit==0 ? $this->total/$this->limit : intval($this->total/$this->limit)+1;
        $page->total_rating = $this->total;
        $page->entry_id = $this->item_id;

        return $page;
    }

    protected $replaceClause = null;

    private function _getReplacer()
    {
        $doc = JFactory::getDocument();

        $query = $this->_db->getQuery (true);
        $query->select ("regexClause,replaceClause,params")
              ->from ("#__jlexreview_replacer")
              ->where( array(
                    "published=1",
                    "(area=1 OR area=2)"
                ));

        $this->replaceClause = $this->_db->setQuery($query)->loadObjectList ();

        if ($this->replaceClause)
        {
            foreach ($this->replaceClause as $replace)
            {
                $params = json_decode($replace->params);
                if ($params)
                {
                    if (isset($params->css_file) && !preg_match('/^\s*$/', $params->css_file))
                    {
                        $css_file = preg_match("/^http/", $params->css_file) ? $params->css_file : JUri::root(true) . "/" . $params->css_file;
                        $doc->addStyleSheet( $css_file );
                    }

                    if (isset($params->css_content) && !empty($params->css_content))
                    {
                        $doc->addStyleDeclaration($params->css_content);
                    }

                    if (isset($params->js_file) && !preg_match('/^\s*$/', $params->css_file))
                    {
                        $js_file = preg_match("/^http/", $params->js_file) ? $params->js_file : JUri::root(true) . "/" . $params->js_file;
                        $doc->addScript( $js_file );
                    }

                    if (isset($params->js_content) && !empty($params->js_content))
                    {
                        $doc->addScriptDeclaration($params->js_content);
                    }
                }
            }
        }
    }

    private function _getMedia($rid)
    {
        $query = $this->_db->getQuery(true);
        $query->select("*")
              ->from("#__jlexreview_attachment")
              ->where("review_id=" . $rid);

        $attachments = $this->_db->setQuery($query)->loadObjectList();

        $return = new stdClass();
        $return->photos = array();
        $return->files  = array();

        if (!$attachments)
        {
            return $return;
        }

        foreach ($attachments as $k=>$media)
        {
            if ($media->file_type=="image")
            {
                $media->thumb = JUri::root(true) . '/media/jlexreview/thumb/' . $media->file_name;
                //$media->full = JUri::root(true) . '/' . $media->path;
                $media->full = JUri::root().'index.php?option=com_jlexreview&view=others&task=photo&n='.$media->file_name.'&w=1000';
                $return->photos[] = $media;
            } else {
                if ($media->file_size >= 1000000000)
                {
                    $media->size = number_format($media->file_size / 1000000000, 2) . ' Gb';
                } elseif ($media->file_size >= 1000000) {
                    $media->size = number_format($media->file_size / 1000000, 2) . ' Mb';
                } else {
                    $media->size = number_format($media->file_size / 1000) . ' Kb';
                }
                $return->files[] = $media;
            }
        }

        return $return;
    }

    private function _getFields($rid)
    {
        $query = $this->_db->getQuery(true);
        $query->select("*")
              ->from("#__jlexreview_fields")
              ->where("review_id=" . $rid);

        $fields = $this->_db->setQuery($query)->loadObjectList("field_id");

        return $fields;
    }

    private function _findId($rid, $limit, $extraWhere = array() )
    {
        switch($this->sort)
        {
            case 'best':
                $sortBy = '(t.count_help-t.count_unhelp) DESC, t.count_help DESC, t.id DESC';
                break;

            case 'popular':
                $sortBy = 't.count_cm DESC, (t.count_help+t.count_unhelp) DESC, t.created DESC';
                break;

            case 'desc':
                $sortBy = 't.created DESC';
                break;

            case 'modified':
                $sortBy = 't.modified DESC, t.created DESC';

            default:
                $sortBy = 't.created ASC';
                break;
        }

        $whereClauses = count($extraWhere)>0 ? implode(" AND ", $extraWhere) : "";
        $whereClauses = str_replace("r.", "t.", $whereClauses);

        // find offset
        $query = "
            SELECT r.id, 
                   r.position,
                   r.published
              FROM (SELECT t.id,
                           t.published,
                           @rownum := @rownum + 1 AS position
                      FROM #__jlexreview t
                      JOIN (SELECT @rownum := 0) r
                      WHERE t.entry_id = (SELECT entry_id FROM #__jlexreview WHERE id={$rid})
                      ".($whereClauses!=""?" AND ".$whereClauses:"")."
                  ORDER BY {$sortBy}) r
             WHERE r.id = {$rid}
        ";
        
        $result = $this->_db->setQuery ($query)->loadObject ();

        if(!$result) return 1;

        $roundpage = intval($result->position/$limit);
        $page = $result->position/$limit>$roundpage ? $roundpage+1:$roundpage ;

        return $page > 0 ? $page : 1;
    }

    public function getSummary()
    {
        $return = new stdClass();
        $return->stars      = $this->getStars();
        $return->criteria   = $this->getCriterias();

        return $return;
    }

    private function getStars()
    {
        $config     = JLexReviewHelperAdmin::getConfig();
        $maxStar    = $config->get("rate_max", 5);

        $return     = new stdClass;
        $return->total = 0;
        $return->details = array();

        for ($i=1; $i<=$maxStar; $i++)
        {
            $return->details[$i] = 0;
        }

        $return->details = array_reverse($return->details, true);

        $sub = $config->get("half_star",0)==1 ? "ROUND" : "FLOOR";

        $query = $this->_db->getQuery(true);
        $query->select("{$sub}(rating) star_rating, COUNT(id) count_rating")
              ->from("#__jlexreview")
              ->where(array(
                    "entry_id=" . $this->_db->quote($this->item_id),
                    "published=1",
                    "rating>0"
                ))
              ->group("{$sub}(rating)");

        $result = $this->_db->setQuery($query)->loadObjectList();

        if (!$result)
        {
            return $return;
        }

        foreach ($result as $item)
        {
            if (!array_key_exists($item->star_rating, $return->details))
            {
                continue;
            }
            $return->total+= $item->count_rating;
            $return->details[$item->star_rating*1] = $item->count_rating;
        }

        return $return;
    }

    private function getCriterias()
    {
        if(empty($this->item)) $this->getItem();
        if(empty($this->item) || empty($this->item->form->fs_data)) return null;

        $criteria    = array();
        $criteriaCid = array();

        foreach ($this->item->form->fs_data as $field)
        {
            if ($field->type=="rating")
            {
                $field->label = jtext::_($field->label);
                $field->total_point     = 0;
                $field->total_rating    = 0;
                $field->rating          = 0;

                $criteria[$field->id] = $field;
                $criteriaCid[] = intval($field->id);
            } else {
                if (@$field->rating=="1")
                {
                    $field->rating_data->total_point    = 0;
                    $field->rating_data->total_rating   = 0;
                    $field->rating_data->rating         = 0;
                    $field->rating_data->label          = jtext::_($field->label);

                    $criteria[$field->rating_data->id] = $field->rating_data;
                    $criteriaCid[] = intval($field->rating_data->id);
                }
            }
        }

        if (!count($criteriaCid)) return null;

        $query = $this->_db->getQuery(true);
        $query->select("f.field_id id, SUM(f.field_value*1) total_point, SUM(IF(f.field_value=0,0,1)) total_rating")
              ->from("#__jlexreview_fields f")
              ->leftJoin('#__jlexreview r ON r.id=f.review_id')
              ->where("f.field_id IN (".implode(",", $criteriaCid).")")
              ->where(array(
                    "r.id IS NOT NULL",
                    "r.published=1",
                    "f.field_value>0",
                    "f.entry_id=" . $this->_db->quote($this->item_id)
                ))
              ->group("f.field_id");

        $result = $this->_db->setQuery($query)->loadObjectList();

        if (!$result) return $criteria;

        foreach ($result as $item)
        {
            if (!array_key_exists($item->id, $criteria) || $item->total_rating<=0) continue;

            $criteria[$item->id]->total_point   = $item->total_point;
            $criteria[$item->id]->total_rating  = $item->total_rating;
            $criteria[$item->id]->rating = $item->total_rating>0 ? (number_format($item->total_point/$item->total_rating,1)) : 0;
        }

        return $criteria;
    }

    protected $langTags = null;

    public function getLangTags()
    {
        $langTags = (include JREVIEW_ADMIN . "/libs/langTags.php");

        $tags = array(
                "*" => array(
                        "text" => JText::_("JR_ALL_LANG"),
                        "count" => 0
                    )
            );

        if (!$this->langTags) return $tags;

        $sum = 0;
        foreach ($this->langTags as $item)
        {
            if ($item->language!="*")
            {
                $tags[$item->language] = array(
                        "text" => $langTags[strtolower($item->language)],
                        "count" => $item->count_item
                    );
            } else {
                $tags["*"]["count"] = $item->count_item;
            }

            $sum+= $item->count_item*1;
        }

        $tags["*"]["count"] = $sum;

        return $tags;
    }
}