Shoora
10/3/2018 - 4:48 AM

Product Import from csv to Opencart's database. Using native Opencart API

Product Import from csv to Opencart's database. Using native Opencart API

<?php

class ProductImportModel
{

	private $db;
	private $language_id = 2;
	private $store_id = 0;

	private $update_images = false;

	public function __construct($db)
	{
		$this->db = $db;
	}

	public function checkProduct($model)
	{

		$trimed_model = str_replace(" ","",$model);

		$result = $this->db->query("SELECT product_id FROM " .DB_PREFIX. "product WHERE model = '". $trimed_model ."'");

		if($result->num_rows >= 1) return true;

		return false;
	}

	public function checkCategoryByName($text,$parent_id)
	{

		$escapedtext = str_replace("\"","'",$text);
	
		$query = $this->db->query("SELECT c.category_id FROM " . DB_PREFIX . "category c LEFT JOIN " . DB_PREFIX . "category_description cd ON (c.category_id = cd.category_id) LEFT JOIN " . DB_PREFIX . "category_to_store c2s ON (c.category_id = c2s.category_id) WHERE c.parent_id = '" . (int)$parent_id . "' AND cd.name LIKE \"" . $escapedtext  . "\" AND cd.language_id = '" . (int)$this->language_id . "' AND c.status = '1' ORDER BY c.sort_order, LCASE(cd.name)");

		return $query->rows;
	}

	public function addCategory($data) 
	{
		$this->db->query("INSERT INTO " . DB_PREFIX . "category SET parent_id = '" . (int)$data['parent_id'] . "', `top` = '" . (isset($data['top']) ? (int)$data['top'] : 0) . "', `column` = '" . (int)$data['column'] . "', sort_order = '" . (int)$data['sort_order'] . "', status = '" . (int)$data['status'] . "', date_modified = NOW(), date_added = NOW()");

		$category_id = $this->db->getLastId();
				
		if (isset($data['image'])) {
			$this->db->query("UPDATE " . DB_PREFIX . "category SET image = '" . $this->db->escape(html_entity_decode($data['image'], ENT_QUOTES, 'UTF-8')) . "' WHERE category_id = '" . (int)$category_id . "'");
		}
		
		foreach ($data['category_description'] as $language_id => $value) {
			if ($value['tpl_product_check'] != 1){
				$value['tpl_product_check'] = 0;
			}
		
			$this->db->query("INSERT INTO " . DB_PREFIX . "category_description SET category_id = '" . (int)$category_id . "', language_id = '" . (int)$language_id . "', name = '" . $this->db->escape($value['name']) . "', meta_keyword = '" . $this->db->escape($value['meta_keyword']) . "', meta_description = '" . $this->db->escape($value['meta_description']) . "', description = '" . $this->db->escape($value['description']) . "', tpl_product = '" . $this->db->escape($value['tpl_product']) . "', tpl_product_check = ". $value['tpl_product_check']);
			
			if ($value['tpl_product_check'] != 1){
				$this->db->query("UPDATE " . DB_PREFIX . "category_description SET tpl_product = '" . $this->db->escape($value['tpl_product']) . "' WHERE tpl_product_check != 1 AND name = '" . $this->db->escape($value['name']) . "' AND language_id = " . (int)$language_id);
			}
		}

		$this->db->query("INSERT INTO " . DB_PREFIX . "category_to_store SET category_id = '" . (int)$category_id . "', store_id = '0'");
			
		// MySQL Hierarchical Data Closure Table Pattern
		$level = 0;
		
		$query = $this->db->query("SELECT * FROM `" . DB_PREFIX . "category_path` WHERE category_id = '" . (int)$data['parent_id'] . "' ORDER BY `level` ASC");
		
		foreach ($query->rows as $result) {
			$this->db->query("INSERT INTO `" . DB_PREFIX . "category_path` SET `category_id` = '" . (int)$category_id . "', `path_id` = '" . (int)$result['path_id'] . "', `level` = '" . (int)$level . "'");
			
			$level++;
		}
		
		$this->db->query("INSERT INTO `" . DB_PREFIX . "category_path` SET `category_id` = '" . (int)$category_id . "', `path_id` = '" . (int)$category_id . "', `level` = '" . (int)$level . "'");
						
		if ($data['keyword']) {
			$this->db->query("INSERT INTO " . DB_PREFIX . "url_alias SET query = 'category_id=" . (int)$category_id . "', keyword = '" . $this->db->escape($data['keyword']) ."-". (int)$category_id  . "'");
		}

		return $category_id;
	}

	public function addProduct($data) {


		$this->db->query("INSERT INTO " . DB_PREFIX . "product SET model = '" . $this->db->escape(str_replace(" ","",$data['model'])) . "', quantity = '1', minimum = '1', upc='111', subtract = '1', stock_status_id = '0', date_available = NOW(), price = '" . (float)$data['price'] . "', status = '1', sort_order = '0', date_added = NOW(), date_modified = NOW()");
		
		$product_id = $this->db->getLastId();

		$images = $this->getImages($data['images'],$product_id);
		
		if (isset($images['image'])) {
			$this->db->query("UPDATE " . DB_PREFIX . "product SET image = '" . $this->db->escape(html_entity_decode($images['image'], ENT_QUOTES, 'UTF-8')) . "' WHERE product_id = '" . (int)$product_id . "'");
		}
		
		foreach ($data['product_description'] as $language_id => $value) {
			$this->db->query("INSERT INTO " . DB_PREFIX . "product_description SET product_id = '" . (int)$product_id . "', language_id = '" . (int)$language_id . "', name = '" . $this->db->escape($value['name']) . "'");
		}

		if (isset($data['product_attribute'])) {
			foreach ($data['product_attribute'] as $product_attribute) {
				if ($product_attribute['attribute_id']) {

					$this->db->query("DELETE FROM " . DB_PREFIX . "product_attribute WHERE product_id = '" . (int)$product_id . "' AND attribute_id = '" . (int)$product_attribute['attribute_id'] . "'");
					
					foreach ($product_attribute['product_attribute_description'] as $language_id => $product_attribute_description) {				
						$this->db->query("INSERT INTO " . DB_PREFIX . "product_attribute SET product_id = '" . (int)$product_id . "', attribute_id = '" . (int)$product_attribute['attribute_id'] . "', language_id = '" . (int)$language_id . "', text = '" .  $this->db->escape($product_attribute_description['text']) . "'");
					}
				}
			}
		}
		
		if (isset($images['product_image'])) {
			foreach ($images['product_image'] as $product_image) {
				$this->db->query("INSERT INTO " . DB_PREFIX . "product_image SET product_id = '" . (int)$product_id . "', image = '" . $this->db->escape(html_entity_decode($product_image['image'], ENT_QUOTES, 'UTF-8')) . "', sort_order = '0'");
			}
		}
		
		if (isset($data['product_category'])) {
			foreach ($data['product_category'] as $category_id) {
				$this->db->query("INSERT INTO " . DB_PREFIX . "product_to_category SET product_id = '" . (int)$product_id . "', category_id = '" . (int)$category_id . "'");
			}
		}
		
		if ($data['keyword']) {
			$this->db->query("INSERT INTO " . DB_PREFIX . "url_alias SET query = 'product_id=" . (int) $product_id . "', keyword = '" . $this->db->escape($data['keyword']) . "-" . (int) $product_id ."'");
		}

		return $product_id;
	}

	public function editProduct($data)
	{
		$trimed_model = str_replace(" ","",$model);

		$result = $this->db->query( "SELECT product_id FROM " .DB_PREFIX. "product WHERE model = '". $trimed_model ."' " );

		if(isset($result->num_rows) ){
			$product_id = $result->row['product_id'];
		}

		$this->db->query("UPDATE " . DB_PREFIX . "product SET price = '" . (float)$data['price'] . "', date_modified = NOW(), upc='111', quantity='1' WHERE product_id = '" . (int)$product_id . "'");

		if($this->update_images){

			$images = $this->getImages($data['images'],$product_id);

			if (isset($images['image'])) {
				$this->db->query("UPDATE " . DB_PREFIX . "product SET image = '" . $this->db->escape(html_entity_decode($images['image'], ENT_QUOTES, 'UTF-8')) . "' WHERE product_id = '" . (int)$product_id . "'");
			}

			$this->db->query("DELETE FROM " . DB_PREFIX . "product_image WHERE product_id = '" . (int)$product_id . "'");
			
			if (isset($images['product_image'])) {
				foreach ($images['product_image'] as $product_image) {
					$this->db->query("INSERT INTO " . DB_PREFIX . "product_image SET product_id = '" . (int)$product_id . "', image = '" . $this->db->escape(html_entity_decode($product_image['image'], ENT_QUOTES, 'UTF-8')) . "', sort_order = '0'");
				}
			}

			$this->update_images = false;
		}

		$this->db->query("DELETE FROM " . DB_PREFIX . "product_attribute WHERE product_id = '" . (int)$product_id . "'");

		if (!empty($data['product_attribute'])) {
			foreach ($data['product_attribute'] as $product_attribute) {
				if ($product_attribute['attribute_id']) {
					$this->db->query("DELETE FROM " . DB_PREFIX . "product_attribute WHERE product_id = '" . (int)$product_id . "' AND attribute_id = '" . (int)$product_attribute['attribute_id'] . "'");
					
					foreach ($product_attribute['product_attribute_description'] as $language_id => $product_attribute_description) {				
						$this->db->query("INSERT INTO " . DB_PREFIX . "product_attribute SET product_id = '" . (int)$product_id . "', attribute_id = '" . (int)$product_attribute['attribute_id'] . "', language_id = '" . (int)$language_id . "', text = '" .  $this->db->escape($product_attribute_description['text']) . "'");
					}
				}
			}
		}

		return $product_id;
	}

	public function getProducts()
	{
		$result = $this->db->query("SELECT product_id,model FROM " .DB_PREFIX. "product WHERE status = 1");
		return $result;
	}

	public function hideOldProducts($start_time)
	{
		$this->db->query("UPDATE " . DB_PREFIX . "product SET `quantity` = '0' WHERE `status` = 1 AND `quantity` = 1  AND `upc` != '111'");
		$this->db->query("UPDATE " . DB_PREFIX . "product SET `upc` = '' ");

	}

	public function getImages($images,$file_name)
	{

		$dir = "data/product/";
		$end = ".jpg";

		$return = array();

		foreach($images as $key => $image){

			$file = $dir . $file_name . "-" . $key . $end;

			if(!file_exists(__DIR__.'/image/'.$file)) {

					$this->update_images = true;

					copy($image,__DIR__.'/image/'.$file);

			} 

			if($key == 0){
				$return["image"] = $file;
			} else {
				$return["product_image"][] = array(
					'image'      => $file
				);
			}
		}

		return $return;
	}

	public function getAttributes() {

		$query  = $this->db->query("SELECT a.attribute_id,ad.name FROM " . DB_PREFIX . "attribute a LEFT JOIN " . DB_PREFIX . "attribute_description ad ON (a.attribute_id = ad.attribute_id) WHERE ad.language_id = '1'");
		$return = array();

		foreach($query->rows as $row){
			$return[$row['name']] = $row['attribute_id'];
		}

		return $return;
	}
}
<?php

require_once('ProductImportModel.php');

class ProductImport 
{

	private $db;
	private $product_model;

	public $product_models = [];

	public function __construct($db)
	{

		$this->db = $db;
		$this->product_model = new ProductImportModel($db);

	}

	public function checkProduct($model)
	{
		return $result = $this->product_model->checkProduct($model);
	}

	function getFile($url){

		$handle = @fopen($url, "r");
		if ($handle) {

			$i = 0;
		    while (($buffer = fgetcsv ( $handle,$length = 4096,$delimiter = "|", $enclosure = '`' )) !== false) {

		    	if( $i == 0 || $buffer[0] == "" ) { $i++; continue; }

		    		$method  = (!$this->checkProduct($buffer[0])) ?  "insert" : "update";

		    		$store = explode(",", $buffer[17]);

		    		$return = array(
								'model'          => $buffer[0],
								'category'       => $buffer[4] ."_". $buffer[5] ."_". $buffer[2] ."_". $buffer[3],
								'name'           => $buffer[3],
								'price'          => $buffer[14],
								'attributes'     => array(
									'carcas'         => $buffer[6],
									'part_number'    => $buffer[7],
									'engine'         => $buffer[8],
									'front_rear'     => $buffer[9],
									'left_right'     => $buffer[10],
									'top_bottom'     => $buffer[11],
									'color'          => $buffer[12],
									'year'           => $buffer[13],
									'info'           => $buffer[15],
									'status'         => $buffer[16],
									'storage'        => $store[0],
									'type_engine'    => $buffer[18],
									'transmission'   => $buffer[19],
									'unik_kod'       => $buffer[0],
								),
								'images'         => explode(",",$buffer[20])
								);

		    		// Test

		    		$category_id = $this->checkCategory( $return['category'] );

		    		if($method == 'insert'){
		    			$product_id = $this->addProduct($return,$category_id);
		    		} else {
		    			$product_id = $this->editProduct($return);
		    		}
					$i++;
		    }
		    if (!feof($handle)) {
		        return "Error: unexpected fgets() fail\n";
		    }

		    fclose($handle);
		    return true;
		}
		
		return false;
	}

	public function checkCategory( $path )
	{

		$parent_id   = 0;
		$category_id = 0;

		$category_details = explode("_",$path);

		foreach($category_details as $category){

			if( $category == '' ) continue;

			$category_name_trimmed = trim($category);

			$rows = $this->product_model->checkCategoryByName($category_name_trimmed,$parent_id);

			if(isset($rows[0])){

				$parent_id = $rows[0]['category_id'];

			} else {

				$data = array(
					'category_description' => array(
						'1' => array(
							'name'              => $category,
							'meta_description'  => '',
							'meta_keyword'      => '',
							'description'       => '',
							'tpl_product'       => '',
							'tpl_product_check' => 0,
							),
						'2' => array(
							'name'              => $category,
							'meta_description'  => '',
							'meta_keyword'      => '',
							'description'       => '',
							'tpl_product'       => '',
							'tpl_product_check' => 0,
							)
						),
						'parent_id'      => $parent_id,
						'filter'         => '',
						'keyword'        => $this->translit($category),
						'image'          => '',
						'status'         => 1,
						'column'         => 1,
						'sort_order'     => 0,
					);

				$parent_id = $this->product_model->addCategory($data);
			}

			$category_id = $parent_id;

		}

		return $category_id;
	}

	public function addProduct($data,$category_id)
	{

		$atributes = $this->getAttibutes();

		$product_attributes = array();

		foreach($data['attributes'] as $attribute_name => $attribute_value){

			if($attribute_value == '' || !isset($atributes[$attribute_name])) continue;

			$attribute_id = $atributes[$attribute_name];

			$datas = array(
					'attribute_id' => $attribute_id,
					'product_attribute_description' => array(
						'1' => array(
							'text' => $attribute_value
							),
						'2' => array(
							'text' => $attribute_value
							),
						),
				);

			$product_attributes[] = $datas;
		}

		$data = array(
			'name'                => $data['name'],
			'price'               => $data['price'],
			'images'              => $data['images'],
			'model'               => $data['model'],
			'product_category'    => array($category_id),
			'product_attribute'   => $product_attributes,
			'keyword'             => $this->translit($data['name']),
			'product_description' => array(
				'1' => array(
					'name' => $data['name']
					),
				'2' => array(
					'name' => $data['name']
					)
				),

			);

		$product_id = $this->product_model->addProduct($data);
		//echo "Add product ". $data['model'] ." as ".$product_id . "<br>";

		return $product_id;
	}

	public function editProduct($data)
	{

		$atributes = $this->getAttibutes();

		$product_attributes = array();

		foreach($data['attributes'] as $attribute_name => $attribute_value){

			if($attribute_value == '' || !isset($atributes[$attribute_name])) continue;

			$attribute_id = $atributes[$attribute_name];

			$datas = array(
					'attribute_id' => $attribute_id,
					'product_attribute_description' => array(
						'1' => array(
							'text' => $attribute_value
							),
						'2' => array(
							'text' => $attribute_value
							),
						),
				);

			$product_attributes[] = $datas;
		}

		$data = array(
			'model'             => $data['model'],
			'price'             => $data['price'],
			'images'            => $data['images'],
			'product_attribute' => $product_attributes,
			);

		$product_id = $this->product_model->editProduct($data);

		//echo "Product " .$data['model'] . " - id ". $product_id ." was updated <br>";
		return $product_id;
	}

	public function hideOldProducts($start_time)
	{
		$this->product_model->hideOldProducts($start_time);
	}

	private function translit($text,$direct='ru_en'){ 
		$L['ru'] = array( 
		  'Ё', 'Ж', 'Ц', 'Ч', 'Щ', 'Ш', 'Ы',  
		  'Э', 'Ю', 'Я', 'ё', 'ж', 'ц', 'ч',  
		  'ш', 'щ', 'ы', 'э', 'ю', 'я', 'А',  
		  'Б', 'В', 'Г', 'Д', 'Е', 'З', 'И',  
		  'Й', 'К', 'Л', 'М', 'Н', 'О', 'П',  
		  'Р', 'С', 'Т', 'У', 'Ф', 'Х', 'Ъ',  
		  'Ь', 'а', 'б', 'в', 'г', 'д', 'е',  
		  'з', 'и', 'й', 'к', 'л', 'м', 'н',  
		  'о', 'п', 'р', 'с', 'т', 'у', 'ф',  
		  'х', 'ъ', 'ь', 'І', 'Ї', 'Є', 'і',
		  'ї', 'є', ' '
		); 
		$L['en'] = array( 
		  "YO", "ZH",  "CZ", "CH", "SHH","SH", "Y'",  
		  "E'", "YU",  "YA", "yo", "zh", "cz", "ch",  
		  "sh", "shh", "y'", "e'", "yu", "ya", "A",  
		  "B" , "V" ,  "G",  "D",  "E",  "Z",  "I",  
		  "J",  "K",   "L",  "M",  "N",  "O",  "P",  
		  "R",  "S",   "T",  "U",  "F",  "X",  "''", 
		  "'",  "a",   "b",  "v",  "g",  "d",  "e",  
		  "z",  "i",   "j",  "k",  "l",  "m",  "n",   
		  "o",  "p",   "r",  "s",  "t",  "u",  "f",   
		  "x",  "''",  "'",  'I',  'YI', 'YE', 'i',
		  'yi', 'ye',  '-' 
		); 
		if($direct=='en_ru'){ 
			$translated = str_replace($L['en'],$L['ru'],$text);         
			$translated = preg_replace('/(?<=[а-яё])Ь/u','ь',$translated); 
			$translated = preg_replace('/(?<=[а-яё])Ъ/u','ъ',$translated); 
			$translated = preg_replace('~[^-а-яіїєА-ЯІЇЄ0-9_]+~u','',$translated);
		}else{
			$translated = str_replace($L['ru'],$L['en'],$text);
			$translated = preg_replace('~[^-a-zA-Z0-9_]+~u','',$translated);
		}
		return $translated; 
	} 

	public function getAttibutes()
	{
		return $attributes = $this->product_model->getAttributes();
	}

}
<?php
ini_set ("display_errors", 1);

$start_time = microtime(true);

// Configuration
if (file_exists('config.php')) {
	require_once('config.php');
} else {
  die("Config file is not exists");
}

require_once(DIR_SYSTEM . 'startup.php');

// Database 
$db = new DB(DB_DRIVER, DB_HOSTNAME, DB_NAME, DB_PASS, DB_USERNAME);

require_once('ProductImport.php');

$productImport = new ProductImport($db);

$urls = array(
	'test_import.csv',
);

$parsedFile = $productImport->getFile( $urls[0] );

if($parsedFile){
	$productImport->hideOldProducts($start_time);
}

$time = microtime(true) - $start_time;
echo "Time of script work is " . $time;