Skip to content

Instantly share code, notes, and snippets.

@ruden

ruden/opencart_to_oscommerce_convert_database.php

Created December 19, 2016 13:14
Show Gist options
  • Save ruden/71455f7cb70af1bf2fc0fa6c3f8e74d6 to your computer and use it in GitHub Desktop.
Save ruden/71455f7cb70af1bf2fc0fa6c3f8e74d6 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
opencart_to_oscommerce_convert_database.php
<?php
/*
osCommerce, Open Source E-Commerce Solutions
http://www.oscommerce.com
Copyright (c) 2016 osCommerce
Released under the GNU General Public License
*/
error_reporting(E_ALL | E_STRICT);
//ini_set('display_errors', true);
function get_microtime() {
list($usec, $sec) = explode(' ', microtime());
return ((float)$usec + (float)$sec);
}
$start = get_microtime();
require_once('config.php');
function tep_db_connect($server = DB_HOSTNAME, $username = DB_USERNAME, $password = DB_PASSWORD, $database = DB_DATABASE, $link = 'db_link') {
global $$link;
$$link = mysqli_connect($server, $username, $password, $database);
if (!mysqli_connect_errno()) {
mysqli_set_charset($$link, 'utf8');
}
return $$link;
}
function tep_db_close($link = 'db_link') {
global $$link;
return mysqli_close($$link);
}
function tep_db_error($query, $errno, $error) {
die('<font color="--000000"><strong>' . $errno . ' - ' . $error . '<br /><br />' . $query . '<br /><br /><small><font color="--ff0000">[TEP STOP]</font></small><br /><br /></strong></font>');
}
function tep_db_query($query, $link = 'db_link') {
global $$link;
$result = mysqli_query($$link, $query) or tep_db_error($query, mysqli_errno($$link), mysqli_error($$link));
return $result;
}
tep_db_connect() or die('Unable to connect to database server!');
function get_bytes($bytes) {
if ($bytes >= 1073741824) {
$bytes = number_format($bytes / 1073741824, 2) . 'Gb';
} elseif ($bytes >= 1048576) {
$bytes = number_format($bytes / 1048576, 2) . 'Mb';
} elseif ($bytes >= 1024) {
$bytes = number_format($bytes / 1024, 2) . 'Kb';
} elseif ($bytes > 1) {
$bytes = $bytes . 'bytes';
} elseif ($bytes == 1) {
$bytes = $bytes . 'byte';
} else {
$bytes = '0 bytes';
}
return $bytes;
}
function prepare_string($str) {
$str = str_replace(array("\x00", "\x0a", "\x0d", "\x1a"), array('\0', '\n', '\r', '\Z'), $str);
$str = str_replace(array("\n", "\r", "\t"), array('\n', '\r', '\t'), $str);
$str = str_replace('\\', '\\\\', $str);
$str = str_replace('\'', '\\\'', $str);
$str = str_replace('\\\n', '\n', $str);
$str = str_replace('\\\r', '\r', $str);
$str = str_replace('\\\t', '\t', $str);
return $str;
}
function convert_url($url) {
if (preg_match('/index\.php\?route=product\/(product|manufacturer\/info|category)/', $url, $matches)) {
$url = str_replace(array($matches[0], '&amp;', '_id', 'path'), array('', '&', 's_id', 'cPath'), $url);
$url[0] = '?';
if (preg_match('/products_id/', $url, $matches)) {
$url = 'product_info.php' . $url;
} elseif (preg_match('/(manufacturers_id|cPach)/', $url, $matches)) {
$url = 'index.php' . $url;
}
}
return $url;
}
set_time_limit(0);
$num_rows = array();
$now = date('Y-m-d H:i:s');
$file = 'db_' . DB_DATABASE . '_' . date('Y-m-d_H-i-s') . '.sql';
$fp = fopen($file, 'w');
$export = 'SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";' . "\n\n";
fputs($fp, $export);
$export = "-- Table: address_book\n\nTRUNCATE address_book;\n\n";
$address_sql = tep_db_query("SELECT * FROM " . DB_PREFIX . "address");
$rows = mysqli_num_rows($address_sql);
$i = 0;
if ($rows > 0)
$export .= "INSERT INTO address_book VALUES ";
while ($address = mysqli_fetch_array($address_sql)) {
$i++;
$export .= "('" . $address['address_id'] . "','" . $address['customer_id'] . "','','" . prepare_string($address['company']) . "','" . prepare_string($address['firstname']) . "','" . prepare_string($address['lastname']) . "','" . prepare_string($address['address_1']) . "','','" . prepare_string($address['postcode']) . "','" . prepare_string($address['city']) . "','','" . $address['country_id'] . "','" . $address['zone_id'] . "')" . ($i == $rows ? ";" : ",") . "\n";
}
$num_rows['address_book'] = $i;
mysqli_free_result($address_sql);
fputs($fp, $export);
$export = "\n-- Table: banners\n\nTRUNCATE banners;\nTRUNCATE banners_history;\n\n";
$banner_sql = tep_db_query("SELECT * FROM " . DB_PREFIX . "banner_image bi LEFT JOIN " . DB_PREFIX . "banner_image_description bid ON bi.banner_image_id = bid.banner_image_id GROUP BY bi.banner_image_id");
$i = 0;
$export .= "INSERT INTO banners VALUES ";
while ($banner = mysqli_fetch_array($banner_sql)) {
$i++;
$export .= "('" . $banner['banner_image_id'] . "','" . prepare_string($banner['title']) . "','" . convert_url($banner['link']) . "','" . str_replace('catalog/', '', $banner['image']) . "','footer','','0',NULL,NULL,'" . $now . "',NULL,'1'),\n";
$max_num = $banner['banner_image_id'];
}
$export .= "('" . ($max_num + 1) . "','osCommerce','http://www.oscommerce.com','banners/oscommerce.gif','footer','', '0',NULL,NULL,'" . $now . "',NULL,'1');\n";
$num_rows['banners'] = $i + 1;
mysqli_free_result($banner_sql);
fputs($fp, $export);
$export = "\n-- Table: categories\n\nTRUNCATE categories;\n\n";
$category_sql = tep_db_query("SELECT * FROM " . DB_PREFIX . "category");
$rows = mysqli_num_rows($category_sql);
$i = 0;
if ($rows > 0)
$export .= "INSERT INTO categories VALUES ";
while ($category = mysqli_fetch_array($category_sql)) {
$i++;
$export .= "('" . $category['category_id'] . "','" . str_replace('catalog/', '', $category['image']) . "','" . $category['parent_id'] . "','" . $category['sort_order'] . "','" . $category['date_added'] . "','" . $category['date_modified'] . "')" . ($i == $rows ? ";" : ",") . "\n";
}
$num_rows['categories'] = $i;
mysqli_free_result($category_sql);
fputs($fp, $export);
$export = "\n-- Table: categories_description\n\nTRUNCATE categories_description;\n\n";
$category_description_sql = tep_db_query("SELECT * FROM " . DB_PREFIX . "category_description");
$rows = mysqli_num_rows($category_description_sql);
$i = 0;
if ($rows > 0)
$export .= "INSERT INTO categories_description VALUES ";
while ($category_description = mysqli_fetch_array($category_description_sql)) {
$i++;
$export .= "('" . $category_description['category_id'] . "','" . $category_description['language_id'] . "','" . prepare_string($category_description['name']) . "')" . ($i == $rows ? ";" : ",") . "\n";
}
$num_rows['categories_description'] = $i;
mysqli_free_result($category_description_sql);
fputs($fp, $export);
$export = "\n-- Table: configuration\n\n";
$setting_sql = tep_db_query("SELECT * FROM " . DB_PREFIX . "setting");
while ($setting = mysqli_fetch_array($setting_sql)) {
switch ($setting['key']) {
case 'config_name':
$export .= "UPDATE configuration SET configuration_value = '" . $setting['value'] . "' WHERE configuration_key = 'STORE_NAME';\n";
break;
case 'config_owner':
$export .= "UPDATE configuration SET configuration_value = '" . $setting['value'] . "' WHERE configuration_key = 'STORE_OWNER';\n";
$config_owner = $setting['value'];
break;
case 'config_email':
$export .= "UPDATE configuration SET configuration_value = '" . $setting['value'] . "' WHERE configuration_key = 'STORE_OWNER_EMAIL_ADDRESS';\n";
$config_email = $setting['value'];
break;
case 'config_zone_id':
$export .= "UPDATE configuration SET configuration_value = '" . $setting['value'] . "' WHERE configuration_key = 'STORE_ZONE';\n";
break;
case 'config_country_id':
$export .= "UPDATE configuration SET configuration_value = '" . $setting['value'] . "' WHERE configuration_key = 'STORE_COUNTRY';\n";
break;
case 'config_address':
$export .= "UPDATE configuration SET configuration_value = '" . $setting['value'] . "' WHERE configuration_key = 'STORE_ADDRESS';\n";
break;
case 'config_telephone':
$export .= "UPDATE configuration SET configuration_value = '" . $setting['value'] . "' WHERE configuration_key = 'STORE_PHONE';\n";
break;
case 'config_mail_alert':
$emails = explode(PHP_EOL, $setting['value']);
if (count($emails) > 0) {
$config_mail_alert = array();
foreach ($emails as $email) {
$config_mail_alert[] = '<' . $email . '>';
}
$export .= "UPDATE configuration SET configuration_value = '" . implode(', ', $config_mail_alert) . "' WHERE configuration_key = 'SEND_EXTRA_ORDER_EMAILS_TO';\n";
}
break;
case 'config_tax':
$export .= "UPDATE configuration SET configuration_value = '" . ($setting['value'] == 1 ? 'true' : 'false') . "' WHERE configuration_key = 'DISPLAY_PRICE_WITH_TAX';\n";
break;
case 'config_currency':
$export .= "UPDATE configuration SET configuration_value = '" . $setting['value'] . "' WHERE configuration_key = 'DEFAULT_CURRENCY';\n";
break;
case 'config_order_status_id':
$export .= "UPDATE configuration SET configuration_value = '" . $setting['value'] . "' WHERE configuration_key = 'DEFAULT_ORDERS_STATUS_ID';\n";
break;
case 'config_currency':
$export .= "UPDATE configuration SET configuration_value = '" . $setting['value'] . "' WHERE configuration_key = 'DEFAULT_CURRENCY';\n";
break;
case 'config_language':
$export .= "UPDATE configuration SET configuration_value = '" . (strpos($setting['value'], '-') ? strstr($setting['value'], '-', true) : $setting['value']) . "' WHERE configuration_key = 'DEFAULT_LANGUAGE';\n";
break;
}
}
$export .= "UPDATE configuration SET configuration_value = '" . (!empty($config_owner) && !empty($config_email) ? '\"' . $config_owner . '\" <' . $config_email . '>' : '') . "' WHERE configuration_key = 'EMAIL_FROM';\n";
$num_rows['configuration'] = '11';
mysqli_free_result($setting_sql);
fputs($fp, $export);
$export = "\n-- Table: countries\n\nTRUNCATE countries;\n\n";
$country_sql = tep_db_query("SELECT * FROM " . DB_PREFIX . "country");
$rows = mysqli_num_rows($country_sql);
$i = 0;
if ($rows > 0)
$export .= "INSERT INTO countries VALUES ";
while ($country = mysqli_fetch_array($country_sql)) {
$i++;
$export .= "('" . $country['country_id'] . "','" . prepare_string($country['name']) . "','" . $country['iso_code_2'] . "','" . $country['iso_code_3'] . "','1')" . ($i == $rows ? ";" : ",") . "\n";
}
$num_rows['countries'] = $i;
mysqli_free_result($country_sql);
fputs($fp, $export);
$export = "\n-- Table: currencies\n\nTRUNCATE currencies;\n";
$currency_sql = tep_db_query("SELECT * FROM " . DB_PREFIX . "currency");
$rows = mysqli_num_rows($currency_sql);
$i = 0;
if ($rows > 0)
$export .= "INSERT INTO currencies VALUES ";
while ($currency = mysqli_fetch_array($currency_sql)) {
$i++;
$export .= "('" . $currency['currency_id'] . "','" . prepare_string($currency['title']) . "','" . $currency['code'] . "','" . $currency['symbol_left'] . "','" . $currency['symbol_right'] . "','.',',','" . $currency['decimal_place'] . "','" . $currency['value'] . "','" . $currency['date_modified'] . "')" . ($i == $rows ? ";" : ",") . "\n";
}
$num_rows['currencies'] = $i;
fputs($fp, $export);
$export = "\n-- Table: customers\n\nTRUNCATE customers;\n\n";
$customer_sql = tep_db_query("SELECT * FROM " . DB_PREFIX . "customer");
$rows = mysqli_num_rows($customer_sql);
$i = 0;
if ($rows > 0)
$export .= "INSERT INTO customers VALUES ";
while ($customer = mysqli_fetch_array($customer_sql)) {
$i++;
$export .= "('" . $customer['customer_id'] . "','','" . prepare_string($customer['firstname']) . "','" . prepare_string($customer['lastname']) . "','0000-00-00 00:00:00','" . prepare_string($customer['email']) . "','" . $customer['address_id'] . "','" . prepare_string($customer['telephone']) . "','" . prepare_string($customer['fax']) . "','','" . $customer['newsletter'] . "')" . ($i == $rows ? ";" : ",") . "\n";
}
$num_rows['customers'] = $i;
mysqli_free_result($customer_sql);
fputs($fp, $export . $export);
$export = "\n-- Table: customers_basket, customers_basket_attributes\n\nTRUNCATE customers_basket;\nTRUNCATE customers_basket_attributes;\n\n";
$cart_sql = tep_db_query("SELECT * FROM " . DB_PREFIX . "cart WHERE customer_id != '0'");
$i = 0;
$j = 0;
while ($cart = mysqli_fetch_array($cart_sql)) {
$products_id = $cart['product_id'];
if ($cart['option'] != '[]') {
$option = json_decode($cart['option'], true);
$attributes = '';
foreach ($option as $opt => $val) {
$attributes .= '{' . (int)$opt . '}' . (int)$val;
}
$products_id = $products_id . $attributes;
foreach ($option as $opt => $val) {
$export .= "INSERT INTO customers_basket_attributes VALUES ('" . $cart['customer_id'] . "','" . $products_id . "','" . (int)$opt . "','" . (int)$val . "')" . ($i == $rows ? ";" : ",") . "\n";
$j++;
}
}
$export .= "INSERT INTO customers_basket VALUES ('" . $cart['customer_id'] . "','" . $products_id . "','" . $cart['quantity'] . "','','" . date('Ymd', strtotime($cart['date_added'])) . "')" . ($i == $rows ? ";" : ",") . "\n";
$i++;
}
$num_rows['customers_basket'] = $i;
$num_rows['customers_basket_attributes'] = $j;
mysqli_free_result($cart_sql);
fputs($fp, $export);
$export = "\n-- Table: customers_info\n\nTRUNCATE customers_info;\n\n";
$customer_activity_sql = tep_db_query("SELECT customer_id, COUNT(customer_id) as count, date_added FROM " . DB_PREFIX . "customer_activity GROUP BY customer_id");
$rows = mysqli_num_rows($customer_activity_sql);
$i = 0;
if ($rows > 0)
$export .= "INSERT INTO customers_info VALUES ";
while ($customer_activity = mysqli_fetch_array($customer_activity_sql)) {
$i++;
$export .= "('" . $customer_activity['customer_id'] . "','','" . $customer_activity['count'] . "','" . $customer_activity['date_added'] . "','','0','',NULL)" . ($i == $rows ? ";" : ",") . "\n";
}
$num_rows['customers_info'] = $i;
mysqli_free_result($customer_activity_sql);
fputs($fp, $export);
$export = "\n-- Table: geo_zones\n\nTRUNCATE geo_zones;\n\n";
$geo_zone_sql = tep_db_query("SELECT * FROM " . DB_PREFIX . "geo_zone");
$rows = mysqli_num_rows($geo_zone_sql);
$i = 0;
if ($rows > 0)
$export .= "INSERT INTO geo_zones VALUES ";
while ($geo_zone = mysqli_fetch_array($geo_zone_sql)) {
$i++;
$export .= "('" . $geo_zone['geo_zone_id'] . "','" . prepare_string($geo_zone['name']) . "','" . prepare_string($geo_zone['description']) . "','" . $geo_zone['date_modified'] . "','" . $geo_zone['date_added'] . "')" . ($i == $rows ? ";" : ",") . "\n";
}
$num_rows['geo_zone'] = $i;;
mysqli_free_result($geo_zone_sql);
fputs($fp, $export);
$export = "\n-- Table: languages\n\nTRUNCATE languages;\n\n";
$language_sql = tep_db_query("SELECT * FROM " . DB_PREFIX . "language");
$languages = array();
$rows = mysqli_num_rows($language_sql);
$i = 0;
if ($rows > 0)
$export .= "INSERT INTO languages VALUES ";
while ($language = mysqli_fetch_array($language_sql)) {
$i++;
$export .= "('" . $language['language_id'] . "','" . prepare_string($language['name']) . "','" . (strpos($language['code'], '-') ? strstr($language['code'], '-', true) : $language['code']) . "','icon.gif','" . $language['directory'] . "','" . $language['sort_order'] . "')" . ($i == $rows ? ";" : ",") . "\n";
$languages[] = $language['language_id'];
}
$num_rows['languages'] = $i;
fputs($fp, $export);
$export = "\n-- Table: manufacturers, manufacturers_info\n\nTRUNCATE manufacturers;\nTRUNCATE manufacturers_info;\n\n";
$manufacturer_sql = tep_db_query("SELECT * FROM " . DB_PREFIX . "manufacturer");
$i = 0;
$j = 0;
while ($manufacturer = mysqli_fetch_array($manufacturer_sql)) {
$export .= "INSERT INTO manufacturers VALUES ('" . $manufacturer['manufacturer_id'] . "','" . $manufacturer['name'] . "','" . str_replace('catalog/', '', $manufacturer['image']) . "','" . $now . "',NULL);\n";
foreach ($languages as $language_id) {
$export .= "INSERT INTO manufacturers_info VALUES ('" . $manufacturer['manufacturer_id'] . "','" . $language_id . "','','0',NULL);\n";
$j++;
}
$i++;
}
$num_rows['manufacturers'] = $i;
$num_rows['manufacturers_info'] = $j;
mysqli_free_result($manufacturer_sql);
fputs($fp, $export);
$export = "\n-- Table: orders\n\nTRUNCATE orders;\n\n";
$order_sql = tep_db_query("SELECT * FROM " . DB_PREFIX . "order");
$rows = mysqli_num_rows($order_sql);
$i = 0;
if ($rows > 0)
$export .= "INSERT INTO orders VALUES ";
while ($order = mysqli_fetch_array($order_sql)) {
$i++;
$export .= "('" . $order['order_id'] . "','" . $order['customer_id'] . "','" . prepare_string($order['firstname'] . ' ' . $order['lastname']) . "','','" . prepare_string($order['payment_address_1']) . "','','" . prepare_string($order['payment_city']) . "','" . $order['payment_postcode'] . "','" . prepare_string($order['payment_zone']) . "','" . prepare_string($order['payment_country']) . "','" . prepare_string($order['telephone']) . "','" . $order['email'] . "','1','" . prepare_string($order['payment_firstname'] . ' ' . $order['payment_lastname']) . "','" . prepare_string($order['payment_company']) . "','" . prepare_string($order['payment_address_1']) . "','','" . prepare_string($order['payment_city']) . "','" . $order['payment_postcode'] . "','" . prepare_string($order['payment_zone']) . "','" . prepare_string($order['payment_country']) . "','1','" . prepare_string($order['shipping_firstname'] . ' ' . $order['shipping_lastname']) . "','" . prepare_string($order['shipping_company']) . "','" . prepare_string($order['shipping_address_1']) . "','','" . prepare_string($order['shipping_city']) . "','" . $order['shipping_postcode'] . "','" . prepare_string($order['shipping_zone']) . "','" . prepare_string($order['shipping_country']) . "','1','" . $order['payment_method'] . "','','','','','" . $order['date_modified'] . "','" . $order['date_added'] . "','" . $order['order_status_id'] . "',NULL,'" . $order['currency_code'] . "','" . $order['currency_value'] . "')" . ($i == $rows ? ";" : ",") . "\n";
}
$num_rows['orders'] = $i;
mysqli_free_result($order_sql);
fputs($fp, $export);
$export = "\n-- Table: orders_products\n\nTRUNCATE orders_products;\n\n";
$order_product_sql = tep_db_query("SELECT * FROM " . DB_PREFIX . "order_product");
$rows = mysqli_num_rows($order_product_sql);
$i = 0;
if ($rows > 0)
$export .= "INSERT INTO orders_products VALUES ";
while ($order_product = mysqli_fetch_array($order_product_sql)) {
$i++;
$export .= "('" . $order_product['order_product_id'] . "','" . $order_product['order_id'] . "','" . $order_product['product_id'] . "','" . prepare_string($order_product['model']) . "','" . prepare_string($order_product['name']) . "','" . $order_product['price'] . "','" . $order_product['total'] . "','" . $order_product['tax'] . "','" . $order_product['quantity'] . "')" . ($i == $rows ? ";" : ",") . "\n";
}
$num_rows['orders_products'] = $i;
mysqli_free_result($order_product_sql);
fputs($fp, $export);
$export = "\n-- Table: orders_products_attributes\n\nTRUNCATE orders_products_attributes;\n\n";
$order_option_sql = tep_db_query("SELECT * FROM " . DB_PREFIX . "order_option");
$rows = mysqli_num_rows($order_option_sql);
$i = 0;
if ($rows > 0)
$export .= "INSERT INTO orders_products_attributes VALUES ";
while ($order_option = mysqli_fetch_array($order_option_sql)) {
$i++;
$export .= "('" . $order_option['order_option_id'] . "','" . $order_option['order_id'] . "','" . $order_option['order_product_id'] . "','" . prepare_string($order_option['name']) . "','" . prepare_string($order_option['value']) . "','0.0000','+')" . ($i == $rows ? ";" : ",") . "\n";
}
$num_rows['orders_products_attributes'] = $i;
mysqli_free_result($order_option_sql);
fputs($fp, $export);
$export = "\n-- Table: orders_status\n\nTRUNCATE orders_status;\n\n";
$order_status_sql = tep_db_query("SELECT * FROM " . DB_PREFIX . "order_status ORDER BY order_status_id");
$i = 0;
while ($order_status = mysqli_fetch_array($order_status_sql)) {
$export .= "INSERT INTO orders_status VALUES ('" . $order_status['order_status_id'] . "','" . $order_status['language_id'] . "','" . prepare_string($order_status['name']) . "','1','0');\n";
$max_num = $order_status['order_status_id'];
$i++;
}
mysqli_free_result($order_status_sql);
foreach ($languages as $language_id) {
$export .= "INSERT INTO orders_status VALUES ('" . ($max_num + 1) . "','" . $language_id . "', 'PayPal [Transactions]', '0', '0');\n";
$i++;
}
$num_rows['orders_status'] = $i;
fputs($fp, $export);
$export = "\n-- Table: orders_status_history\n\nTRUNCATE orders_status_history;\n\n";
$order_history_sql = tep_db_query("SELECT * FROM " . DB_PREFIX . "order_history");
$rows = mysqli_num_rows($order_history_sql);
$i = 0;
if ($rows > 0)
$export .= "INSERT INTO orders_status_history VALUES ";
while ($order_history = mysqli_fetch_array($order_history_sql)) {
$i++;
$export .= "('" . $order_history['order_history_id'] . "','" . $order_history['order_id'] . "','" . $order_history['order_status_id'] . "','" . $order_history['date_added'] . "','" . $order_history['notify'] . "','" . prepare_string($order_history['comment']) . "')" . ($i == $rows ? ";" : ",") . "\n";
}
$num_rows['orders_status_history'] = $i;
mysqli_free_result($order_history_sql);
fputs($fp, $export);
$export = "\n-- Table: orders_total\n\nTRUNCATE orders_total;\n\n";
$order_total_sql = tep_db_query("SELECT * FROM " . DB_PREFIX . "order_total");
$order_total_order_id = null;
$rows = mysqli_num_rows($order_total_sql);
$i = 0;
if ($rows > 0)
$export .= "INSERT INTO orders_total VALUES ";
while ($order_total = mysqli_fetch_array($order_total_sql)) {
$i++;
if ($order_total_order_id != $order_total['order_id']) {
$order_total_order_id = $order_total['order_id'];
$currency_sql = tep_db_query("SELECT c.symbol_left, c.symbol_right FROM " . DB_PREFIX . "order o LEFT JOIN " . DB_PREFIX . "currency c ON o.currency_code = c.code WHERE o.order_id = '" . $order_total['order_id'] . "'");
$currency = mysqli_fetch_array($currency_sql);
$symbol_left = $currency['symbol_left'];
$symbol_right = $currency['symbol_right'];
}
$export .= "('" . $order_total['order_total_id'] . "','" . $order_total['order_id'] . "','" . prepare_string($order_total['title']) . ':' . "','" . ($order_total['code'] == 'total' ? '<strong>' . $symbol_left . $order_total['value'] . $symbol_right . '</strong>' : $symbol_left . $order_total['value'] . $symbol_right) . "','" . $order_total['value'] . "','" . 'ot_' . str_replace('_', '', $order_total['code']) . "','" . $order_total['sort_order'] . "')" . ($i == $rows ? ";" : ",") . "\n";
}
$num_rows['orders_total'] = $i;
mysqli_free_result($order_total_sql);
fputs($fp, $export);
$export = "\n-- Table: products\n\nTRUNCATE products;\n\n";
$product_sql = tep_db_query("SELECT * FROM " . DB_PREFIX . "product");
$rows = mysqli_num_rows($product_sql);
$i = 0;
if ($rows > 0)
$export .= "INSERT INTO products VALUES ";
while ($product = mysqli_fetch_array($product_sql)) {
$i++;
$order_product_sql = tep_db_query("SELECT COUNT(product_id) as ordered FROM " . DB_PREFIX . "order_product WHERE product_id = '" . $product['product_id'] . "'");
$order_product = mysqli_fetch_array($order_product_sql);
$export .= "('" . $product['product_id'] . "','" . $product['quantity'] . "','" . prepare_string($product['model']) . "','" . str_replace('catalog/', '', $product['image']) . "','" . $product['price'] . "','" . $product['date_added'] . "','" . $product['date_modified'] . "','" . $product['date_available'] . "','" . $product['weight'] . "','" . $product['status'] . "','" . $product['tax_class_id'] . "','" . $product['manufacturer_id'] . "','" . $order_product['ordered'] . "')" . ($i == $rows ? ";" : ",") . "\n";
}
$num_rows['products'] = $i;
mysqli_free_result($product_sql);
fputs($fp, $export);
$export = "\n-- Table: products_attributes\n\nTRUNCATE products_attributes;\n\n";
$product_option_value_sql = tep_db_query("SELECT * FROM " . DB_PREFIX . "product_option_value");
$rows = mysqli_num_rows($product_option_value_sql);
$i = 0;
if ($rows > 0)
$export .= "INSERT INTO products_attributes VALUES ";
while ($product_option_value = mysqli_fetch_array($product_option_value_sql)) {
$i++;
$export .= "('" . $product_option_value['product_option_value_id'] . "','" . $product_option_value['product_id'] . "','" . $product_option_value['option_id'] . "','" . $product_option_value['option_value_id'] . "','" . $product_option_value['price'] . "','" . $product_option_value['price_prefix'] . "')" . ($i == $rows ? ";" : ",") . "\n";
}
$num_rows['products_attributes'] = $i;
mysqli_free_result($product_option_value_sql);
fputs($fp, $export);
$export = "\n-- Table: products_description\n\nTRUNCATE products_description;\n\n";
$product_description_sql = tep_db_query("SELECT * FROM " . DB_PREFIX . "product_description");
$rows = mysqli_num_rows($product_description_sql);
$i = 0;
if ($rows > 0)
$export .= "INSERT INTO products_description VALUES ";
while ($product_description = mysqli_fetch_array($product_description_sql)) {
$i++;
$export .= "('" . $product_description['product_id'] . "','" . $product_description['language_id'] . "','" . prepare_string($product_description['name']) . "','" . str_replace(array('\r',
'\n',
'\t'), '', htmlspecialchars_decode(prepare_string($product_description['description']))) . "','','0')" . ($i == $rows ? ";" : ",") . "\n";
}
$num_rows['products_description'] = $i;
mysqli_free_result($product_description_sql);
fputs($fp, $export);
$export = "\n-- Table: products_images\n\nTRUNCATE products_images;\n\n";
$product_image_sql = tep_db_query("SELECT * FROM " . DB_PREFIX . "product_image");
$rows = mysqli_num_rows($product_image_sql);
$i = 0;
if ($rows > 0)
$export .= "INSERT INTO products_images VALUES ";
while ($product_image = mysqli_fetch_array($product_image_sql)) {
$i++;
$export .= "('" . $product_image['product_image_id'] . "','" . $product_image['product_id'] . "','" . trim(str_replace('catalog/', '', $product_image['image'])) . "',NULL,'" . $product_image['sort_order'] . "')" . ($i == $rows ? ";" : ",") . "\n";
}
$num_rows['products_images'] = $i;
mysqli_free_result($product_image_sql);
fputs($fp, $export);
$export = "\n-- Table: products_to_categories\n\nTRUNCATE products_to_categories;\n\n";
$product_to_category_sql = tep_db_query("SELECT * FROM " . DB_PREFIX . "product_to_category");
$rows = mysqli_num_rows($product_to_category_sql);
$i = 0;
if ($rows > 0)
$export .= "INSERT INTO products_to_categories VALUES ";
while ($product_to_category = mysqli_fetch_array($product_to_category_sql)) {
$i++;
$export .= "('" . $product_to_category['product_id'] . "','" . $product_to_category['category_id'] . "')" . ($i == $rows ? ";" : ",") . "\n";
}
$num_rows['products_to_categories'] = $i;
mysqli_free_result($product_to_category_sql);
fputs($fp, $export);
$export = "\n-- Table: products_options\n\nTRUNCATE products_options;\n\n";
$option_description_sql = tep_db_query("SELECT * FROM " . DB_PREFIX . "option_description");
$rows = mysqli_num_rows($option_description_sql);
$i = 0;
if ($rows > 0)
$export .= "INSERT INTO products_options VALUES ";
while ($option_description = mysqli_fetch_array($option_description_sql)) {
$i++;
$export .= "('" . $option_description['option_id'] . "','" . $option_description['language_id'] . "','" . prepare_string($option_description['name']) . "')" . ($i == $rows ? ";" : ",") . "\n";
}
$num_rows['products_option'] = $i;
mysqli_free_result($option_description_sql);
fputs($fp, $export);
$export = "\n-- Table: products_options_values\n\nTRUNCATE products_options_values;\n\n";
$option_value_description_sql = tep_db_query("SELECT * FROM " . DB_PREFIX . "option_value_description");
$rows = mysqli_num_rows($option_value_description_sql);
$i = 0;
if ($rows > 0)
$export .= "INSERT INTO products_options_values VALUES ";
while ($option_value_description = mysqli_fetch_array($option_value_description_sql)) {
$i++;
$export .= "('" . $option_value_description['option_value_id'] . "','" . $option_value_description['language_id'] . "','" . prepare_string($option_value_description['name']) . "')" . ($i == $rows ? ";" : ",") . "\n";
}
$num_rows['products_options_values'] = $i;
mysqli_free_result($option_value_description_sql);
fputs($fp, $export);
$export = "\n-- Table: products_options_values_to_products_options\n\nTRUNCATE products_options_values_to_products_options;\n\n";
$product_option_value_sql = tep_db_query("SELECT * FROM " . DB_PREFIX . "product_option_value");
$rows = mysqli_num_rows($product_option_value_sql);
$i = 0;
if ($rows > 0)
$export .= "INSERT INTO products_options_values_to_products_options VALUES ";
while ($product_option_value = mysqli_fetch_array($product_option_value_sql)) {
$i++;
$export .= "('" . $product_option_value['product_option_value_id'] . "','" . $product_option_value['option_id'] . "','" . $product_option_value['option_value_id'] . "')" . ($i == $rows ? ";" : ",") . "\n";
}
$num_rows['products_options_values_to_products_options'] = $i;
mysqli_free_result($product_option_value_sql);
fputs($fp, $export);
$export = "\n-- Table: reviews, reviews_description\n\nTRUNCATE reviews;\nTRUNCATE reviews_description;\n\n";
$review_sql = tep_db_query("SELECT * FROM " . DB_PREFIX . "review");
$i = 0;
if ($rows > 0)
while ($review = mysqli_fetch_array($review_sql)) {
$export .= "INSERT INTO reviews VALUES ('" . $review['review_id'] . "','" . $review['product_id'] . "','" . $review['customer_id'] . "','" . prepare_string($review['author']) . "','" . $review['rating'] . "','" . $review['date_added'] . "','" . $review['date_modified'] . "','" . $review['status'] . "','0');\n";
$export .= "INSERT INTO reviews_description VALUES ('" . $review['review_id'] . "','" . $languages[0] . "','" . prepare_string($review['text']) . "');\n";
$i++;
}
$num_rows['reviews'] = $i;
$num_rows['reviews_description'] = $i;
mysqli_free_result($review_sql);
fputs($fp, $export);
$export = "\n-- Table: sessions\n\nTRUNCATE sessions;\n\n";
fputs($fp, $export);
$export = "\n-- Table: specials\n\nTRUNCATE specials;\n\n";
$product_special_sql = tep_db_query("SELECT distinct * FROM " . DB_PREFIX . "product_special WHERE priority = (SELECT MIN(priority) from " . DB_PREFIX . "product_special)");
$rows = mysqli_num_rows($product_special_sql);
$i = 0;
if ($rows > 0)
$export .= "INSERT INTO specials VALUES ";
while ($product_special = mysqli_fetch_array($product_special_sql)) {
$i++;
$export .= "('" . $product_special['product_special_id'] . "','" . $product_special['product_id'] . "','" . $product_special['price'] . "','" . $now . "',NULL,'" . $product_special['date_start'] . "',NULL,'" . ($product_special['date_end'] != '0000-00-00' && strtotime(date('Y-m-d')) > strtotime($product_special['date_end']) ? '0' : '1') . "')" . ($i == $rows ? ";" : ",") . "\n";
}
$num_rows['specials'] = $i;
mysqli_free_result($product_special_sql);
fputs($fp, $export);
$export = "\n-- Table: tax_class\n\nTRUNCATE tax_class;\n\n";
$tax_class_sql = tep_db_query("SELECT * FROM " . DB_PREFIX . "tax_class");
$rows = mysqli_num_rows($tax_class_sql);
$i = 0;
if ($rows > 0)
$export .= "INSERT INTO tax_class VALUES ";
while ($tax_class = mysqli_fetch_array($tax_class_sql)) {
$i++;
$export .= "('" . $tax_class['tax_class_id'] . "','" . prepare_string($tax_class['title']) . "','" . prepare_string($tax_class['description']) . "','" . $tax_class['date_modified'] . "','" . $tax_class['date_added'] . "')" . ($i == $rows ? ";" : ",") . "\n";
}
$num_rows['tax_class'] = $i;
mysqli_free_result($tax_class_sql);
fputs($fp, $export);
$export = "\n-- Table: tax_rates\n\nTRUNCATE tax_rates;\n\n";
$tax_rate_sql = tep_db_query("SELECT * FROM " . DB_PREFIX . "tax_rate tra LEFT JOIN " . DB_PREFIX . "tax_rule tru ON tra.tax_rate_id = tru.tax_rate_id GROUP BY tra.tax_rate_id");
$rows = mysqli_num_rows($tax_rate_sql);
$i = 0;
if ($rows > 0)
$export .= "INSERT INTO tax_rates VALUES ";
while ($tax_rate = mysqli_fetch_array($tax_rate_sql)) {
$i++;
$export .= "('" . $tax_rate['tax_rate_id'] . "','" . $tax_rate['geo_zone_id'] . "','" . $tax_rate['tax_class_id'] . "','" . $tax_rate['priority'] . "','" . $tax_rate['rate'] . "','" . prepare_string($tax_rate['name']) . "','" . $tax_rate['date_modified'] . "','" . $tax_rate['date_added'] . "')" . ($i == $rows ? ";" : ",") . "\n";
}
$num_rows['tax_rates'] = $i;
mysqli_free_result($tax_rate_sql);
fputs($fp, $export);
$export = "\n-- Table: zones\n\nTRUNCATE zones;\n\n";
$zone_sql = tep_db_query("SELECT * FROM " . DB_PREFIX . "zone");
$rows = mysqli_num_rows($zone_sql);
$i = 0;
if ($rows > 0)
$export .= "INSERT INTO zones VALUES ";
while ($zone = mysqli_fetch_array($zone_sql)) {
$i++;
$export .= "('" . $zone['zone_id'] . "','" . $zone['country_id'] . "','" . prepare_string($zone['code']) . "','" . prepare_string($zone['name']) . "')" . ($i == $rows ? ";" : ",") . "\n";
}
$num_rows['zones'] = $i;
mysqli_free_result($zone_sql);
fputs($fp, $export);
$export = "\n-- Table: zones_to_geo_zones\n\nTRUNCATE zones_to_geo_zones;\n\n";
$zone_to_geo_zone_sql = tep_db_query("SELECT * FROM " . DB_PREFIX . "zone_to_geo_zone");
$rows = mysqli_num_rows($zone_to_geo_zone_sql);
$i = 0;
if ($rows > 0)
$export .= "INSERT INTO zones_to_geo_zones VALUES ";
while ($zone_to_geo_zone = mysqli_fetch_array($zone_to_geo_zone_sql)) {
$i++;
$export .= "('" . $zone_to_geo_zone['zone_to_geo_zone_id'] . "','" . $zone_to_geo_zone['country_id'] . "','" . $zone_to_geo_zone['zone_id'] . "','" . $zone_to_geo_zone['geo_zone_id'] . "'," . ($zone_to_geo_zone['date_modified'] == '0000-00-00 00:00:00' ? "NULL" : "'" . $zone_to_geo_zone['date_modified'] . "'") . "," . ($zone_to_geo_zone['date_added'] == '0000-00-00 00:00:00' ? "'" . $now . "'" : "'" . $zone_to_geo_zone['date_added'] . "'") . ")" . ($i == $rows ? ";" : ",") . "\n";
}
$num_rows['zones_to_geo_zones'] = $i;
mysqli_free_result($zone_to_geo_zone_sql);
fputs($fp, $export);
fclose($fp);
tep_db_close();
echo '<pre>';
echo "OpenCart -> osCommerce\n";
echo "+------------------------------------------------------+\n";
echo "| Tables | Rows |\n";
echo "+------------------------------------------------------+\n";
foreach ($num_rows as $table => $row) {
echo sprintf("| %-43s | %6d |\n", $table, $row);
}
echo "+------------------------------------------------------+\n\n";
echo 'Time: ' . round(get_microtime() - $start, 4) . 'sec' . "\n";
echo 'File Size: ' . get_bytes(filesize($file)) . "\n\n";
echo '<a href="' . $file . '">Download ' . $file . '</a>';
echo '</pre>';
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment