Last active
March 28, 2018 20:46
-
-
Save Marko-M/3510bb24f52629b512dba2fa40c49c0d to your computer and use it in GitHub Desktop.
Revisions
-
Marko-M revised this gist
Mar 28, 2018 . 1 changed file with 251 additions and 23 deletions.There are no files selected for viewing
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters. Learn more about bidirectional Unicode charactersOriginal file line number Diff line number Diff line change @@ -1,23 +1,251 @@ 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` ) -
Mar 28, 2018 . 1 changed file with 22 additions and 8 deletions.There are no files selected for viewing
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters. Learn more about bidirectional Unicode charactersOriginal file line number Diff line number Diff line change @@ -1,9 +1,23 @@ 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`) -
Mar 28, 2018 . 1 changed file with 9 additions and 251 deletions.There are no files selected for viewing
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters. Learn more about bidirectional Unicode charactersOriginal file line number Diff line number Diff line change @@ -1,251 +1,9 @@ INSERT INTO `catalog_product_bundle_stock_index` SELECT `bo`.`parent_id`, `cw`.`website_id`, `cis`.`stock_id`, `bo`.`option_id`, MAX(IF(e.required_options = 0, i.stock_status, 0)) AS `status` FROM `catalog_product_bundle_option` AS `bo` CROSS JOIN `core_website` AS `cw` CROSS JOIN `cataloginventory_stock` AS `cis` LEFT JOIN `catalog_product_bundle_selection` AS `bs` ON bs.option_id = bo.option_id LEFT JOIN `cataloginventory_stock_status_idx` AS `i` ON i.product_id = bs.product_id AND i.website_id = cw.website_id AND i.stock_id = cis.stock_id LEFT JOIN `catalog_product_entity` AS `e` ON e.entity_id = bs.product_id WHERE (cw.website_id != 0) AND (bo.required = 0) GROUP BY `bo`.`parent_id`, `cw`.`website_id`, `cis`.`stock_id`, `bo`.`option_id` HAVING (MAX(IF(e.required_options = 0, i.stock_status, 0)) = 1) ON DUPLICATE KEY UPDATE `stock_status` = VALUES(`stock_status`) -
Mar 28, 2018 .There are no files selected for viewing
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters. Learn more about bidirectional Unicode charactersOriginal file line number Diff line number Diff line change @@ -0,0 +1,251 @@ 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` )