<?php
/**
 * Available fields model
 *
 * @package		CSVIVirtueMart
 * @author 		Roland Dalmulder
 * @link 		http://www.csvimproved.com
 * @copyright 	Copyright (C) 2006 - 2012 RolandD Cyber Produksi
 * @license 	GNU/GPLv3 http://www.gnu.org/licenses/gpl-3.0.html
 * @version 	$Id: availablefields.php 1892 2012-02-11 11:01:09Z RolandD $
 */

defined( '_JEXEC' ) or die( 'Direct Access to this location is not allowed.' );

jimport( 'joomla.application.component.model' );

/**
 * Available fields Model
 *
 * @package CSVIVirtueMart
 */
class CsvivirtuemartModelAvailablefields extends JModel {

	/**
	* Items total
	* @var integer
	*/
	var $_total = null;

	/**
	* Pagination object
	* @var object
	*/
	var $_pagination = null;

	/**
	* Constructor
	*
	* @author RolandD
	* @access public
	*/
	public function __construct() {
		parent::__construct();

		$mainframe = JFactory::getApplication();
		$option	= JRequest::getCmd('option');

		// Get pagination request variables
		$limit 				= $mainframe->getUserStateFromRequest('global.list.limit', 'limit', $mainframe->getCfg('list_limit'), 'int');
		$limitstart 		= JRequest::getVar('limitstart', 0, '', 'int');

		// In case limit has been changed, adjust it
		$limitstart = ($limit != 0 ? (floor($limitstart / $limit)* $limit) : 0);

		$this->setState('limit', $limit);
		$this->setState('limitstart', $limitstart);
	}

	function getData() {
        // if data hasn't already been obtained, load it
        if (empty($this->_data)) {
            $query = $this->_buildQuery();
            $this->_data = $this->_getList($query, $this->getState('limitstart'), $this->getState('limit'));
        }
        return $this->_data;
    }

    public function getTotal() {
        // Load the content if it doesn't already exist
        if (empty($this->_total)) {
            $query = $this->_buildQuery();
            $this->_total = $this->_getListCount($query);
        }
        return $this->_total;
    }

    function getPagination() {
        // Load the content if it doesn't already exist
        if (empty($this->_pagination)) {
            jimport('joomla.html.pagination');
            $this->_pagination = new JPagination($this->getTotal(), $this->getState('limitstart'), $this->getState('limit') );
        }
        return $this->_pagination;
    }

	/**
	 * Create the query to retrieve available fields
	 *
	 * @copyright
	 * @author 		RolandD
	 * @todo
	 * @see
	 * @access 		private
	 * @param
	 * @return
	 * @since 		3.0
	 */
	private function _buildQuery() {
		$mainframe = Jfactory::getApplication();
		$db = JFactory::getDBO();

		// Load filters
		$filter = $mainframe->getUserStateFromRequest('availablefields.searchtemplatetype',	'searchtemplatetype',	'',	'word');
		$filter_avfields = JRequest::getString('filter_avfields', false);
		$filter_idfields = JRequest::getBool('filter_idfields', false);
		$filter_order		= $mainframe->getUserStateFromRequest('availablefields.filter_order',		'filter_order',		'csvi_name',	'cmd');
		if ($filter_order == '') $filter_order = 'csvi_name';
		$filter_order_Dir	= $mainframe->getUserStateFromRequest('availablefields.filter_order_Dir',	'filter_order_Dir',	'asc',	'word');
		if ($filter_order_Dir == '') $filter_order_Dir = 'asc';
		$filter_state		= $mainframe->getUserStateFromRequest('availablefields.filter_state',		'filter_state',		'',	'word');
		$filters = array();

		// Start query
		$q = "SELECT * FROM ".$db->nameQuote('#__csvivirtuemart_available_fields')." AS c ";

		// Add filters
		if ($filter) {
			$table = $this->getAvailableFields($filter, false);
			$filters[] = '('.$db->nameQuote('vm_table')." = '" . implode( "' OR vm_table ='", $table )."')";
		}

		if ($filter_avfields) $filters[] = "(csvi_name LIKE ".$db->Quote('%'.$filter_avfields.'%')." OR vm_name LIKE ".$db->Quote('%'.$filter_avfields.'%')." OR vm_table LIKE ".$db->Quote('%'.$filter_avfields.'%').")";

		// Do not load ID fields
		if (!$filter_idfields) $filters[] = "(csvi_name NOT LIKE '%\_id' AND csvi_name NOT LIKE 'id')";


		if (count($filters) > 0) $q .= " WHERE ".implode(' AND ', $filters);
		if ($filter_order) $q .= " ORDER BY ".$filter_order.' '.$filter_order_Dir;

		return $q;
	}

	/**
	 * Fill the available fields table
	 *
	 * @copyright
	 * @author		RolandD
	 * @todo
	 * @see
	 * @access 		public
	 * @param
	 * @return
	 * @since 		3.0
	 */
	public function getFillAvailableFields() {
		// Load the session data
		$db = JFactory::getDBO();
		$session = JFactory::getSession();
		$option = JRequest::getVar('option');
		$tables = unserialize($session->get($option.'.showtables'));

		foreach ($tables as $tablename) {
			$this->getAvailableFieldsSingle();
		}

		$csvilog = unserialize($session->get($option.'.csvilog'));
		JRequest::setVar('csvilog', $csvilog);
		return;
	}

	/**
	 * Prepare for available fields importing
	 *
	 * @copyright
	 * @author 		RolandD
	 * @todo
	 * @see
	 * @access 		public
	 * @param
	 * @return
	 * @since 		3.5
	 */
	public function prepareAvailableFields() {
		$db = JFactory::getDBO();
		// Load the session data
		$session = JFactory::getSession();
		$option = JRequest::getVar('option');
		$settings = JRequest::getVar('settings');
		$csvilog = JRequest::getVar('csvilog');

		// Clean the session
		$session->set($option.'.tables', serialize('0'));
		$session->set($option.'.customtables', serialize('0'));
		$session->set($option.'.csvilog', serialize('0'));

		// Empty the available fields first
		$q = "TRUNCATE TABLE `#__csvivirtuemart_available_fields`";
		$db->setQuery($q);
		if ($db->query()) $csvilog->AddStats('empty', JText::_('COM_CSVIVIRTUEMART_AVAILABLE_FIELDS_TABLE_EMPTIED'));
		else $csvilog->AddStats('error', JText::_('COM_CSVIVIRTUEMART_AVAILABLE_FIELDS_TABLE_COULD_NOT_BE_EMPTIED'));

		// Add some custom fields
		jimport('joomla.filesystem.file');
		if (JFile::exists(JPATH_COMPONENT.'/helpers/availablefields_extra.sql')) {
			$q = JFile::read(JPATH_COMPONENT.'/helpers/availablefields_extra.sql');
			$db->setQuery($q);
			if ($db->query()) $csvilog->AddStats('added', JText::_('COM_CSVIVIRTUEMART_CUSTOM_AVAILABLE_FIELDS_HAVE_BEEN_ADDED'));
			else $csvilog->AddStats('error', JText::_('COM_CSVIVIRTUEMART_CUSTOM_AVAILABLE_FIELDS_HAVE_NOT_BEEN_ADDED'));
		}
		else $csvilog->AddStats('error', JText::sprintf('AVAILABLEFIELDS_EXTRA_NOT_FOUND', JPATH_COMPONENT.'/helpers/availablefields_extra.sql'));

		// Load the tables to import
		$settings = JRequest::getVar('settings');
		$tablenames = array('vm_category',
							'vm_category_xref',
							'vm_country',
							'vm_coupons',
							'vm_currency',
							'vm_manufacturer',
							'vm_manufacturer_category',
							'vm_order_item',
							'vm_order_payment',
							'vm_order_status',
							'vm_order_user_info',
							'vm_orders',
							'vm_payment_method',
							'vm_product',
							'vm_product_attribute',
							'vm_product_attribute_sku',
							'vm_product_category_xref',
							'vm_product_discount',
							'vm_product_files',
							'vm_product_mf_xref',
							'vm_product_price',
							'vm_product_product_type_xref',
							'vm_product_relations',
							'vm_product_reviews',
							'vm_product_type',
							'vm_product_type_parameter',
							'vm_product_type_x',
							'vm_shipping_rate',
							'vm_shopper_group',
							'vm_shopper_vendor_xref',
							'vm_tax_rate',
							'vm_userfield',
							'vm_user_info',
							'vm_vendor',
							'vm_vendor_category',
							'vm_waiting_list',
							'vmfilteringcat',
							'vmfilteringsubcat',
							'users');
		// Merge the tables
		$customtables = $settings->getSetting('tablelist');
		if (!is_array($customtables)) $customtables = array($customtables);
		$tablenames = array_merge($tablenames, $customtables);

		$showtables = array();
		foreach ($tablenames as $key => $tablename) {
			// Collect the table names for the product types
			if ($tablename == 'vm_product_type_x') {
				$q = "SHOW TABLES LIKE '%vm_product_type_%'";
				$db->setQuery($q);
				$name_tables = $db->loadResultArray();
				foreach ($name_tables as $nkey => $name_table) {
					if (stristr('0123456789', substr($name_table, -1))) {
						$showtables[] = str_replace($db->getPrefix(), '', $name_table);
					}
				}
			}
			else $showtables[] = $tablename;
		}

		// Add the tables to the session
		$session->set($option.'.showtables', serialize($showtables));
		$session->set($option.'.customtables', serialize($customtables));
		$session->set($option.'.csvilog', serialize($csvilog));
	}

	/**
	 * Import the available fields in steps
	 *
	 * @copyright
	 * @author 		RolandD
	 * @todo
	 * @see
	 * @access 		public
	 * @param
	 * @return
	 * @since 		3.5
	 */
	public function getAvailableFieldsSingle() {
		$db = JFactory::getDBO();
		$queries = array();

		// Load the session data
		$session = JFactory::getSession();
		$option = JRequest::getVar('option');
		$csvilog = unserialize($session->get($option.'.csvilog'));
		$tables = unserialize($session->get($option.'.showtables'));
		$customtables = unserialize($session->get($option.'.customtables'));
		$lines = unserialize($session->get($option.'.linesprocessed'));
		if (empty($lines)) $lines = 0;
		$lines++;
		// Set the line number
		$csvilog->setLinenumber($lines);
		$errors = false;

		if (!empty($tables)) {
			// Get the first table
			$showtable = array_shift($tables);

			// Get the primary key for the table
			$primarykey = CsviHelper::getPrimaryKey($showtable);

			$fields = $this->DbFields($showtable, true);
			if (is_array($fields)) {
				// Check if the field is a core field
				if (in_array($showtable, $customtables)) $core = 0;
				else $core = 1;

				// Process all fields
				foreach ($fields as $name => $value) {
					// Set the CSVI name
					$csviname = $name;

					// Check if the field is a primary field
					if ($primarykey == $name) $primary = 1;
					else $primary = 0;

					// Rename certain fields
					switch ($showtable) {
						case 'vm_country':
							switch ($name) {
								case 'country_name':
									$csviname = $name;
									break;
								default:
									$csviname = false;
									break;
							}
							break;
						case 'vm_product':
							switch($name) {
								case 'cdate':
									$csviname = 'product_cdate';
									break;
								case 'mdate':
									$csviname = 'product_mdate';
									break;
							}
							break;
						case 'vm_product_discount':
							switch($name) {
								case 'amount':
									$csviname = 'product_discount';
									break;
								case 'start_date':
									$csviname = 'product_discount_date_start';
									break;
								case 'end_date':
									$csviname = 'product_discount_date_end';
									break;
							}
							break;
						case 'vm_tax_rate':
							switch($name) {
								case 'tax_rate':
									$csviname = 'product_tax';
									break;
							}
							break;
						case 'vm_product_type_paramter':
							switch($name) {
								case 'parameter_name':
									$csviname = 'product_type_parameter_name';
									break;
								case 'parameter_label':
									$csviname = 'product_type_parameter_label';
									break;
								case 'parameter_description':
									$csviname = 'product_type_parameter_description';
									break;
								case 'parameter_list_order':
									$csviname = 'product_type_parameter_list_order';
									break;
								case 'parameter_parameter_type':
									$csviname = 'product_type_parameter_type';
									break;
								case 'parameter_values':
									$csviname = 'product_type_parameter_values';
									break;
								case 'parameter_multiselect':
									$csviname = 'product_type_parameter_multiselect';
									break;
								case 'parameter_default':
									$csviname = 'product_type_parameter_default';
									break;
								case 'parameter_unit':
									$csviname = 'product_type_parameter_unit';
									break;
							}
							break;
						case 'vm_product_files':
							switch($name) {
								case 'file_name':
									$csviname = 'product_files_file_name';
									break;
								case 'file_title':
									$csviname = 'product_files_file_title';
									break;
								case 'file_description':
									$csviname = 'product_files_file_description';
									break;
								case 'file_url':
									$csviname = 'product_files_file_url';
									break;
								case 'file_published':
									$csviname = 'product_files_file_published';
									break;
							}
							break;
						case 'vm_manufacturer':
							switch($name) {
								case 'mf_name':
									$csviname = 'manufacturer_name';
									break;
								case 'mf_email':
									$csviname = 'manufacturer_email';
									break;
								case 'mf_desc':
									$csviname = 'manufacturer_desc';
									break;
								case 'mf_url':
									$csviname = 'manufacturer_url';
									break;
								case 'mf_category_id':
									$csviname = 'manufacturer_category_id';
									break;
							}
							break;
						case 'vm_manufacturer_category':
							switch($name) {
								case 'mf_category_id':
									$csviname = 'manufacturer_category_id';
									break;
								case 'mf_category_name':
									$csviname = 'manufacturer_category_name';
									break;
								case 'mf_category_desc':
									$csviname = 'manufacturer_category_desc';
									break;
							}
							break;
						case 'vm_category':
							switch($name) {
								case 'products_per_row':
									$csviname = 'category_products_per_row';
									break;
								case 'list_order':
									$csviname = 'category_list_order';
									break;
							}
							break;
						case 'vm_orders':
							switch($name) {
								case 'cdate':
									$csviname = 'order_date';
									break;
								case 'mdate':
									$csviname = 'order_modified_date';
									break;
							}
							break;
						case 'vm_order_item':
							switch($name) {
								case 'product_item_price':
									$csviname = 'product_price';
									break;
								case 'order_item_sku':
									$csviname = 'product_sku';
									break;
								case 'order_item_name':
									$csviname = 'product_name';
									break;
							}
							break;
						case 'users':
							if ($core) {
								switch(strtolower($name)) {
									case 'email':
										$csviname = 'user_email';
										break;
									case 'sendemail':
										$csviname = 'sendemail';
										break;
									case 'registerdate':
										$csviname = 'registerdate';
										break;
								}
							}
							break;
					}
					if ($csviname) {
						$queries[] = "("
									."0,"
									.$db->Quote($csviname).","
									.$db->Quote($name).","
									.$db->Quote($value).","
									.$db->Quote($core).","
									.$db->Quote($primary)
									.")\n";
						$q = "INSERT IGNORE INTO ".$db->nameQuote('#__csvivirtuemart_available_fields')." VALUES ("
							."0,"
							.$db->Quote($csviname).","
							.$db->Quote($name).","
							.$db->Quote($value).","
							.$db->Quote($core).","
							.$db->Quote($primary).")";
						$db->setQuery($q);

						if (!$db->query()) $errors = true;

					}
				} // foreach
				// Check for any errors
				if (!$errors) {
					JRequest::setVar('updatetable', $showtable);
					$csvilog->AddStats('added', JText::sprintf('COM_CSVIVIRTUEMART_AVAILABLE_FIELDS_HAVE_BEEN_ADDED', $showtable));
				}
				else {
					$csvilog->AddStats('error', JText::_('COM_CSVIVIRTUEMART_AVAILABLE_FIELDS_HAVE_NOT_BEEN_ADDED'));
				}
			} // is_array

			// Assign the tables to the session
			$session->set($option.'.showtables', serialize($tables));
			$session->set($option.'.linesprocessed', serialize($lines));

			$continue = true;

		} // empty
		else {
			JRequest::setVar('csvilog', $csvilog);

			// Clear the session
			$session->set($option.'.tables', serialize('0'));
			$session->set($option.'.customtables', serialize('0'));
			$session->set($option.'.csvilog', serialize('0'));
			$session->set($option.'.linesprocessed', serialize('0'));

			// Set the run ID
			JRequest::setVar('run_id', $csvilog->getId());

			$continue = false;
		}
		// Assign the log to the session
		$session->set($option.'.csvilog', serialize($csvilog));

		return $continue;
	}

	/**
	 * Creates an array of custom database fields the user can use for import/export
	 *
	 * @copyright
	 * @author 		RolandD
	 * @todo
	 * @see
	 * @access 		public
	 * @param
	 * @return		array of custom database fields
	 * @since 		3.0
	 */
	public function DbFields($table, $addname=false) {
		$db = JFactory::getDBO();
		$customfields = array();
		$q = "SHOW COLUMNS FROM ".$db->nameQuote('#__'.$table);
		$db->setQuery($q);
		$fields = $db->loadObjectList();
		if (count($fields) > 0) {
			foreach ($fields as $key => $field) {
				if ($addname) $customfields[$field->Field] = $table;
				else $customfields[$field->Field] = null;
			}
		}
		return $customfields;
	}

	/**
	 * Retrieve a list of available fields from a certain table
	 *
	 * @copyright
	 * @author 		RolandD
	 * @todo
	 * @see
	 * @access 		private
	 * @param
	 * @return 		object	list of available fields
	 * @since 		3.0
	 */
	private function getAvailableDbFields($table) {
		$db = JFactory::getDBO();
		$tables = $db->nameQuote('vm_table')." = '" . implode( "' OR vm_table ='", $table )."'";
		$q = "SELECT LOWER(csvi_name) FROM ".$db->nameQuote('#__csvivirtuemart_available_fields')." WHERE ".$tables." GROUP BY csvi_name";
		$db->setQuery($q);
		return $db->loadResultArray();
	}

	/**
	 * Get the fields belonging to a certain operation type
	 *
	 * @copyright
	 * @author 		RolandD
	 * @todo
	 * @see
	 * @access 		public
	 * @param 		string	$type		contains the name of the supported fields to check
	 * @param		bool	$fields		set whether the fields or the tables should be returned
	 * @return 		array	list of tables or fields
	 * @since		3.0
	 */
	public function getAvailableFields($type, $fields=true, $table_name='') {
		switch (strtolower($type)) {
			case 'customimport':
			case 'customexport':
				$db = JFactory::getDBO();
				$q = "SELECT vm_table FROM #__csvivirtuemart_available_fields
					WHERE core = 0 ";
				if (!empty($table_name)) $q .= "AND vm_table = ".$db->Quote($table_name);
				$q .= " GROUP BY vm_table";
				$db->setQuery($q);
				$tables = $db->loadResultArray();
				break;
			case 'productimport':
			case 'productexport':
				$tables = array('vm_product', 'vm_product_price', 'vm_product_category_xref',
								'vm_product_discount', 'vm_product_attribute',
								'vm_product_attribute_sku', 'vm_product_relations',
								'vm_manufacturer', 'vm_product_mf_xref',
								'vm_tax_rate', 'vm_shopper_group');
				break;
			case 'multiplepricesimport':
			case 'multiplepricesexport':
				$tables = array('vm_product_price');
				break;
			case 'couponsimport':
			case 'couponsexport':
				$tables = array('vm_coupons');
				break;
			case 'productfilesimport':
			case 'productfilesexport':
				$tables = array('vm_product_files');
				break;
			case 'producttypeimport':
			case 'producttypeexport':
				$tables = array('vm_product_type');
				break;
			case 'producttypeparametersimport':
			case 'producttypeparametersexport':
				$tables = array('vm_product_type_parameter');
				break;
			case 'producttypenamesimport':
			case 'producttypenamesexport':
				/**
				* An array of all fields that are available for a product type name import
				*
				* The list only contains 2 fields but more fieldnames need to be added
				* to import data. These fieldnames are the product type parameter names.
				* This allows for filling the parameters with data.
				*/
				$db = JFactory::getDBO();
				$q = "SELECT CONCAT('vm_product_type_', product_type_id) AS producttypes FROM #__vm_product_type";
				$db->setQuery($q);
				$tables = $db->loadResultArray();
				array_push($tables, 'vm_product_product_type_xref');
				break;
			case 'manufacturerimport':
			case 'manufacturerexport':
				$tables = array('vm_manufacturer');
				break;
			case 'manufacturercategoryimport':
				$tables = array('vm_manufacturer_category');
				break;
			case 'categorydetailsimport':
			case 'categorydetailsexport':
				$tables = array('vm_category');
				break;
			case 'userinfoimport':
			case 'userinfoexport':
				$tables = array('vm_user_info', 'vm_shopper_group', 'vm_shopper_vendor_xref', 'users');
				break;
			case 'orderimport':
			case 'orderexport':
				$tables = array('vm_orders', 'vm_order_item', 'vm_order_user_info',
								'vm_order_payment', 'vm_order_status', 'vm_payment_method',
								'vm_product_mf_xref', 'vm_manufacturer', 'users', 'vm_country');
				break;
			case 'orderitemimport':
				$tables = array('vm_order_item');
				break;
			case 'orderitemexport':
				$tables = array('vm_order_item', 'vm_order_status');
				break;
			case 'shippingratesimport':
			case 'shippingratesexport':
				$tables = array('vm_shipping_rate', 'vm_currency');
				break;
			case 'productreviewsimport':
			case 'productreviewsexport':
				$tables = array('vm_product_reviews');
				break;
			case 'taxratesimport':
			case 'taxratesexport':
				$tables = array('vm_tax_rate');
				break;
			case 'userfieldsimport':
			case 'userfieldsexport':
				$tables = array('vm_userfield');
				break;
			case 'waitinglistimport':
			case 'waitinglistexport':
				$tables = array('vm_waiting_list','vm_product','vm_user_info');
				break;
			case 'vmf_featuresimport':
			case 'vmf_featuresexport':
				$tables = array('vmfilteringcat');
				break;
			case 'vmf_valuesimport':
			case 'vmf_valuesexport':
				$tables = array('vmfilteringsubcat', 'vmfilteringcat');
				break;
		}
		// Add the template type fields
		$tables[] = strtolower($type);

		// Check if we are viewing available fields
		if (!$fields) return $tables;
		else {
			$avfields = $this->getAvailableDbFields($tables);
			if (substr($type, -6) == 'import') $avfields[] = 'combine';
			sort($avfields);
			return $avfields;
		}
	}

	/**
	* Check if there are enough fields in the database
	*
	* @author RolandD
	* @access public
	* @return bool true|false
	*/
	public function getFieldCheck() {
		$db = JFactory::getDBO();
		$q = 'SELECT COUNT(id) FROM #__csvivirtuemart_available_fields';
		$db->setQuery($q);
		if ($db->loadResult() > 0) return true;
		else return false;
	}
}
?>