Skip to content

Instantly share code, notes, and snippets.

@Marko-M

Marko-M/magento_bundle_product_type_price_indexer.sql

Last active March 28, 2018 20:46
Show Gist options
  • Save Marko-M/3510bb24f52629b512dba2fa40c49c0d to your computer and use it in GitHub Desktop.
Save Marko-M/3510bb24f52629b512dba2fa40c49c0d to your computer and use it in GitHub Desktop.
Download ZIP
Magento Bundle Product Type Price Indexer
Raw
magento_bundle_product_type_price_indexer.sql
INSERT INTO `catalog_product_index_price_bundle_idx`
SELECT `e`.`entity_id`,
`cg`.`customer_group_id`,
`cw`.`website_id`,
IF(IF(Ifnull(tas_tax_class_id.value_id, -1) > 0, tas_tax_class_id.value, tad_tax_class_id.value) IS NOT NULL, IF(Ifnull(tas_tax_class_id.value_id, -1) > 0, tas_tax_class_id.value, tad_tax_class_id.value), 0) AS `tax_class_id`,
1 AS `price_type`,
IF(IF(IF(Ifnull(tas_special_from_date.value_id, -1) > 0, tas_special_from_date.value, tad_special_from_date.value) IS NULL, 1, IF(IF(Ifnull(tas_special_from_date.value_id, -1) > 0, tas_special_from_date.value, tad_special_from_date.value) <= cwd.website_date, 1, 0)) > 0
AND IF(IF(Ifnull(tas_special_to_date.value_id, -1) > 0, tas_special_to_date.value, tad_special_to_date.value) IS NULL, 1, IF(IF(Ifnull(tas_special_to_date.value_id, -1) > 0, tas_special_to_date.value, tad_special_to_date.value) >= cwd.website_date, 1, 0)) > 0
AND IF(Ifnull(tas_special_price.value_id, -1) > 0, tas_special_price.value, tad_special_price.value) > 0
AND IF(Ifnull(tas_special_price.value_id, -1) > 0, tas_special_price.value, tad_special_price.value) < 100 , IF(Ifnull(tas_special_price.value_id, -1) > 0, tas_special_price.value, tad_special_price.value), 0) AS `special_price`,
tp.min_price AS `tier_percent`,
IF(IF(Ifnull(tas_price.value_id, -1) > 0, tas_price.value, tad_price.value) IS NULL, 0, IF(Ifnull(tas_price.value_id, -1) > 0, tas_price.value, tad_price.value)) AS `orig_price`,
IF(IF(IF(gp.price IS NOT NULL
AND gp.price > 0
AND gp.price < 100, gp.price, 0) > 0, Round(IF(Ifnull(tas_price.value_id, -1) > 0, tas_price.value, tad_price.value) - (IF(Ifnull(tas_price.value_id, -1) > 0, tas_price.value, tad_price.value) * (IF(gp.price IS NOT NULL
AND gp.price > 0
AND gp.price < 100, gp.price, 0) / 100)), 2), NULL) IS NOT NULL
AND IF(IF(gp.price IS NOT NULL
AND gp.price > 0
AND gp.price < 100, gp.price, 0) > 0, Round(IF(Ifnull(tas_price.value_id, -1) > 0, tas_price.value, tad_price.value) - (IF(Ifnull(tas_price.value_id, -1) > 0, tas_price.value, tad_price.value) * (IF(gp.price IS NOT NULL
AND gp.price > 0
AND gp.price < 100, gp.price, 0) / 100)), 2), NULL) < IF(IF(IF(IF(Ifnull(tas_special_from_date.value_id, -1) > 0, tas_special_from_date.value, tad_special_from_date.value) IS NULL, 1, IF(IF(Ifnull(tas_special_from_date.value_id, -1) > 0, tas_special_from_date.value, tad_special_from_date.value) <= cwd.website_date, 1, 0)) > 0
AND IF(IF(Ifnull(tas_special_to_date.value_id, -1) > 0, tas_special_to_date.value, tad_special_to_date.value) IS NULL, 1, IF(IF(Ifnull(tas_special_to_date.value_id, -1) > 0, tas_special_to_date.value, tad_special_to_date.value) >= cwd.website_date, 1, 0)) > 0
AND IF(Ifnull(tas_special_price.value_id, -1) > 0, tas_special_price.value, tad_special_price.value) > 0
AND IF(Ifnull(tas_special_price.value_id, -1) > 0, tas_special_price.value, tad_special_price.value) < 100 , IF(Ifnull(tas_special_price.value_id, -1) > 0, tas_special_price.value, tad_special_price.value), 0) > 0, Round(IF(Ifnull(tas_price.value_id, -1) > 0, tas_price.value, tad_price.value) * (IF(IF(IF(Ifnull(tas_special_from_date.value_id, -1) > 0, tas_special_from_date.value, tad_special_from_date.value) IS NULL, 1, IF(IF(Ifnull(tas_special_from_date.value_id, -1) > 0, tas_special_from_date.value, tad_special_from_date.value) <= cwd.website_date, 1, 0)) > 0
AND IF(IF(Ifnull(tas_special_to_date.value_id, -1) > 0, tas_special_to_date.value, tad_special_to_date.value) IS NULL, 1, IF(IF(Ifnull(tas_special_to_date.value_id, -1) > 0, tas_special_to_date.value, tad_special_to_date.value) >= cwd.website_date, 1, 0)) > 0
AND IF(Ifnull(tas_special_price.value_id, -1) > 0, tas_special_price.value, tad_special_price.value) > 0
AND IF(Ifnull(tas_special_price.value_id, -1) > 0, tas_special_price.value, tad_special_price.value) < 100 , IF(Ifnull(tas_special_price.value_id, -1) > 0, tas_special_price.value, tad_special_price.value), 0) / 100), 2), IF(Ifnull(tas_price.value_id, -1) > 0, tas_price.value, tad_price.value)), IF(IF(gp.price IS NOT NULL
AND gp.price > 0
AND gp.price < 100, gp.price, 0) > 0, Round(IF(Ifnull(tas_price.value_id, -1) > 0, tas_price.value, tad_price.value) - (IF(Ifnull(tas_price.value_id, -1) > 0, tas_price.value, tad_price.value) * (IF(gp.price IS NOT NULL
AND gp.price > 0
AND gp.price < 100, gp.price, 0) / 100)), 2), NULL), IF(IF(IF(IF(Ifnull(tas_special_from_date.value_id, -1) > 0, tas_special_from_date.value, tad_special_from_date.value) IS NULL, 1, IF(IF(Ifnull(tas_special_from_date.value_id, -1) > 0, tas_special_from_date.value, tad_special_from_date.value) <= cwd.website_date, 1, 0)) > 0
AND IF(IF(Ifnull(tas_special_to_date.value_id, -1) > 0, tas_special_to_date.value, tad_special_to_date.value) IS NULL, 1, IF(IF(Ifnull(tas_special_to_date.value_id, -1) > 0, tas_special_to_date.value, tad_special_to_date.value) >= cwd.website_date, 1, 0)) > 0
AND IF(Ifnull(tas_special_price.value_id, -1) > 0, tas_special_price.value, tad_special_price.value) > 0
AND IF(Ifnull(tas_special_price.value_id, -1) > 0, tas_special_price.value, tad_special_price.value) < 100 , IF(Ifnull(tas_special_price.value_id, -1) > 0, tas_special_price.value, tad_special_price.value), 0) > 0, Round(IF(Ifnull(tas_price.value_id, -1) > 0, tas_price.value, tad_price.value) * (IF(IF(IF(Ifnull(tas_special_from_date.value_id, -1) > 0, tas_special_from_date.value, tad_special_from_date.value) IS NULL, 1, IF(IF(Ifnull(tas_special_from_date.value_id, -1) > 0, tas_special_from_date.value, tad_special_from_date.value) <= cwd.website_date, 1, 0)) > 0
AND IF(IF(Ifnull(tas_special_to_date.value_id, -1) > 0, tas_special_to_date.value, tad_special_to_date.value) IS NULL, 1, IF(IF(Ifnull(tas_special_to_date.value_id, -1) > 0, tas_special_to_date.value, tad_special_to_date.value) >= cwd.website_date, 1, 0)) > 0
AND IF(Ifnull(tas_special_price.value_id, -1) > 0, tas_special_price.value, tad_special_price.value) > 0
AND IF(Ifnull(tas_special_price.value_id, -1) > 0, tas_special_price.value, tad_special_price.value) < 100 , IF(Ifnull(tas_special_price.value_id, -1) > 0, tas_special_price.value, tad_special_price.value), 0) / 100), 2), IF(Ifnull(tas_price.value_id, -1) > 0, tas_price.value, tad_price.value))) AS `price`,
IF(IF(IF(gp.price IS NOT NULL
AND gp.price > 0
AND gp.price < 100, gp.price, 0) > 0, Round(IF(Ifnull(tas_price.value_id, -1) > 0, tas_price.value, tad_price.value) - (IF(Ifnull(tas_price.value_id, -1) > 0, tas_price.value, tad_price.value) * (IF(gp.price IS NOT NULL
AND gp.price > 0
AND gp.price < 100, gp.price, 0) / 100)), 2), NULL) IS NOT NULL
AND IF(IF(gp.price IS NOT NULL
AND gp.price > 0
AND gp.price < 100, gp.price, 0) > 0, Round(IF(Ifnull(tas_price.value_id, -1) > 0, tas_price.value, tad_price.value) - (IF(Ifnull(tas_price.value_id, -1) > 0, tas_price.value, tad_price.value) * (IF(gp.price IS NOT NULL
AND gp.price > 0
AND gp.price < 100, gp.price, 0) / 100)), 2), NULL) < IF(IF(IF(IF(Ifnull(tas_special_from_date.value_id, -1) > 0, tas_special_from_date.value, tad_special_from_date.value) IS NULL, 1, IF(IF(Ifnull(tas_special_from_date.value_id, -1) > 0, tas_special_from_date.value, tad_special_from_date.value) <= cwd.website_date, 1, 0)) > 0
AND IF(IF(Ifnull(tas_special_to_date.value_id, -1) > 0, tas_special_to_date.value, tad_special_to_date.value) IS NULL, 1, IF(IF(Ifnull(tas_special_to_date.value_id, -1) > 0, tas_special_to_date.value, tad_special_to_date.value) >= cwd.website_date, 1, 0)) > 0
AND IF(Ifnull(tas_special_price.value_id, -1) > 0, tas_special_price.value, tad_special_price.value) > 0
AND IF(Ifnull(tas_special_price.value_id, -1) > 0, tas_special_price.value, tad_special_price.value) < 100 , IF(Ifnull(tas_special_price.value_id, -1) > 0, tas_special_price.value, tad_special_price.value), 0) > 0, Round(IF(Ifnull(tas_price.value_id, -1) > 0, tas_price.value, tad_price.value) * (IF(IF(IF(Ifnull(tas_special_from_date.value_id, -1) > 0, tas_special_from_date.value, tad_special_from_date.value) IS NULL, 1, IF(IF(Ifnull(tas_special_from_date.value_id, -1) > 0, tas_special_from_date.value, tad_special_from_date.value) <= cwd.website_date, 1, 0)) > 0
AND IF(IF(Ifnull(tas_special_to_date.value_id, -1) > 0, tas_special_to_date.value, tad_special_to_date.value) IS NULL, 1, IF(IF(Ifnull(tas_special_to_date.value_id, -1) > 0, tas_special_to_date.value, tad_special_to_date.value) >= cwd.website_date, 1, 0)) > 0
AND IF(Ifnull(tas_special_price.value_id, -1) > 0, tas_special_price.value, tad_special_price.value) > 0
AND IF(Ifnull(tas_special_price.value_id, -1) > 0, tas_special_price.value, tad_special_price.value) < 100 , IF(Ifnull(tas_special_price.value_id, -1) > 0, tas_special_price.value, tad_special_price.value), 0) / 100), 2), IF(Ifnull(tas_price.value_id, -1) > 0, tas_price.value, tad_price.value)), IF(IF(gp.price IS NOT NULL
AND gp.price > 0
AND gp.price < 100, gp.price, 0) > 0, Round(IF(Ifnull(tas_price.value_id, -1) > 0, tas_price.value, tad_price.value) - (IF(Ifnull(tas_price.value_id, -1) > 0, tas_price.value, tad_price.value) * (IF(gp.price IS NOT NULL
AND gp.price > 0
AND gp.price < 100, gp.price, 0) / 100)), 2), NULL), IF(IF(IF(IF(Ifnull(tas_special_from_date.value_id, -1) > 0, tas_special_from_date.value, tad_special_from_date.value) IS NULL, 1, IF(IF(Ifnull(tas_special_from_date.value_id, -1) > 0, tas_special_from_date.value, tad_special_from_date.value) <= cwd.website_date, 1, 0)) > 0
AND IF(IF(Ifnull(tas_special_to_date.value_id, -1) > 0, tas_special_to_date.value, tad_special_to_date.value) IS NULL, 1, IF(IF(Ifnull(tas_special_to_date.value_id, -1) > 0, tas_special_to_date.value, tad_special_to_date.value) >= cwd.website_date, 1, 0)) > 0
AND IF(Ifnull(tas_special_price.value_id, -1) > 0, tas_special_price.value, tad_special_price.value) > 0
AND IF(Ifnull(tas_special_price.value_id, -1) > 0, tas_special_price.value, tad_special_price.value) < 100 , IF(Ifnull(tas_special_price.value_id, -1) > 0, tas_special_price.value, tad_special_price.value), 0) > 0, Round(IF(Ifnull(tas_price.value_id, -1) > 0, tas_price.value, tad_price.value) * (IF(IF(IF(Ifnull(tas_special_from_date.value_id, -1) > 0, tas_special_from_date.value, tad_special_from_date.value) IS NULL, 1, IF(IF(Ifnull(tas_special_from_date.value_id, -1) > 0, tas_special_from_date.value, tad_special_from_date.value) <= cwd.website_date, 1, 0)) > 0
AND IF(IF(Ifnull(tas_special_to_date.value_id, -1) > 0, tas_special_to_date.value, tad_special_to_date.value) IS NULL, 1, IF(IF(Ifnull(tas_special_to_date.value_id, -1) > 0, tas_special_to_date.value, tad_special_to_date.value) >= cwd.website_date, 1, 0)) > 0
AND IF(Ifnull(tas_special_price.value_id, -1) > 0, tas_special_price.value, tad_special_price.value) > 0
AND IF(Ifnull(tas_special_price.value_id, -1) > 0, tas_special_price.value, tad_special_price.value) < 100 , IF(Ifnull(tas_special_price.value_id, -1) > 0, tas_special_price.value, tad_special_price.value), 0) / 100), 2), IF(Ifnull(tas_price.value_id, -1) > 0, tas_price.value, tad_price.value))) AS `min_price`,
IF(IF(IF(gp.price IS NOT NULL
AND gp.price > 0
AND gp.price < 100, gp.price, 0) > 0, Round(IF(Ifnull(tas_price.value_id, -1) > 0, tas_price.value, tad_price.value) - (IF(Ifnull(tas_price.value_id, -1) > 0, tas_price.value, tad_price.value) * (IF(gp.price IS NOT NULL
AND gp.price > 0
AND gp.price < 100, gp.price, 0) / 100)), 2), NULL) IS NOT NULL
AND IF(IF(gp.price IS NOT NULL
AND gp.price > 0
AND gp.price < 100, gp.price, 0) > 0, Round(IF(Ifnull(tas_price.value_id, -1) > 0, tas_price.value, tad_price.value) - (IF(Ifnull(tas_price.value_id, -1) > 0, tas_price.value, tad_price.value) * (IF(gp.price IS NOT NULL
AND gp.price > 0
AND gp.price < 100, gp.price, 0) / 100)), 2), NULL) < IF(IF(IF(IF(Ifnull(tas_special_from_date.value_id, -1) > 0, tas_special_from_date.value, tad_special_from_date.value) IS NULL, 1, IF(IF(Ifnull(tas_special_from_date.value_id, -1) > 0, tas_special_from_date.value, tad_special_from_date.value) <= cwd.website_date, 1, 0)) > 0
AND IF(IF(Ifnull(tas_special_to_date.value_id, -1) > 0, tas_special_to_date.value, tad_special_to_date.value) IS NULL, 1, IF(IF(Ifnull(tas_special_to_date.value_id, -1) > 0, tas_special_to_date.value, tad_special_to_date.value) >= cwd.website_date, 1, 0)) > 0
AND IF(Ifnull(tas_special_price.value_id, -1) > 0, tas_special_price.value, tad_special_price.value) > 0
AND IF(Ifnull(tas_special_price.value_id, -1) > 0, tas_special_price.value, tad_special_price.value) < 100 , IF(Ifnull(tas_special_price.value_id, -1) > 0, tas_special_price.value, tad_special_price.value), 0) > 0, Round(IF(Ifnull(tas_price.value_id, -1) > 0, tas_price.value, tad_price.value) * (IF(IF(IF(Ifnull(tas_special_from_date.value_id, -1) > 0, tas_special_from_date.value, tad_special_from_date.value) IS NULL, 1, IF(IF(Ifnull(tas_special_from_date.value_id, -1) > 0, tas_special_from_date.value, tad_special_from_date.value) <= cwd.website_date, 1, 0)) > 0
AND IF(IF(Ifnull(tas_special_to_date.value_id, -1) > 0, tas_special_to_date.value, tad_special_to_date.value) IS NULL, 1, IF(IF(Ifnull(tas_special_to_date.value_id, -1) > 0, tas_special_to_date.value, tad_special_to_date.value) >= cwd.website_date, 1, 0)) > 0
AND IF(Ifnull(tas_special_price.value_id, -1) > 0, tas_special_price.value, tad_special_price.value) > 0
AND IF(Ifnull(tas_special_price.value_id, -1) > 0, tas_special_price.value, tad_special_price.value) < 100 , IF(Ifnull(tas_special_price.value_id, -1) > 0, tas_special_price.value, tad_special_price.value), 0) / 100), 2), IF(Ifnull(tas_price.value_id, -1) > 0, tas_price.value, tad_price.value)), IF(IF(gp.price IS NOT NULL
AND gp.price > 0
AND gp.price < 100, gp.price, 0) > 0, Round(IF(Ifnull(tas_price.value_id, -1) > 0, tas_price.value, tad_price.value) - (IF(Ifnull(tas_price.value_id, -1) > 0, tas_price.value, tad_price.value) * (IF(gp.price IS NOT NULL
AND gp.price > 0
AND gp.price < 100, gp.price, 0) / 100)), 2), NULL), IF(IF(IF(IF(Ifnull(tas_special_from_date.value_id, -1) > 0, tas_special_from_date.value, tad_special_from_date.value) IS NULL, 1, IF(IF(Ifnull(tas_special_from_date.value_id, -1) > 0, tas_special_from_date.value, tad_special_from_date.value) <= cwd.website_date, 1, 0)) > 0
AND IF(IF(Ifnull(tas_special_to_date.value_id, -1) > 0, tas_special_to_date.value, tad_special_to_date.value) IS NULL, 1, IF(IF(Ifnull(tas_special_to_date.value_id, -1) > 0, tas_special_to_date.value, tad_special_to_date.value) >= cwd.website_date, 1, 0)) > 0
AND IF(Ifnull(tas_special_price.value_id, -1) > 0, tas_special_price.value, tad_special_price.value) > 0
AND IF(Ifnull(tas_special_price.value_id, -1) > 0, tas_special_price.value, tad_special_price.value) < 100 , IF(Ifnull(tas_special_price.value_id, -1) > 0, tas_special_price.value, tad_special_price.value), 0) > 0, Round(IF(Ifnull(tas_price.value_id, -1) > 0, tas_price.value, tad_price.value) * (IF(IF(IF(Ifnull(tas_special_from_date.value_id, -1) > 0, tas_special_from_date.value, tad_special_from_date.value) IS NULL, 1, IF(IF(Ifnull(tas_special_from_date.value_id, -1) > 0, tas_special_from_date.value, tad_special_from_date.value) <= cwd.website_date, 1, 0)) > 0
AND IF(IF(Ifnull(tas_special_to_date.value_id, -1) > 0, tas_special_to_date.value, tad_special_to_date.value) IS NULL, 1, IF(IF(Ifnull(tas_special_to_date.value_id, -1) > 0, tas_special_to_date.value, tad_special_to_date.value) >= cwd.website_date, 1, 0)) > 0
AND IF(Ifnull(tas_special_price.value_id, -1) > 0, tas_special_price.value, tad_special_price.value) > 0
AND IF(Ifnull(tas_special_price.value_id, -1) > 0, tas_special_price.value, tad_special_price.value) < 100 , IF(Ifnull(tas_special_price.value_id, -1) > 0, tas_special_price.value, tad_special_price.value), 0) / 100), 2), IF(Ifnull(tas_price.value_id, -1) > 0, tas_price.value, tad_price.value))) AS `max_price`,
IF(tp.min_price IS NOT NULL, Round(IF(Ifnull(tas_price.value_id, -1) > 0, tas_price.value, tad_price.value) - (IF(Ifnull(tas_price.value_id, -1) > 0, tas_price.value, tad_price.value) * (tp.min_price / 100)), 2), NULL) AS `tier_price`,
IF(tp.min_price IS NOT NULL, Round(IF(Ifnull(tas_price.value_id, -1) > 0, tas_price.value, tad_price.value) - (IF(Ifnull(tas_price.value_id, -1) > 0, tas_price.value, tad_price.value) * (tp.min_price / 100)), 2), NULL) AS `base_tier`,
IF(IF(gp.price IS NOT NULL
AND gp.price > 0
AND gp.price < 100, gp.price, 0) > 0, Round(IF(Ifnull(tas_price.value_id, -1) > 0, tas_price.value, tad_price.value) - (IF(Ifnull(tas_price.value_id, -1) > 0, tas_price.value, tad_price.value) * (IF(gp.price IS NOT NULL
AND gp.price > 0
AND gp.price < 100, gp.price, 0) / 100)), 2), NULL) AS `group_price`,
IF(IF(gp.price IS NOT NULL
AND gp.price > 0
AND gp.price < 100, gp.price, 0) > 0, Round(IF(Ifnull(tas_price.value_id, -1) > 0, tas_price.value, tad_price.value) - (IF(Ifnull(tas_price.value_id, -1) > 0, tas_price.value, tad_price.value) * (IF(gp.price IS NOT NULL
AND gp.price > 0
AND gp.price < 100, gp.price, 0) / 100)), 2), NULL) AS `base_group_price`,
gp.price AS `group_price_percent`
FROM `catalog_product_entity` AS `e`
CROSS JOIN `customer_group` AS `cg`
CROSS JOIN `core_website` AS `cw`
INNER JOIN `core_store_group` AS `csg`
ON csg.group_id = cw.default_group_id
INNER JOIN `core_store` AS `cs`
ON cs.store_id = csg.default_store_id
INNER JOIN `catalog_product_website` AS `pw`
ON pw.product_id = e.entity_id
AND pw.website_id = cw.website_id
INNER JOIN `catalog_product_index_website` AS `cwd`
ON cw.website_id = cwd.website_id
LEFT JOIN `catalog_product_index_tier_price` AS `tp`
ON tp.entity_id = e.entity_id
AND tp.website_id = cw.website_id
AND tp.customer_group_id = cg.customer_group_id
LEFT JOIN `catalog_product_index_group_price` AS `gp`
ON gp.entity_id = e.entity_id
AND gp.website_id = cw.website_id
AND gp.customer_group_id = cg.customer_group_id
INNER JOIN `catalog_product_entity_int` AS `tad_status`
ON tad_status.entity_id = e.entity_id
AND tad_status.attribute_id = 96
AND tad_status.store_id = 0
LEFT JOIN `catalog_product_entity_int` AS `tas_status`
ON tas_status.entity_id = e.entity_id
AND tas_status.attribute_id = 96
AND tas_status.store_id = cs.store_id
LEFT JOIN `catalog_product_entity_int` AS `tad_tax_class_id`
ON tad_tax_class_id.entity_id = e.entity_id
AND tad_tax_class_id.attribute_id = 121
AND tad_tax_class_id.store_id = 0
LEFT JOIN `catalog_product_entity_int` AS `tas_tax_class_id`
ON tas_tax_class_id.entity_id = e.entity_id
AND tas_tax_class_id.attribute_id = 121
AND tas_tax_class_id.store_id = cs.store_id
INNER JOIN `catalog_product_entity_int` AS `ta_price_type`
ON ta_price_type.entity_id = e.entity_id
AND ta_price_type.attribute_id = 123
AND ta_price_type.store_id = 0
LEFT JOIN `catalog_product_entity_decimal` AS `tad_price`
ON tad_price.entity_id = e.entity_id
AND tad_price.attribute_id = 75
AND tad_price.store_id = 0
LEFT JOIN `catalog_product_entity_decimal` AS `tas_price`
ON tas_price.entity_id = e.entity_id
AND tas_price.attribute_id = 75
AND tas_price.store_id = cs.store_id
LEFT JOIN `catalog_product_entity_decimal` AS `tad_special_price`
ON tad_special_price.entity_id = e.entity_id
AND tad_special_price.attribute_id = 76
AND tad_special_price.store_id = 0
LEFT JOIN `catalog_product_entity_decimal` AS `tas_special_price`
ON tas_special_price.entity_id = e.entity_id
AND tas_special_price.attribute_id = 76
AND tas_special_price.store_id = cs.store_id
LEFT JOIN `catalog_product_entity_datetime` AS `tad_special_from_date`
ON tad_special_from_date.entity_id = e.entity_id
AND tad_special_from_date.attribute_id = 77
AND tad_special_from_date.store_id = 0
LEFT JOIN `catalog_product_entity_datetime` AS `tas_special_from_date`
ON tas_special_from_date.entity_id = e.entity_id
AND tas_special_from_date.attribute_id = 77
AND tas_special_from_date.store_id = cs.store_id
LEFT JOIN `catalog_product_entity_datetime` AS `tad_special_to_date`
ON tad_special_to_date.entity_id = e.entity_id
AND tad_special_to_date.attribute_id = 78
AND tad_special_to_date.store_id = 0
LEFT JOIN `catalog_product_entity_datetime` AS `tas_special_to_date`
ON tas_special_to_date.entity_id = e.entity_id
AND tas_special_to_date.attribute_id = 78
AND tas_special_to_date.store_id = cs.store_id
INNER JOIN `cataloginventory_stock_status` AS `ciss`
ON ciss.product_id = e.entity_id
AND ciss.website_id = cw.website_id
WHERE (
e.type_id='bundle')
AND (
IF(Ifnull(tas_status.value_id, -1) > 0, tas_status.value, tad_status.value)=1)
AND (
ta_price_type.value=1)
AND (
ciss.stock_status = 1)
on duplicate KEY
UPDATE `tax_class_id` = VALUES
(
`tax_class_id`
)
,
`price_type` = VALUES
(
`price_type`
)
,
`special_price` = VALUES
(
`special_price`
)
,
`tier_percent` = VALUES
(
`tier_percent`
)
,
`orig_price` = VALUES
(
`orig_price`
)
,
`price` = VALUES
(
`price`
)
,
`min_price` = VALUES
(
`min_price`
)
,
`max_price` = VALUES
(
`max_price`
)
,
`tier_price` = VALUES
(
`tier_price`
)
,
`base_tier` = VALUES
(
`base_tier`
)
,
`group_price` = VALUES
(
`group_price`
)
,
`base_group_price` = VALUES
(
`base_group_price`
)
,
`group_price_percent` = VALUES
(
`group_price_percent`
)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment