SELECT DISTINCT Sku
FROM `core.axion`.data_shopify_variants
WHERE
NOT Sku LIKE '%\_%'
AND NOT Sku = ''
AND DATEDIFF(NOW(), LastUpdate) >= 1
AND NOT Sku IN ('CURRENT', 'COMPLETE')
ORDER BY LastUpdate
SELECT
pdt.PR_PRODUCTCODE AS ProductCode,
phd.PH_NAME AS PromotionName,
pdt.PR_PRICE AS DiscountPrice,
pdt.PR_NEWPRICE AS OriginalPrice
FROM trader.promotion_header phd
LEFT JOIN trader.promotion_detail pdt
ON phd.PH_ID = pdt.PR_ID
WHERE
CURDATE() BETWEEN
STR_TO_DATE(phd.PH_STARTDATE, '%d.%m.%Y')
- INTERVAL 7 DAY
AND STR_TO_DATE(phd.PH_ENDDATE, '%d.%m.%Y')
- INTERVAL 1 DAY
SELECT
CASE
WHEN Archived = 'Archived' THEN 'Archived'
WHEN RangeItem = 'Y' AND stock_level > 0 THEN 'In Stock'
WHEN RangeItem = 'Y' AND stock_level <= 0 THEN 'In Stock'
WHEN ListItem = 'Y' AND stock_level > 0 THEN 'In Stock'
WHEN ListItem = 'Y' AND stock_level <= 0 THEN 'Supplier Order'
WHEN Discontinued = 'Y' AND stock_level > 0 THEN 'In Stock'
WHEN Discontinued = 'Y' AND stock_level <= 0 THEN 'Archived'
ELSE 'Error'
END AS MetaCanOrderMessage
FROM (
SELECT
p.PM_PRODUCTCODE,
IF(p.PM_IS_WEB = 0
OR p.PM_Status_id IN (3, 7, 8, 9, 11),
'Archived', '') AS Archived,
IF(p.PM_STATUS_ID = 4, 'Y', 'N') AS RangeItem,
IF(p.PM_STATUS_ID = 1, 'Y', 'N') AS ListItem,
IF(p.PM_Status_id IN (5, 6, 12), 'Y', 'N') AS Discontinued,
COALESCE(SUM(CASE
WHEN pl.PL_SUBTYPE1 = '' AND pl.PL_SUBTYPE2 = ''
AND pl_store_id = '299' AND pl_location_id = 1
THEN pl_qtyonhand
WHEN pl.PL_SUBTYPE1 = '' AND pl.PL_SUBTYPE2 = ''
AND pl_store_id != '299' AND pl_location_id = 0
THEN pl_qtyonhand
ELSE 0
END), 0) AS stock_level
FROM trader.products AS p
INNER JOIN trader.product_status AS ps
ON p.PM_STATUS_ID = ps.PS_ID
LEFT JOIN trader.product_ledger AS pl
ON pl.PL_PRODUCTCODE = p.PM_PRODUCTCODE
WHERE p.PM_PRODUCTCODE = '{product_code}'
GROUP BY p.PM_PRODUCTCODE
) AS Ex1
SELECT ProductCode, IsPrimary
FROM trader.meta_product_alternates
WHERE Guid = (
SELECT Guid
FROM trader.meta_product_alternates
WHERE ProductCode = '{product_code}'
)
SELECT
(Weight_1 + Weight_2 + Weight_3
+ Weight_4 + Weight_5) AS Weight,
(Length_1 + Length_2 + Length_3
+ Length_4 + Length_5) AS Length,
(Height_1 + Height_2 + Height_3
+ Height_4 + Height_5) AS Height,
(Width_1 + Width_2 + Width_3
+ Width_4 + Width_5) AS Width
FROM trader.meta_product
WHERE CODE = '{product_code}'
SELECT
PM_PRODUCTCODE,
PM_SIMILIARITEMCODE AS ExtendedWarranty,
PM_WEIGHTCODE AS WarrantyCode,
PM_SHELFLIFE AS GuaranteePeriod,
PM_COSTBEFOREREBATE AS WarrantyPeriod
FROM trader.products
WHERE PM_Productcode = '{product_code}'
SELECT ProductCode, ActivePromos, PromoIds, DiscountPrices
FROM (
SELECT
pdt.PR_PRODUCTCODE AS ProductCode,
COUNT(*) AS ActivePromos,
GROUP_CONCAT(phd.PH_ID) AS PromoIds,
GROUP_CONCAT(pdt.PR_PRICE) AS DiscountPrices
FROM trader.promotion_header phd
LEFT JOIN trader.promotion_detail pdt
ON phd.PH_ID = pdt.PR_ID
WHERE
CURDATE() BETWEEN
STR_TO_DATE(phd.PH_STARTDATE, '%d.%m.%Y')
- INTERVAL 7 DAY
AND STR_TO_DATE(phd.PH_ENDDATE, '%d.%m.%Y')
- INTERVAL 1 DAY
GROUP BY pdt.PR_PRODUCTCODE
) AS promo_items
WHERE ActivePromos > 1
SELECT ReportCode
FROM def_report
WHERE ReportCode IN ({in_clause})
SELECT textname
FROM def_text
WHERE textname IN ({in_clause})
SELECT DISTINCT
phd.PH_NAME AS name,
phd.PH_STARTDATE AS start_date,
phd.PH_ENDDATE AS end_date,
COUNT(DISTINCT pdt.PR_PRODUCTCODE) AS product_count
FROM trader.promotion_header AS phd
JOIN trader.promotion_detail AS pdt
ON phd.PH_ID = pdt.PR_ID
WHERE STR_TO_DATE(phd.PH_ENDDATE, '%d.%m.%Y') >= CURDATE()
AND STR_TO_DATE(phd.PH_STARTDATE, '%d.%m.%Y') <= CURDATE()
GROUP BY phd.PH_NAME, phd.PH_STARTDATE, phd.PH_ENDDATE
ORDER BY product_count DESC
SELECT
pdt.PR_PRODUCTCODE AS sku,
COALESCE(inv.meta_description, pdt.META_DESCRIPTION) AS description,
pdt.PR_PRICE AS original_price,
pdt.PR_NEWPRICE AS promo_price,
pdt.PR_MARKDOWN AS markdown,
phd.PH_NAME AS promo_name
FROM trader.promotion_detail AS pdt
JOIN trader.promotion_header AS phd
ON phd.PH_ID = pdt.PR_ID
LEFT JOIN trader.inventory AS inv
ON inv.meta_product_code = pdt.PR_PRODUCTCODE
WHERE STR_TO_DATE(phd.PH_ENDDATE, '%d.%m.%Y') >= CURDATE()
AND STR_TO_DATE(phd.PH_STARTDATE, '%d.%m.%Y') <= CURDATE()
ORDER BY phd.PH_NAME, pdt.PR_PRODUCTCODE
SELECT Value FROM def_parameter
WHERE Parameter = 'cache_max_age_hours'
AND Block = 'SHOPIFY'
SELECT Value FROM def_parameter
WHERE Parameter = 'dry_run_mode'
AND Block = 'SHOPIFY'
SELECT DISTINCT UPPER(CL_DESCRIPTION)
FROM trader.colour_types
SELECT DISTINCT UPPER(PXC_COLOUR_DESCRIPTION)
FROM trader.product_colour
SELECT DISTINCT UPPER(
SUBSTRING_INDEX(
p.PM_DESCRIPTION, ' - ', -1
)
)
FROM trader.meta_product_alternates a
JOIN trader.products p
ON a.ProductCode = p.PM_PRODUCTCODE
WHERE a.Option1 IN (
'furniture', 'Furniture',
'lounge', 'Lounge'
)
AND p.PM_DESCRIPTION LIKE '%% - %%'
SELECT image_hash
FROM `core.axion`.data_shopify_images
WHERE Sku = '{sku}'
AND image_url LIKE '%{filename}%'
LIMIT 1
INSERT INTO `core.axion`.data_shopify_images
(Sku, image_url, image_hash, last_update)
VALUES (
'{sku}',
'{image_url}',
'{image_hash}',
NOW()
)
ON DUPLICATE KEY UPDATE
image_hash = '{image_hash}',
last_update = NOW()
SELECT
phd.PH_NAME AS `promotion_name`
, pdt.PR_PRICE AS `promotion_price`
, pdt.PR_PRICE - pdt.PR_NEWPRICE AS `promotion_discount_amount`
, pdt.PR_NEWPRICE AS `original_price`
, mpr.Web_Instalment AS `credit_installment`
, mpr.Web_Deposit AS `credit_deposit`
, (mpr.Web_Totalprice - mpr.Web_Deposit) / NULLIF(mpr.Web_Instalment, 0) AS `credit_months`
, mpr.Web_Totalprice AS `credit_price`
, phd.PH_STARTDATE AS `start_date`
, phd.PH_ENDDATE AS `end_date`
FROM `trader`.promotion_header phd
JOIN `trader`.promotion_detail pdt
ON phd.PH_ID = pdt.PR_ID
LEFT JOIN `trader`.meta_product mpr
ON pdt.PR_PRODUCTCODE = mpr.Code
WHERE pdt.PR_PRODUCTCODE = '{sku}'
AND STR_TO_DATE(phd.PH_ENDDATE, '%d.%m.%Y') >= CURDATE()
AND STR_TO_DATE(phd.PH_STARTDATE, '%d.%m.%Y') <= CURDATE()
ORDER BY pdt.PR_PRICE ASC, phd.PH_NAME
SELECT
pdt.PR_PRODUCTCODE AS `product_code`
, phd.PH_NAME AS `promotion_name`
, pdt.PR_PRICE AS `promotion_price`
, pdt.PR_PRICE - pdt.PR_NEWPRICE AS `promotion_discount_amount`
, pdt.PR_NEWPRICE AS `original_price`
, mpr.Web_Instalment AS `credit_installment`
, mpr.Web_Deposit AS `credit_deposit`
, (mpr.Web_Totalprice - mpr.Web_Deposit) / NULLIF(mpr.Web_Instalment, 0) AS `credit_months`
, mpr.Web_Totalprice AS `credit_price`
, phd.PH_STARTDATE AS `start_date`
, phd.PH_ENDDATE AS `end_date`
FROM `trader`.promotion_header phd
JOIN `trader`.promotion_detail pdt
ON phd.PH_ID = pdt.PR_ID
LEFT JOIN `trader`.meta_product mpr
ON pdt.PR_PRODUCTCODE = mpr.Code
WHERE pdt.PR_PRODUCTCODE IN ('{sku_in_clause}')
AND STR_TO_DATE(phd.PH_ENDDATE, '%d.%m.%Y') >= CURDATE()
AND STR_TO_DATE(phd.PH_STARTDATE, '%d.%m.%Y') <= CURDATE()
ORDER BY pdt.PR_PRODUCTCODE, pdt.PR_PRICE ASC, phd.PH_NAME
SELECT
PM_WEIGHTCODE,
PM_SHELFLIFE,
PM_COSTBEFOREREBATE,
PM_SIMILIARITEMCODE
FROM trader.products
WHERE PM_Productcode = '{sku}'
SELECT PM_STDSELLINGPRICE
FROM trader.products
WHERE PM_PRODUCTCODE = '{warranty_code}'
SELECT *
FROM `{def_table}`
WHERE ServiceCode LIKE '{service}'
SELECT DISTINCT
CONCAT(DAYNAME(StartDate), ' ', startdate),
CONCAT(DAYNAME(EndDate), ' ', Enddate),
Name
FROM (
SELECT
'$storeid$', H.PH_ID, H.PH_STARTDATE,
DATE(STR_TO_DATE(
REPLACE(H.PH_STARTDATE, '.', '-'),
'%d-%m-%Y'
)) AS StartDate,
DATE(STR_TO_DATE(
REPLACE(H.PH_ENDDATE, '.', '-'),
'%d-%m-%Y'
)) AS EndDate,
D.PR_PRODUCTCODE,
H.PH_NAME AS Name
FROM trader.promotion_header AS H
LEFT JOIN trader.promotion_detail AS D
ON H.PH_ID = D.PR_ID
WHERE H.PH_ALL_STORES = 1
) AS A
WHERE NAME LIKE '{promo_name}'
LIMIT 1
SELECT LongDescription
FROM trader.meta_product
WHERE Code = '{product_code}'
SELECT COALESCE(Feature{feature_num}, '')
FROM trader.meta_product
WHERE Code = '{product_code}'
SELECT PXC_COLOUR_DESCRIPTION
FROM TRADER.product_colour AS C
WHERE C.PXC_PRODUCTCODE = '{product_code}'
SELECT ColourName
FROM `core.axion`.data_shopify_colourmap
WHERE ColourCode = '{colour_code}'
LIMIT 1
SELECT ColourCode, ColourName
FROM `core.axion`.data_shopify_colourmap
INSERT INTO `core.axion`.data_shopify_variants
(Sku, product_id, graphql_id, title, LastUpdate)
VALUES
('{sku}', '{product_id}', '{graphql_id}', '{title}', NOW())
ON DUPLICATE KEY UPDATE
product_id = '{product_id}',
graphql_id = '{graphql_id}',
title = '{title}',
LastUpdate = NOW()
DELETE FROM `core.axion`.data_shopify_variants
WHERE Sku = '{sku}' OR Sku LIKE '{sku}\\_%'
CREATE TABLE IF NOT EXISTS `core.axion`.data_shopify_dryrun (
id INT AUTO_INCREMENT PRIMARY KEY,
run_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
sku VARCHAR(100) NOT NULL,
variant_sku VARCHAR(100),
product_title VARCHAR(500),
option_level INT DEFAULT 0,
price DECIMAL(10,2),
compare_at_price DECIMAL(10,2),
base_price DECIMAL(10,2),
promotion_name VARCHAR(255),
promotion_price DECIMAL(10,2),
promotion_discount DECIMAL(10,2),
original_price DECIMAL(10,2),
credit_installment DECIMAL(10,2),
credit_deposit DECIMAL(10,2),
credit_months INT,
credit_price DECIMAL(10,2),
warranty_code INT,
guarantee_period INT,
warranty_period INT,
extended_warranty VARCHAR(100),
extended_warranty_cost DECIMAL(10,2),
option1 VARCHAR(100),
option2 VARCHAR(100),
option3 VARCHAR(100),
weight_grams DECIMAL(10,2),
metafields_json TEXT,
product_input_json TEXT,
verified BOOLEAN DEFAULT FALSE,
verification_notes TEXT,
INDEX idx_sku (sku),
INDEX idx_run_timestamp (run_timestamp),
INDEX idx_verified (verified)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
REPLACE INTO `core.axion`.data_shopify_variants
(Sku, id, product_id, graphql_id, stockitem_id,
title, price, compare_at_price, inventory_policy,
option1, barcode, weight, weight_unit, taxable,
inventory_item_id, inventory_quantity,
Pims_description, LastUpdate)
VALUES (
'{sku}', '{variant_id}', '{product_id}', '{graphql_id}',
'{stockitem_id}', '{title}', '{price}', '{compare_at_price}',
'{inventory_policy}', '{option1}', '{barcode}',
'{weight}', '{weight_unit}', '{taxable}',
'{inventory_item_id}', '{inventory_quantity}',
'{pims_description}', NOW())
DELETE FROM `core.axion`.data_shopify_inventory
WHERE Sku = '{sku}'
INSERT INTO `core.axion`.data_shopify_inventory
(Sku, product_id, graphql_id, Title, last_sync)
VALUES
('{sku}', '{product_id}', '{graphql_id}', '{title}', NOW())
ON DUPLICATE KEY UPDATE
product_id = '{product_id}',
graphql_id = '{graphql_id}',
Title = '{title}',
last_sync = NOW()
SELECT a.Productcode, a.Description FROM trader.meta_product_alternates a
WHERE a.Guid = '{group_guid}'
ORDER BY a.IsPrimary DESC, a.Productcode
SELECT Productcode
FROM trader.meta_product_alternates
WHERE Guid = '{group_guid}'
ORDER BY IsPrimary DESC, Productcode
SELECT PM_SIMILIARITEMCODE AS ExtendedWarranty
FROM trader.products
WHERE PM_Productcode = '{product_code}'
SELECT P.PM_STDSELLINGPRICE
FROM trader.products AS P
WHERE P.PM_PRODUCTCODE = '{warranty_code}'
INSERT INTO {base_variants} (Sku)
VALUES ('{sku}')
UPDATE {base_variants}
SET
Reject = '{reason}',
LastUpdate = NOW()
WHERE Sku = '{sku}'
INSERT INTO {base_variants} (Sku, Id, Audited, Product_id)
VALUES ('{sku}', '', '', '')
INSERT INTO {base_variants_all} (Sku, Id, Audited, Product_id)
VALUES ('{sku}', '', '', '')
DELETE FROM `core.axion`.data_shopify_meta
WHERE Variant_id = '{variant_id}'
DELETE FROM `core.axion`.data_shopify_variants
WHERE Id = '{variant_id}'
SELECT
phd.PH_ID,
phd.PH_NAME,
STR_TO_DATE(
phd.PH_STARTDATE, '%d.%m.%Y'
) AS start_date,
STR_TO_DATE(
phd.PH_ENDDATE, '%d.%m.%Y'
) AS end_date,
phd.PH_ALL_STORES,
COUNT(pdt.PR_PRODUCTCODE) AS product_count,
CASE
WHEN CURDATE() > STR_TO_DATE(
phd.PH_ENDDATE, '%d.%m.%Y'
) THEN 'past'
WHEN CURDATE() < STR_TO_DATE(
phd.PH_STARTDATE, '%d.%m.%Y'
) THEN 'future'
ELSE 'current'
END AS period
FROM trader.promotion_header phd
LEFT JOIN trader.promotion_detail pdt
ON phd.PH_ID = pdt.PR_ID
WHERE
STR_TO_DATE(
phd.PH_ENDDATE, '%d.%m.%Y'
) >= CURDATE()
- INTERVAL {window_past_days} DAY
AND STR_TO_DATE(
phd.PH_STARTDATE, '%d.%m.%Y'
) <= CURDATE()
+ INTERVAL {window_future_days} DAY
GROUP BY
phd.PH_ID, phd.PH_NAME,
phd.PH_STARTDATE, phd.PH_ENDDATE,
phd.PH_ALL_STORES
ORDER BY start_date
SELECT
pdt.PR_ID AS promo_id,
pdt.PR_PRODUCTCODE AS sku,
pdt.PR_PRICE AS discount_price,
pdt.PR_NEWPRICE AS original_price
FROM trader.promotion_detail pdt
WHERE pdt.PR_ID IN ({id_list})
ORDER BY pdt.PR_ID, pdt.PR_PRODUCTCODE
SELECT COUNT(*) FROM `core.axion`.data_shopify_variants
WHERE
NOT Sku LIKE '%\_%'
AND NOT Sku = ''
AND DATEDIFF(NOW(), LastUpdate) >= 1
AND NOT Sku IN ('CURRENT', 'COMPLETE')
SELECT COUNT(DISTINCT Sku)
FROM `core.axion`.data_shopify_variants
WHERE NOT Sku = ''
AND NOT Sku IN ('CURRENT', 'COMPLETE')
SELECT PM_PRODUCTCODE
FROM trader.products
WHERE PM_IS_WEB = 1
AND PM_PRODUCTCODE IN ({in_clause})
SELECT
Sku, Price, CompareAtPrice, ProductId
FROM `core.axion`.data_shopify_variants
WHERE Sku IN ({in_clause})
SELECT ProductId, GraphqlId
FROM `core.axion`.data_shopify_variants
WHERE Sku = '{sku}'
AND NOT ProductId = ''
LIMIT 1
SELECT PR_PRODUCTCODE
FROM trader.promotion_header AS Ph
LEFT JOIN trader.promotion_detail AS PhD
ON Ph.PH_ID = PhD.PR_ID
WHERE
DATEDIFF(
NOW(),
STR_TO_DATE(
PH_ENDDATE, '%d.%m.%Y'
)
) <= 7
AND COALESCE(
DATEDIFF(
NOW(),
STR_TO_DATE(
PH_STARTDATE, '%d.%m.%Y'
)
), -6
) >= -1
SELECT DISTINCT
phd.PH_NAME AS name,
phd.PH_STARTDATE AS start_date,
phd.PH_ENDDATE AS end_date,
COUNT(DISTINCT pdt.PR_PRODUCTCODE) AS product_count
FROM trader.promotion_header AS phd
JOIN trader.promotion_detail AS pdt
ON phd.PH_ID = pdt.PR_ID
WHERE STR_TO_DATE(phd.PH_ENDDATE, '%d.%m.%Y') < CURDATE()
GROUP BY phd.PH_NAME, phd.PH_STARTDATE, phd.PH_ENDDATE
ORDER BY STR_TO_DATE(phd.PH_ENDDATE, '%d.%m.%Y') DESC
LIMIT 2
SELECT DISTINCT
pdt.PR_PRODUCTCODE AS sku,
phd.PH_NAME AS promo_name,
pdt.META_DESCRIPTION AS description
FROM trader.promotion_header AS phd
JOIN trader.promotion_detail AS pdt
ON phd.PH_ID = pdt.PR_ID
WHERE STR_TO_DATE(phd.PH_ENDDATE, '%d.%m.%Y') < CURDATE()
AND STR_TO_DATE(phd.PH_ENDDATE, '%d.%m.%Y') >= (
SELECT MAX(STR_TO_DATE(ph2.PH_ENDDATE, '%d.%m.%Y'))
FROM trader.promotion_header ph2
JOIN trader.promotion_detail pd2 ON ph2.PH_ID = pd2.PR_ID
WHERE STR_TO_DATE(ph2.PH_ENDDATE, '%d.%m.%Y') < CURDATE()
)
ORDER BY phd.PH_NAME, pdt.PR_PRODUCTCODE
SELECT DISTINCT Sku
FROM `core.axion`.data_shopify_variants
WHERE
NOT Sku LIKE '%\_%'
AND NOT Sku = ''
AND DATEDIFF(NOW(), LastUpdate) >= 1
AND NOT Sku IN ('CURRENT', 'COMPLETE')
ORDER BY LastUpdate
SELECT Value
FROM def_Parameter
WHERE Parameter = 'webactive'
AND Block = 'FULLHOUSE'
SELECT p.PM_DESCRIPTION
FROM trader.meta_product_alternates a
JOIN trader.products p
ON a.ProductCode = p.PM_PRODUCTCODE
WHERE a.Guid = (
SELECT Guid
FROM trader.meta_product_alternates
WHERE ProductCode = '{sku}'
)
AND a.Option1
IN ('furniture', 'Furniture',
'lounge', 'Lounge')
INSERT INTO `core.axion`.data_shopify_inventory
(
product_id, graphql_id, title, description_html,
status, vendor, product_type, created_at,
updated_at, tags, variant_count, variants,
image_count, price_min, price_max,
promotion_name, promotion_price, last_sync
)
VALUES (
{product_id}, '{graphql_id}', '{title}',
'{desc_html}', '{status}', '{vendor}',
'{product_type}', '{created_at}', '{updated_at}',
'{tags}', {variant_count}, '{variants_json}',
{image_count}, {price_min}, {price_max},
'{promotion_name}', {promotion_price}, NOW()
)
ON DUPLICATE KEY UPDATE
graphql_id = '{graphql_id}',
title = '{title}',
description_html = '{desc_html}',
status = '{status}',
vendor = '{vendor}',
product_type = '{product_type}',
created_at = '{created_at}',
updated_at = '{updated_at}',
tags = '{tags}',
variant_count = {variant_count},
variants = '{variants_json}',
image_count = {image_count},
price_min = {price_min},
price_max = {price_max},
promotion_name = '{promotion_name}',
promotion_price = {promotion_price},
last_sync = NOW()
SELECT
product_id, graphql_id, title, status, vendor,
product_type, variant_count, last_sync,
TIMESTAMPDIFF(HOUR, last_sync, NOW()) as hours_since_sync,
price_min, price_max, promotion_name, promotion_price
FROM `core.axion`.data_shopify_inventory
WHERE product_id IN (
SELECT DISTINCT product_id
FROM `core.axion`.data_shopify_variants
WHERE Sku = '{sku}'
)
LIMIT 1
UPDATE `core.axion`.data_shopify_inventory
SET last_sync = NOW()
WHERE product_id IN (
SELECT DISTINCT product_id
FROM `core.axion`.data_shopify_variants
WHERE Sku = '{sku}'
)
SELECT product_id
FROM `core.axion`.data_shopify_variants
WHERE Sku = '{sku}'
LIMIT 1
SELECT Sku
FROM `core.axion`.data_shopify_variants
WHERE product_id = '{product_id}'
LIMIT 1
UPDATE trader.products
SET {web_status_field} = 0
WHERE PM_PRODUCTCODE = '{sku}'
SELECT DISTINCT product_id
FROM `core.axion`.data_shopify_inventory
ORDER BY last_sync DESC
INSERT INTO {base}
(product_id, graphql_id, title, description, vendor,
product_type, status, sku, last_update)
VALUES (
'{product_id}', '{gid}', '{title}',
'{description}', '{vendor}',
'{product_type}', '{status}', '{sku}', NOW()
)
ON DUPLICATE KEY UPDATE
graphql_id = '{gid}',
title = '{title}',
description = '{description}',
vendor = '{vendor}',
product_type = '{product_type}',
status = '{status}',
last_update = NOW()
INSERT INTO {base_variants}
(product_id, variant_id, graphql_id, Sku, price,
compare_at_price, inventory_quantity, weight, last_update)
VALUES (
'{product_id}', '{variant_id}', '{variant_gid}',
'{variant_sku}', '{price}', '{compare_at_price}',
{inventory_qty}, {weight}, NOW()
)
ON DUPLICATE KEY UPDATE
graphql_id = '{variant_gid}',
price = '{price}',
compare_at_price = '{compare_at_price}',
inventory_quantity = {inventory_qty},
weight = {weight},
last_update = NOW()
DELETE FROM `core.axion`.data_shopify_meta
WHERE product_ID = '{product_id}';
DELETE FROM `core.axion`.data_shopify_variants
WHERE product_ID = '{product_id}';
DELETE FROM `core.axion`.data_shopify_variants_all
WHERE product_ID = '{product_id}';
SELECT Product_id
FROM `core.axion`.data_shopify_variants
WHERE Sku = '{sku}'
AND NOT Product_id = ''
SELECT product_id
FROM data_shopify
WHERE sku = '{sku}'
ORDER BY Id DESC
DELETE FROM data_shopify_Variants_all
WHERE Product_id = '{product_id}'
INSERT INTO {base_variants}
(Sku, Id, Product_id, Audited)
VALUES (
'{sku}',
'{variant_id}',
'{product_id}',
''
)
UPDATE {base_variants}
SET stockitem_id = '{inventory_item_id}'
WHERE sku = '{sku}'
AND Id = '{variant_id}'
REPLACE INTO {base_meta_all}
(Id, Product_id, Variant_id, Sku)
VALUES (
'{metafield_id}',
'{product_id}',
'{variant_id}',
'{sku}'
)
SELECT id
FROM {base_meta_all}
WHERE product_id = '{product_id}'
AND variant_id = '{variant_id}'
AND `KEY` = '{key_name}'
REPLACE INTO `core.axion`.data_shopify_price
(sku, id, compare_at_price, price, updated_at)
VALUES (
'{sku}', '{variant_id}',
'{compare_at_price}', '{price}', NOW()
)
SELECT Guid
FROM trader.meta_product_alternates
WHERE ProductCode = '{product_code}'
SELECT Productcode
FROM trader.meta_product_alternates
WHERE Guid = '{guid}'
AND IsPrimary = 'Y'
REPLACE INTO `core.axion`.data_shopify_images
(sku, image_name, image_size)
VALUES (
'{sku}',
'{image_name}',
'{image_size}'
)
SELECT product_id, graphql_id
FROM ( SELECT product_id, graphql_id FROM `core.axion`.data_shopify_inventory WHERE Sku = '{sku}' AND product_id != '' UNION SELECT product_id, graphql_id FROM `core.axion`.data_shopify_variants WHERE Sku = '{sku}' AND product_id != '' ) AS combined
LIMIT 1
REPLACE INTO {base_variants} (
Sku
, Id
, Product_id
, graphql_id
, Audited
)
VALUES (
'{sku}'
, ''
, '{product_id}'
, '{graphql_id}'
, 'Y'
)
REPLACE INTO `core.axion`.data_shopify_locations
(location_id, graphql_id, name, is_active, updated_at)
VALUES (
'{location_id}',
'{graphql_id}',
'{name}',
{is_active},
NOW()
)
SELECT sku, id, stockitem_id, graphql_id
FROM `core.axion`.data_shopify_variants
WHERE NOT stockitem_id = ''
AND NOT id = ''
SELECT COUNT(*) FROM `core.axion`.data_shopify_locations
WHERE stockitem_id = '{stockitem_id}'
AND location_id = '{location_id}'
REPLACE INTO `core.axion`.data_shopify_locations
(sku, stockitem_id, location_id, updated_at)
VALUES (
'{sku}',
'{stockitem_id}',
'{location_id}',
NOW()
)
INSERT INTO def_parameter (Parameter, Block, Value)
VALUES (
'cache_max_age_hours',
'SHOPIFY',
'{max_age}'
)
ON DUPLICATE KEY UPDATE
Value = '{max_age}'
SELECT
COUNT(DISTINCT sku) as unique_skus,
COUNT(*) as total_variants,
COUNT(DISTINCT CASE WHEN promotion_name != ''
THEN sku END) as products_on_promotion,
AVG(price) as avg_price,
MIN(price) as min_price,
MAX(price) as max_price,
AVG(credit_installment) as avg_installment,
COUNT(DISTINCT CASE WHEN credit_months > 0
THEN sku END) as products_with_credit
FROM {base_dryrun}
WHERE {where_clause}
DELETE FROM {base_dryrun}
WHERE run_timestamp < '{before_timestamp}'
TRUNCATE TABLE {base_dryrun}
INSERT INTO {base_dryrun} (
sku,
variant_sku,
product_title,
option_level,
price,
compare_at_price,
base_price,
promotion_name,
promotion_price,
promotion_discount,
original_price,
credit_installment,
credit_deposit,
credit_months,
credit_price,
warranty_code,
guarantee_period,
warranty_period,
extended_warranty,
extended_warranty_cost,
option1,
option2,
option3,
weight_grams,
metafields_json,
product_input_json
) VALUES (
'{sku}',
'{variant_sku}',
'{product_title}',
{option_level},
{price},
{compare_at_price},
{base_price},
'{promotion_name}',
{promotion_price},
{promotion_discount},
{original_price},
{credit_installment},
{credit_deposit},
{credit_months},
{credit_price},
{warranty_code},
{guarantee_period},
{warranty_period},
'{extended_warranty}',
{extended_warranty_cost},
'{option1}',
'{option2}',
'{option3}',
{weight_grams},
'{metafields_json}',
'{product_input_json}'
)
UPDATE trader.promotion_header
SET
PH_STARTDATE = CONCAT(RIGHT(PH_STARTDATE,2),'.',MID(PH_STARTDATE,6,2),'.',LEFT(PH_STARTDATE,4))
WHERE
PH_STARTDATE LIKE '%-%-%';
UPDATE trader.promotion_header
SET
PH_ENDDATE = CONCAT(RIGHT(PH_ENDDATE,2),'.',MID(PH_ENDDATE,6,2),'.',LEFT(PH_ENDDATE,4))
WHERE
PH_ENDDATE LIKE '%-%-%';
SELECT
variant_sku,
price,
compare_at_price,
credit_installment,
credit_price,
credit_months,
promotion_name,
promotion_price
FROM {base_dryrun}
WHERE {where_clause}
SELECT
p.PM_ProductCode AS code,
p.PM_Stdsellingprice AS total_price,
p.PM_DESCRIPTION AS description,
COALESCE(p.PM_ExtDescription, '') AS ext_description,
pb.PB_NAME AS brand,
COALESCE(C1.META_NAME, C1.CT_NAME, '') AS category,
COALESCE(C2.META_NAME, C2.CT_NAME, '') AS subcategory,
COALESCE(C3.META_NAME, C3.CT_NAME, '') AS thirdcategory,
IF(ps.ps_allowsale = 1, '1', '0') AS allowsale,
IF(ps.ps_alloworder = 1, '1', '0') AS alloworder,
p.PM_IS_WEB AS is_web,
p.PM_SIMILIARITEMCODE AS warranty_item,
p.PM_WEIGHTCODE AS warranty_code,
p.PM_SHELFLIFE AS guarantee_period,
p.PM_COSTBEFOREREBATE AS warranty_period
FROM trader.products AS p
LEFT JOIN trader.product_brands AS pb
ON p.pm_brand_id = pb.pb_id
LEFT JOIN trader.product_status AS ps
ON p.pm_status_id = ps.ps_id
LEFT JOIN trader.category AS C1
ON p.PM_CATEGORY_ID1 = C1.CT_CODE
LEFT JOIN trader.category AS C2
ON p.PM_CATEGORY_ID2 = C2.CT_CODE
LEFT JOIN trader.category AS C3
ON p.PM_CATEGORY_ID3 = C3.CT_CODE
WHERE p.PM_ProductCode IN ({in_clause})
SELECT
pdt.PR_PRODUCTCODE AS code,
phd.PH_NAME AS promotion_name,
pdt.PR_PRICE AS promotion_price,
pdt.PR_NEWPRICE AS original_price
FROM trader.promotion_header phd
LEFT JOIN trader.promotion_detail pdt
ON phd.PH_ID = pdt.PR_ID
WHERE
DATEDIFF(NOW(),
STR_TO_DATE(PH_ENDDATE, '%d.%m.%Y')
) <= 0
AND DATEDIFF(NOW(),
STR_TO_DATE(PH_STARTDATE, '%d.%m.%Y')
) >= -1
AND pdt.PR_PRODUCTCODE IN ({in_clause})
SELECT
Code AS code,
(Weight_1 + Weight_2 + Weight_3
+ Weight_4 + Weight_5) AS weight,
(Length_1 + Length_2 + Length_3
+ Length_4 + Length_5) AS length_total,
(Height_1 + Height_2 + Height_3
+ Height_4 + Height_5) AS height_total,
(Width_1 + Width_2 + Width_3
+ Width_4 + Width_5) AS width_total,
LongDescription AS long_description,
Description AS features,
Web_Instalment AS credit_installment,
Web_Deposit AS credit_deposit,
Web_Totalprice AS credit_price
FROM trader.meta_product
WHERE Code IN ({in_clause})
SELECT
p.PM_PRODUCTCODE AS code,
CASE
WHEN p.PM_IS_WEB = 0
OR p.PM_Status_id IN (3, 7, 8, 9, 11)
THEN 'Archived'
WHEN p.PM_STATUS_ID IN (4, 1)
AND stock.stock_level > 0
THEN 'In Stock'
WHEN p.PM_STATUS_ID = 4
AND stock.stock_level <= 0
THEN 'In Stock'
WHEN p.PM_STATUS_ID = 1
AND stock.stock_level <= 0
THEN 'Supplier Order'
WHEN p.PM_Status_id IN (5, 6, 12)
AND stock.stock_level > 0
THEN 'In Stock'
WHEN p.PM_Status_id IN (5, 6, 12)
AND stock.stock_level <= 0
THEN 'Archived'
ELSE 'Error'
END AS meta_can_order_message,
COALESCE(stock.stock_level, 0) AS stock_level,
COALESCE(supp.lead_days, 0) AS lead_days
FROM trader.products AS p
LEFT JOIN (
SELECT
pl.PL_PRODUCTCODE,
COALESCE(SUM(CASE
WHEN pl_store_id = '299'
AND pl_location_id = 1
THEN pl_qtyonhand
WHEN pl_store_id != '299'
AND pl_location_id = 0
THEN pl_qtyonhand
ELSE 0
END), 0) AS stock_level
FROM trader.product_ledger AS pl
WHERE pl.PL_SUBTYPE1 = ''
AND pl.PL_SUBTYPE2 = ''
GROUP BY pl.PL_PRODUCTCODE
) AS stock
ON stock.PL_PRODUCTCODE = p.PM_PRODUCTCODE
LEFT JOIN (
SELECT
ps.PS_PRODUCTCODE AS code,
MIN(NULLIF(ps.PS_LEADTIME, 0)) AS lead_days
FROM trader.product_suppliers AS ps
GROUP BY ps.PS_PRODUCTCODE
) AS supp
ON supp.code = p.PM_PRODUCTCODE
WHERE p.PM_PRODUCTCODE IN ({in_clause})
SELECT
P.PM_PRODUCTCODE AS code,
COALESCE(M_exact.TargetCat1, M_wild.TargetCat1)
AS target_cat1,
COALESCE(M_exact.TargetCat2, M_wild.TargetCat2)
AS target_cat2,
COALESCE(M_exact.TargetCat3, M_wild.TargetCat3)
AS target_cat3
FROM trader.products AS P
LEFT JOIN trader.meta_categorymap AS M_exact
ON P.PM_CATEGORY_ID1 = M_exact.Ct_code1
AND P.PM_CATEGORY_ID2 = M_exact.Ct_code2
AND P.PM_CATEGORY_ID3 = M_exact.Ct_code3
LEFT JOIN trader.meta_categorymap AS M_wild
ON P.PM_CATEGORY_ID1 = M_wild.Ct_code1
AND P.PM_CATEGORY_ID2 = M_wild.Ct_code2
AND (M_wild.Ct_code3 = ''
OR M_wild.Ct_code3 IS NULL)
WHERE P.PM_PRODUCTCODE IN ({in_clause})
{'BEDROOM SUITE': 'Bedroom', 'BASE SETS': 'Bedroom', 'BED SETS': 'Bedroom', 'KITCHEN APPLIANCES': 'Kitchen', 'REFRIGERATION': 'Fridges', 'LAUNDRY': 'Laundry', 'LOUNGE SUITE': 'Lounge', 'DINING ROOM': 'Dining', 'CHEST OF DRAWERS': 'Storage', 'WALL UNITS': 'Storage', 'OFFICE': 'Office', 'OUTDOOR': 'Outdoor', 'KIDS': 'Kids', 'DECOR': 'Decor', 'ELECTRONICS': 'Electronics', 'FRIDGES': 'Fridges', 'COOKWARE': 'Kitchen', 'SMALL APPLIANCES': 'Appliances', 'FURNITURE': 'Furniture', 'WHITES': 'Appliances', 'BEDDING': 'Bedding'}