<?php
/**
 * @package     JLex Review
 * @version     5
 * @copyright   Copyright JLexArt. All rights reserved
 * @license     GNU General Public License version 2 or later;
 * @author      www.jlexart.com
 */

defined ( "_JEXEC" ) or die;

class JLexReviewModelItems extends JModelLegacy
{
	public $limitstart = 0;

	public $limit = 20;

	public $total = 0;

    public $filters = [];

    public $sort = "item.latest_time DESC";

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

		$query->select("SQL_CALC_FOUND_ROWS item.*")
			  ->from("#__jlexreview_entry AS item")
			  ->select("u.username AS author")
			  ->leftJoin("#__users AS u ON item.created_by=u.id")
			  ->select("r.rating AS latest_rating")
			  ->leftJoin("#__jlexreview AS r ON item.latest_id=r.id");

        $wClauses = [];
        if(array_key_exists("q", $this->filters))
        {
            $kw = $this->_db->quote("%".$this->filters["q"]."%");
            $wClauses[] = "item.object_name LIKE ".$kw;
        }

        if(array_key_exists("publish", $this->filters))
        {
            $wClauses[] = "item.published=".$this->_db->quote($this->filters["publish"]);
        }

        if(array_key_exists("object", $this->filters))
        {
            $wClauses[] = "item.object=".$this->_db->quote($this->filters["object"]);
        }

        if(array_key_exists("hl", $this->filters))
        {
            $wClauses[] = "item.id=".$this->_db->quote($this->filters["hl"]);
        }

        if(array_key_exists("section_file", $this->filters))
        {
            $wClauses[] = "item.section_file=".$this->_db->quote($this->filters["section_file"]);
        }
     
		if(count($wClauses)) $query->where($wClauses);

        // sorting
		$query->order($this->sort);

		$items = $this->_db->setQuery($query, $this->limitstart, $this->limit)->loadObjectList();
		$this->total = (int) $this->_db->setQuery("SELECT FOUND_ROWS()")->loadResult();

		if(!$items) return null;

		foreach($items as $k=>&$item)
		{
            if($config->get("link_type",0)=="1")
            {
                $up = JLexReviewHelperAdmin::getItemDetail($item->object, $item->object_id, $item->object_name, $item->url);
                $items[$k]->url = $up->url;
                $items[$k]->object_name = $up->title;
            } else {
                $items[$k]->url = urldecode($item->url);
                $items[$k]->url = preg_match("/^https?:\/\//", $item->url) ? $item->url : JUri::root(true) . "/" . ltrim($item->url,"/");
            }
			
            $items[$k]->rating = number_format($item->rating, 1);
            $items[$k]->url2reviews = JUri::base(true) . "/index.php?option=com_jlexreview&view=reviews&entry_id=" . $item->id;
            $items[$k]->url2author = JUri::base(true) . "/index.php?option=com_users&task=user.edit&id=" . $item->created_by;
            $items[$k]->url2edit = JUri::base(true) . "/index.php?option=com_jlexreview&view=items&layout=form&id=" . $item->id;
		}

		return $items;
	}

	public function getObjects()
	{
		$query = $this->_db->getQuery(true);
		$query->select("object, COUNT(*) AS count_item")
			  ->from("#__jlexreview_entry")
			  ->group("object")
			  ->order("object ASC");

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

		return $objects;
	}

	public function getPagaNav()
	{
		jimport("joomla.html.pagination");
		return new JPagination($this->total, $this->limitstart, $this->limit);
	}

	public $id = 0;

	public function getItem()
	{
		$query = $this->_db->getQuery(true);
		$query->select("*")
			  ->from("#__jlexreview_entry")
			  ->where("id=".$this->_db->quote($this->id));

		$row = $this->_db->setQuery($query)->loadObject();

        if(!$row) return null;

		$row->url = urldecode($row->url);
		return $row;
	}

	public function getForm()
    {
        $path = dirname (__FILE__) . "/forms/item.xml";
        $form = JForm::getInstance("jreview_item", $path);
        $config = JLexReviewHelperAdmin::getConfig();

        $form->addFieldPath(dirname(__FILE__) . "/fields");
        $form->addRulePath(dirname(__FILE__) . "/rules");

        // for editor
        $xml = '<form><fields name="jform">';
        $xml.= '<fieldset name="editor">';
            $xml.= '<field type="jrating" 
                            name="editor_rating" label="JR_RATING" 
                            max="'.$config->def("rate_max",5).'" 
                            first="'.$config->def("rate_default",5).'" 
                            half="'.$config->def("half_star",0).'" 
                            validate="jrating" showon="editor_mode:1" />';
        $xml.= '</fieldset></fields></form>';

        $form->load($xml);

        if($this->id>0)
        {
            $item = $this->getItem();
            if(!$item)
            {
                throw new Exception(JText::_("JR_PAGE_NOT_FOUND"), 404);
            }

            $form->bind(array("jform"=>$item));
        } else {
            // try get tmp data
            $session = JFactory::getSession();
            $data = $session->get("jreview_item_tmp", null);

            if(is_array($data)) $form->bind($data);
        }

        return $form;
    }

    public function save($data)
    {
    	$row = $this->getTable("Object", "TableJR");
    	$row->bind($data["jform"]);

    	if(!$row->check())
    	{
    		$this->setError($row->getError());
    		return false;
    	}

    	if(!$row->store())
    	{
            $this->setError($row->getError());
			//$this->setError(jtext::_('JR_AN_ERROR_WHILE_SAVING_DATA'));
    		return false;
    	}

        // sync - author subscription
        $sync = JLexReviewHelperAdmin::getSync($row->object, $row->object_id);
        $sync->action("author_follow", $row->id);

    	return $row->id;
    }

    public function state($state=1)
    {
    	$app = JFactory::getApplication();
    	$cid = $app->getInput()->get("cid", null, "array");

    	if(!$cid || !count($cid))
    	{
    		throw new Exception("Please first make a selection from the list.", 500);
    		return false;
    	}

        $safe = [];
    	foreach($cid as $id)
    	{
            if(preg_match("/^[1-9][0-9]*$/", $id)) $safe[]=$id;
    	}

        if(!count($safe))
        {
            throw new Exception("Please first make a selection from the list.", 500);
            return false;
        }

        $query = $this->_db->getQuery(true);
        $query->update("#__jlexreview_entry")
              ->set("published=".$this->_db->quote($state))
              ->where("id IN(".implode(",", $safe).")");

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

    	return true;
    }

    public function remove()
    {
    	$app = JFactory::getApplication();
    	$cid = $app->getInput()->get("cid",null,"array");

    	if(!$cid || !count($cid))
    	{
    		throw new Exception("Please first make a selection from the list.", 500);
            return false;
    	}

    	foreach ($cid as $id)
    	{
            if(!preg_match("/^[1-9][0-9]*$/", $id)) continue;

    		$row = $this->getTable("Object", "TableJR");
            $row->load($id);
    		$row->delete();
    	}

    	return true;
    }

    public function truncate()
    {
    	$app = JFactory::getApplication();
    	$cid = $app->getInput()->get("cid",null,"array");

    	if(!$cid || !count($cid))
    	{
    		throw new Exception("Please first make a selection from the list.", 500);
    		return false;
    	}

        $query = $this->_db->getQuery(true);
        $tables = ["#__jlexreview", "#__jlexreview_attachment", "#__jlexreview_cm"];

    	foreach($cid as $id)
    	{
            if(!preg_match("/^[1-9][0-9]*$/", $id)) continue;

            foreach($tables as $tb)
            {
                $query->clear()
                      ->delete($tb)
                      ->where("entry_id=".$this->_db->quote($id));

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

            JLexReviewHelperAdmin::updateEntryParams($id);
    	}

    	return true;
    }

    public function recalculate($all=false)
    {
    	$app = JFactory::getApplication();
    	
        if(!$all)
        {
            $cid = $app->getInput()->get("cid",null,"array");

            if (!$cid || !count($cid))
            {
                throw new Exception("Please first make a selection from the list.", 500);
                return false;
            }

            foreach($cid as $id)
            {
                if(!preg_match("/^[1-9][0-9]*$/", $id)) continue;
                JLexReviewHelperAdmin::updateEntryParams((int) $id);
            }
        } else {
            $query = $this->_db->getQuery(true);
            $query->select("SQL_CALC_FOUND_ROWS id")
                  ->from("#__jlexreview_entry")
                  ->order("id ASC");

            $cid = $this->_db->setQuery($query, $this->offset, $this->limit)->loadColumn();

            if(!$cid) return;

            foreach($cid as $id)
                JLexReviewHelperAdmin::updateEntryParams((int) $id);

            $this->offset += $this->limit;
            $this->recalculate(true);
        }

    	return true;
    }

    public function export($id)
    {
        $query = $this->_db->getQuery(true);
        $query->select("e.object_name, f.params")
              ->from("#__jlexreview_entry e")
              ->leftJoin("#__jlexreview_form f ON e.section_file=f.id")
              ->where("e.id=".$this->_db->quote($id));

        $item = $this->_db->setQuery($query)->loadObject();

        if(!$item)
        {
            $this->setError("Item not found.");
            return false;
        }

        $params = json_decode($item->params);
        $fields = $params->fs_data;

        $columns = [
            "Headline",
            "Rating Summary",
            "Author name",
            "Author email",
            "Date",
        ];

        $column_fields = [];

        $data = [];

        foreach($fields as $field)
        {
            if($field->type=="html") continue;

            if($field->type=="checkbox")
            {
                $lines = explode("\n", $field->options);
                foreach($lines as $ll)
                {
                    $ll = explode("|", $ll);
                    $columns[] = $field->label. " (".$ll[1].")";
                    $column_fields[]="f".$field->id."_".$ll[0];
                }
            } else {
                $columns[] = $field->label;
                $column_fields[]="f".$field->id;
            }
            
            if(@$field->rating==1)
            {
                $columns[] = $field->label." (Rating)";
                $column_fields[]="f".$field->rating_data->id;
            }
        }

        // list reviews
        $stop = false;
        $limit = 30;
        $offset = 0;

        do {
            $query->clear()
                  ->select("r.*")
                  ->select("IF(r.created_by>0,u.username,author_name) a1")
                  ->select("IF(r.created_by>0,u.email,author_email) a2")
                  ->from("#__jlexreview r")
                  ->leftJoin("#__users u ON r.created_by=u.id")
                  ->where([
                    "r.entry_id=".$this->_db->quote($id),
                    "r.published=1"
                  ])
                  ->order("r.id ASC");

            $reviews = $this->_db->setQuery($query, $limit, $offset)->loadObjectList();

            if(!$reviews)
            {
                $stop = true;
                //continue;
            }

            foreach($reviews as $r)
            {
                $row = [
                    $r->title,
                    $r->rating,
                    $r->a1,
                    $r->a2,
                    $r->created
                ];

                $query->clear()
                      ->select("*")
                      ->from("#__jlexreview_fields")
                      ->where([
                        "review_id=".$this->_db->quote($r->id),
                        "field_type!=".$this->_db->quote("html")
                      ]);

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

                if($f1)
                {
                    $fr = [];
                    foreach($f1 as $f2)
                    {
                        if($f2->field_multiple==1){
                            $f3 = explode("|", $f2->field_value);
                            if(!empty($f3))
                            {
                                foreach($f3 as $f4)
                                {
                                    $fr["f".$f2->field_id."_".$f4]=1;
                                }
                            }
                        } else {
                            $fr["f".$f2->field_id]=$f2->field_value;
                        }
                    }

                    foreach($column_fields as $c_field)
                    {
                        $row[] = array_key_exists($c_field, $fr)?$fr[$c_field]:"";
                    }
                }

                $data[] = $row;
            }

        } while ($stop==true);  

        // create download file
        error_reporting(0);
        
        header('Content-Type: text/csv');
        header('Content-Disposition: attachment; filename="'.htmlentities($item->object_name).'.csv"');

        $fp = fopen('php://output', 'wb');
        fputcsv($fp, $columns);

        foreach($data as $line)
            fputcsv($fp, $line);

        fclose($fp);

        exit;
    }
}