Skip to content

Instantly share code, notes, and snippets.

@eapdob
Last active June 7, 2020 20:22
Show Gist options
  • Save eapdob/9656f7393c3ffe30408f517a8e42a1e8 to your computer and use it in GitHub Desktop.
Save eapdob/9656f7393c3ffe30408f517a8e42a1e8 to your computer and use it in GitHub Desktop.
opencart-translate-create-seo-urls
<?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