|
, -1) > 0, tas_special_from_date.value, tad_special_from_date.value) IS NULL OR DATE(IF(IFNULL(tas_special_from_date.value_id, -1) > 0, tas_special_from_date.value, tad_special_from_date.value)) <= cwd.website_date AND IF(IFNULL(tas_special_to_date.value_id, -1) > 0, tas_special_to_date.value, tad_special_to_date.value) IS NULL OR DATE(IF(IFNULL(tas_special_to_date.value_id, -1) > 0, tas_special_to_date.value, tad_special_to_date.value)) >= cwd.website_date, ta_special_price.value, ~0), IFNULL((IF(tier_price_1.value_id is NULL AND tier_price_2.value_id is NULL AND tier_price_3.value_id is NULL AND tier_price_4.value_id is NULL, NULL, LEAST(IFNULL((IF(tier_price_1.value = 0, ROUND(ta_price.value * (1 - ROUND(tier_price_1.percentage_value * cwd.rate, 4) / 100), 4), ROUND(tier_price_1.value * cwd.rate, 4))), ~0), IFNULL((IF(tier_price_2.value = 0, ROUND(ta_price.value * (1 - ROUND(tier_price_2.percentage_value * cwd.rate, 4) / 100), 4), ROUND(tier_price_2.value * cwd.rate, 4))), ~0), IFNULL((IF(tier_price_3.value = 0, ROUND(ta_price.value * (1 - ROUND(tier_price_3.percentage_value * cwd.rate, 4) / 100), 4), ROUND(tier_price_3.value * cwd.rate, 4))), ~0), IFNULL((IF(tier_price_4.value = 0, ROUND(ta_price.value * (1 - ROUND(tier_price_4.percentage_value * cwd.rate, 4) / 100), 4), ROUND(tier_price_4.value * cwd.rate, 4))), ~0)))), ~0))), 0) AS `max_price`, IF(tier_price_1.value_id is NULL AND tier_price_2.value_id is NULL AND tier_price_3.value_id is NULL AND tier_price_4.value_id is NULL, NULL, LEAST(IFNULL((IF(tier_price_1.value = 0, ROUND(ta_price.value * (1 - ROUND(tier_price_1.percentage_value * cwd.rate, 4) / 100), 4), ROUND(tier_price_1.value * cwd.rate, 4))), ~0), IFNULL((IF(tier_price_2.value = 0, ROUND(ta_price.value * (1 - ROUND(tier_price_2.percentage_value * cwd.rate, 4) / 100), 4), ROUND(tier_price_2.value * cwd.rate, 4))), ~0), IFNULL((IF(tier_price_3.value = 0, ROUND(ta_price.value * (1 - ROUND(tier_price_3.percentage_value * cwd.rate, 4) / 100), 4), ROUND(tier_price_3.value * cwd.rate, 4))), ~0), IFNULL((IF(tier_price_4.value = 0, ROUND(ta_price.value * (1 - ROUND(tier_price_4.percentage_value * cwd.rate, 4) / 100), 4), ROUND(tier_price_4.value * cwd.rate, 4))), ~0))) AS `tier_price` FROM `catalog_product_entity` AS `e`
2 CROSS JOIN `customer_group` AS `cg`
3 INNER JOIN `catalog_product_website` AS `pw` ON pw.product_id = e.entity_id
4 INNER JOIN `catalog_product_index_website` AS `cwd` ON pw.website_id = cwd.website_id
5 LEFT JOIN `catalog_product_index_tier_price` AS `tp` ON tp.entity_id = e.entity_id AND tp.customer_group_id = cg.customer_group_id AND tp.website_id = pw.website_id
6 LEFT JOIN `catalog_product_entity_tier_price` AS `tier_price_1` ON tier_price_1.row_id = e.row_id AND tier_price_1.all_groups = 0 AND tier_price_1.customer_group_id = cg.customer_group_id AND tier_price_1.qty = 1 AND tier_price_1.website_id = 0
7 LEFT JOIN `catalog_product_entity_tier_price` AS `tier_price_2` ON tier_price_2.row_id = e.row_id AND tier_price_2.all_groups = 0 AND tier_price_2.customer_group_id = cg.customer_group_id AND tier_price_2.qty = 1 AND tier_price_2.website_id = pw.website_id
8 LEFT JOIN `catalog_product_entity_tier_price` AS `tier_price_3` ON tier_price_3.row_id = e.row_id AND tier_price_3.all_groups = 1 AND tier_price_3.customer_grou |