<?php
/**
 * @version     1.4.7
 * @package     sellacious
 *
 * @copyright   Copyright (C) 2012-2017 Bhartiy Web Technologies. All rights reserved.
 * @license     SPL Sellacious Private License; see http://www.sellacious.com/spl.html
 * @author      Izhar Aazmi <info@bhartiy.com> - http://www.bhartiy.com
 */
// no direct access
defined('_JEXEC') or die;

/**
 * Methods supporting a list of Sellacious records.
 *
 * @since  1.0.0
 */
class SellaciousModelProducts extends SellaciousModelList
{
	/**
	 * Constructor.
	 *
	 * @param   array  $config  An optional associative array of configuration settings.
	 *
	 * @see     JController
	 * @since   1.6
	 */
	public function __construct($config = array())
	{
		if (empty($config['filter_fields']))
		{
			$config['filter_fields'] = array(
				'id', 'a.id',
				'local_sku', 'a.local_sku',
				'title', 'a.title',
				'category_title',
				'ordering', 'a.ordering',
				'state', 'a.state',
				'created_by', 'a.created_by',
				'p.seller_company',
				'p.product_price',
				'l.publish_up',
				'l.publish_down',
				'psx.stock',
			);
		}

		parent::__construct($config);
	}

	/**
	 * Method to auto-populate the model state.
	 *
	 * This method should only be called once per instantiation and is designed
	 * to be called on the first call to the getState() method unless the model
	 * configuration flag to ignore the request is set.
	 *
	 * 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   12.2
	 */
	protected function populateState($ordering = null, $direction = null)
	{
		parent::populateState($ordering, $direction);

		$seller_uid   = $this->state->get('filter.seller_uid');
		$multi_seller = $this->helper->config->get('multi_seller', 0);

		if (!$multi_seller && !$seller_uid)
		{
			// Removed force selection of default seller when multi-seller is off. Now it will be a fallback if not filtered by
			$seller_uid = $this->helper->config->get('default_seller');

			$this->state->set('filter.seller_uid', $seller_uid);
		}
	}

	/**
	 * Build an SQL query to load the list data.
	 *
	 * @return  JDatabaseQuery
	 * @since   1.6
	 */
	protected function getListQuery()
	{
		$db    = $this->getDbo();
		$query = $db->getQuery(true);

		$query->select('a.*')
			->from($db->qn('#__sellacious_products', 'a'));

		// Build category list as a sub-query to avoid grouping collisions.
		$cQuery = $this->_db->getQuery(true);
		$cQuery->select('GROUP_CONCAT(c.title ORDER BY c.lft SEPARATOR \', \') AS category_title')
				->from($db->qn('#__sellacious_categories', 'c'))
				->select('pc.product_id')
				->join('LEFT', $db->qn('#__sellacious_product_categories', 'pc') . ' ON c.id = pc.category_id')
				->group('pc.product_id');

		$query->select('cc.category_title')
				->join('left', "({$cQuery}) AS cc ON cc.product_id = a.id");

		// Filter by search in name
		$this->filterSearch($query);

		// Filter by published state
		$state = $this->getState('filter.state');

		if (is_numeric($state))
		{
			$query->where('a.state = ' . (int) $state);
			// ->where('c.state = ' . (int) $state);
		}
		elseif ($state == '')
		{
			// @20151123 - Show everything i.e. 0, 1
			$query->where('(a.state = 1 OR a.state = 0)');
			// ->where('(c.state = 1 OR c.state = 0)');
		}

		// Filter by published state
		if ($category = $this->getState('filter.category'))
		{
			$query->where('a.id IN (SELECT product_id FROM #__sellacious_product_categories WHERE category_id = ' . (int) $category . ')');
		}

		if ($type = $this->getState('filter.type'))
		{
			$query->where('a.type = ' . $db->q($type));
		}

		// Filter by published state
		if ($manufacturer = $this->getState('filter.manufacturer'))
		{
			$query->where('a.manufacturer_id = ' . (int) $manufacturer);
		}

		if ($this->helper->access->check('product.list'))
		{
			if ($seller_uid = $this->getState('filter.seller_uid'))
			{
				$query->where('p.seller_uid = ' . (int) $seller_uid);
			}
		}
		elseif ($this->helper->access->check('product.list.own'))
		{
			$me = JFactory::getUser();
			$query->where('(p.seller_uid = ' . (int) $me->id . ' OR a.owned_by = ' . (int) $me->id . ')');
		}
		else
		{
			$query->where('0');
		}

		$this->extendItemQuery($query);
		$this->specialListingQuery($query);
		$this->basicListingQuery($query);

		if ($ordering = $this->state->get('list.fullordering', 'l.publish_up DESC'))
		{
			$query->order($db->escape($ordering));
		}

		return $query;
	}

	/**
	 * Filter the list query by search text and other filters
	 *
	 * @param   JDatabaseQuery  $query
	 *
	 * @return  void
	 *
	 * @since   1.0.0
	 */
	protected function filterSearch($query)
	{
		$db     = $this->_db;
		$search = $this->getState('filter.search');

		if (!empty($search))
		{
			if (stripos($search, 'id:') === 0)
			{
				$query->where('a.id = ' . (int) substr($search, 3));
			}
			elseif (stripos(strtolower($search), 'sku:') === 0)
			{
				$search = $db->q('%' . $db->escape(substr($search, 4), true) . '%', false);
				$query->where('(a.local_sku LIKE ' . $search . ')');
			}
			elseif (stripos(strtolower($search), 't:') === 0)
			{
				$search = $db->q('%' . $db->escape(substr($search, 2), true) . '%', false);
				$query->where('(a.title LIKE ' . $search . ')');
			}
			else
			{
				$search = $db->q('%' . $db->escape($search, true) . '%', false);
				$where  = array(
					'a.local_sku LIKE ' . $search,
					'a.title LIKE ' . $search,
					'a.description LIKE ' . $search,
				);
				$query->where('(' . implode(' OR ', $where) . ')');
			}
		}

		$price_display = $this->getState('filter.price_display');

		if (is_numeric($price_display))
		{
			$query->where('psx.price_display = ' . (int) $price_display);
		}

		$selling = $this->getState('list.selling');

		if (is_numeric($selling))
		{
			$query->where('psx.state = ' . (int) $selling);
		}
	}

	/**
	 * Extend items seller etc properties to basic query
	 *
	 * @param   JDatabaseQuery  $query
	 *
	 * @return  void
	 *
	 * @since   1.0.0
	 */
	protected function extendItemQuery($query)
	{
		// PSX(+T) applied
		$db     = $this->_db;
		$pQuery = $db->getQuery(true);

		// Add price and stock info
		$pQuery->select('pp.product_id, pp.seller_uid, pp.id AS price_id, pp.cost_price, pp.margin, pp.margin_type')
			->select('pp.list_price, pp.calculated_price, pp.product_price, pp.product_price AS sales_price, pp.ovr_price, pp.is_fallback')
			->from($db->qn('#__sellacious_product_prices', 'pp'))
			->where('pp.is_fallback = 1');

		// Add seller information
		$pQuery->select('ss.title AS seller_company')
			->join('LEFT', $db->qn('#__sellacious_sellers', 'ss') . ' ON ss.user_id = pp.seller_uid');

		$pQuery->select('u.email AS seller_email')
			->join('INNER', $db->qn('#__users', 'u') . ' ON u.id = pp.seller_uid');

		$g_currency       = $this->helper->currency->getGlobal('code_3');
		$c_currency       = $this->helper->currency->current('code_3');
		$listing_currency = $this->helper->config->get('listing_currency');
		$seller_currency  = $listing_currency ? $this->_db->qn('ss.currency') : $this->_db->q($g_currency);
		$default_fx_rate  = $this->helper->currency->convert(1, $g_currency, $c_currency);

		$pQuery->select($seller_currency . ' AS seller_currency, su.mobile AS seller_mobile')
			->join('LEFT', $db->qn('#__sellacious_profiles', 'su') . ' ON su.user_id = pp.seller_uid');

		// Add forex, allow g_currency to be used as fallback if the seller's currency is not set for a row
		$pQuery->select('IFNULL(fx.x_factor, ' . $db->q($default_fx_rate) . ') AS forex_rate')
			->join('LEFT', '#__sellacious_forex AS fx ON fx.x_from = ' . $seller_currency . ' AND fx.x_to = ' . $db->q($c_currency) . ' AND fx.state = 1');

		// Now append everything to the main query
		$query->select('p.*');

		if ($this->helper->config->get('shipped_by') == 'seller')
		{
			$query->select("CASE a.type WHEN 'physical' THEN psp.flat_shipping WHEN 'package' THEN psk.flat_shipping END AS flat_shipping")
				->select("CASE a.type WHEN 'physical' THEN psp.shipping_flat_fee WHEN 'package' THEN psk.shipping_flat_fee END AS shipping_flat_fee");
		}
		else
		{
			$flat_shipping     = $this->helper->config->get('flat_shipping');
			$shipping_flat_fee = $flat_shipping ? $this->helper->config->get('shipping_flat_fee') : 0;

			$query->select($db->q($flat_shipping) . ' AS flat_shipping')->select($db->q($shipping_flat_fee) . ' AS shipping_flat_fee');
		}

		$query->select('psx.price_display, psx.stock, psx.over_stock, psx.stock + psx.over_stock AS stock_capacity, psx.state AS is_selling')
			->select("CASE a.type WHEN 'physical' THEN psp.listing_type WHEN 'package' THEN psk.listing_type END AS listing_type")
			->select("CASE a.type WHEN 'physical' THEN psp.item_condition WHEN 'package' THEN psk.item_condition END AS item_condition")
			->select("CASE a.type WHEN 'physical' THEN psp.whats_in_box WHEN 'package' THEN psk.whats_in_box END AS whats_in_box")
			->select("CASE a.type WHEN 'physical' THEN psp.return_days WHEN 'package' THEN psk.return_days END AS return_days")
			->select("CASE a.type WHEN 'physical' THEN psp.return_tnc WHEN 'package' THEN psk.return_tnc END AS return_tnc")
			->select("CASE a.type WHEN 'physical' THEN psp.exchange_days WHEN 'package' THEN psk.exchange_days END AS exchange_days")
			->select("CASE a.type WHEN 'physical' THEN psp.exchange_tnc WHEN 'package' THEN psk.exchange_tnc END AS exchange_tnc");

		$query->join('LEFT', '(' . $pQuery . ') AS p ON p.product_id = a.id')
			->join('LEFT', $db->qn('#__sellacious_product_sellers', 'psx') . ' ON psx.product_id = p.product_id AND psx.seller_uid = p.seller_uid')
			->join('LEFT', $db->qn('#__sellacious_physical_sellers', 'psp') . ' ON psp.psx_id = psx.id')
			// ->join('LEFT', $db->qn('#__sellacious_eproduct_sellers', 'pse') . ' ON pse.psx_id = psx.id')
			->join('LEFT', $db->qn('#__sellacious_package_sellers', 'psk') . ' ON psk.psx_id = psx.id');
	}

	/**
	 * Add a comma separated list of subscribed Special Category IDs
	 *
	 * @param   JDatabaseQuery  $query
	 *
	 * @return  void
	 *
	 * @since   1.0.0
	 */
	protected function basicListingQuery($query)
	{
		$db     = $this->getDbo();
		$now    = JFactory::getDate()->toSql();
		$nullDt = $db->getNullDate();

		// Special category listing
		$sub = $db->getQuery(true);
		$sub->select('l.seller_uid, l.product_id')
			->select('l.publish_up, l.publish_down, l.state')
			->from($db->qn('#__sellacious_seller_listing', 'l'))
			->where('l.category_id = 0')
			->where('l.publish_up != ' . $db->q($nullDt))
			->where('l.publish_down != ' . $db->q($nullDt))
			->where('l.publish_up < ' . $db->q($now))
			->where('l.publish_down > ' . $db->q($now))
			->where('l.state = 1')
			->group('l.seller_uid, l.product_id');

		$query->select('l.publish_up AS listing_publish_up, l.publish_down AS listing_publish_down, l.state AS listing_state')
			->join('LEFT', "($sub) AS l ON l.product_id = a.id AND l.seller_uid = p.seller_uid");
	}

	/**
	 * Add a comma separated list of subscribed Special Category IDs
	 *
	 * @param   JDatabaseQuery  $query
	 *
	 * @return  void
	 *
	 * @since   1.0.0
	 */
	protected function specialListingQuery($query)
	{
		$db     = $this->getDbo();
		$now    = JFactory::getDate()->toSql();
		$nullDt = $db->getNullDate();

		// Special category listing
		$sub = $db->getQuery(true);
		$sub->select('l.seller_uid, l.product_id')
			->select('GROUP_CONCAT(l.category_id) AS spl_categories')
			->from($db->qn('#__sellacious_seller_listing', 'l'))
			->where('l.category_id > 0')
			->where('l.publish_up != ' . $db->q($nullDt))
			->where('l.publish_down != ' . $db->q($nullDt))
			->where('l.publish_up < ' . $db->q($now))
			->where('l.publish_down > ' . $db->q($now))
			->where('l.state = 1')
			->group('l.seller_uid, l.product_id');

		$query->select('spl_categories')
			->join('LEFT', "($sub) AS spl ON spl.product_id = a.id AND spl.seller_uid = p.seller_uid");
	}
}
