<?php
class ModelExtensionFeedGoogleProductFeed extends Model {

	// Custom Labels Delimiter. Change this if the comma causes problems with the .CSV file
	private $cl_delimiter = ',';
	

	public function editSetting($gpf_status) {
		$query = $this->db->query("DELETE FROM `" . DB_PREFIX . "setting` WHERE `key` = 'feed_google_product_feed_status'");
		
		$query = $this->db->query("INSERT INTO `" . DB_PREFIX . "setting` SET `key` = 'feed_google_product_feed_status', `value` = " . (int)$gpf_status . ", `code` = 'google_product_feed', `serialized` = 0");
	}

	public function enableAllProducts() {
		$query = $this->db->query("UPDATE `" . DB_PREFIX . "product_gpf` SET `gpf_status` ='1'");
		
	}

	public function disableAllProducts() {
		$query = $this->db->query("UPDATE `" . DB_PREFIX . "product_gpf` SET `gpf_status` ='0'");
		
	}

	public function copyGTIN($gtin, $to) {
		$query = $this->db->query("SELECT product_id, " . $gtin . " as original_gtin from  `" . DB_PREFIX . "product`");
		
		foreach ($query->rows as $result) {

		if($to == 'All Products') {
			$mysqlquery = "UPDATE " . DB_PREFIX . "product_gpf SET gtin = '" . $this->db->escape($result['original_gtin']) ."' WHERE product_id = '" . $result['product_id'] ."'";
		} else {
			$mysqlquery = "UPDATE " . DB_PREFIX . "product_gpf SET gtin = '" . $this->db->escape($result['original_gtin']) ."' WHERE product_id = '" . $result['product_id'] ."' AND gtin = ''";		
		}
		
			$query = $this->db->query($mysqlquery);
		}
		
	}

	public function copyMPN($mpn, $to) {
		$query = $this->db->query("SELECT product_id, " . $mpn . " as original_mpn from  `" . DB_PREFIX . "product`");
		
		foreach ($query->rows as $result) {

		if($to == 'All Products') {
			$mysqlquery = "UPDATE " . DB_PREFIX . "product SET mpn = '" . $this->db->escape($result['original_mpn']) ."' WHERE product_id = '" . $result['product_id'] ."'";
		} else {
			$mysqlquery = "UPDATE " . DB_PREFIX . "product SET mpn = '" . $this->db->escape($result['original_mpn']) ."' WHERE product_id = '" . $result['product_id'] ."' AND mpn = ''";		
		}
		
			$query = $this->db->query($mysqlquery);
		}
		
	}
	
	public function copyProductCategory($to) {
		$default_google_product_category = $this->config->get('gpf_default_google_product_category');
		
		if(isset($default_google_product_category)) {
		
			if($to == 'All Products') {
				$mysqlquery = "UPDATE " . DB_PREFIX . "product_gpf SET google_product_category = '" . $this->db->escape($default_google_product_category) ."'";
			} else {
				$mysqlquery = "UPDATE " . DB_PREFIX . "product_gpf SET google_product_category = '" . $this->db->escape($default_google_product_category) ."' WHERE google_product_category = ''";		
			}
		
			$query = $this->db->query($mysqlquery);
		}
		
	}
	
	public function copyCondition($to) {
		$default_google_condition = $this->config->get('gpf_condition');
		
		if(isset($default_google_condition)) {
		
			if($to == 'All Products') {
				$mysqlquery = "UPDATE " . DB_PREFIX . "product_gpf SET `condition` = '" . $default_google_condition . "'";
			} else {
				$mysqlquery = "UPDATE " . DB_PREFIX . "product_gpf SET `condition` = '" . $default_google_condition ."' WHERE `condition` = ''";		
			}
		
			$query = $this->db->query($mysqlquery);
		}
		
	}

	public function copyOosStatus($to) {
		$default_oos_status = $this->config->get('gpf_oos_status');
		
		if(isset($default_oos_status)) {
		
			if($to == 'All Products') {
				$mysqlquery = "UPDATE " . DB_PREFIX . "product_gpf SET oos_status = '" . $default_oos_status ."'";
			} else {
				$mysqlquery = "UPDATE " . DB_PREFIX . "product_gpf SET oos_status = '" . $default_oos_status ."' WHERE oos_status = ''";		
			}
		
			$query = $this->db->query($mysqlquery);
		}
		
	}

	public function replaceAvForOrder() {
		$query = $this->db->query("UPDATE `" . DB_PREFIX . "product_gpf` SET `oos_status` ='Out of Stock' WHERE `oos_status` = 'Available for Order'");
		
	}

	public function copyIdentifierExists($to) {
		$default_identifier_exists = $this->config->get('gpf_identifier_exists');
		
		if(isset($default_identifier_exists)) {
		
			if($to == 'All Products') {
				$mysqlquery = "UPDATE " . DB_PREFIX . "product_gpf SET identifier_exists = '" . $default_identifier_exists ."'";
			} else {
				$mysqlquery = "UPDATE " . DB_PREFIX . "product_gpf SET identifier_exists = '" . $default_identifier_exists ."' WHERE identifier_exists = ''";		
			}
		
			$query = $this->db->query($mysqlquery);
		}
		
	}

	public function copySizeSystem($to) {
		$query = $this->db->query("SELECT value FROM `" . DB_PREFIX . "setting` WHERE `key` = 'gpf_size_system'");
		
		if($query->num_rows) {
			$default_gpf_size_system = $query->row['value'];
		}
		
		if(isset($default_gpf_size_system)) {
		
			if($to == 'All Products') {
				$mysqlquery = "UPDATE " . DB_PREFIX . "product_gpf SET gpf_size_system = '" . $default_gpf_size_system ."'";
			} else {
				$mysqlquery = "UPDATE " . DB_PREFIX . "product_gpf SET gpf_size_system = '" . $default_gpf_size_system ."' WHERE gpf_size_system = ''";		
			}
		
			$query = $this->db->query($mysqlquery);
		}
		
	}

	public function copyManufacturer($to) {
		$manufacturers = array();
		$query = $this->db->query("SELECT p.product_id, m.name from  `" . DB_PREFIX . "product` p LEFT JOIN " . DB_PREFIX . "manufacturer m ON (p.manufacturer_id = m.manufacturer_id)");
		
		foreach ($query->rows as $result) {
			$manufacturers[] = array (
				'product_id' 		=> $result['product_id'],
				'name'            	=> $result['name']);
		}

		
		foreach ($manufacturers as $manufacturer) {
		if($to == 'All Products') {
			$mysqlquery = "UPDATE " . DB_PREFIX . "product_gpf SET brand = '" . $this->db->escape($manufacturer['name']) ."' WHERE `product_id` = '" . (int)$manufacturer['product_id'] ."'";
		} else {
			$mysqlquery = "UPDATE " . DB_PREFIX . "product_gpf SET brand = '" . $this->db->escape($manufacturer['name']) ."' WHERE `product_id` = '" . (int)$manufacturer['product_id'] ."' AND brand = ''";		
		}
			$query = $this->db->query($mysqlquery);
		}
	}
	
	public function getNoOfProducts($store_id = 0) {
		
		$query = $this->db->query("SELECT p.product_id FROM " . DB_PREFIX . "product p LEFT JOIN " .DB_PREFIX . "product_to_store p2s ON (p.product_id = p2s.product_id) LEFT JOIN " . DB_PREFIX . "product_gpf pg ON (p.product_id = pg.product_id) WHERE p2s.store_id = '" . $store_id . "' AND p.status = '1' AND pg.gpf_status = '1' ");
		
		if ($query->num_rows) {
			return $query->num_rows;
		} else {
			return 0;
		}
	}
		
	public function getProducts($data = array()) {
		if ($data) {
			$sql = "SELECT *, p.product_id, pd.name AS name, p.image, m.name AS manufacturer FROM " . DB_PREFIX . "product p LEFT JOIN " . DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id) LEFT JOIN " . DB_PREFIX . "manufacturer m ON (p.manufacturer_id = m.manufacturer_id) LEFT JOIN " . DB_PREFIX . "product_gpf pg ON (p.product_id = pg.product_id)";
			
			if (!empty($data['filter_category_id'])) {
				$sql .= " LEFT JOIN " . DB_PREFIX . "product_to_category p2c ON (p.product_id = p2c.product_id)";			
			}
					
			$sql .= " WHERE pd.language_id = '" . (int)$this->config->get('config_language_id') . "'"; 
			
			if (!empty($data['filter_name'])) {
				$sql .= " AND LCASE(pd.name) LIKE '" . $this->db->escape(utf8_strtolower($data['filter_name'])) . "%'";
			}

			if (!empty($data['filter_model'])) {
				$sql .= " AND LCASE(p.model) LIKE '" . $this->db->escape(utf8_strtolower($data['filter_model'])) . "%'";
			}
			
			if (!empty($data['filter_manufacturer'])) {
				$sql .= " AND LCASE(m.name) LIKE '" . $this->db->escape(utf8_strtolower($data['filter_manufacturer'])) . "%'";
			}
			
			if (isset($data['filter_quantity']) && !is_null($data['filter_quantity'])) {
				$sql .= " AND p.quantity = '" . $this->db->escape($data['filter_quantity']) . "'";
			}
			
			if (isset($data['filter_status']) && !is_null($data['filter_status'])) {
				$sql .= " AND pg.gpf_status = '" . (int)$data['filter_status'] . "'";
			}
					
			if (!empty($data['filter_category_id'])) {
				if (!empty($data['filter_sub_category'])) {
					$implode_data = array();
					
					$implode_data[] = "category_id = '" . (int)$data['filter_category_id'] . "'";
					
					$this->load->model('catalog/category');
					
					$categories = $this->model_catalog_category->getCategories($data['filter_category_id']);
					
					foreach ($categories as $category) {
						$implode_data[] = "p2c.category_id = '" . (int)$category['category_id'] . "'";
					}
					
					$sql .= " AND (" . implode(' OR ', $implode_data) . ")";			
				} else {
					$sql .= " AND p2c.category_id = '" . (int)$data['filter_category_id'] . "'";
				}
			}
			
			$sql .= " GROUP BY p.product_id";
						
			$sort_data = array(
				'pd.name',
				'p.model',
				'manufacturer',
				'pg.gpf_status',
				'p.sort_order'
			);	
			
			if (isset($data['sort']) && in_array($data['sort'], $sort_data)) {
				$sql .= " ORDER BY " . $data['sort'];	
			} else {
				$sql .= " ORDER BY pd.name";	
			}
			
			if (isset($data['order']) && ($data['order'] == 'DESC')) {
				$sql .= " DESC";
			} else {
				$sql .= " ASC";
			}
		
			if (isset($data['start']) || isset($data['limit'])) {
				if ($data['start'] < 0) {
					$data['start'] = 0;
				}				

				if ($data['limit'] < 1) {
					$data['limit'] = 20;
				}	
			
				$sql .= " LIMIT " . (int)$data['start'] . "," . (int)$data['limit'];
			}	
			
			$query = $this->db->query($sql);
		
			return $query->rows;
		} else {
			$query = $this->db->query("SELECT DISTINCT *, pd.name AS name, p.image, m.name AS manufacturer FROM " . DB_PREFIX . "product p LEFT JOIN " . DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id) LEFT JOIN " . DB_PREFIX . "manufacturer m ON (p.manufacturer_id = m.manufacturer_id) LEFT JOIN " . DB_PREFIX . "product_gpf pg ON (p.product_id = pg.product_id) WHERE pd.language_id = '" . (int)$this->config->get('config_language_id') . "' ORDER BY pd.name ASC");

			$product_data = $query->rows;		
		}		
		return $product_data;
	}

	public function getProductTotals($data = array()) {
		if ($data) {
			$sql = "SELECT p.product_id, pd.name AS name, p.image, m.name AS manufacturer FROM " . DB_PREFIX . "product p LEFT JOIN " . DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id) LEFT JOIN " . DB_PREFIX . "manufacturer m ON (p.manufacturer_id = m.manufacturer_id) LEFT JOIN " . DB_PREFIX . "product_gpf pg ON (p.product_id = pg.product_id)";
			
			if (!empty($data['filter_category_id'])) {
				$sql .= " LEFT JOIN " . DB_PREFIX . "product_to_category p2c ON (p.product_id = p2c.product_id)";			
			}
					
			$sql .= " WHERE pd.language_id = '" . (int)$this->config->get('config_language_id') . "'"; 
			
			if (!empty($data['filter_name'])) {
				$sql .= " AND LCASE(pd.name) LIKE '" . $this->db->escape(utf8_strtolower($data['filter_name'])) . "%'";
			}

			if (!empty($data['filter_model'])) {
				$sql .= " AND LCASE(p.model) LIKE '" . $this->db->escape(utf8_strtolower($data['filter_model'])) . "%'";
			}
			
			if (!empty($data['filter_manufacturer'])) {
				$sql .= " AND LCASE(m.name) LIKE '" . $this->db->escape(utf8_strtolower($data['filter_manufacturer'])) . "%'";
			}
			
			if (isset($data['filter_quantity']) && !is_null($data['filter_quantity'])) {
				$sql .= " AND p.quantity = '" . $this->db->escape($data['filter_quantity']) . "'";
			}
			
			if (isset($data['filter_status']) && !is_null($data['filter_status'])) {
				$sql .= " AND pg.gpf_status = '" . (int)$data['filter_status'] . "'";
			}
					
			if (!empty($data['filter_category_id'])) {
				if (!empty($data['filter_sub_category'])) {
					$implode_data = array();
					
					$implode_data[] = "category_id = '" . (int)$data['filter_category_id'] . "'";
					
					$this->load->model('catalog/category');
					
					$categories = $this->model_catalog_category->getCategories($data['filter_category_id']);
					
					foreach ($categories as $category) {
						$implode_data[] = "p2c.category_id = '" . (int)$category['category_id'] . "'";
					}
					
					$sql .= " AND (" . implode(' OR ', $implode_data) . ")";			
				} else {
					$sql .= " AND p2c.category_id = '" . (int)$data['filter_category_id'] . "'";
				}
			}
			
			$sql .= " GROUP BY p.product_id";
						
			
			$query = $this->db->query($sql);
		
			return $query->num_rows;
		} else {
			$query = $this->db->query("SELECT DISTINCT *, pd.name AS name, p.image, m.name AS manufacturer FROM " . DB_PREFIX . "product p LEFT JOIN " . DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id) LEFT JOIN " . DB_PREFIX . "manufacturer m ON (p.manufacturer_id = m.manufacturer_id) LEFT JOIN " . DB_PREFIX . "product_gpf pg ON (p.product_id = pg.product_id) WHERE pd.language_id = '" . (int)$this->config->get('config_language_id') . "' ORDER BY pd.name ASC");

			return $query->num_rows;		
		}		
	}

	public function getProductData($product_id) {
	
			$product_data = array();
			
			$query = $this->db->query("SELECT model FROM " . DB_PREFIX . "product  WHERE product_id = '" . (int)$product_id . "'");

			$product_data['model'] = $query->row['model'];
			
			$query = $this->db->query("SELECT name FROM " . DB_PREFIX . "product_description  WHERE product_id = '" . (int)$product_id . "' AND language_id = '" . (int)$this->config->get('config_language_id') ."'");

			$product_data['name'] = $query->row['name'];
	
			return $product_data;
		
	}


	public function exportCsv($file_name) {
		$gpf_fields = array(
			'gpf_status',
			'gtin',
			'google_product_category',
			'brand',
			'gender',
			'agegroup',
			'colour',
			'size',
			'condition',
			'adwords_grouping',
			'adwords_labels',
			'adwords_redirect',
			'oos_status',
			'identifier_exists',
			'custom_labels',
			'gpf_size_system',
			'gpf_size_type',
			'gpf_availability_date',
			'gpf_availability_time',
			'gpf_is_bundle',
			'unit_pricing_measure',
			'unit_pricing_base_measure',
			'energy_efficiency_class',
			'promotion_id'
		);
		
		
		$db_fields = array();
		$query = $this->db->query("SHOW COLUMNS FROM " . DB_PREFIX . "product_gpf");
		if($query->num_rows) {
			foreach($query->rows as $row) {
				$db_fields[] = $row['Field'];
			}
		}
		
		$sql = "SELECT p.product_id, pd.name, p.model, p.mpn";
		$output = '"Type","product_id","name","model","mpn"';
		
		$output .= ',"v:gtin","v:mpn","v:size","v:colour","v:pattern","v:material","v:image"';		
		
		foreach($gpf_fields as $gpf_field) {
			if(in_array($gpf_field, $db_fields)) {
				$sql .= ', pg.' . $gpf_field;
				$output .= ',"' . $gpf_field . '"';
			}
		}
		
		$output .= "\n";
		$this->write($output, $file_name);
		$output = '';
	
		$sql .= " FROM " . DB_PREFIX . "product p LEFT JOIN " . DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id) LEFT JOIN " . DB_PREFIX . "product_gpf pg ON (p.product_id = pg.product_id) WHERE pd.language_id = '" . (int)$this->config->get('config_language_id') . "'";
		$query = $this->db->query($sql);
		
		foreach($query->rows as $result) {
			$output .= '"D","' . $result['product_id'] .'","' . $result['name'] .'","' . $result['model'] .'","' . $result['mpn'] .'",,,,,,,';
			foreach($gpf_fields as $gpf_field) {
				if(in_array($gpf_field, $db_fields)) {
					if($gpf_field == 'custom_labels' && $this->cl_delimiter != ',') {
					$result[$gpf_field] = str_replace($this->cl_delimiter, '|', $result[$gpf_field]);
					}
					$output .= ',"' . $result[$gpf_field] . '"';
				}
			}			
			$output .= "\n";
			$this->write($output, $file_name);
			$output = '';
			
			$variants = false;
			$variants_result = $this->db->query("SELECT * FROM " . DB_PREFIX . "product_variants WHERE product_id = " . (int)$result['product_id']);
			if($variants_result->num_rows) {
				$variants = true;
			}
			
			if($variants) {
				foreach($variants_result->rows as $variant) {
					$output .= '"V","' . $result['product_id'] .'","' . $result['name'] .'","' . $result['model'] .'",,';
					$output .= '"' . $variant['v_gtin'] . '","' . $variant['v_mpn'] . '","' . $variant['v_size'] . '","' . $variant['v_colour'] . '","' . $variant['v_pattern'] . '","' . $variant['v_material'] . '","' . $variant['v_image'] . '"';
					$output .= "\n";
					$this->write($output, $file_name);
					$output = '';
				}
			}
		}
	}

	public function importCsv($file_name) {
		$query = $this->db->query("SHOW COLUMNS FROM " . DB_PREFIX . "product_gpf");		
		$table_columns = $query->rows;

		$field_array = array();
		$field_array[0] ='Type';
		foreach($table_columns as $row) {
			$field_array[] = $row['Field'];
		}
		
		// First Pass - product table	
		$fp = fopen($file_name, 'r');
		
		$fields = fgetcsv($fp, 1000, ",");
			
		while (($data = fgetcsv($fp, 1000, ",")) !== FALSE) {
			if($data[0] == 'D' || $data[0] == 'd') {
				$query = '';
				$query2 = '';
				
				foreach($fields as $key => $column) {
					
					if($column == 'product_id') {
						$product_id = $data[$key];
						continue;
					}
					if($column == 'model' || $column == 'name' || $column == 'Type') {
						continue;
					}

					if($column == 'custom_labels') {
						$cl = $data[$key];
						if($cl != '' && $this->cl_delimiter != ',') {
							$cl_array = explode($this->cl_delimiter, $cl);
							for($i = 0; $i <= 4; $i++) {
								if(!isset($cl_array[$i])) {
									$cl_array[$i] = '';
								}
							}
							$cl = implode(',', $cl_array);
						}
						$data[$key] = $cl;
					}

					if($column == 'mpn') {
						$query2 = "UPDATE " . DB_PREFIX . "product SET `mpn` = '" . $this->db->escape($data[$key]) . "' WHERE product_id = " . (int)$product_id;
						
					} else {
						if(in_array($column, $field_array)) {
							foreach($table_columns as $row) {
								if($row['Field'] == $column) {
									$type = substr(strtolower($row['Type']), 0, 3);
								}
							}
							if($query == '') {
							// Don't include a comma at the beginning
								if($type == 'int') {
									$query .= '`' . $column . "` = " . (int)$data[$key];
								} else {
									$query .= '`' . $column . "` = '" . $this->db->escape($data[$key]) . "'";
								}
							} else {
							// Do include a comma at the beginning
								if($type == 'int') {
									$query .= ", `" . $column . "` = " . (int)$data[$key];
								} else {
									$query .= ", `" . $column . "` = '" . $this->db->escape($data[$key]) . "'";
								}
							}
						}
					}
				}
				$full_query = "UPDATE " . DB_PREFIX . "product_gpf SET " . $query . " WHERE product_id = " . (int)$product_id;
				$query = $this->db->query($full_query);	
				if($query2) {
					$query = $this->db->query($query2);	
				}
			}
		}	
		
		fclose($fp);

// Second Pass - Product Variants
		$product_variants = array();
		$columns = array();
		
		$fp = fopen($file_name, 'r');
		
		$fields = fgetcsv($fp, 1000, ",");
		foreach($fields as $key => $column) {
			$columns[$column] = $key;
		}
			
		while (($data = fgetcsv($fp, 1000, ",")) !== FALSE) {
			
			if($data[0] == 'V' || $data[0] == 'v') {
		
				if($data[$columns['v:gtin']] != '' || $data[$columns['v:mpn']] != '' || $data[$columns['v:size']] != '' || $data[$columns['v:colour']] != '' || $data[$columns['v:material']] != '' || $data[$columns['v:pattern']] != '' || $data[$columns['v:image']] != '') {
					$product_variants[$data[$columns['product_id']]][] = array(
						'gtin'		=> $this->db->escape($data[$columns['v:gtin']]),
						'mpn'		=> $this->db->escape($data[$columns['v:mpn']]),
						'size'		=> $this->db->escape($data[$columns['v:size']]),
						'colour'	=> $this->db->escape($data[$columns['v:colour']]),
						'pattern'	=> $this->db->escape($data[$columns['v:pattern']]),
						'material'	=> $this->db->escape($data[$columns['v:material']]),
						'image'		=> $this->db->escape($data[$columns['v:image']])
					);
				}
			}
		
		}
		
		foreach($product_variants as $key => $variants) {
			$query = $this->db->query("DELETE FROM " . DB_PREFIX . "product_variants WHERE product_id = " . (int)$key);
			foreach($variants as $variant) {
				$query = $this->db->query("INSERT INTO " . DB_PREFIX . "product_variants (`product_id`, `v_gtin`, `v_mpn`, `v_size`, `v_colour`, `v_pattern`, `v_material`, `v_image`) VALUES (" . (int)$key . ", '" . $variant['gtin'] . "', '" . $variant['mpn'] . "', '" . $variant['size'] . "', '" . $variant['colour'] . "', '" . $variant['pattern'] . "', '" . $variant['material'] . "', '" . $variant['image'] . "')");
			}
		}
		
	}

	public function exportXlsx($file_name) {
		
// Products		
		$gtin = $this->config->get('gpf_gtin');
		if($gtin == '' || $gtin == 'gtin') {
			$gtin = ' ';
		}

		$header = array(
			'product_id'=>'0',
			'name'=>'@',
			'model'=>'@',
			'mpn'=>'@',
			$gtin=>'@',
			'gpf_status'=>'0',
			'gtin'=>'@',
			'google_product_category'=>'@',
			'brand'=>'@',
			'gender'=>'@',
			'agegroup'=>'@',
			'colour'=>'@',
			'size'=>'@',
			'condition'=>'@',
			'adwords_grouping'=>'@',
			'adwords_labels'=>'@',
			'adwords_redirect'=>'0',
			'oos_status'=>'0',
			'identifier_exists'=>'@',
			'custom_labels'=>'@',
			'gpf_size_system'=>'@',
			'gpf_size_type'=>'@',
			'gpf_availability_date'=>'YYYY-MM-DD',
			'gpf_availability_time'=>'YYYY-MM-DD',
			'gpf_is_bundle'=>'@',
			'unit_pricing_measure'=>'@',
			'unit_pricing_base_measure'=>'@',
			'energy_efficiency_class'=>'@',
			'promotion_id'=>'@'
		);
		
		$gpf_fields = array();
		foreach($header as $key => $value) {
			if($key != ' ') {
				$gpf_fields[] = $key;
			}
		}
		
		$db_fields = array();
		$query = $this->db->query("SHOW COLUMNS FROM " . DB_PREFIX . "product_gpf");
		if($query->num_rows) {
			foreach($query->rows as $row) {
				$db_fields[] = $row['Field'];
			}
		}
		
		$sql = "SELECT p.product_id, pd.name, p.model, p.mpn";
		if($gtin != 'gtin' && $gtin != ' ') {
			$sql .= ", p." . $gtin;
		}
		
		
		foreach($gpf_fields as $gpf_field) {
			if(in_array($gpf_field, $db_fields)) {
				$sql .= ', pg.' . $gpf_field;
			}
		}
			
		$sql .= " FROM " . DB_PREFIX . "product p LEFT JOIN " . DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id) LEFT JOIN " . DB_PREFIX . "product_gpf pg ON (p.product_id = pg.product_id) WHERE pd.language_id = '" . (int)$this->config->get('config_language_id') . "'";
		$query = $this->db->query($sql);
		


		// Create a simple spreadsheet with two sheets;
		require_once '../system/library/google_product_feed/PHPExcel.php';

		// Create new PHPExcel object
		$objPHPExcel = new PHPExcel();
		
		// Create a first sheet, representing product data
		$objPHPExcel->setActiveSheetIndex(0);

		// Set Header Row
		$rowCount = 1;  
		//start of printing column names as names of MySQL fields  
		$column = 'A';
		foreach($gpf_fields as $field) {
			$objPHPExcel->getActiveSheet()->setCellValue($column . $rowCount, $field);	// strip_tags($value) ?
			$column++;
		}  


		// Add Data
		$rowCount = 2;
		foreach($query->rows as $row) {
			$column = 'A';
			foreach($row as $key => $value) {
				$objPHPExcel->getActiveSheet()->setCellValue($column . $rowCount, $value);	// strip_tags($value) ?
				$column++;
			}
			$rowCount++;
		}
		// Rename sheet
		$objPHPExcel->getActiveSheet()->setTitle('Product Data');








// Product Variants
		$header = array(
			'product_id'=>'0',
			'name'=>'@',
			'model'=>'@',
			'v_gtin'=>'@',
			'v_mpn'=>'@',
			'v_size'=>'@',
			'v_colour'=>'@',
			'v_pattern'=>'@',
			'v_material'=>'@',
			'v_image'=>'@'
		);
		
		$gpf_fields = array();
		foreach($header as $key => $value) {
			$gpf_fields[] = $key;
		}
		
		$db_fields = array();
		$query = $this->db->query("SHOW COLUMNS FROM " . DB_PREFIX . "product_variants");
		if($query->num_rows) {
			foreach($query->rows as $row) {
				$db_fields[] = $row['Field'];
			}
		}
		
		$sql = "SELECT p.product_id, pd.name, p.model";
		
		
		foreach($gpf_fields as $gpf_field) {
			if(in_array($gpf_field, $db_fields)) {
				if($gpf_field != 'product_id') {
					$sql .= ', pv.' . $gpf_field;
				}
			}
		}
			
		$sql .= " FROM " . DB_PREFIX . "product_variants pv LEFT JOIN " . DB_PREFIX . "product_description pd ON (pv.product_id = pd.product_id) LEFT JOIN " . DB_PREFIX . "product p ON (p.product_id = pv.product_id) WHERE pd.language_id = '" . (int)$this->config->get('config_language_id') . "'";
		$query = $this->db->query($sql);
		
		// Create a new worksheet, after the default sheet
		$objPHPExcel->createSheet();
		
		// Set the new sheet as the active sheet
		$objPHPExcel->setActiveSheetIndex(1);
		
		// Set Header Row
		$rowCount = 1;
		
		//start of printing column names as names of MySQL fields  
		$column = 'A';
		foreach($gpf_fields as $field) {
			$objPHPExcel->getActiveSheet()->setCellValue($column . $rowCount, $field);	// strip_tags($value) ?
			$column++;
		}  


		// Add Data
		$rowCount = 2;
		foreach($query->rows as $row) {
			$column = 'A';
			foreach($row as $key => $value) {
				$objPHPExcel->getActiveSheet()->setCellValue($column . $rowCount, $value);	// strip_tags($value) ?
				$column++;
			}
			$rowCount++;
		}
		// Rename sheet
		$objPHPExcel->getActiveSheet()->setTitle('Product Variants');
		


		// Save Excel 2007 file
		$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
		$objWriter->save($file_name);

		
	}

	public function importXlsx($file_name) {
		
		require_once '../system/library/google_product_feed/PHPExcel.php';
		include '../system/library/google_product_feed/PHPExcel/IOFactory.php';
		
		/**  Identify the type of $inputFileName  **/
		$inputFileType = PHPExcel_IOFactory::identify($file_name);
		$objReader = PHPExcel_IOFactory::createReader($inputFileType);

		// function toArray ()
		//		nullvalue 				null -		value returned if the cell doesn't exist;
		//		calculateformulas		true - 		return results of calculation
		//		formatdata				true -		apply cell formatting ?
		//		return cell ref			true -		returns array indexed by row and column ID
		//								false -		return a simple array of rows and columns indexed by number counting from zero
		
		$chunksize = 100; // rows
		
		$worksheet_data = $objReader->listWorksheetInfo($file_name);
		
		$sheets = array();
		foreach($worksheet_data as $key => $data) {
			$sheets[$data['worksheetName']] = array(
				'index'				=> $key,
				'totalRows'			=> $data['totalRows'],
				'lastColumnLetter'	=> $data['lastColumnLetter']
			);
		}
		// PRODUCT DATA SHEET
		
		if(isset($sheets['Product Data'])) {
			
			// Get fields in table `product_gpf`
			$query = $this->db->query("SHOW COLUMNS IN " . DB_PREFIX . "product_gpf");
			$fields = array();
			if($query->num_rows) {
				foreach($query->rows as $row) {
					$fields[] = $row['Field'];
				}
			}
		
			$num_rows = $sheets['Product Data']['totalRows'];  
			$last_col = $sheets['Product Data']['lastColumnLetter'];
			$range = 'A1:' . $last_col . '1';
			$index = $sheets['Product Data']['index'];
			
			// Get Header Row
			$objPHPExcel = $objReader->load($file_name);
			$objPHPExcel->setActiveSheetIndex($index);
			$sheet_data = $objPHPExcel->getActiveSheet()->rangeToArray('A1:' . $last_col . '1',null,true,true,true);
			$header = $sheet_data[1];
			
			foreach($header as $col => $value) {
				if($value == 'product_id') {
					$product_id_col = $col;
				}
				if($value == $this->config->get('gpf_gtin')) {
					$gtin = $col;
				}
				if($value == 'mpn') {
					$mpn = $col;
				}
			}
			
			// Get Chunks
			for($r = 2; $r <= $num_rows; $r += $chunksize) {
				if($r + $chunksize - 1 <= $num_rows) {
					$end_row = $r + $chunksize - 1;
				} else {
					$end_row = $num_rows;
				}
				
				$sheet_data = $objPHPExcel->getActiveSheet()->rangeToArray('A' . $r . ':' . $last_col . $end_row,null,true,true,true);
				
				foreach($sheet_data as $row) {
					$comma = false;
					$sql = "UPDATE " . DB_PREFIX . "product_gpf SET ";
					foreach($row as $key => $value) {
						
						if($key == $product_id_col) {
							continue;
						} elseif(in_array($header[$key], $fields)) {
							if($comma == true) {
								$sql .= ", `" . $header[$key] . "` = '" . $this->db->escape($value) . "'";
							} else {
								$sql .= "`" . $header[$key] . "` = '" . $this->db->escape($value) . "'";
								$comma = true;
							}
						}
					}
					$sql .= " WHERE `product_id` = " . (int)$row[$product_id_col];
					$this->db->query($sql);
					
					if(isset($gtin) || isset($mpn)) {
						$sql = "UPDATE " . DB_PREFIX . "product SET ";
						
						if(isset($gtin) && $gtin != 'gtin') {
							$sql .= "`" . $header[$gtin] . "` = '" . $row[$gtin] . "'";
						}
						if(isset($mpn)) {
							if(isset($gtin) && $header[$gtin] != 'gtin') {
								$sql .= ", `" . $header[$mpn] . "` = '" . $row[$mpn] . "'";
							} else {
								$sql .= "`" . $header[$mpn] . "` = '" . $row[$mpn] . "'";
							}
						}
						$sql .= " WHERE `product_id` = " . (int)$row[$product_id_col];
						$this->db->query($sql);
					}
				}
			}
		}
		
		
		// Product Variants		
		if(isset($sheets['Product Variants'])) {

			// Get fields in table `product_variants`
			$query = $this->db->query("SHOW COLUMNS IN " . DB_PREFIX . "product_variants");
			$fields = array();
			if($query->num_rows) {
				foreach($query->rows as $row) {
					$fields[] = $row['Field'];
				}
			}
		
			$num_rows = $sheets['Product Variants']['totalRows'];  
			$last_col = $sheets['Product Variants']['lastColumnLetter'];
			$range = 'A1:' . $last_col . '1';
			$index = $sheets['Product Variants']['index'];
			
			// Get Header Row
//			$objPHPExcel = $objReader->load($file_name);
			$objPHPExcel->setActiveSheetIndex($index);
			$sheet_data = $objPHPExcel->getActiveSheet()->rangeToArray('A1:' . $last_col . '1',null,true,true,true);
			$header = $sheet_data[1];
		
			foreach($header as $col => $value) {
				if($value == 'product_id') {
					$product_id_col = $col;
				}
			}
			
			$product_variants = array();
			
			// Get Chunks
			for($r = 2; $r <= $num_rows; $r += $chunksize) {
				if($r + $chunksize - 1 <= $num_rows) {
					$end_row = $r + $chunksize - 1;
				} else {
					$end_row = $num_rows;
				}
				
				$sheet_data = $objPHPExcel->getActiveSheet()->rangeToArray('A' . $r . ':' . $last_col . $end_row,null,true,true,true);
			
				foreach($sheet_data as $row) {
					$product_id = $row[$product_id_col];
					$variants = array();
					
					foreach($row as $key => $value) {
						if($key == $product_id_col) {
							continue;
						} elseif(in_array($header[$key], $fields)) {
							$variants[$header[$key]] = $value;
						}
					}
					$product_variants[$product_id][] = $variants;
				}
			}
		
			foreach($product_variants as $key => $variants) {
				$query = $this->db->query("DELETE FROM " . DB_PREFIX . "product_variants WHERE product_id = " . (int)$key);
				
				foreach($variants as $variant) {
					$sql = "INSERT INTO " . DB_PREFIX . "product_variants SET `product_id` = " . (int)$key;
					foreach($variant as $fld => $value) {
						$sql .= ", `" . $fld . "` = '" . $this->db->escape($value) . "'";
					}
					$query = $this->db->query($sql);
				}
			}
		}
	}

	public function validateCsv($file_name) {
	
		$errors = array();

		$query = $this->db->query("SHOW COLUMNS FROM " . DB_PREFIX . "product_gpf");		
		$table_columns = $query->rows;

		$field_array = array();
		foreach($table_columns as $row) {
			$field_array[] = $row['Field'];
		}
	
		$fp = fopen($file_name, 'r');
		
		$fields = fgetcsv($fp, 1000, ",");
			
		while (($data = fgetcsv($fp, 1000, ",")) !== FALSE) {
			$query = '';
			$query2 = '';
			
			foreach($fields as $key => $column) {
				if($column == 'gtin' && $data[$key] != '' && preg_match("/^([0-9]{12,13}|[0-9]{8})$/", $data[$key]) == 0) {
					if(isset($errors['gtin'])) {
						$errors['gtin']++;
					} else {
						$errors['gtin'] = 1;
					}
				}

				if($column == 'gpf_availability_date' && $data[$key] != '' && preg_match("/^[0-9]{4}-[0-9]{2}-[0-9]{2}$/", $data[$key]) == 0) {
					if(isset($errors['gpf_availability_date'])) {
						$errors['gpf_availability_date']++;
					} else {
						$errors['gpf_availability_date'] = 1;
					}
				}

				if($column == 'gpf_availability_time' && $data[$key] != '' && preg_match("/^[0-9]{1,2}:[0-9]{2}$/", $data[$key]) == 0) {
					if(isset($errors['gpf_availability_time'])) {
						$errors['gpf_availability_time']++;
					} else {
						$errors['gpf_availability_time'] = 1;
					}
				}
			}
		}	
		
		fclose($fp);
	
		return $errors;
	}	
		
	
	public function getCategories($parent_id = 0) {
		$category_data = $this->cache->get('category.' . (int)$this->config->get('config_language_id') . '.' . (int)$parent_id);
	
		if (!$category_data) {
			$category_data = array();
		
			$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "category c LEFT JOIN " . DB_PREFIX . "category_description cd ON (c.category_id = cd.category_id) WHERE c.parent_id = '" . (int)$parent_id . "' AND cd.language_id = '" . (int)$this->config->get('config_language_id') . "' ORDER BY c.sort_order, cd.name ASC");
		
			foreach ($query->rows as $result) {
				$category_data[] = array(
					'category_id' => $result['category_id'],
					'name'        => $this->getPath($result['category_id'], $this->config->get('config_language_id')),
					'status'  	  => $result['status'],
					'sort_order'  => $result['sort_order']
				);
			
				$category_data = array_merge($category_data, $this->getCategories($result['category_id']));
			}	
	
			$this->cache->set('category.' . (int)$this->config->get('config_language_id') . '.' . (int)$parent_id, $category_data);
		}
		
		return $category_data;
	}
	
	public function getOptions() {
		$options = array();
		
		$query = $this->db->query("SELECT * FROM `" . DB_PREFIX . "option_description`");
		
		if($query->num_rows) {
			foreach($query->rows as $result) {
				$options[] = array(
					'option_id'	=> $result['option_id'],
					'option'	=> $result['name']
				);
			}
		}
		return $options;		
	}
	
	public function generateProductVariants($data) {
	
		$language = $this->config->get('config_language');
		$query = $this->db->query("SELECT * FROM `" . DB_PREFIX . "language` WHERE `code` = '" . $language . "'");
		$language_id = $query->row['language_id'];
	
		$size = array($data['size1'], $data['size2'], $data['size3']);
		$colour = $data['colour'];
		$material = $data['material'];
		$pattern = $data['pattern'];
		
		if(!$data['product_id']) {
			// All Products (from Google Product Feed extension page)
			$query = $this->db->query("SELECT DISTINCT product_id FROM " . DB_PREFIX . "product_option WHERE option_id = " . (int)$data['size1'] . " OR option_id = " . (int)$data['size2'] . " OR option_id = " . (int)$data['size3'] . " OR option_id = " . (int)$colour . " OR option_id = " . (int)$material . " OR option_id = " . (int)$pattern);
			
			$all_products = array();
			$existing_product_variants = array();
			if($query->num_rows) {
				foreach($query->rows as $row) {
					$all_products[] = $row['product_id'];
				}
			}
			
			if($data['all'] == 'No') {
				// Get Existing Product Variants
				$query = $this->db->query("SELECT DISTINCT product_id FROM " . DB_PREFIX . "product_variants");
				if($query->num_rows) {
					foreach($query->rows as $row) {
						$existing_product_variants[] = $row['product_id'];
					}
				}
			} else {
				$query = $this->db->query("TRUNCATE TABLE " . DB_PREFIX . "product_variants");
			}
			
			$product_list = array_diff($all_products, $existing_product_variants);
			
			foreach($product_list as $product_id) {
				$options = array();
				$query = $this->db->query("SELECT pov.option_id, ovd.name, ovd.language_id FROM " . DB_PREFIX . "product_option_value pov LEFT JOIN " . DB_PREFIX . "option_value_description ovd ON (pov.option_value_id = ovd.option_value_id) WHERE product_id = " . (int)$product_id . " AND ovd.language_id = " . $language_id);
				if($query->num_rows) {

					foreach($query->rows as $row) {
						$options[] = array(
							'option_id'		=> $row['option_id'],
							'name'			=> $row['name']
							);
					}
				}
				$s = array();
				$c = array();
				$p = array();
				$m = array();
				foreach($options as $option) {
					if(in_array($option['option_id'], $size)) {
						$s[] = $option['name'];
					}
					if($option['option_id'] == $colour) {
						$c[] = $option['name'];
					}
					if($option['option_id'] == $pattern) {
						$p[] = $option['name'];
					}
					if($option['option_id'] == $material) {
						$m[] = $option['name'];
					}
				}
				
				if(empty($s)) {
					$s[] = '';
				}
				if(empty($c)) {
					$c[] = '';
				}
				if(empty($p)) {
					$p[] = '';
				}
				if(empty($m)) {
					$m[] = '';
				}
				$product_variants = array();
				foreach($s as $size_option) {
					foreach($c as $colour_option) {
						foreach($p as $pattern_option) {
							foreach($m as $material_option) {
								$product_variants[] = array(
									'mpn'		=> '',
									'size'		=> $size_option,
									'colour'	=> $colour_option,
									'pattern'	=> $pattern_option,
									'material'	=> $material_option,
									'image'		=> ''
								);
							}
						}
					}
				}
				
				foreach($product_variants as $variant) {
					$query = $this->db->query("INSERT INTO " . DB_PREFIX . "product_variants SET product_id = " . (int)$product_id . ", v_mpn = '" . $this->db->escape($variant['mpn']) . "', v_size = '" . $this->db->escape($variant['size']) . "', v_colour = '" . $this->db->escape($variant['colour']) . "', v_pattern = '" . $this->db->escape($variant['pattern']) . "', v_material = '" . $this->db->escape($variant['material']) . "', v_image = '" . $this->db->escape($variant['image']) . "'");
				
				}
				
			}
		} elseif(is_array($data['product_id'])) {
			//Bulk Update
			foreach($data['product_id'] as $product_id) {
				$options = array();

				$query = $this->db->query("SELECT pov.option_id, ovd.name, ovd.language_id FROM " . DB_PREFIX . "product_option_value pov LEFT JOIN " . DB_PREFIX . "option_value_description ovd ON (pov.option_value_id = ovd.option_value_id) WHERE product_id = " . (int)$product_id . " AND ovd.language_id = " . $language_id);

				if($query->num_rows) {
					foreach($query->rows as $row) {
						$options[] = array(
							'option_id'		=> $row['option_id'],
							'name'			=> $row['name']
							);
					}
				}
								
				$s = array();
				$c = array();
				$p = array();
				$m = array();
				foreach($options as $option) {
					if(in_array($option['option_id'], $size)) {
						$s[] = $option['name'];
					}
					if($option['option_id'] == $colour) {
						$c[] = $option['name'];
					}
					if($option['option_id'] == $pattern) {
						$p[] = $option['name'];
					}
					if($option['option_id'] == $material) {
						$m[] = $option['name'];
					}
				}
				
				if(empty($s)) {
					$s[] = '';
				}
				if(empty($c)) {
					$c[] = '';
				}
				if(empty($p)) {
					$p[] = '';
				}
				if(empty($m)) {
					$m[] = '';
				}
				$product_variants = array();
				foreach($s as $size_option) {
					foreach($c as $colour_option) {
						foreach($p as $pattern_option) {
							foreach($m as $material_option) {
								$product_variants[] = array(
									'mpn'		=> '',
									'size'		=> $size_option,
									'colour'	=> $colour_option,
									'pattern'	=> $pattern_option,
									'material'	=> $material_option,
									'image'		=> ''
								);
							}
						}
					}
				}
			
				$query = $this->db->query("DELETE FROM " . DB_PREFIX . "product_variants WHERE product_id = " . (int)$product_id);
				foreach($product_variants as $variant) {
					$query = $this->db->query("INSERT INTO " . DB_PREFIX . "product_variants SET product_id = " . (int)$product_id . ", v_mpn = '" . $this->db->escape($variant['mpn']) . "', v_size = '" . $this->db->escape($variant['size']) . "', v_colour = '" . $this->db->escape($variant['colour']) . "', v_pattern = '" . $this->db->escape($variant['pattern']) . "', v_material = '" . $this->db->escape($variant['material']) . "', v_image = '" . $this->db->escape($variant['image']) . "'");
				
				}
			}
			
		} else {
			// Individual Product
				$product_id = $data['product_id'];
				$options = array();
				$query = $this->db->query("SELECT pov.option_id, ovd.name, ovd.language_id FROM " . DB_PREFIX . "product_option_value pov LEFT JOIN " . DB_PREFIX . "option_value_description ovd ON (pov.option_value_id = ovd.option_value_id) WHERE product_id = " . (int)$product_id . " AND ovd.language_id = " . $language_id);
				if($query->num_rows) {
					foreach($query->rows as $row) {
						$options[] = array(
							'option_id'		=> $row['option_id'],
							'name'			=> $row['name']
							);
					}
				}
								
				$s = array();
				$c = array();
				$p = array();
				$m = array();
				foreach($options as $option) {
					if(in_array($option['option_id'], $size)) {
						$s[] = $option['name'];
					}
					if($option['option_id'] == $colour) {
						$c[] = $option['name'];
					}
					if($option['option_id'] == $pattern) {
						$p[] = $option['name'];
					}
					if($option['option_id'] == $material) {
						$m[] = $option['name'];
					}
				}
				
				if(empty($s)) {
					$s[] = '';
				}
				if(empty($c)) {
					$c[] = '';
				}
				if(empty($p)) {
					$p[] = '';
				}
				if(empty($m)) {
					$m[] = '';
				}
				$product_variants = array();
				foreach($s as $size_option) {
					foreach($c as $colour_option) {
						foreach($p as $pattern_option) {
							foreach($m as $material_option) {
								$product_variants[] = array(
									'mpn'		=> '',
									'size'		=> $size_option,
									'colour'	=> $colour_option,
									'pattern'	=> $pattern_option,
									'material'	=> $material_option,
									'image'		=> ''
								);
							}
						}
					}
				}
			
				$query = $this->db->query("DELETE FROM " . DB_PREFIX . "product_variants WHERE product_id = " . (int)$product_id);
				foreach($product_variants as $variant) {
					$query = $this->db->query("INSERT INTO " . DB_PREFIX . "product_variants SET product_id = " . (int)$product_id . ", v_mpn = '" . $this->db->escape($variant['mpn']) . "', v_size = '" . $this->db->escape($variant['size']) . "', v_colour = '" . $this->db->escape($variant['colour']) . "', v_pattern = '" . $this->db->escape($variant['pattern']) . "', v_material = '" . $this->db->escape($variant['material']) . "', v_image = '" . $this->db->escape($variant['image']) . "'");
				
				}
		
		}
	}
	
	public function getPath($category_id) {
		$query = $this->db->query("SELECT name, parent_id FROM " . DB_PREFIX . "category c LEFT JOIN " . DB_PREFIX . "category_description cd ON (c.category_id = cd.category_id) WHERE c.category_id = '" . (int)$category_id . "' AND cd.language_id = '" . (int)$this->config->get('config_language_id') . "' ORDER BY c.sort_order, cd.name ASC");
		
		if ($query->row['parent_id']) {
			return $this->getPath($query->row['parent_id'], $this->config->get('config_language_id')) . ' > ' . $query->row['name'];
		} else {
			return $query->row['name'];
		}
	}
		
	public function checkGPFTables() {
	
		// GPF Table
		$gpf_table_created = false;
		$query = $this->db->query("SHOW TABLES LIKE '".DB_PREFIX."product_gpf'");
		
		if($query->num_rows != 1) {
			$gpf_table_created = true;
			$result = $this->db->query("CREATE TABLE IF NOT EXISTS `" . DB_PREFIX . "product_gpf` (
				  `gpf_id` int(11) NOT NULL auto_increment,
				  `product_id` int(11) NOT NULL,
				  `gpf_status` int(11) NOT NULL,
				  `gtin` varchar(255) NOT NULL,
				  `google_product_category` varchar(255) NOT NULL,
				  `brand` varchar(64) NOT NULL,
				  `gender` varchar(20) NOT NULL,
				  `agegroup` varchar(20) NOT NULL,
				  `colour` varchar(255) NOT NULL,
				  `size` varchar(255) NOT NULL,
				  `condition` varchar(20) NOT NULL,
				  `promotion_id` varchar(255) NOT NULL,
				  `adwords_grouping` varchar(64) NOT NULL,
				  `adwords_labels` varchar(255) NOT NULL,
				  `adwords_redirect` int(11) NOT NULL,
				  `oos_status` varchar(20) NOT NULL,
				  `gpf_size_system` varchar(20) NOT NULL,
				  `gpf_size_type` varchar(20) NOT NULL,
				  `gpf_availability_date` varchar(10) NOT NULL,
				  `gpf_availability_time` varchar(5) NOT NULL,
				  `gpf_is_bundle` varchar(10) NOT NULL,
				  `unit_pricing_measure` varchar(20) NOT NULL,
				  `unit_pricing_base_measure` varchar(20) NOT NULL,
				  `energy_efficiency_class` varchar(20) NOT NULL,
				  `identifier_exists` varchar(20) NOT NULL,
				  `custom_labels` varchar(256) NOT NULL,
				  PRIMARY KEY (`gpf_id`)) ENGINE=MyISAM  DEFAULT CHARSET=utf8"
			);
		}

		$query = $this->db->query("SHOW COLUMNS FROM `".DB_PREFIX."product_gpf`");
		if($query->num_rows) {
			foreach($query->rows as $row) {
				if($row['Field'] == 'gtin' && $row['Type'] != 'varchar(255)') {
					$result = $this->db->query("ALTER TABLE `" . DB_PREFIX . "product_gpf` MODIFY  `gtin` VARCHAR(255) NOT NULL");					
				}
			}
		}

		$query = $this->db->query("SELECT * FROM `" . DB_PREFIX . "product_gpf` LIMIT 1");
		if(!$query->num_rows) {
			$this->db->query("INSERT INTO " . DB_PREFIX . "product_gpf SET product_id = 0");
			$query = $this->db->query("SELECT * FROM `" . DB_PREFIX . "product_gpf` LIMIT 1");
			$this->db->query("DELETE FROM " . DB_PREFIX . "product_gpf WHERE product_id = 0");
		}

		if (!isset($query->row['gpf_status'])) {
			$result = $this->db->query("ALTER TABLE `" . DB_PREFIX . "product_gpf` ADD  `gpf_status` INT NOT NULL");
		}
				
		
		if (!isset($query->row['gtin'])) {
			$result = $this->db->query("ALTER TABLE `" . DB_PREFIX . "product_gpf` ADD  `gtin` VARCHAR(255) NOT NULL");
		}
		

		if (!isset($query->row['google_product_category'])) {
			$result = $this->db->query("ALTER TABLE `" . DB_PREFIX . "product_gpf` ADD  `google_product_category` VARCHAR(255) NOT NULL");
		}
				

		if (!isset($query->row['brand'])) {
			$result = $this->db->query("ALTER TABLE `" . DB_PREFIX . "product_gpf` ADD  `brand` VARCHAR(64) NOT NULL");
		}
				
		if (!isset($query->row['gender'])) {
			$result = $this->db->query("ALTER TABLE `" . DB_PREFIX . "product_gpf` ADD  `gender` VARCHAR(20) NOT NULL");
		}
				
		if (!isset($query->row['agegroup'])) {
			$result = $this->db->query("ALTER TABLE `" . DB_PREFIX . "product_gpf` ADD  `agegroup` VARCHAR(20) NOT NULL");
		}
				
		if (!isset($query->row['colour'])) {
			$result = $this->db->query("ALTER TABLE `" . DB_PREFIX . "product_gpf` ADD  `colour` VARCHAR(255) NOT NULL");
		}
			
		if (!isset($query->row['size'])) {
			$result = $this->db->query("ALTER TABLE `" . DB_PREFIX . "product_gpf` ADD  `size` VARCHAR(255) NOT NULL");
		}
				
		if (!isset($query->row['condition'])) {
			$result = $this->db->query("ALTER TABLE `" . DB_PREFIX . "product_gpf` ADD  `condition` VARCHAR(20) NOT NULL");
		}
				
		if (!isset($query->row['promotion_id'])) {
			$result = $this->db->query("ALTER TABLE `" . DB_PREFIX . "product_gpf` ADD  `promotion_id` VARCHAR(255) NOT NULL");
		}
				
		if (!isset($query->row['adwords_grouping'])) {
			$result = $this->db->query("ALTER TABLE `" . DB_PREFIX . "product_gpf` ADD  `adwords_grouping` VARCHAR(64) NOT NULL");
		}
				
		if (!isset($query->row['adwords_labels'])) {
			$result = $this->db->query("ALTER TABLE `" . DB_PREFIX . "product_gpf` ADD  `adwords_labels` VARCHAR(255) NOT NULL");	
		}
		
		if (!isset($query->row['adwords_redirect'])) {
			$result = $this->db->query("ALTER TABLE `" . DB_PREFIX . "product_gpf` ADD  `adwords_redirect` INT NOT NULL");
		}
				
		if (!isset($query->row['oos_status']))
		{
			$result = $this->db->query("ALTER TABLE `" . DB_PREFIX . "product_gpf` ADD  `oos_status` VARCHAR(20) NOT NULL");
		}

		if (!isset($query->row['gpf_size_system'])) {
			$result = $this->db->query("ALTER TABLE `" . DB_PREFIX . "product_gpf` ADD  `gpf_size_system` VARCHAR(20) NOT NULL");
		}
				
		if (!isset($query->row['gpf_size_type'])) {
			$result = $this->db->query("ALTER TABLE `" . DB_PREFIX . "product_gpf` ADD  `gpf_size_type` VARCHAR(20) NOT NULL");
		}
				
		if (!isset($query->row['gpf_availability_date'])) {
			$result = $this->db->query("ALTER TABLE `" . DB_PREFIX . "product_gpf` ADD  `gpf_availability_date` VARCHAR(10) NOT NULL");
		}
				
		if (!isset($query->row['gpf_availability_time'])) {
			$result = $this->db->query("ALTER TABLE `" . DB_PREFIX . "product_gpf` ADD  `gpf_availability_time` VARCHAR(5) NOT NULL");
		}
				
		if (!isset($query->row['gpf_is_bundle'])) {
			$result = $this->db->query("ALTER TABLE `" . DB_PREFIX . "product_gpf` ADD  `gpf_is_bundle` VARCHAR(10) NOT NULL");
		}
				
		if (!isset($query->row['unit_pricing_measure'])) {
			$result = $this->db->query("ALTER TABLE `" . DB_PREFIX . "product_gpf` ADD  `unit_pricing_measure` VARCHAR(20) NOT NULL");
		}
		
		if (!isset($query->row['unit_pricing_base_measure'])) {
			$result = $this->db->query("ALTER TABLE `" . DB_PREFIX . "product_gpf` ADD  `unit_pricing_base_measure` VARCHAR(20) NOT NULL");
		}
		
		if (!isset($query->row['energy_efficiency_class'])) {
			$result = $this->db->query("ALTER TABLE `" . DB_PREFIX . "product_gpf` ADD  `energy_efficiency_class` VARCHAR(20) NOT NULL");
		}
		
		if (!isset($query->row['identifier_exists'])) {
			$result = $this->db->query("ALTER TABLE `" . DB_PREFIX . "product_gpf` ADD  `identifier_exists` VARCHAR(20) NOT NULL");
		}
				
		if (!isset($query->row['custom_labels'])) {
			$result = $this->db->query("ALTER TABLE `" . DB_PREFIX . "product_gpf` ADD  `custom_labels` VARCHAR(256) NOT NULL");
		}
		
		if($gpf_table_created) {
		// Check if this is an upgrade from 1.5.x
			$query = $this->db->query("SELECT * FROM `" . DB_PREFIX . "product`");
			
			$update_query = 'SELECT product_id';
			
			if (isset($query->row['gpf_status'])) {
				$update_query .= ", gpf_status";
			}
					
			
			if (isset($query->row['gtin'])) {
				$update_query .= ", gtin";
			}
					
			
			if (isset($query->row['google_product_category'])) {
				$update_query .= ", google_product_category";
			}
					

			if (isset($query->row['brand'])) {
				$update_query .= ", brand";
			}
					
			if (isset($query->row['gender'])) {
				$update_query .= ", gender";
			}
					
			if (isset($query->row['agegroup'])) {
				$update_query .= ", agegroup";
			}
					
			if (isset($query->row['colour'])) {
				$update_query .= ", colour";
			}
				
			if (isset($query->row['size'])) {
				$update_query .= ", size";
			}
					
			if (isset($query->row['condition'])) {
				$update_query .= ", `condition`";
			}
					
			if (isset($query->row['promotion_id'])) {
				$update_query .= ", promotion_id";
			}
					
			if (isset($query->row['adwords_grouping'])) {
				$update_query .= ", adwords_grouping";
			}
					
			if (isset($query->row['adwords_labels'])) {
				$update_query .= ", adwords_labels";
			}
			
			if (isset($query->row['adwords_redirect'])) {
				$update_query .= ", adwords_redirect";
			}
					
			if (isset($query->row['oos_status']))
			{
				$update_query .= ", oos_status";
			}

			if (isset($query->row['gpf_size_system'])) {
				$update_query .= ", gpf_size_system";
			}
					
			if (isset($query->row['gpf_size_type'])) {
				$update_query .= ", gpf_size_type";
			}
					
			if (isset($query->row['gpf_availability_date'])) {
				$update_query .= ", gpf_availability_date";
			}
					
			if (isset($query->row['gpf_availability_time'])) {
				$update_query .= ", gpf_availability_time";
			}
					
			if (isset($query->row['gpf_is_bundle'])) {
				$update_query .= ", gpf_is_bundle";
			}
					
			if (isset($query->row['unit_pricing_measure'])) {
				$update_query .= ", unit_pricing_measure";
			}
			
			if (isset($query->row['unit_pricing_base_measure'])) {
				$update_query .= ", unit_pricing_base_measure";
			}
			
			if (isset($query->row['energy_efficiency_class'])) {
				$update_query .= ", energy_efficiency_class";
			}
			
			if (isset($query->row['identifier_exists'])) {
				$update_query .= ", identifier_exists";
			}
					
			if (isset($query->row['custom_labels'])) {
				$update_query .= ", custom_labels";
			}
			
			$update_query .= " FROM `" . DB_PREFIX . "product`";
			
			$query = $this->db->query($update_query);
			
			foreach($query->rows as $row) {
				$insert_query = "INSERT INTO " . DB_PREFIX . "product_gpf SET product_id = " . (int)$row['product_id'];
				foreach($row as $key => $value) {
					if($key != 'product_id') {
						$insert_query .= ", `" . $key . "` = '" . $value . "'";
					}
				}
				$this->db->query($insert_query);		
			}
		
		}
				
		// Add Google Shopping Tracker Table
		$query = $this->db->query("SHOW TABLES LIKE '".DB_PREFIX."google_shopping_tracker'");
		if($query->num_rows != 1) {
			$result = $this->db->query("CREATE TABLE IF NOT EXISTS `" . DB_PREFIX ."google_shopping_tracker` (
							  `tracker_id` int(11) NOT NULL auto_increment,
							  `visitor_ip` VARCHAR(20) NOT NULL,
							  `visitor_user_agent` VARCHAR(1024) NOT NULL,
							  `visitor_hour` SMALLINT(2) NOT NULL,
							  `visitor_minute` SMALLINT(2) NOT NULL,
							  `visitor_date` TIMESTAMP NOT NULL,
							  `visitor_day` SMALLINT(2) NOT NULL,
							  `visitor_month` SMALLINT(2) NOT NULL,
							  `visitor_year` SMALLINT(4) NOT NULL,
							  `visitor_referrer` VARCHAR(1024) NOT NULL,
							  `product_id` int(11) NOT NULL,
							  `source` VARCHAR(64) NOT NULL,
							  `grouping` VARCHAR(64) NOT NULL,
							  PRIMARY KEY  (`tracker_id`)
							) ENGINE=MyISAM  DEFAULT CHARSET=utf8");
							
			
		}

		// Add Product Variants Table
		$query = $this->db->query("SHOW TABLES LIKE '".DB_PREFIX."product_variants'");
		if($query->num_rows != 1) {
			$result = $this->db->query("CREATE TABLE IF NOT EXISTS `" . DB_PREFIX ."product_variants` (
							  `v_id` int(11) NOT NULL auto_increment,							
							  `product_id` int(11) NOT NULL,
							  `v_gtin` VARCHAR(60) NOT NULL,
							  `v_mpn` VARCHAR(15) NOT NULL,
							  `v_size` VARCHAR(40) NOT NULL,
							  `v_colour` VARCHAR(40) NOT NULL,
							  `v_pattern` VARCHAR(40) NOT NULL,
							  `v_material` VARCHAR(40) NOT NULL,
							  `v_image` VARCHAR(128) NOT NULL,
							  PRIMARY KEY  (`v_id`)
							) ENGINE=MyISAM  DEFAULT CHARSET=utf8");
			
		}

		$flag = 0;
		$variants = 1;
		
		$query = $this->db->query("SELECT * FROM ".DB_PREFIX."product_variants LIMIT 1");
		if(!$query->num_rows) {
			$variants = 0;
			$this->db->query("INSERT INTO ".DB_PREFIX."product_variants SET product_id = 0");
			$query = $this->db->query("SELECT * FROM ".DB_PREFIX."product_variants LIMIT 1");
			$this->db->query("DELETE FROM ".DB_PREFIX."product_variants WHERE product_id = 0");
		}
		
		if (!isset($query->row['v_gtin'])) {
			$result = $this->db->query("ALTER TABLE `" . DB_PREFIX . "product_variants` ADD  `v_gtin` VARCHAR(60) NOT NULL");
			$flag = 1;
		}
				
		if (!isset($query->row['v_mpn'])) {
			$result = $this->db->query("ALTER TABLE `" . DB_PREFIX . "product_variants` ADD  `v_mpn` VARCHAR(15) NOT NULL");
			$flag = 1;
		}
				
		if (!isset($query->row['v_size'])) {
			$result = $this->db->query("ALTER TABLE `" . DB_PREFIX . "product_variants` ADD  `v_size` VARCHAR(40) NOT NULL");
			$flag = 1;
		}
				
		if (!isset($query->row['v_colour'])) {
			$result = $this->db->query("ALTER TABLE `" . DB_PREFIX . "product_variants` ADD  `v_colour` VARCHAR(40) NOT NULL");
			$flag = 1;
		}
				
		if (!isset($query->row['v_pattern'])) {
			$result = $this->db->query("ALTER TABLE `" . DB_PREFIX . "product_variants` ADD  `v_pattern` VARCHAR(40) NOT NULL");
			$flag = 1;
		}
				
		if (!isset($query->row['v_material'])) {
			$result = $this->db->query("ALTER TABLE `" . DB_PREFIX . "product_variants` ADD  `v_material` VARCHAR(40) NOT NULL");
			$flag = 1;
		}
				
		if (!isset($query->row['v_image'])) {
			$result = $this->db->query("ALTER TABLE `" . DB_PREFIX . "product_variants` ADD  `v_image` VARCHAR(128) NOT NULL");
			$flag = 1;
		}
				
		if (!isset($query->row['v_id'])) {
			$result = $this->db->query("ALTER TABLE `" . DB_PREFIX . "product_variants` CHANGE `product_id` `product_id` INT(11) NOT NULL");
			$result = $this->db->query("ALTER TABLE `" . DB_PREFIX . "product_variants` DROP PRIMARY KEY");
			$result = $this->db->query("ALTER TABLE `" . DB_PREFIX . "product_variants` ADD  `v_id` INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST");
			$flag = 1;
		}
		
/*		if($flag == 1 && $variants == 1) {
			//Convert Variants
			$query = $this->db->query("SELECT * FROM ".DB_PREFIX."product_variants");
			foreach($query->rows as $row) {
				$product_variants = array();
				$product_variants = unserialize($row['variants']);
				foreach($product_variants as $variant) {
					if(!isset($variant['mpn'])) {
						$variant['mpn'] = '';
					}
					$this->db->query("INSERT INTO `" . DB_PREFIX . "product_variants` SET product_id = " . (int)$row['product_id'] . ", v_mpn = '" . $variant['mpn'] . "', v_size = '" . $variant['size'] . "', v_colour = '" . $variant['colour'] . "', v_pattern = '" . $variant['pattern'] . "', v_material = '" . $variant['material'] . "', v_image = '" . $variant['image'] . "'");
				}
				$this->db->query("DELETE FROM `" . DB_PREFIX . "product_variants` WHERE variants != ''");
			}
		}
*/				
		
		// Convert language code for taxonomy
		$query = $this->db->query("SELECT value FROM `" . DB_PREFIX . "setting` WHERE `KEY` = 'gpf_lang'");
		
		
		$new_language = '';
		if ($query->num_rows) {
			$language = $query->row;
			$us_data = @unserialize($data['variants']);
			if ($language['value'] == 'cs') {
				$new_language = 'cs-CZ';
			}
			if ($language['value'] == 'de') {
				$new_language = 'de_DE';
			}
			if ($language['value'] == 'en') {
				$new_language = 'en-US';
			}
			if ($language['value'] == 'es') {
				$new_language = 'es-ES';
			}
			if ($language['value'] == 'fr') {
				$new_language = 'fr-FR';
			}
			if ($language['value'] == 'it') {
				$new_language = 'it-IT';
			}
			if ($language['value'] == 'nl') {
				$new_language = 'nl-NL';
			}
			if ($language['value'] == 'br') {
				$new_language = 'pt-BR';
			}
			if ($language['value'] == 'cn') {
				$new_language = 'ch-US';
			}
			if ($language['value'] == 'jp') {
				$new_language = 'ja-JP';
			}
		}
		
		if ($new_language != '') {
			$result = $this->db->query("UPDATE " . DB_PREFIX . "setting SET `value` = '" . $new_language . "' WHERE `key` = 'google_product_feed_lang'");
			
		}

	}


	
	public function checkGPFTable() {
		
		// This function checks that every product is included in the table `product_gpf` and updates the table as necessary
		
		$query = $this->db->query("SHOW TABLES LIKE '".DB_PREFIX."product_gpf'");
		
		if($query->num_rows != 1) {
			$this->checkGPFTables();
		}

		$query = $this->db->query("SELECT product_id FROM " . DB_PREFIX . "product ORDER BY product_id ASC");
		$products = array();
		
		foreach($query->rows as $row) {
			$products[] = $row['product_id'];
		}
		
		$query = $this->db->query("SELECT DISTINCT product_id FROM " . DB_PREFIX . "product_gpf ORDER BY product_id ASC");
		$gpf_products = array();
		
		foreach($query->rows as $row) {
			$gpf_products[] = $row['product_id'];
		}
		
		$gpf_update = array_diff($products, $gpf_products);
		
		if(!empty($gpf_update)) {
			foreach($gpf_update as $product_id) {
				$query = $this->db->query("INSERT INTO " . DB_PREFIX . "product_gpf SET product_id = " . (int)$product_id);
			}
		}
		
		$gpf_remove = array_diff($gpf_products, $products);
		
		if(!empty($gpf_remove)) {
			foreach($gpf_remove as $product_id) {
				$query = $this->db->query("DELETE FROM " . DB_PREFIX . "product_gpf WHERE product_id = " . (int)$product_id);
			}
		}	
		
	}
	
	private function write($data, $file_name) {
	
		$fp = fopen($file_name, 'a');
		fwrite($fp, $data);
		fclose($fp);
	}
}
?>