Created
April 7, 2020 17:04
-
-
Save IP-CAM/d2c088aef9c7fd3245d8103ea280ca2f to your computer and use it in GitHub Desktop.
Product Import from csv to Opencart's database. Using native Opencart API
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<?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; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<?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(); | |
} | |
} |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<?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; | |
} | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment