2022-04-24
23076
32
3994
8 minutes.


Update on the site of the online store prices, availability, photos through the E-Trade Tunnel module for self-written CMS (SQL queries)

To create a template for uploading to the site via the E-Trade Tunnel integration module, open the "Download product catalog" window by clicking on the shortcut on the desktop
elbuz etrade jumper export products
or by selecting a menu item
elbuz etrade jumper export products
then press "+" and select the format "E-Trade Tunnel"
elbuz etrade jumper export products

After creating the download template, you can switch to edit mode by clicking on the "Pencil" button.
elbuz etrade jumper export products
If you have not previously installed the E-Trade Tunnel module on your hosting, then this must be done according to these instructions (up to the point of downloading data).

Select "Manual Mode (SQL)", configure access to the site and the site database

Next, open the site update section based on SQL queries

Example of updating a site based on CMS OpenCart

Add uuid fields once for site database tables to store ID from Jumper

ALTER TABLE oc_category ADD uuid VARCHAR(36) NOT NULL, ADD INDEX (uuid);;; ALTER TABLE oc_product ADD uuid VARCHAR(36) NOT NULL, ADD INDEX (uuid);;; ALTER TABLE oc_manufacturer ADD uuid VARCHAR(36) NOT NULL, ADD INDEX (uuid);;; ALTER TABLE oc_attribute ADD uuid VARCHAR(36) NOT NULL, ADD INDEX (uuid);;; ALTER TABLE oc_attribute_group ADD uuid VARCHAR(36) NOT NULL, ADD INDEX (uuid);;;

Update the identifier in the uuid field based on the site database tables

UPDATE oc_category SET uuid=category_id WHERE uuid='';;; UPDATE oc_product SET uuid=product_id WHERE uuid='';;; UPDATE oc_manufacturer SET uuid=manufacturer_id WHERE uuid='';;; UPDATE oc_attribute SET uuid=attribute_id WHERE uuid='';;;


Note the records in the temporary tables of E-Trade, which are in the database of the site

Categories

UPDATE etrade_category_temp etrade_temp, oc_category c SET etrade_temp. category_id = c. category_id WHERE etrade_temp. uuid = c. uuid;;; UPDATE etrade_category_temp etrade_temp, oc_category c SET etrade_temp. parent_id = c. category_id WHERE etrade_temp. uuid_parent = c. uuid;;; UPDATE etrade_category_temp SET row_exist = 1 WHERE category_id>0;;;

Goods

UPDATE etrade_product_temp etrade_temp, oc_product p SET etrade_temp. product_id = p. product_id WHERE etrade_temp. uuid = p. uuid;;; UPDATE etrade_product_to_category_temp etrade_temp, oc_product p SET etrade_temp. product_id = p. product_id WHERE etrade_temp. product_uuid = p. uuid;;; UPDATE etrade_product_to_category_temp etrade_temp, oc_category c SET etrade_temp. category_id = c. category_id WHERE etrade_temp. category_uuid = c. uuid;;; UPDATE etrade_product_temp etrade_temp, oc_category c SET etrade_temp. category_id = c. category_id WHERE etrade_temp. category_uuid = c. uuid;;; UPDATE etrade_product_temp etrade_temp, oc_manufacturer m SET etrade_temp. manufacturer_id = m. manufacturer_id WHERE etrade_temp. manufacturer_uuid = m. uuid;;; UPDATE etrade_product_temp SET row_exist = 1 WHERE product_id>0;;;

Manufacturers

UPDATE etrade_manufacturer_temp etrade_temp, oc_manufacturer m SET etrade_temp. manufacturer_id = m. manufacturer_id WHERE etrade_temp. uuid = m. uuid;;; UPDATE etrade_manufacturer_temp SET row_exist = 1 WHERE manufacturer_id>0;;;

Attributes and values

UPDATE etrade_attribute_block_temp etrade_temp, oc_attribute_group ag SET etrade_temp. attribute_block_id = ag. attribute_group_id WHERE etrade_temp. uuid = ag. uuid;;; UPDATE etrade_attribute_temp etrade_temp, oc_attribute a SET etrade_temp. attribute_id = a. attribute_id WHERE etrade_temp. uuid = a. uuid;;; UPDATE etrade_attribute_temp etrade_temp, oc_attribute_group ag SET etrade_temp. attribute_block_id = ag. attribute_group_id WHERE etrade_temp. block_uuid = ag. uuid;;; UPDATE etrade_product_attribute_temp etrade_temp, oc_attribute a SET etrade_temp. attribute_id = a. attribute_id WHERE etrade_temp. attribute_uuid = a. uuid;;; UPDATE etrade_product_attribute_temp etrade_temp, oc_product p SET etrade_temp. product_id = p. product_id WHERE etrade_temp. product_uuid = p. uuid;;; UPDATE etrade_attribute_block_temp SET row_exist = 1 WHERE attribute_block_id>0;;; UPDATE etrade_attribute_temp SET row_exist = 1 WHERE attribute_id>0;;;

SQL queries for inserting and updating records in the site database

UPDATE oc_category, etrade_category_temp etrade_temp SET oc_category. date_modified = NOW(), oc_category. sort_order = etrade_temp. sort_order, oc_category. status = etrade_temp. status WHERE etrade_temp. row_exist = 1 AND etrade_temp. category_id = oc_category. category_id;;; UPDATE oc_category_description, etrade_category_temp etrade_temp SET oc_category_description. name = etrade_temp. name WHERE etrade_temp. row_exist = 1 AND etrade_temp. category_id = oc_category_description. category_id AND oc_category_description. language_id = 1;;; UPDATE oc_manufacturer, etrade_manufacturer_temp etrade_temp SET oc_manufacturer. name = etrade_temp. name WHERE etrade_temp. row_exist = 1 AND etrade_temp. manufacturer_id = oc_manufacturer. manufacturer_id;;; UPDATE oc_product, etrade_product_temp etrade_temp SET oc_product. date_modified = NOW(), oc_product. manufacturer_id = etrade_temp. manufacturer_id, oc_product. model = etrade_temp. model, oc_product. sku = etrade_temp. mpn, oc_product. price = etrade_temp. price, oc_product. quantity = etrade_temp. quantity, oc_product. status = etrade_temp. status, oc_product. weight = etrade_temp. weight WHERE etrade_temp. row_exist = 1 AND etrade_temp. product_id = oc_product. product_id AND etrade_temp. type_id=1;;; UPDATE oc_product_description, etrade_product_temp etrade_temp SET oc_product_description. name = etrade_temp. name WHERE etrade_temp. row_exist = 1 AND etrade_temp. product_id = oc_product_description. product_id AND etrade_temp. type_id = 1 AND oc_product_description. language_id = 1;;; INSERT INTO oc_category (date_added, sort_order, status, uuid) SELECT NOW() as `date_added`, sort_order, status, uuid FROM etrade_category_temp WHERE row_exist = 0 GROUP BY uuid;;; UPDATE etrade_category_temp etrade_temp, oc_category site_table SET etrade_temp. category_id = site_table. category_id WHERE etrade_temp. row_exist = 0 AND etrade_temp. uuid = site_table. uuid;;; UPDATE etrade_category_temp, (SELECT category_id, uuid FROM etrade_category_temp) as t2 SET etrade_category_temp. parent_id = t2. category_id WHERE t2. uuid = etrade_category_temp. uuid_parent;;; UPDATE etrade_category_temp, oc_category site_table SET site_table. parent_id = etrade_category_temp. parent_id WHERE site_table. uuid = etrade_category_temp. uuid;;; UPDATE etrade_product_temp etrade_temp, oc_category site_table SET etrade_temp. category_id = site_table. category_id WHERE etrade_temp. category_uuid = site_table. uuid;;; UPDATE etrade_product_to_category_temp etrade_temp, oc_category site_table SET etrade_temp. category_id = site_table. category_id WHERE etrade_temp. category_uuid = site_table. uuid;;; INSERT INTO oc_category_description (category_id, description, language_id, meta_description, meta_keyword, name) SELECT category_id, description_short, 1 as language_id, meta_description, meta_keyword, name FROM etrade_category_temp WHERE row_exist = 0;;; INSERT INTO oc_manufacturer (name, uuid) SELECT name, uuid FROM etrade_manufacturer_temp WHERE row_exist = 0 GROUP BY uuid;;; UPDATE etrade_manufacturer_temp etrade_temp, oc_manufacturer site_table SET etrade_temp. manufacturer_id = site_table. manufacturer_id WHERE etrade_temp. row_exist = 0 AND etrade_temp. uuid = site_table. uuid;;; UPDATE etrade_product_temp etrade_temp, oc_manufacturer site_table SET etrade_temp. manufacturer_id = site_table. manufacturer_id WHERE etrade_temp. manufacturer_uuid = site_table. uuid;;; INSERT INTO oc_product (date_added, date_available, manufacturer_id, model, sku, price, quantity, status, uuid, weight) SELECT NOW() as `date_added`, NOW() as `date_available`, manufacturer_id, model, mpn, price, quantity, status, uuid, weight FROM etrade_product_temp WHERE row_exist = 0 AND etrade_product_temp. type_id=1 GROUP BY uuid;;; UPDATE etrade_product_temp etrade_temp, oc_product site_table SET etrade_temp. product_id = site_table. product_id WHERE etrade_temp. row_exist = 0 AND etrade_temp. uuid = site_table. uuid AND etrade_temp. type_id=1;;; UPDATE etrade_product_to_category_temp etrade_temp, oc_product site_table SET etrade_temp. product_id = site_table. product_id WHERE etrade_temp. product_uuid = site_table. uuid;;; <code>UPDATE etrade_image_temp etrade_temp, oc_product site_table SET etrade_temp. item_id = site_table. product_id WHERE etrade_temp. row_type="product" AND etrade_temp. item_uuid = site_table. uuid;;;

INSERT INTO oc_product_description (description, language_id, meta_description, meta_keyword, name, product_id)
SELECT description_full, 1 as language_id, meta_description, meta_keyword, name, product_id
FROM etrade_product_temp
WHERE row_exist = 0 AND etrade_product_temp. type_id=1;;;

UPDATE etrade_image_temp etrade_temp, oc_product site_table
SET site_table. image = etrade_temp. image
WHERE etrade_temp. row_type = "product" AND
etrade_temp. item_id = site_table. product_id;;;

Description of temporary tables and fields created when updating the site

Field name Description
currency_id Currency ID
uuid Unique symbolic identifier
code Currency code
name Currency name
rate Exchange rate
is_default The flag is the default currency
Field name Description
language_id Language ID
uuid Unique symbolic identifier
code Language code
name The name of the language
is_default Default language
code_from_site Language code downloaded from the site
Field name Description
row_exist Flag - an entry found in the site database
contractor_id Counterparty ID
uuid Unique symbolic identifier
group_name Name of the group of contractors
name Name of the counterparty
status Activity
county Country
city The city
address Address
address_law Legal address
phones Phone
url Link to the site
email Email mail
contact_person Contact person
description Note
sort_order Sort order
Then there may be user-created fields
Field name Description
uuid Unique symbolic identifier
code Code
name Name
is_base_unit The flag is the base unit
status Activity
unit_name Name
unit_name_alias The name is synonymous
unit_name_local The name is local
type Type
odds Conversion factor
Field name Description
store_id Store ID
uuid Unique symbolic identifier
name Store name
is_default The flag is the default
url Link to the site
Field name Description
warehouse_id Warehouse ID
store_id Store ID
uuid Unique symbolic identifier of a warehouse
name The name of the warehouse
is_default The flag is the default
store_uuid Unique symbolic store identifier
description Note
c_latitude Coordinate latitude
c_longitude The longitude coordinate
sort_order Sort order
is_virtual The flag is a virtual warehouse
Field name Description
row_exist Flag - an entry found in the site database
category_id Category ID
parent_id Parent category ID
language_id Language ID
name Category name
status Activity
image Link to the picture
uuid Unique symbolic identifier of the category
uuid_parent Unique symbolic identifier of the parent category
seo_url CNC
product_count Number of products in category
sort_order Sort order
description_short The description is short
description_full The description is complete
meta_title SEO meta title
meta_description SEO meta description
meta_keyword SEO meta keywords
seo_h1 Text for the h1 tag
name_full List of all categories that include this category
language_uuid Unique symbolic language identifier
left_key Left key
right_key The right key
level Nesting level
id_path Listing category IDs
id_path_uuid Enumeration of uuid categories
seo_url _full SEO with all categories
category_id_jumper Category ID in the Jumper database
parent_id_jumper The parent category ID in the Jumper database
Then there may be user-created fields
Field name Description
row_exist Flag - an entry found in the site database
category_id Category ID
store_id Store ID
category_uuid Unique symbolic identifier of the category
store_uuid Unique symbolic store identifier
Field name Description
row_exist Flag - an entry found in the site database
product_id Product ID
language_id Language ID
category_id Category ID
manufacturer_id Manufacturer ID
sku Internal article
price Price
price_rrp RRC
quantity Quantity
model Model
upc Barcode
ean Barcode
Jan Barcode
isbn Barcode
mpn Manufacturer's SKU
stock_location Location in the warehouse
points Number of points
date_available Availability date
warranty Warranty
weight Weight
length Length
width Width
height Height
subtract_quantity Flag - deduct from the warehouse when ordering goods
order_minimum Minimum order quantity
sort_order Sort order
status Activity
viewed Number of views
price_old The old price
delivery_cost Shipping cost
contractor_id Counterparty ID
uuid Unique symbolic identifier
category_uuid Unique symbolic identifier of the category
manufacturer_uuid Unique symbolic identifier of the manufacturer
sku_supply Article of the contractor (supplier)
seo_url CNC
ext_url External link to the product
product_id _ext Product ID on the external site
name Product name
description_short The description is short
description_full The description is complete
meta_description SEO meta description
meta_keyword SEO meta keywords
meta_title SEO meta title
seo_h1 Text for the h1 tag
tag List of tags
language_uuid Unique symbolic language identifier
flag_archive The flag is an archival product
flag_xml_export Flag - unload on the trading floor
flag_popular The flag is a popular product
flag_best_deal Flag is the best buy
flag_fix_price Flag - fixed price
stock_status_id Availability status ID
stock_status_name Availability status name
stock_status_image Availability status picture
stock_status_color Availability status color
manufacturer_name Manufacturer's name
contractor_name Name of the counterparty
profit_value Profit, value
profit_persent Profit, interest
price_cost Price cost
flag_new_product Flag - new product (novelty)
type_id Product type, 1 - regular product, 2 - option product.
uuid_parent Unique symbolic identifier of the parent product
id_parent Unique numerical identifier of the parent product
measure_uuid Unique symbolic identifier of the unit of measurement
product_id_jumper Product ID in the Jumper database
Then there may be user-created fields
Field name Description
row_exist Flag - an entry found in the site database
product_id Product ID
store_id Store ID
product_uuid Unique symbolic identifier of the goods
store_uuid Unique symbolic store identifier
quantity Quantity
price Price
price_rrp RRC
price_cost Price cost
stock_status_name Availability status
Field name Description
row_exist Flag - an entry found in the site database
product_id Product ID
warehouse_id Warehouse ID
product_uuid Unique symbolic identifier of the goods
warehouse_uuid Unique symbolic identifier of a warehouse
quantity Quantity
price Price
stock_status_name Availability status
Field name Description
product_id Product ID
category_id Category ID
product_uuid Unique symbolic identifier of the goods
category_uuid Unique symbolic identifier of the category
sort_order Sort order
is_main The flag is the main category
Field name Description
row_exist Flag - an entry found in the site database
manufacturer_id Manufacturer ID
language_id Language ID
name Manufacturer's name
image Logo
url Link to the manufacturer's website
seo_url SEO link
uuid Unique symbolic identifier
description_short The description is short
description_full The description is complete
meta_title SEO meta title
meta_description SEO meta description
meta_keyword SEO meta keywords
seo_h1 Text for the h1 tag
language_uuid Unique symbolic language identifier
address Address
address_service Address of service centers
tag List of tags
Then there may be user-created fields
Field name Description
row_exist Flag - an entry found in the site database
manufacturer_id Manufacturer ID
store_id Store ID
manufacturer_uuid Unique symbolic identifier of the manufacturer
store_uuid Unique symbolic store identifier
Field name Description
row_exist Flag - an entry found in the site database
attribute_block_id Attribute block ID
language_id Language ID
name Attribute block name
description Attribute block description
sort_order Sort order
image Picture
uuid Unique character attribute block identifier
language_uuid Unique symbolic language identifier
code Attribute Block (CNC) Code
status Activity
flag_exported Flag - the fact of uploading data to the site
Field name Description
row_exist Flag - an entry found in the site database
row_exist _option Flag - an entry about the option found in the site database
attribute_id Product attribute ID
attribute_block_id Attribute block ID
language_id Language ID
option_id Option ID
name Attribute name
description Attribute description
sort_order Sort order
image Attribute image
required Flag - required
type_code Attribute type
type_code_filter Attribute type for the filter
block_uuid Unique character attribute block identifier
uuid Unique symbolic attribute identifier
language_uuid Unique symbolic language identifier
code Attribute Code (CNC)
show_on_page Flag - show on the page
status Activity
attribute_uuid_parent Unique symbolic identifier of the parent attribute
attribute_main The flag is the main attribute
flag_option Flag - cargo option
flag_expand Flag - list of values opened (for filter)
filter_max_attribute (for filter)
filter_max_attribute_value (for filter)
group_by_value Flag - group by values (for filter)
flag_exported Flag - the fact of uploading data to the site
postfix Additional text
flag_multiline Flag - multiline values
type_code_site The type of attribute obtained from the site
Field name Description
category_id Category ID
attribute_id Attribute ID
category_uuid Unique symbolic identifier of the category
attribute_uuid Unique symbolic attribute identifier
sort_order Sort order
filter Flag Use for filter
flag_global For all categories
row_exist Flag - an entry found in the site database
filter_max_attribute (for filter)
filter_max_attribute_value (for filter)
Field name Description
row_exist Flag - an entry found in the site database
product_id Product ID
id_parent Parent Attribute ID
attribute_id Product attribute ID
attribute_block_id Attribute block ID
language_id Language ID
option_id Option ID
attribute_main The flag is the main attribute
type_code Attribute type
attribute_value Attribute value
product_uuid Unique symbolic identifier of the goods
uuid_parent Unique symbolic identifier of the parent attribute
attribute_uuid Unique symbolic attribute identifier
language_uuid Unique symbolic language identifier
flag_option Flag - cargo option
required Flag - required
attribute_value_id Attribute value ID
value_crc32 Attribute CRC checksum
flag_exported Flag - the fact of uploading data to the site
sort_order Sort order
attribute_color Attribute color
measure_uuid Unique symbolic identifier of the unit of measurement
attribute_block_uuid Unique character attribute block identifier
stock_status_id_main Availability status of the goods
stock_status_id Availability status from option
quantity Quantity
price Price
price_prefix Price addition type (= - +) when selecting an option
points_prefix Type of adding points (= - +) when selecting an option
weight_prefix The type of weight gain (= - +) when selecting an option
points Points
weight Weight
subtract Flag - deduct from warehouse when ordering
sku Article options
row_uuid Unique symbolic record identifier
Then there may be user-created fields
Field name Description
row_exist Flag - an entry found in the site database
attribute_value_id The value ID for the attribute
attribute_id Product attribute ID
language_id Language ID
attribute_uuid Unique symbolic attribute identifier
language_uuid Unique symbolic language identifier
sort_order Sort order
code Attribute Code (CNC)
attribute_value The meaning of the attribute
postfix Additional text
description Description of the meaning
value_crc32 CRC checksum value
measure_uuid Unique symbolic identifier of the unit of measurement
Field name Description
row_exist Flag - an entry found in the site database
row_type Record type (category, product)
item_id Item ID (Category ID, Product ID)
image Directory file name
image_cloud_url Link to the photo
image_original Link to the photo received from the site
image_site_path Storage path on the site
image_path Storage path
image_file_name File name
image_path_original Site storage path (obtained from the site)
image_file_name_original File name obtained from the site
saved_path The way to save
saved_file_name File name to save
sort_order Sort order
uuid Unique symbolic identifier of the photo
item_uuid Unique symbolic identifier of an element (category, product)
downloaded Flag - photo uploaded to the site
flag_from_site Flag - photo received from the site
Field name Description
row_type Record type (category, product)
item_id Item ID (Category ID, Product ID)
alt Text for the alt tag
title Text for the title tag
product_image_id Photo ID
item_uuid Unique symbolic identifier of an element (category, product)
language_uuid Unique symbolic language identifier
Field name Description
row_exist Flag - an entry found in the site database
product_id Product ID
uuid Unique symbolic identifier of the goods
video_id Video link ID
url Video link
Field name Description
row_exist Flag - an entry found in the site database
article_id Article ID
parent_id Parent category (section) ID
uuid Unique symbolic identifier of the article
uuid_parent Unique symbolic identifier of the parent category (section)
is_category Flag is a category (section)
status Activity
sort_order Sort order
product_id Product ID (product reference)
language_uuid Unique symbolic language identifier
description Note
seo_url CNC
flag_comment Flag - comment
name The title of the article
description_short The description is short
description_full The text of the article
meta_title SEO meta title
meta_keyword SEO meta keywords
meta_description SEO meta description
seo_h1 Text for the h1 tag
tag List of tags


How to get a numeric category ID for products that is stored in the Jumper database
SELECT t2. category_id_jumper, t2. parent_id_jumper, t1. product_id_jumper
FROM etrade_product_temp t1
INNER JOIN etrade_category_temp t2 ON t1. category_uuid = t2. uuid


Read also: Import products on Opencart
Automation of online stores


Copywriter ElbuzGroup


Contents:



Save a link to this article


Next