Last active
June 7, 2020 20:22
-
-
Save eapdob/9656f7393c3ffe30408f517a8e42a1e8 to your computer and use it in GitHub Desktop.
opencart-translate-create-seo-urls
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 ControllerToolTranslate extends Controller | |
{ | |
private $yandexApi; | |
public function __construct($registry) | |
{ | |
parent::__construct($registry); | |
require_once(DIR_SYSTEM . 'library/urlify.php'); | |
require_once(DIR_SYSTEM . 'library/yandexapi.php'); | |
$this->yandexApi = YandexApi::Factory(); | |
} | |
public function index() | |
{ | |
$products = []; | |
$categories = []; | |
$manufacturers = []; | |
$options_description = []; | |
$options_value_description = []; | |
$attributes_description = []; | |
$attributes_groups_description = []; | |
$product_seo_url = []; | |
$product_wholesale = []; | |
// products | |
$sql = "SELECT * FROM " . DB_PREFIX . "product_description WHERE language_id = 1"; | |
$res = $this->db->query($sql); | |
if ($res->num_rows > 0) { | |
$i = 0; | |
foreach ($res->rows as $row) { | |
// ru | |
$products[$i]['1']['product_id'] = $row['product_id']; | |
$products[$i]['1']['name'] = $row['name']; | |
$products[$i]['1']['description'] = $row['description']; | |
$products[$i]['1']['meta_description'] = $row['meta_description']; | |
$products[$i]['1']['meta_keyword'] = $row['meta_keyword']; | |
$products[$i]['1']['seo_title'] = $row['seo_title']; | |
$products[$i]['1']['seo_h1'] = $row['seo_h1']; | |
$products[$i]['1']['tag'] = $row['tag']; | |
// ua | |
$name = $row['name']; | |
$description = $row['description']; | |
$meta_description = $row['meta_description']; | |
$meta_keyword = $row['meta_keyword']; | |
$seo_title = $row['seo_title']; | |
$seo_h1 = $row['seo_h1']; | |
$tag = $row['tag']; | |
// translate | |
if (!$this->yandexApi->plainTranslate($row['name'], 'ru', 'uk', $name)) { | |
$name = $row['name']; | |
}; | |
if (!$this->yandexApi->plainTranslate($row['description'], 'ru', 'uk', $description, 'html')) { | |
$description = $row['description']; | |
}; | |
if (!$this->yandexApi->plainTranslate($row['meta_description'], 'ru', 'uk', $meta_description)) { | |
$meta_description = $row['meta_description']; | |
}; | |
if (!$this->yandexApi->plainTranslate($row['meta_keyword'], 'ru', 'uk', $meta_keyword)) { | |
$meta_keyword = $row['meta_keyword']; | |
}; | |
if (!$this->yandexApi->plainTranslate($row['seo_title'], 'ru', 'uk', $seo_title)) { | |
$seo_title = $row['seo_title']; | |
}; | |
if (!$this->yandexApi->plainTranslate($row['seo_h1'], 'ru', 'uk', $seo_h1)) { | |
$seo_h1 = $row['seo_h1']; | |
}; | |
if (!$this->yandexApi->plainTranslate($row['tag'], 'ru', 'uk', $tag)) { | |
$tag = $row['tag']; | |
}; | |
$products[$i]['3']['product_id'] = $row['product_id']; | |
$products[$i]['3']['name'] = $name; | |
$products[$i]['3']['description'] = $description; | |
$products[$i]['3']['meta_description'] = $meta_description; | |
$products[$i]['3']['meta_keyword'] = $meta_keyword; | |
$products[$i]['3']['seo_title'] = $seo_title; | |
$products[$i]['3']['seo_h1'] = $seo_h1; | |
$products[$i]['3']['tag'] = $tag; | |
$i++; | |
if ($i == 30) { | |
break; | |
} | |
} | |
$tmp_p = $products; | |
$sql = "TRUNCATE TABLE " . DB_PREFIX . "product_description"; | |
$this->db->query($sql); | |
foreach ($products as $product_i) { | |
foreach ($product_i as $lang_id => $product) { | |
$sql = "INSERT INTO " . DB_PREFIX . "product_description (product_id, language_id, name, description, meta_description, meta_keyword, seo_title, seo_h1, tag) VALUES ('" . $this->db->escape($product['product_id']) . "', '" . $this->db->escape($lang_id) . "', '" . $this->db->escape($product['name']) . "', '" . $this->db->escape($product['description']) . "', '" . $this->db->escape($product['meta_description']) . "', '" . $this->db->escape($product['meta_keyword']) . "', '" . $this->db->escape($product['seo_title']) . "', '" . $this->db->escape($product['seo_h1']) . "', '" . $this->db->escape($product['tag']) . "') "; | |
$this->db->query($sql); | |
} | |
} | |
} | |
// categories | |
$sql = "SELECT * FROM " . DB_PREFIX . "category_description WHERE language_id = 1"; | |
$res = $this->db->query($sql); | |
if ($res->num_rows > 0) { | |
$i = 0; | |
foreach ($res->rows as $row) { | |
// ru | |
$categories[$i]['1']['category_id'] = $row['category_id']; | |
$categories[$i]['1']['name'] = $row['name']; | |
$categories[$i]['1']['description'] = $row['description']; | |
$categories[$i]['1']['meta_description'] = $row['meta_description']; | |
$categories[$i]['1']['meta_keyword'] = $row['meta_keyword']; | |
$categories[$i]['1']['seo_title'] = $row['seo_title']; | |
$categories[$i]['1']['seo_h1'] = $row['seo_h1']; | |
// ua | |
$name = $row['name']; | |
$description = $row['description']; | |
$meta_description = $row['meta_description']; | |
$meta_keyword = $row['meta_keyword']; | |
$seo_title = $row['seo_title']; | |
$seo_h1 = $row['seo_h1']; | |
// translate | |
if (!$this->yandexApi->plainTranslate($row['name'], 'ru', 'uk', $name)) { | |
$name = $row['name']; | |
}; | |
if (!$this->yandexApi->plainTranslate($row['description'], 'ru', 'uk', $description, 'html')) { | |
$description = $row['description']; | |
}; | |
if (!$this->yandexApi->plainTranslate($row['meta_description'], 'ru', 'uk', $meta_description)) { | |
$meta_description = $row['meta_description']; | |
}; | |
if (!$this->yandexApi->plainTranslate($row['meta_keyword'], 'ru', 'uk', $meta_keyword)) { | |
$meta_keyword = $row['meta_keyword']; | |
}; | |
if (!$this->yandexApi->plainTranslate($row['seo_title'], 'ru', 'uk', $seo_title)) { | |
$seo_title = $row['seo_title']; | |
}; | |
if (!$this->yandexApi->plainTranslate($row['seo_h1'], 'ru', 'uk', $seo_h1)) { | |
$seo_h1 = $row['seo_h1']; | |
}; | |
$categories[$i]['3']['category_id'] = $row['category_id']; | |
$categories[$i]['3']['name'] = $name; | |
$categories[$i]['3']['description'] = $description; | |
$categories[$i]['3']['meta_description'] = $meta_description; | |
$categories[$i]['3']['meta_keyword'] = $meta_keyword; | |
$categories[$i]['3']['seo_title'] = $seo_title; | |
$categories[$i]['3']['seo_h1'] = $seo_h1; | |
$i++; | |
} | |
$tmp_c = $categories; | |
$sql = "TRUNCATE TABLE " . DB_PREFIX . "category_description"; | |
$this->db->query($sql); | |
foreach ($categories as $category_i) { | |
foreach ($category_i as $lang_id => $category) { | |
$sql = "INSERT INTO " . DB_PREFIX . "category_description (category_id, language_id, name, description, meta_description, meta_keyword, seo_title, seo_h1) VALUES ('" . $this->db->escape($category['category_id']) . "', '" . $this->db->escape($lang_id) . "', '" . $this->db->escape($category['name']) . "', '" . $this->db->escape($category['description']) . "', '" . $this->db->escape($category['meta_description']) . "', '" . $this->db->escape($category['meta_keyword']) . "', '" . $this->db->escape($category['seo_title']) . "', '" . $this->db->escape($category['seo_h1']) . "') "; | |
$this->db->query($sql); | |
} | |
} | |
} | |
// manufacturer | |
$sql = "SELECT * FROM " . DB_PREFIX . "manufacturer_description WHERE language_id = 1"; | |
$res = $this->db->query($sql); | |
if ($res->num_rows > 0) { | |
$i = 0; | |
foreach ($res->rows as $row) { | |
// ru | |
$manufacturers[$i]['1']['manufacturer_id'] = $row['manufacturer_id']; | |
$manufacturers[$i]['1']['description'] = $row['description']; | |
$manufacturers[$i]['1']['meta_description'] = $row['meta_description']; | |
$manufacturers[$i]['1']['meta_keyword'] = $row['meta_keyword']; | |
$manufacturers[$i]['1']['seo_title'] = $row['seo_title']; | |
$manufacturers[$i]['1']['seo_h1'] = $row['seo_h1']; | |
// ua | |
$description = $row['description']; | |
$meta_description = $row['meta_description']; | |
$meta_keyword = $row['meta_keyword']; | |
$seo_title = $row['seo_title']; | |
$seo_h1 = $row['seo_h1']; | |
// translate | |
if (!$this->yandexApi->plainTranslate($row['description'], 'ru', 'uk', $description, 'html')) { | |
$description = $row['description']; | |
}; | |
if (!$this->yandexApi->plainTranslate($row['meta_description'], 'ru', 'uk', $meta_description)) { | |
$meta_description = $row['meta_description']; | |
}; | |
if (!$this->yandexApi->plainTranslate($row['meta_keyword'], 'ru', 'uk', $meta_keyword)) { | |
$meta_keyword = $row['meta_keyword']; | |
}; | |
if (!$this->yandexApi->plainTranslate($row['seo_title'], 'ru', 'uk', $seo_title)) { | |
$seo_title = $row['seo_title']; | |
}; | |
if (!$this->yandexApi->plainTranslate($row['seo_h1'], 'ru', 'uk', $seo_h1)) { | |
$seo_h1 = $row['seo_h1']; | |
}; | |
$manufacturers[$i]['3']['manufacturer_id'] = $row['manufacturer_id']; | |
$manufacturers[$i]['3']['description'] = $description; | |
$manufacturers[$i]['3']['meta_description'] = $meta_description; | |
$manufacturers[$i]['3']['meta_keyword'] = $meta_keyword; | |
$manufacturers[$i]['3']['seo_title'] = $seo_title; | |
$manufacturers[$i]['3']['seo_h1'] = $seo_h1; | |
$i++; | |
} | |
$tmp_m = $manufacturers; | |
$sql = "TRUNCATE TABLE " . DB_PREFIX . "manufacturer_description"; | |
$this->db->query($sql); | |
foreach ($manufacturers as $manufacturer_i) { | |
foreach ($manufacturer_i as $lang_id => $manufacturer) { | |
$sql = "INSERT INTO " . DB_PREFIX . "manufacturer_description (manufacturer_id, language_id, description, meta_description, meta_keyword, seo_title, seo_h1) VALUES ('" . $this->db->escape($manufacturer['manufacturer_id']) . "', '" . $this->db->escape($lang_id) . "', '" . $this->db->escape($manufacturer['description']) . "', '" . $this->db->escape($manufacturer['meta_description']) . "', '" . $this->db->escape($manufacturer['meta_keyword']) . "', '" . $this->db->escape($manufacturer['seo_title']) . "', '" . $this->db->escape($manufacturer['seo_h1']) . "') "; | |
$this->db->query($sql); | |
} | |
} | |
} | |
// option description | |
$sql = "SELECT * FROM " . DB_PREFIX . "option_description WHERE language_id = 1"; | |
$res = $this->db->query($sql); | |
if ($res->num_rows > 0) { | |
$i = 0; | |
foreach ($res->rows as $row) { | |
// ru | |
$options_description[$i]['1']['option_id'] = $row['option_id']; | |
$options_description[$i]['1']['name'] = $row['name']; | |
// ua | |
$name = $row['name']; | |
if (!$this->yandexApi->plainTranslate($row['name'], 'ru', 'uk', $name)) { | |
$name = $row['name']; | |
}; | |
$options_description[$i]['3']['option_id'] = $row['option_id']; | |
$options_description[$i]['3']['name'] = $name; | |
$i++; | |
} | |
$tmp_od = $options_description; | |
$sql = "TRUNCATE TABLE " . DB_PREFIX . "option_description"; | |
$this->db->query($sql); | |
foreach ($options_description as $option_description_i) { | |
foreach ($option_description_i as $lang_id => $option_description) { | |
$sql = "INSERT INTO " . DB_PREFIX . "option_description (option_id, language_id, name) VALUES ('" . $this->db->escape($option_description['option_id']) . "', '" . $this->db->escape($lang_id) . "', '" . $this->db->escape($option_description['name']) . "') "; | |
$this->db->query($sql); | |
} | |
} | |
} | |
// option value description | |
$sql = "SELECT * FROM " . DB_PREFIX . "option_value_description WHERE language_id = 1"; | |
$res = $this->db->query($sql); | |
if ($res->num_rows > 0) { | |
$i = 0; | |
foreach ($res->rows as $row) { | |
// ru | |
$options_value_description[$i]['1']['option_value_id'] = $row['option_value_id']; | |
$options_value_description[$i]['1']['option_id'] = $row['option_id']; | |
$options_value_description[$i]['1']['name'] = $row['name']; | |
// ua | |
$name = $row['name']; | |
if (!$this->yandexApi->plainTranslate($row['name'], 'ru', 'uk', $name)) { | |
$name = $row['name']; | |
}; | |
$options_value_description[$i]['3']['option_value_id'] = $row['option_value_id']; | |
$options_value_description[$i]['3']['option_id'] = $row['option_id']; | |
$options_value_description[$i]['3']['name'] = $name; | |
$i++; | |
} | |
$tmp_ovd = $options_value_description; | |
$sql = "TRUNCATE TABLE " . DB_PREFIX . "option_value_description"; | |
$this->db->query($sql); | |
foreach ($options_value_description as $option_value_description_i) { | |
foreach ($option_value_description_i as $lang_id => $option_value_description) { | |
$sql = "INSERT INTO " . DB_PREFIX . "option_value_description (option_value_id, option_id, language_id, name) VALUES ('" . $this->db->escape($option_value_description['option_value_id']) . "', '" . $this->db->escape($option_value_description['option_id']) . "', '" . $this->db->escape($lang_id) . "', '" . $this->db->escape($option_value_description['name']) . "') "; | |
$this->db->query($sql); | |
} | |
} | |
} | |
// attribute description | |
$sql = "SELECT * FROM " . DB_PREFIX . "attribute_description WHERE language_id = 1"; | |
$res = $this->db->query($sql); | |
if ($res->num_rows > 0) { | |
$i = 0; | |
foreach ($res->rows as $row) { | |
// ru | |
$attributes_description[$i]['1']['attribute_id'] = $row['attribute_id']; | |
$attributes_description[$i]['1']['name'] = $row['name']; | |
// ua | |
$name = $row['name']; | |
// translate | |
if (!$this->yandexApi->plainTranslate($row['name'], 'ru', 'uk', $name)) { | |
$name = $row['name']; | |
}; | |
$attributes_description[$i]['3']['attribute_id'] = $row['attribute_id']; | |
$attributes_description[$i]['3']['name'] = $name; | |
$i++; | |
} | |
$tmp_ad = $attributes_description; | |
$sql = "TRUNCATE TABLE " . DB_PREFIX . "attribute_description"; | |
$this->db->query($sql); | |
foreach ($attributes_description as $attributes_description_i) { | |
foreach ($attributes_description_i as $lang_id => $attribute_description) { | |
$sql = "INSERT INTO " . DB_PREFIX . "attribute_description (attribute_id, language_id, name) VALUES ('" . $this->db->escape($attribute_description['attribute_id']) . "', '" . $this->db->escape($lang_id) . "', '" . $this->db->escape($attribute_description['name']) . "') "; | |
$this->db->query($sql); | |
} | |
} | |
} | |
// attribute group description | |
$sql = "SELECT * FROM " . DB_PREFIX . "attribute_group_description WHERE language_id = 1"; | |
$res = $this->db->query($sql); | |
if ($res->num_rows > 0) { | |
$i = 0; | |
foreach ($res->rows as $row) { | |
// ru | |
$attributes_groups_description[$i]['1']['attribute_group_id'] = $row['attribute_group_id']; | |
$attributes_groups_description[$i]['1']['name'] = $row['name']; | |
// ua | |
$name = $row['name']; | |
// translate | |
if (!$this->yandexApi->plainTranslate($row['name'], 'ru', 'uk', $name)) { | |
$name = $row['name']; | |
}; | |
$attributes_groups_description[$i]['3']['attribute_group_id'] = $row['attribute_group_id']; | |
$attributes_groups_description[$i]['3']['name'] = $name; | |
$i++; | |
} | |
$tmp_agd = $attributes_groups_description; | |
$sql = "TRUNCATE TABLE " . DB_PREFIX . "attribute_group_description"; | |
$this->db->query($sql); | |
foreach ($attributes_groups_description as $attributes_group_description_i) { | |
foreach ($attributes_group_description_i as $lang_id => $attribute_group_description) { | |
$sql = "INSERT INTO " . DB_PREFIX . "attribute_group_description (attribute_group_id, language_id, name) VALUES ('" . $this->db->escape($attribute_group_description['attribute_group_id']) . "', '" . $this->db->escape($lang_id) . "', '" . $this->db->escape($attribute_group_description['name']) . "') "; | |
$this->db->query($sql); | |
} | |
} | |
} | |
// seo url | |
$sql = "SELECT product_id, name FROM " . DB_PREFIX . "product_description WHERE language_id = 1"; | |
$res = $this->db->query($sql); | |
if ($res->num_rows > 0) { | |
foreach ($res->rows as $row) { | |
$name = $row['name']; | |
// urlify | |
$urlify = URLify::filter($name); | |
$new_urlify = URLify::transliterate($urlify); | |
$product_seo_url[$row['product_id']] = $new_urlify; | |
} | |
} | |
$tmp_psu = $product_seo_url; | |
$sql = "SELECT query, keyword FROM " . DB_PREFIX . "url_alias WHERE query LIKE 'product_id=%'"; | |
$res = $this->db->query($sql); | |
if ($res->num_rows > 0) { | |
foreach ($res->rows as $row) { | |
$product_id = explode('=', $row['query']); | |
$product_id = isset($product_id[1]) ? $product_id[1] : 0; | |
if ($product_id != 0 && isset($product_seo_url[$product_id])) { | |
// save product ean for buy wholesale | |
$product_wholesale[$product_id] = $row['keyword']; | |
// change keyword to urlify | |
$sql = "UPDATE " . DB_PREFIX . "url_alias SET keyword = '" . $this->db->escape($product_seo_url[$product_id]) . "' WHERE query = 'product_id=" . $this->db->escape($product_id) . "' "; | |
$this->db->query($sql); | |
} | |
} | |
// whosale | |
foreach ($product_wholesale as $product_id => $url) { | |
$sql = "UPDATE " . DB_PREFIX . "product SET ean = '" . $this->db->escape($url) . "' WHERE product_id = '" . $this->db->escape($product_id) . "' "; | |
$this->db->query($sql); | |
} | |
} | |
} | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment