<?php
/*------------------------------------------------------------------------
# com_zhosmmap - Zh OpenStreetMap
# ------------------------------------------------------------------------
# author:    Dmitry Zhuk
# copyright: Copyright (C) 2011 zhuk.cc. All Rights Reserved.
# license:   http://www.gnu.org/licenses/gpl-2.0.html GNU/GPLv2 or later
# website:   http://zhuk.cc
# Technical Support Forum: http://forum.zhuk.cc/
-------------------------------------------------------------------------*/
namespace ZhukDL\Component\ZhOSMMap\Administrator\Model;

// No direct access to this file
defined('_JEXEC') or die('Restricted access');

use Joomla\CMS\Component\ComponentHelper;
use Joomla\CMS\MVC\Model\ListModel;
use Joomla\CMS\Table\Table;
use Joomla\Database\ParameterType;
use Joomla\Utilities\ArrayHelper;
use Joomla\Database\DatabaseIterator;
use Joomla\Database\DatabaseQuery;

/**
 * Model
 */
class MaproutersModel extends ListModel
{
    
    public function __construct($config = array())
    {
        if (empty($config['filter_fields'])) {
            $config['filter_fields'] = array(
                'id', 'h.id',
                'title', 'h.title',
                'mapid', 'h.mapid',
                'published', 'h.published',
                'catid', 'h.catid', 'category_title',
            );
        }

        parent::__construct($config);
    }



    
	/**
	 * Method to get a store id based on model configuration state.
	 *
	 * This is necessary because the model is used by the component and
	 * different modules that might need different sets of data or different
	 * ordering requirements.
	 *
	 * @param   string  $id  A prefix for the store id.
	 *
	 * @return  string  A store id.
	 *
	 * @since   1.6
	 */
	protected function getStoreId($id = '')
	{
		// Compile the store id.
		$id .= ':' . $this->getState('filter.search');
		$id .= ':' . $this->getState('filter.published');
		$id .= ':' . serialize($this->getState('filter.category_id'));
		$id .= ':' . $this->getState('filter.level');
		$id .= ':' . $this->getState('filter.mapid');

		return parent::getStoreId($id);
	}

	/**
	 * Method to auto-populate the model state.
	 *
	 * Note. Calling getState in this method will result in recursion.
	 *
	 * @param   string  $ordering   An optional ordering field.
	 * @param   string  $direction  An optional direction (asc|desc).
	 *
	 * @return  void
	 *
	 * @since   1.6
	 */
	protected function populateState($ordering = 'h.title', $direction = 'asc')
	{
		// Load the parameters.
		$this->setState('params', ComponentHelper::getParams('com_zhosmmap'));

		// List state information.
		parent::populateState($ordering, $direction);
	}
    
    /**
     * Method to build an SQL query to load the list data.
     *
     * @return    string    An SQL query
     */
    protected function getListQuery() 
    {
        // Create a new query object.
        $db = $this->getDBO();
        $query = $db->getQuery(true);

        $query->select('h.id,h.title,h.mapid,h.published,h.publish_up,h.publish_down,h.catid,c.title as category, c.language as category_language,m.title as mapname');
        $query->from('#__zhosmmaps_routers as h');
        $query->leftJoin('#__categories as c on h.catid=c.id');
        $query->leftJoin('#__zhosmmaps_maps as m on h.mapid=m.id');


        // Filter by search in title
        $search = $this->getState('filter.search');
        if (!empty($search)) {
            $search = '%' . str_replace(' ', '%', trim($search)) . '%';				
            $query->where($db->quoteName('h.title') . ' LIKE :search')
                  ->bind(':search', $search);
        }
        
        // Filter by published state
        $published = $this->getState('filter.published');
        if (is_numeric($published)) {
            $published = (int)$published;
			$query->where($db->quoteName('h.published') . ' = :published')
				  ->bind(':published', $published, ParameterType::INTEGER);
        } elseif ($published === '') {
            $query->where('h.published IN (0, 1)');
        }

        // Filter by categories and by level
		$categoryId = $this->getState('filter.category_id', array());
		$level      = (int) $this->getState('filter.level');

		if (!is_array($categoryId))
		{
			$categoryId = $categoryId ? array($categoryId) : array();
		}

		// Case: Using both categories filter and by level filter
		if (count($categoryId))
		{
			$categoryId = ArrayHelper::toInteger($categoryId);
			$categoryTable = Table::getInstance('Category', 'JTable');
			$subCatItemsWhere = array();

			foreach ($categoryId as $key => $filter_catid)
			{
				$categoryTable->load($filter_catid);

				// Because values to $query->bind() are passed by reference, using $query->bindArray() here instead to prevent overwriting.
				$valuesToBind = [$categoryTable->lft, $categoryTable->rgt];

				if ($level)
				{
					$valuesToBind[] = $level + $categoryTable->level - 1;
				}

				// Bind values and get parameter names.
				$bounded = $query->bindArray($valuesToBind);

				$categoryWhere = $db->quoteName('c.lft') . ' >= ' . $bounded[0] . ' AND ' . $db->quoteName('c.rgt') . ' <= ' . $bounded[1];

				if ($level)
				{
					$categoryWhere .= ' AND ' . $db->quoteName('c.level') . ' <= ' . $bounded[2];
				}

				$subCatItemsWhere[] = '(' . $categoryWhere . ')';
			}

			$query->where('(' . implode(' OR ', $subCatItemsWhere) . ')');
		}

		// Case: Using only the by level filter
		elseif ($level = (int) $level)
		{
			$query->where($db->quoteName('c.level') . ' <= :level')
				  ->bind(':level', $level, ParameterType::INTEGER);
		}
        
        // Filter by mapid.
        $mapId = $this->getState('filter.mapid');
        if (is_numeric($mapId)) {
            $mapId = (int) $mapId;
 			$query->where($db->quoteName('h.mapid') . ' = :mapid')
				  ->bind(':mapid', $mapId, ParameterType::INTEGER);
        }

        // Add the list ordering clause.
        $orderCol    = $this->state->get('list.ordering', 'h.title');
        $orderDirn    = $this->state->get('list.direction', 'ASC');
        
        if ($orderCol === 'h.ordering' || $orderCol === 'category_title')
		{
			$ordering = [
				$db->quoteName('c.title') . ' ' . $db->escape($orderDirn),
				//$db->quoteName('h.ordering') . ' ' . $db->escape($orderDirn),
			];
		}
		else
		{
			$ordering = $db->escape($orderCol) . ' ' . $db->escape($orderDirn);
		}

		$query->order($ordering);
      
        return $query;
    }
    
 
}
