Downloading a product catalog via E-Trade HTTP Tunnel for self-written CMS (SQL queries)

The E-Trade HTTP Tunnel integration module is an application designed for the simple interaction of E-Trade Jumper with the database of an online store (website).

The module is developed in the PHP programming language, MySQL DBMS is supported as the site database. To configure and connect the module, you will need to perform the following steps:

Transfer files with the module to the hosting account of your site, for example, using FTP.
Select your site's CMS from the list available in E-Trade Jumper.
Enter a link to your site in E-Trade Jumper and click the "Connect" button.

We can set up integration with your site, for this, send us an email. mail access to your site via FTP, this is enough for configuration.

Installing the E-Trade Tunnel integration module on your hosting

Download the module from this link extract the contents of the etrade_http_tunnel2.zip archive to a temporary folder on your local PC. The archive contains directories and files
Rewrite the contents of the unpacked archive to the root folder of the installed site (online store), along with the etrade_http_tunnel2 folder. This can be done, for example, using the FileZilla FTP client .

Setting up the E-Trade Tunnel integration module to work with the website database of an individual CMS (self-written)

To download a catalog of products from the site, open the "Download products to the catalog" window by clicking on the shortcut on the desktop
elbuz etrade jumper import products data tunnel
Or select a menu item

Next, select the E-Trade Tunnel data format, open the "CMS Selection" tab, select the "Manual mode (SQL queries)" item in the drop-down list, specify the address of your site
elbuz etrade jumper import data tunnel
Open the "Access Settings" tab, specify a link to the E-Trade Tunnel module, login and password, and also specify the parameters for accessing the site database

Open the "Select CMS" tab, check the connection

After the connection is successfully established, you need to register SQL queries to get data from the site, open the "Download" tab, click the "Configure SQL queries" button
elbuz etrade jumper import data tunnel

Compose SQL queries based on the structure of your database

To obtain data from the site, it is necessary to create separate SQL queries for categories, products, manufacturers, attribute blocks, attributes, and product attribute values.
Each SQL query specifies the data type, for example, to get the SQL categories, the query starts like this
SELECT 'category' as row_type
In each data type, the order of the fields must be strictly observed. If there is no field in your database, just specify it as an empty stub - "" as language_code
SQL queries end with three semicolons - ;;;

An example of SQL queries to get a product catalog

SELECT 'category' as row_type,
 category_uuid,
 category_uuid_parent,
 language_code,
 category_id,
 parent_id,
 sort_order,
 status,
 image,
 seo_url,
 product_count,
 name,
 description_short,
 description_full,
 meta_title,
 meta_description,
 meta_keyword,
 h1,
 image_url
 FROM category c
 GROUP BY category_id, language_code;;;

 SELECT 'product' as row_type,
 product_uuid,
 category_uuid,
 language_code,
 manufacturer_uuid,
 product_id,
 category_id,
 manufacturer_id,
 status,
 price,
 price_rrp,
 price_old,
 quantity,
 stock_status,
 manufacturer_name,
 name,
 description_short,
 description_full,
 meta_title,
 meta_description,
 meta_keyword,
 h1,
 tag,
 model,
 seo_url,
 sort_order,
 sku,
 upc,
 ean,
 jan,
 isbn,
 mpn,
 location,
 points,
 date_available,
 weight,
 length,
 width,
 height,
 warranty,
 subtract,
 minimum,
 viewed,
 date_added,
 date_modified,
 flag_xml_export,
 flag_popular,
 flag_best_deal,
 flag_fix_price,
 sku_supply,
 delivery_cost,
 ext_url,
 flag_new_product,
<code>

type_id,
product_uuid_parent,
FROM product
GROUP BY product_id, language_code;;;

SELECT 'product_category' as row_type,
product_uuid,
category_uuid,
product_id,
category_id,
main_category,
sort_order
FROM product_to_category
GROUP BY product_id, category_id;;;

SELECT 'product_image' as row_type,
product_uuid,
product_id,
image_file_name,
image_url,
sort_order,
image_uuid
FROM image

SELECT 'manufacturer' as row_type,
manufacturer_uuid,
manufacturer_id,
language_code,
sort_order,
status,
name,
image,
meta_title,
meta_description,
meta_keywords,
url,
seo_url,
warranty,
seo_h1,
description,
manufacturer_address,
manufacturer_service,
image_url
FROM manufacturer;;;

SELECT 'attribute_block' as row_type,
attribute_block_uuid,
attribute_block_id,
language_code,
name,
sort_order,
image,
description
FROM attribute_group
GROUP BY attribute_block_id, language_code;;;

SELECT 'attribute' as row_type,
attribute_uuid,
attribute_block_uuid,
language_code,
name,
attribute_id,
attribute_block_id,
sort_order,
image,
attribute_type,
required,
code,
flag_option,
flag_multiline,
show_on_page,
flag_expand,
status,
description
FROM attribute a
GROUP BY attribute_id, language_code;;;

SELECT 'product_attribute_lang' as row_type,
product_uuid,
attribute_uuid,
language_code,
product_id,
attribute_id,
product_attribute_value,
attribute_image,
0 as flag_multiline
FROM product_attribute
GROUP BY product_id, attribute_id, language_code;;;

Description of types

Data type Category (category)

Field name	Data type	Description
row_type	Line	Fixed "category"
category_uuid	Line	The unique symbolic identifier of the category. If there is no such identifier in the site database table, then specify a unique numeric identifier, for example from the category_id field. The field is required.
category_uuid_parent	Line	The unique symbolic identifier of the parent category. If there is no such identifier in the site database table, then specify a unique numeric identifier, for example, from the parent_id field. The field is required.
language_code	Line	Language code, for example ru. If the site stores all categories for one language, then leave this field blank.
category_id	Number	Category unique numeric ID
parent_id	Number	Unique numeric ID of the parent category
sort_order	Number	Sorting order
status	Number 0 or 1	Activity status
image	Line	Image file name
seo_url	Line	SEO category (no website address)
product_count	Number	Number of products in a category
name	Line	name of category
description_short	Line	Short description
description_full	Line	Full description
meta_title	Line	SEO meta header
meta_description	Line	SEO meta description
meta_keyword	Line	SEO meta keywords
h1	Line	The h1 tag (if, in addition to the category name, there is also a separate name for the h1 tag)
image_url	Line	Link to the category image (where you can download the image)

Data type Product

Field name	Data type	Description
row_type	Line	Fixed "product"
product_uuid	Line	The unique symbolic identifier of the item. If there is no such identifier in the site database table, then specify a unique numeric identifier, for example from the product_id field. The field is required.
category_uuid	Line	The unique symbolic identifier of the category. The main category for the product. If there is no such identifier in the site database table, then specify a unique numeric identifier, for example from the category_id field. The field is required.
language_code	Line	Language code, for example ru. If the site stores all products for one language, then leave this field blank.
manufacturer_uuid	Line	Manufacturer's unique symbolic identifier. If there is no such identifier in the site database table, then specify a unique numeric identifier, for example, from the manufacturer_id field.
product_id	Number	Unique numeric product identifier
category_id	Number	Category unique numeric ID
manufacturer_id	Number	Manufacturer's unique numeric identifier
status	Number 0 or 1
price	Number	The price of the product. Decimal separator point.
price_rrp	Number	RRP - the recommended retail price of the goods. Decimal separator point.
price_old	Number	Old item price
quantity	Number	Quantity of goods
stock_status	Line	The symbolic status of the product, for example, Yes, No, On order.
manufacturer_name	Line	Manufacturer's name
name	Line	Name of product
description_short	Line	Brief product description
description_full	Line	Full product description
meta_title	Line	SEO meta header
meta_description	Line	SEO meta description
meta_keyword	Line	SEO meta keywords
h1	Line	The h1 tag (if, in addition to the title, there is also a separate text for the h1 tag)
tag	Line	Tag List
model	Line	Item Model
seo_url	Line	SEO product (no website address)
sort_order	Number	Sorting order
sku	Line	Internal item number
upc	Line	UPC barcode
ean	Line	Barcode EAN
Barcode JAN	Line
isbn	Line	Barcode ISBN
mpn	Line	Manufacturer's SKU
location	Line	Warehouse location
points	Number	Number of points when ordering goods
date_available	Line	Product Availability Date
weight	Number	Item weight
length	Number	Item length
width	Number	Width
height	Number	Height
warranty	Line	Warranty
subtract	Number 0 or 1	Subtract quantity from stock when ordering
minimum	Number 0 or 1	Minimum available order quantity
viewed	Number	Number of product views
date_added	Line	Date the product was added
date_modified	Line	Item modification date
flag_xml_export	Number 0 or 1	XML upload flag (marketplace)
flag_popular	Number 0 or 1	Flag - Popular Item
flag_best_deal	Number 0 or 1	Flag - Best Buy
flag_fix_price	Number 0 or 1	Flag - Fixed price
sku_supply	Line	Article of the counterparty (supplier)
delivery_cost	Number	Cost of delivery
ext_url	Line	External link to the product on the site
flag_new_product	Number 0 or 1	Flag - New Item (new)
type_id	Number	Product type. 1 - regular product, 2 - option product.
product_uuid_parent	Line	The unique symbolic identifier of the parent product. If there is no such identifier in the site database table, then specify a unique numeric identifier from the parent product, for example, from the product_id field.

Data type Product+Category (product_category)

List of links to product categories, for example 1 product can have several links to other categories

Field name	Data type	Description
row_type	Line	Fixed "product_category"
product_uuid	Line	The unique symbolic identifier of the item. If there is no such identifier in the site database table, then specify a unique numeric identifier, for example from the product_id field. The field is required.
category_uuid	Line	The unique symbolic identifier of the category. The main category for the product. If there is no such identifier in the site database table, then specify a unique numeric identifier, for example from the category_id field. The field is required.
product_id	Number	Unique numeric product identifier
category_id	Number	Category unique numeric ID
main_category	Number 0 or 1	The flag is the main product category
sort_order	Number	Sorting order

Data type Product photo (product_image)

Field name	Data type	Description
row_type	Line	Fixed "product_image"
product_uuid	Line	The unique symbolic identifier of the item. If there is no such identifier in the site database table, then specify a unique numeric identifier, for example from the product_id field. The field is required.
product_id	Number	Unique numeric product identifier
image_file_name	Line	Product photo file name
image_url	Line	Link to the photo of the product (where you can download the photo)
sort_order	Number	Sorting order
image_uuid	Line	The unique symbolic ID of the link to the photo.

Data type Manufacturers of products (manufacturer)

Field name	Data type	Description
row_type	Line	Fixed "manufacturer"
manufacturer_uuid	Line	Unique symbolic identifier of the product manufacturer. If there is no such identifier in the site database table, then specify a unique numeric identifier, for example, from the manufacturer_id field. The field is required.
manufacturer_id	Number	Manufacturer's unique numeric identifier
language_code	Line	Language code, for example ru. If the site stores all manufacturers for the same language, then leave this field blank.
sort_order	Number	Sorting order
status	Number 0 or 1	Activity status
name	Line	Manufacturer's name
image	Line	Product photo file name
meta_title	Line	SEO meta header
meta_description	Line	SEO meta description
meta_keywords	Line	SEO meta keywords
url	Line	Link to the manufacturer's website
seo_url	Line	SEO manufacturer (no website address)
warranty	Line	Manufacturer's Warranty
seo_h1	Line	The h1 tag (if, in addition to the title, there is also a separate text for the h1 tag)
description	Line	Manufacturer's Description
manufacturer_address	Line	Manufacturer's address
manufacturer_service	Line	List of manufacturer's service centers
image_url	Line	Link to the photo of the product (where you can download the photo)

Data type Attribute blocks (attribute_block)

Field name	Data type	Description
row_type	Line	Fixed "attribute_block"
attribute_block_uuid	Line	The unique symbolic identifier of the attribute block. If there is no such identifier in the site database table, then specify a unique numeric identifier, for example from the attribute_block_id field. The field is required.
attribute_block_id	Number	Unique numeric attribute block identifier
language_code	Line	Language code, for example ru. If the site stores all attribute blocks for the same language, then leave this field blank.
name	Line	Attribute block name
sort_order	Number	Sorting order
image	Line	Attribute Block Photo File Name
description	Line	Description of the attribute block

Data type Product attributes (attribute)

Field name	Data type	Description
row_type	Line	Fixed "attribute"
attribute_uuid	Line	The unique character identifier of the attribute. If there is no such identifier in the site database table, then specify a unique numeric identifier, for example from the attribute_id field. The field is required.
attribute_group_uuid	Line	The unique character identifier of the attribute block. If there is no such identifier in the site database table, then specify a unique numeric identifier, for example, from the attribute_block_id field
language_code	Line	Language code, for example ru. If the site stores all attributes for one language, then leave this field blank.
name	Line	Attribute name
attribute_id	Number	Attribute unique numeric ID
attribute_block_id	Number	Unique numeric attribute block identifier
sort_order	Number	Sorting order
image	Line	Attribute Block Photo File Name
attribute_type	Line	Attribute type. Available values: select, checkbox, radio, slider, image, datetime, text, text_area, decimal, float
required	Number 0 or 1	Flag - required
code	Line	Symbolic Attribute Code
flag_option	Number 0 or 1	Flag - Item Option
flag_multiline	Number 0 or 1	Flag - Multi-string values
show_on_page	Number 0 or 1	Flag - show in the product card
flag_expand	Number 0 or 1	Flag - When filtering products, the list of values is expanded
status	Number 0 or 1	Attribute Activity
description	Line	Attribute description

Data type Product attribute values (product_attribute_lang)

Field name	Data type	Description
row_type	Line	Fixed "product_attribute_lang"
product_uuid	Line	The unique symbolic identifier of the item. If there is no such identifier in the site database table, then specify a unique numeric identifier, for example from the attribute_id field. The field is required.
attribute_uuid	Line	The unique character identifier of the attribute. If there is no such identifier in the site database table, then specify a unique numeric identifier, for example from the attribute_id field. The field is required.
language_code	Line	Language code, for example ru. If the site stores all attributes for one language, then leave this field blank.
product_id	Number	Unique numeric product identifier
attribute_id	Number	Attribute unique numeric ID
product_attribute_value	Line	Attribute value
sort_order	Number	Sorting order
attribute_image	Line	Attribute value photo file name
flag_multiline	Number 0 or 1	Flag - Multi-string values

hotline parser