SELECT
			NOW() AS `cached`,
			`cre`.`id`,
			IF(`cnt`.`pcr_modified` IS NOT NULL, `cnt`.`pcr_modified`, `cre`.`pdc_modified`) AS `modified`,
			`cre`.`pdc_parent` AS `parent`,
			`cre`.`pdc_number` AS `number`,
			`cre`.`pdc_package_unit` AS `package_unit`,
			`cre`.`pdc_layout` AS `layout`,
			IF(`cre`.`pdc_layout` = 'voucher', 'V', 'P') AS `species`,
			`cre`.`pdc_gtin` AS `gtin`,
			`cre`.`pdc_pzn` AS `pzn`,
			`cre`.`pdc_ppn` AS `ppn`,
			`cre`.`pdc_type` AS `type`,
			`cre`.`pdc_vegan` AS `vegan`,
			`cre`.`pdc_spf` AS `spf`,
			`ccl`.`ptx_name_short` AS `ccl`,
			`cln`.`ptx_name_short` AS `cln`,
			`cre`.`pdc_color` AS `colorid`,
			`cre`.`pdc_color_code` AS `color_code`,
			@name:=IF(`ccl`.`ptx_name_short` IS NOT NULL, `ccl`.`ptx_name_short`, IF(`cln`.`ptx_name_short` IS NOT NULL, `cln`.`ptx_name_short`, `cre`.`pdc_name`)) AS `name`,
			IF(`ccl`.`ptx_teaser` IS NOT NULL, `ccl`.`ptx_teaser`, `cln`.`ptx_teaser`) AS `teaser`,
			IF(`ccl`.`ptx_caption` IS NOT NULL, `ccl`.`ptx_caption`, `cln`.`ptx_caption`) AS `caption`,
			IF(`ccl`.`ptx_description` IS NOT NULL, `ccl`.`ptx_description`, `cln`.`ptx_description`) AS `description`,
			IF(`ccl`.`ptx_features` IS NOT NULL, `ccl`.`ptx_features`, `cln`.`ptx_features`) AS `features`,
			IF(`ccl`.`ptx_application` IS NOT NULL, `ccl`.`ptx_application`, `cln`.`ptx_application`) AS `application`,
			IF(`ccl`.`ptx_color` IS NOT NULL, `ccl`.`ptx_color`, `cln`.`ptx_color`) AS `color`,
			IF(`ccl`.`ptx_ingredient` IS NOT NULL, `ccl`.`ptx_ingredient`, `cln`.`ptx_ingredient`) AS `ingredient`,
			IF(`ccl`.`ptx_info` IS NOT NULL, `ccl`.`ptx_info`, `cln`.`ptx_info`) AS `info`,

			IF(`ing1`.`ingredients` IS NULL, `ing2`.`ingredients`, `ing1`.`ingredients`) AS `ingredient`,
			IF(`ing1`.`mapping` IS NULL, `ing2`.`mapping`, `ing1`.`mapping`) AS `incimapping`,

			IF(`ccl`.`ptx_whatis` IS NOT NULL, `ccl`.`ptx_whatis`, `cln`.`ptx_whatis`) AS `whatis`,
			IF(`ccl`.`ptx_forwhat` IS NOT NULL, `ccl`.`ptx_forwhat`, `cln`.`ptx_forwhat`) AS `forwhat`,
			IF(`ccl`.`ptx_why` IS NOT NULL, `ccl`.`ptx_why`, `cln`.`ptx_why`) AS `why`,

			IF(`ccl`.`ptx_html` IS NOT NULL, `ccl`.`ptx_html`, `cln`.`ptx_html`) AS `html`,
			IF(`ccl`.`ptx_seo_content1` IS NOT NULL, `ccl`.`ptx_seo_content1`, `cln`.`ptx_seo_content1`) AS `seo_content1`,
			IF(`ccl`.`ptx_seo_content2` IS NOT NULL, `ccl`.`ptx_seo_content2`, `cln`.`ptx_seo_content2`) AS `seo_content2`,
			IF(`ccl`.`ptx_synonym_extended` IS NOT NULL, `ccl`.`ptx_synonym_extended`, `cln`.`ptx_synonym_extended`) AS `synonyms`,
			IF(`ccl`.`ptx_meta_title` IS NOT NULL, `ccl`.`ptx_meta_title`, `cln`.`ptx_meta_title`) AS `meta_title`,
			IF(`ccl`.`ptx_meta_keywords` IS NOT NULL, `ccl`.`ptx_meta_keywords`, `cln`.`ptx_meta_keywords`) AS `meta_keywords`,
			IF(`ccl`.`ptx_meta_description` IS NOT NULL, `ccl`.`ptx_meta_description`, `cln`.`ptx_meta_description`) AS `meta_description`,
			IF(`ccl`.`ptx_video` IS NOT NULL, `ccl`.`ptx_video`, `cln`.`ptx_video`) AS `video`,
			@available:=IF(`cnt`.`pcr_available` IS NOT NULL, `cnt`.`pcr_available`, `cre`.`pdc_available`) AS `available`,
			@status:=`cnt`.`pcr_available_status` AS `status`,
			@available_status:=`cnt`.`pcr_available_status` AS `available_status`,
			@available_date:=`cnt`.`pcr_available_date` AS `available_date`,
			IF(`cnt`.`pcr_available_status` BETWEEN 1 AND 2 AND `cnt`.`pcr_available` BETWEEN 1 AND 4, 1, 0) AS `visible`, 
			1 AS `b2c`,
			1 AS `b2b`, 
			1 AS `b2d`, 
			`cre`.`pdc_brand` AS `brand`,

			@release:=IF(`cnt`.`pcr_release` IS NOT NULL, `cnt`.`pcr_release`, `cre`.`pdc_release`) AS `release`,
			@expires:=IF(`cnt`.`pcr_expires` IS NOT NULL, `cnt`.`pcr_expires`, `cre`.`pdc_expires`) AS `expires`,
			@new:=IF(`cnt`.`pcr_new` IS NOT NULL, `cnt`.`pcr_new`, `cre`.`pdc_new`) AS `new`,
			@giftidea:=IF(`cnt`.`pcr_giftidea` IS NOT NULL, `cnt`.`pcr_giftidea`, `cre`.`pdc_giftidea`) AS `giftidea`,
			@is_new:=IF((@new >= NOW() AND @new IS NOT NULL), 1, 0) AS `is_new`,
			IF(`cnt`.`pcr_subscribable` IS NOT NULL, `cnt`.`pcr_subscribable`, 1) AS `subscribable`,
			IF(`cnt`.`pcr_condition_type` IS NOT NULL, `cnt`.`pcr_condition_type`, 'bpoints') AS `condition_type`,
			IF(`cnt`.`pcr_condition_value` IS NOT NULL, `cnt`.`pcr_condition_value`, 2) AS `condition_value`,

			IF(`cnt`.`pcr_bpoints_multiplier` IS NOT NULL, `cnt`.`pcr_bpoints_multiplier`, 1) AS `multiplier`,

			`cnt`.`pcr_vat` AS `vat`,

			@is_price_discountable:=IF(`cnt`.`pcr_discountable` IS NOT NULL, `cnt`.`pcr_discountable`, `cre`.`pdc_discountable`) AS `is_price_discountable`, 
				@price_regular:=CAST(`cnt`.`pcr_price_regular` AS DECIMAL(8,2)) AS `price_regular`,
				@price_value:=CAST(
					IF(`cnt`.`pcr_price_value` IS NULL, 0, `cnt`.`pcr_price_value`) AS DECIMAL(8,2)) AS `price_value`,
				@is_promotion:=IF(`cnt`.`pcr_price_regular` < IF(`cnt`.`pcr_price_value` IS NULL, 0, `cnt`.`pcr_price_value`), 1, 0) AS `is_promotion`, 
				@price_calculate:=CAST(
					IF(`cnt`.`pcr_price_value` > IF(`cnt`.`pcr_price_value` IS NULL, 0, `cnt`.`pcr_price_value`), `cnt`.`pcr_price_value`, IF(`cnt`.`pcr_price_value` IS NULL, 0, `cnt`.`pcr_price_value`)) AS DECIMAL(8,2)) AS `price_calculate`,
				@price_offer:=CAST(
					IF(`cnt`.`pcr_price_offer` IS NULL, 0, `cnt`.`pcr_price_offer`) AS DECIMAL(8,2)) AS `price_offer`,
				@price_unit:=`cnt`.`pcr_price_unit` AS `price_unit`,
			
				`cnt`.`pcr_price_offer_release` AS `price_offer_release`,
				`cnt`.`pcr_price_offer_expires` AS `price_offer_expires`,

				@is_price_offer:=IF((`cnt`.`pcr_price_offer_release` < NOW() AND `cnt`.`pcr_price_offer_expires` > NOW() AND `cnt`.`pcr_price_offer` > 0 AND `cnt`.`pcr_price_offer` < `cnt`.`pcr_price_regular`), 1, 0) AS `is_price_offer`,
				@offer:=IF(@is_price_offer = 1, `cnt`.`pcr_price_offer_expires`, IF(`cnt`.`pcr_offer` IS NOT NULL, `cnt`.`pcr_offer`, `cre`.`pdc_offer`)) AS `offer`,
				@is_offer:=IF(((@offer >= NOW() AND @offer IS NOT NULL) OR @is_price_offer = 1), 1, 0) AS `is_offer`,

				@price:=CAST(IF(@is_price_offer = 1, @price_offer, @price_regular) AS DECIMAL(8,2)) AS `price`,
				@sortprice:=CAST(IF(@is_price_offer = 1, @price_offer, @price_regular) AS DECIMAL(8,2) ) AS `sortprice`,

				@price_advantage:=IF(((@is_price_offer = 1) OR (CAST(IF(`cnt`.`pcr_price_value` IS NULL, 0, `cnt`.`pcr_price_value`) AS DECIMAL(8,2)) > CAST(`cnt`.`pcr_price_regular` AS DECIMAL(8,2)))), 1, 0) AS `price_advantage`,
				@price_saving_amount_order:=0 AS `price_saving_amount_order`,
				@price_saving_amount_product:=0 AS `price_saving_amount_product`,
				@price_saving_amount:=IF(@price_advantage = 1, IF(@is_price_offer = 1, (@price_regular - @price_offer), (@price_calculate - @price)), 0) AS `price_saving_amount`,
				IF(@price_advantage = 1, ROUND(@price_saving_amount * 100 / IF(@is_price_offer = 1, @price_regular, @price_calculate)), 0) AS `price_saving_percent`,
				IF(@is_price_offer = 1, 5, 0) AS `discounted`, 
				IF(@is_price_offer = 0 AND @is_price_discountable = 1, 1, 0) AS `discountable`, 
				
				0 AS `freegoods_limit`, 
				0 AS `freegoods_quantity`, 
				NULL AS `freegoods_product`, 
				
				IF(@is_promotion = 1, 'promotion', IF(@is_offer = 1, 'sale', IF(@is_new = 1, 'new', IF(IF(`cnt`.`pcr_flaged` IS NOT NULL, `cnt`.`pcr_flaged`, `cre`.`pdc_flaged`) >= NOW(), IF(`cnt`.`pcr_flaged_type` IS NOT NULL, `cnt`.`pcr_flaged_type`, `cre`.`pdc_flaged_type`), NULL)))) AS `flagged`,

			`cnt`.`pcr_popularity` AS `popularity`,
			@amount:=`cre`.`pdc_amount` AS `amount`,
			@amount AS `contents`,
			`cre`.`pdc_measurement` AS `measurement`,
			`cre`.`pdc_divided` AS `divided`,
			`cre`.`pdc_weight` AS `weight`,
			`cre`.`pdc_volume` AS `volume`,
			`cre`.`pdc_dimensions_length` AS `dimensions_length`,
			`cre`.`pdc_dimensions_width` AS `dimensions_width`,
			`cre`.`pdc_dimensions_height` AS `dimensions_height`,

			`cre`.`pdc_hazardous_goods` AS `hazardous_goods`,
			`cre`.`pdc_hazardous_goods_weight` AS `hazardous_goods_weight`,
			`cre`.`pdc_hazardous_goods_class_road` AS `hazardous_goods_class_road`,
			`cre`.`pdc_hazardous_goods_class_sea` AS `hazardous_goods_class_sea`,
			`cre`.`pdc_hazardous_goods_class_air` AS `hazardous_goods_class_air`,
			`cre`.`pdc_origin_country` AS `origin_country`,
			`cre`.`pdc_customs_tariff_number` AS `customs_tariff_number`,

			@price_per_100:=ROUND(@price / @amount * 100, 2) AS `price_per_100`,
			@price_per_100 AS `unitprice`,
			IF(`pcc1`.`primary` IS NOT NULL, `pcc1`.`primary`, `pc1`.`primary`) AS `line`,
			@catcaption1:=`cl1`.`ctc_caption` AS `catcaption1`,
			@catalias1:=`cl1`.`ctc_alias` AS `catalias1`,
			@catcaption2:=`cl2`.`ctc_caption` AS `catcaption2`,
			@catalias2:=`cl2`.`ctc_alias` AS `catalias2`,
			@catcaption3:=`cl3`.`ctc_caption` AS `catcaption3`,
			@catalias3:=`cl3`.`ctc_alias` AS `catalias3`,
			CONCAT_WS('/', @catalias1, @catalias2, @catalias3) AS `path`,
			`cnt`.`pcr_redirect` AS `redirect`,
			`cnt`.`pcr_garnish_blocked` AS `garnish_blocked`, 
			`cnt`.`pcr_promos_blocked` AS `promos_blocked`, 
			CONCAT(IF(@catcaption3 IS NOT NULL, @catcaption3, IF(@catcaption2 IS NOT NULL, @catcaption2, IF(@catcaption1 IS NOT NULL, @catcaption1, ''))), ' ', @name) AS `longname`,
			TRIM(CONCAT_WS(' ', @catcaption1, @catcaption2, @catcaption3, @name)) AS `fullname`,
			IF(`cnt`.`pcr_index_A` IS NOT NULL, `cnt`.`pcr_index_A`, `cre`.`pdc_index_A`) AS `index_A`,
			IF(`cnt`.`pcr_index_B` IS NOT NULL, `cnt`.`pcr_index_B`, `cre`.`pdc_index_B`) AS `index_B`,
			IF(`cnt`.`pcr_index_C` IS NOT NULL, `cnt`.`pcr_index_C`, `cre`.`pdc_index_C`) AS `index_C`,
			IF(`cnt`.`pcr_index_D` IS NOT NULL, `cnt`.`pcr_index_D`, `cre`.`pdc_index_D`) AS `index_D`,
			IF(`gva`.`id` IS NULL, 0, 1) AS `giveaways`,
			`cre`.`pdc_folder` AS `folder`
		FROM
			`babordb001`.`tbl_products_core` AS `cre`
		LEFT JOIN
			`babordb001`.`tbl_products_country_b2c` AS `cnt`
		ON (
			`cnt`.`id` = `cre`.`id` AND
			`cnt`.`country` = 'US' 
			)
		
		LEFT JOIN
			`babordb001`.`tbl_products_ingredients` AS `ing1`
		ON (
			`ing1`.`id` = `cre`.`id` AND
			`ing1`.`language` = 'EN'
			)
		LEFT JOIN
			`babordb001`.`tbl_products_ingredients` AS `ing2`
		ON (
			`ing2`.`id` = `cre`.`id` AND
			`ing2`.`language` = 'EN'
			)
		LEFT JOIN
			`babordb001`.`tbl_products_giveaways` AS `gva`
		ON (
			`gva`.`id` = `cre`.`id` AND
			`gva`.`country` = 'US'
			)

		LEFT JOIN
			`babordb001`.`tbl_products_language` AS `cln`
		ON (
			`cln`.`id` = `cre`.`id` AND
			`cln`.`language` = 'EN'
			)
		LEFT JOIN
			`babordb001`.`tbl_products_country_language` AS `ccl`
		ON (
			`ccl`.`id` = `cre`.`id` AND
			`ccl`.`country` = 'US' AND
			`ccl`.`language` = 'EN'
			)
			
		LEFT JOIN
			`babordb001`.`tbl_products_categorize` AS `pc1` 
		ON (
			`pc1`.`id` = `cre`.`id` AND
			`pc1`.`type` = 'srn' 
			)
		LEFT JOIN
			`babordb001`.`tbl_products_categorize_country` AS `pcc1` 
		ON (
			`pcc1`.`id` = `cre`.`id` AND
			`pcc1`.`type` = 'srn' AND 
			`pcc1`.`country` = 'US' 
			)
		LEFT JOIN 
			`babordb001`.`tbl_categorize_language` AS `cl1` 
		ON (
			`cl1`.`id` = IF(`pcc1`.`primary` IS NOT NULL, `pcc1`.`primary` ,`pc1`.`primary`) AND
			`cl1`.`country` = 'US' AND
			`cl1`.`language` = 'EN'
			)
		LEFT JOIN
			`babordb001`.`tbl_products_categorize` AS `pc2` 
		ON (
			`pc2`.`id` = `cre`.`id` AND
			`pc2`.`type` = 'srn' 
			)
		LEFT JOIN
			`babordb001`.`tbl_products_categorize_country` AS `pcc2` 
		ON (
			`pcc2`.`id` = `cre`.`id` AND
			`pcc2`.`type` = 'srn' AND 
			`pcc2`.`country` = 'US' 
			)
		LEFT JOIN 
			`babordb001`.`tbl_categorize_language` AS `cl2` 
		ON (
			`cl2`.`id` = IF(`pcc2`.`secondary` IS NOT NULL, `pcc2`.`secondary` ,`pc2`.`secondary`) AND
			`cl2`.`country` = 'US' AND
			`cl2`.`language` = 'EN'
			)
		LEFT JOIN
			`babordb001`.`tbl_products_categorize` AS `pc3` 
		ON (
			`pc3`.`id` = `cre`.`id` AND
			`pc3`.`type` = 'srn' 
			)
		LEFT JOIN
			`babordb001`.`tbl_products_categorize_country` AS `pcc3` 
		ON (
			`pcc3`.`id` = `cre`.`id` AND
			`pcc3`.`type` = 'srn' AND 
			`pcc3`.`country` = 'US' 
			)
		LEFT JOIN 
			`babordb001`.`tbl_categorize_language` AS `cl3` 
		ON (
			`cl3`.`id` =  IF(`pcc3`.`tertiary` IS NOT NULL, `pcc3`.`tertiary` ,`pc3`.`tertiary`) AND
			`cl3`.`country` = 'US' AND
			`cl3`.`language` = 'EN'
			)
		WHERE
			( `cre`.`id` = 58759 ) AND
			
			(IF(`cnt`.`pcr_release` IS NOT NULL, `cnt`.`pcr_release`, `cre`.`pdc_release`) <= NOW() OR IF(`cnt`.`pcr_release` IS NOT NULL, `cnt`.`pcr_release`, `cre`.`pdc_release`) IS NULL) AND
			(IF(`cnt`.`pcr_expires` IS NOT NULL, `cnt`.`pcr_expires`, `cre`.`pdc_expires`) >= NOW() OR IF(`cnt`.`pcr_expires` IS NOT NULL, `cnt`.`pcr_expires`, `cre`.`pdc_expires`) IS NULL) AND
			(IF(`cnt`.`pcr_available` IS NOT NULL, `cnt`.`pcr_available`, `cre`.`pdc_available`) BETWEEN 1 AND 4) AND
			(`cnt`.`pcr_available_status` BETWEEN 1 AND 2) 
		GROUP BY
			`cre`.`id`
		LIMIT 1;
  • Mattifying Foundation 03 almond
  • Mattifying Foundation 03 almond
  • Mattifying Foundation 03 almond
  • Mattifying Foundation 03 almond
Mattifying Foundation 03 almond

AGE ID

Mattifying Foundation 03 almond

Contents: 30 ml


“Liquid powder” for a vibrant matte complexion.

Available

Color selection

    SELECT
    			NOW() AS `cached`,
    			`cre`.`id`,
    			IF(`cnt`.`pcr_modified` IS NOT NULL, `cnt`.`pcr_modified`, `cre`.`pdc_modified`) AS `modified`,
    			`cre`.`pdc_parent` AS `parent`,
    			`cre`.`pdc_number` AS `number`,
    			`cre`.`pdc_package_unit` AS `package_unit`,
    			`cre`.`pdc_layout` AS `layout`,
    			IF(`cre`.`pdc_layout` = 'voucher', 'V', 'P') AS `species`,
    			`cre`.`pdc_gtin` AS `gtin`,
    			`cre`.`pdc_pzn` AS `pzn`,
    			`cre`.`pdc_ppn` AS `ppn`,
    			`cre`.`pdc_type` AS `type`,
    			`cre`.`pdc_vegan` AS `vegan`,
    			`cre`.`pdc_spf` AS `spf`,
    			`ccl`.`ptx_name_short` AS `ccl`,
    			`cln`.`ptx_name_short` AS `cln`,
    			`cre`.`pdc_color` AS `colorid`,
    			`cre`.`pdc_color_code` AS `color_code`,
    			@name:=IF(`ccl`.`ptx_name_short` IS NOT NULL, `ccl`.`ptx_name_short`, IF(`cln`.`ptx_name_short` IS NOT NULL, `cln`.`ptx_name_short`, `cre`.`pdc_name`)) AS `name`,
    			IF(`ccl`.`ptx_teaser` IS NOT NULL, `ccl`.`ptx_teaser`, `cln`.`ptx_teaser`) AS `teaser`,
    			IF(`ccl`.`ptx_caption` IS NOT NULL, `ccl`.`ptx_caption`, `cln`.`ptx_caption`) AS `caption`,
    			IF(`ccl`.`ptx_description` IS NOT NULL, `ccl`.`ptx_description`, `cln`.`ptx_description`) AS `description`,
    			IF(`ccl`.`ptx_features` IS NOT NULL, `ccl`.`ptx_features`, `cln`.`ptx_features`) AS `features`,
    			IF(`ccl`.`ptx_application` IS NOT NULL, `ccl`.`ptx_application`, `cln`.`ptx_application`) AS `application`,
    			IF(`ccl`.`ptx_color` IS NOT NULL, `ccl`.`ptx_color`, `cln`.`ptx_color`) AS `color`,
    			IF(`ccl`.`ptx_ingredient` IS NOT NULL, `ccl`.`ptx_ingredient`, `cln`.`ptx_ingredient`) AS `ingredient`,
    			IF(`ccl`.`ptx_info` IS NOT NULL, `ccl`.`ptx_info`, `cln`.`ptx_info`) AS `info`,
    
    			IF(`ing1`.`ingredients` IS NULL, `ing2`.`ingredients`, `ing1`.`ingredients`) AS `ingredient`,
    			IF(`ing1`.`mapping` IS NULL, `ing2`.`mapping`, `ing1`.`mapping`) AS `incimapping`,
    
    			IF(`ccl`.`ptx_whatis` IS NOT NULL, `ccl`.`ptx_whatis`, `cln`.`ptx_whatis`) AS `whatis`,
    			IF(`ccl`.`ptx_forwhat` IS NOT NULL, `ccl`.`ptx_forwhat`, `cln`.`ptx_forwhat`) AS `forwhat`,
    			IF(`ccl`.`ptx_why` IS NOT NULL, `ccl`.`ptx_why`, `cln`.`ptx_why`) AS `why`,
    
    			IF(`ccl`.`ptx_html` IS NOT NULL, `ccl`.`ptx_html`, `cln`.`ptx_html`) AS `html`,
    			IF(`ccl`.`ptx_seo_content1` IS NOT NULL, `ccl`.`ptx_seo_content1`, `cln`.`ptx_seo_content1`) AS `seo_content1`,
    			IF(`ccl`.`ptx_seo_content2` IS NOT NULL, `ccl`.`ptx_seo_content2`, `cln`.`ptx_seo_content2`) AS `seo_content2`,
    			IF(`ccl`.`ptx_synonym_extended` IS NOT NULL, `ccl`.`ptx_synonym_extended`, `cln`.`ptx_synonym_extended`) AS `synonyms`,
    			IF(`ccl`.`ptx_meta_title` IS NOT NULL, `ccl`.`ptx_meta_title`, `cln`.`ptx_meta_title`) AS `meta_title`,
    			IF(`ccl`.`ptx_meta_keywords` IS NOT NULL, `ccl`.`ptx_meta_keywords`, `cln`.`ptx_meta_keywords`) AS `meta_keywords`,
    			IF(`ccl`.`ptx_meta_description` IS NOT NULL, `ccl`.`ptx_meta_description`, `cln`.`ptx_meta_description`) AS `meta_description`,
    			IF(`ccl`.`ptx_video` IS NOT NULL, `ccl`.`ptx_video`, `cln`.`ptx_video`) AS `video`,
    			@available:=IF(`cnt`.`pcr_available` IS NOT NULL, `cnt`.`pcr_available`, `cre`.`pdc_available`) AS `available`,
    			@status:=`cnt`.`pcr_available_status` AS `status`,
    			@available_status:=`cnt`.`pcr_available_status` AS `available_status`,
    			@available_date:=`cnt`.`pcr_available_date` AS `available_date`,
    			IF(`cnt`.`pcr_available_status` BETWEEN 1 AND 2 AND `cnt`.`pcr_available` BETWEEN 1 AND 4, 1, 0) AS `visible`, 
    			1 AS `b2c`,
    			1 AS `b2b`, 
    			1 AS `b2d`, 
    			`cre`.`pdc_brand` AS `brand`,
    
    			@release:=IF(`cnt`.`pcr_release` IS NOT NULL, `cnt`.`pcr_release`, `cre`.`pdc_release`) AS `release`,
    			@expires:=IF(`cnt`.`pcr_expires` IS NOT NULL, `cnt`.`pcr_expires`, `cre`.`pdc_expires`) AS `expires`,
    			@new:=IF(`cnt`.`pcr_new` IS NOT NULL, `cnt`.`pcr_new`, `cre`.`pdc_new`) AS `new`,
    			@giftidea:=IF(`cnt`.`pcr_giftidea` IS NOT NULL, `cnt`.`pcr_giftidea`, `cre`.`pdc_giftidea`) AS `giftidea`,
    			@is_new:=IF((@new >= NOW() AND @new IS NOT NULL), 1, 0) AS `is_new`,
    			IF(`cnt`.`pcr_subscribable` IS NOT NULL, `cnt`.`pcr_subscribable`, 1) AS `subscribable`,
    			IF(`cnt`.`pcr_condition_type` IS NOT NULL, `cnt`.`pcr_condition_type`, 'bpoints') AS `condition_type`,
    			IF(`cnt`.`pcr_condition_value` IS NOT NULL, `cnt`.`pcr_condition_value`, 2) AS `condition_value`,
    
    			IF(`cnt`.`pcr_bpoints_multiplier` IS NOT NULL, `cnt`.`pcr_bpoints_multiplier`, 1) AS `multiplier`,
    
    			`cnt`.`pcr_vat` AS `vat`,
    
    			@is_price_discountable:=IF(`cnt`.`pcr_discountable` IS NOT NULL, `cnt`.`pcr_discountable`, `cre`.`pdc_discountable`) AS `is_price_discountable`, 
    				@price_regular:=CAST(`cnt`.`pcr_price_regular` AS DECIMAL(8,2)) AS `price_regular`,
    				@price_value:=CAST(
    					IF(`cnt`.`pcr_price_value` IS NULL, 0, `cnt`.`pcr_price_value`) AS DECIMAL(8,2)) AS `price_value`,
    				@is_promotion:=IF(`cnt`.`pcr_price_regular` < IF(`cnt`.`pcr_price_value` IS NULL, 0, `cnt`.`pcr_price_value`), 1, 0) AS `is_promotion`, 
    				@price_calculate:=CAST(
    					IF(`cnt`.`pcr_price_value` > IF(`cnt`.`pcr_price_value` IS NULL, 0, `cnt`.`pcr_price_value`), `cnt`.`pcr_price_value`, IF(`cnt`.`pcr_price_value` IS NULL, 0, `cnt`.`pcr_price_value`)) AS DECIMAL(8,2)) AS `price_calculate`,
    				@price_offer:=CAST(
    					IF(`cnt`.`pcr_price_offer` IS NULL, 0, `cnt`.`pcr_price_offer`) AS DECIMAL(8,2)) AS `price_offer`,
    				@price_unit:=`cnt`.`pcr_price_unit` AS `price_unit`,
    			
    				`cnt`.`pcr_price_offer_release` AS `price_offer_release`,
    				`cnt`.`pcr_price_offer_expires` AS `price_offer_expires`,
    
    				@is_price_offer:=IF((`cnt`.`pcr_price_offer_release` < NOW() AND `cnt`.`pcr_price_offer_expires` > NOW() AND `cnt`.`pcr_price_offer` > 0 AND `cnt`.`pcr_price_offer` < `cnt`.`pcr_price_regular`), 1, 0) AS `is_price_offer`,
    				@offer:=IF(@is_price_offer = 1, `cnt`.`pcr_price_offer_expires`, IF(`cnt`.`pcr_offer` IS NOT NULL, `cnt`.`pcr_offer`, `cre`.`pdc_offer`)) AS `offer`,
    				@is_offer:=IF(((@offer >= NOW() AND @offer IS NOT NULL) OR @is_price_offer = 1), 1, 0) AS `is_offer`,
    
    				@price:=CAST(IF(@is_price_offer = 1, @price_offer, @price_regular) AS DECIMAL(8,2)) AS `price`,
    				@sortprice:=CAST(IF(@is_price_offer = 1, @price_offer, @price_regular) AS DECIMAL(8,2) ) AS `sortprice`,
    
    				@price_advantage:=IF(((@is_price_offer = 1) OR (CAST(IF(`cnt`.`pcr_price_value` IS NULL, 0, `cnt`.`pcr_price_value`) AS DECIMAL(8,2)) > CAST(`cnt`.`pcr_price_regular` AS DECIMAL(8,2)))), 1, 0) AS `price_advantage`,
    				@price_saving_amount_order:=0 AS `price_saving_amount_order`,
    				@price_saving_amount_product:=0 AS `price_saving_amount_product`,
    				@price_saving_amount:=IF(@price_advantage = 1, IF(@is_price_offer = 1, (@price_regular - @price_offer), (@price_calculate - @price)), 0) AS `price_saving_amount`,
    				IF(@price_advantage = 1, ROUND(@price_saving_amount * 100 / IF(@is_price_offer = 1, @price_regular, @price_calculate)), 0) AS `price_saving_percent`,
    				IF(@is_price_offer = 1, 5, 0) AS `discounted`, 
    				IF(@is_price_offer = 0 AND @is_price_discountable = 1, 1, 0) AS `discountable`, 
    				
    				0 AS `freegoods_limit`, 
    				0 AS `freegoods_quantity`, 
    				NULL AS `freegoods_product`, 
    				
    				IF(@is_promotion = 1, 'promotion', IF(@is_offer = 1, 'sale', IF(@is_new = 1, 'new', IF(IF(`cnt`.`pcr_flaged` IS NOT NULL, `cnt`.`pcr_flaged`, `cre`.`pdc_flaged`) >= NOW(), IF(`cnt`.`pcr_flaged_type` IS NOT NULL, `cnt`.`pcr_flaged_type`, `cre`.`pdc_flaged_type`), NULL)))) AS `flagged`,
    
    			`cnt`.`pcr_popularity` AS `popularity`,
    			@amount:=`cre`.`pdc_amount` AS `amount`,
    			@amount AS `contents`,
    			`cre`.`pdc_measurement` AS `measurement`,
    			`cre`.`pdc_divided` AS `divided`,
    			`cre`.`pdc_weight` AS `weight`,
    			`cre`.`pdc_volume` AS `volume`,
    			`cre`.`pdc_dimensions_length` AS `dimensions_length`,
    			`cre`.`pdc_dimensions_width` AS `dimensions_width`,
    			`cre`.`pdc_dimensions_height` AS `dimensions_height`,
    
    			`cre`.`pdc_hazardous_goods` AS `hazardous_goods`,
    			`cre`.`pdc_hazardous_goods_weight` AS `hazardous_goods_weight`,
    			`cre`.`pdc_hazardous_goods_class_road` AS `hazardous_goods_class_road`,
    			`cre`.`pdc_hazardous_goods_class_sea` AS `hazardous_goods_class_sea`,
    			`cre`.`pdc_hazardous_goods_class_air` AS `hazardous_goods_class_air`,
    			`cre`.`pdc_origin_country` AS `origin_country`,
    			`cre`.`pdc_customs_tariff_number` AS `customs_tariff_number`,
    
    			@price_per_100:=ROUND(@price / @amount * 100, 2) AS `price_per_100`,
    			@price_per_100 AS `unitprice`,
    			IF(`pcc1`.`primary` IS NOT NULL, `pcc1`.`primary`, `pc1`.`primary`) AS `line`,
    			@catcaption1:=`cl1`.`ctc_caption` AS `catcaption1`,
    			@catalias1:=`cl1`.`ctc_alias` AS `catalias1`,
    			@catcaption2:=`cl2`.`ctc_caption` AS `catcaption2`,
    			@catalias2:=`cl2`.`ctc_alias` AS `catalias2`,
    			@catcaption3:=`cl3`.`ctc_caption` AS `catcaption3`,
    			@catalias3:=`cl3`.`ctc_alias` AS `catalias3`,
    			CONCAT_WS('/', @catalias1, @catalias2, @catalias3) AS `path`,
    			`cnt`.`pcr_redirect` AS `redirect`,
    			`cnt`.`pcr_garnish_blocked` AS `garnish_blocked`, 
    			`cnt`.`pcr_promos_blocked` AS `promos_blocked`, 
    			CONCAT(IF(@catcaption3 IS NOT NULL, @catcaption3, IF(@catcaption2 IS NOT NULL, @catcaption2, IF(@catcaption1 IS NOT NULL, @catcaption1, ''))), ' ', @name) AS `longname`,
    			TRIM(CONCAT_WS(' ', @catcaption1, @catcaption2, @catcaption3, @name)) AS `fullname`,
    			IF(`cnt`.`pcr_index_A` IS NOT NULL, `cnt`.`pcr_index_A`, `cre`.`pdc_index_A`) AS `index_A`,
    			IF(`cnt`.`pcr_index_B` IS NOT NULL, `cnt`.`pcr_index_B`, `cre`.`pdc_index_B`) AS `index_B`,
    			IF(`cnt`.`pcr_index_C` IS NOT NULL, `cnt`.`pcr_index_C`, `cre`.`pdc_index_C`) AS `index_C`,
    			IF(`cnt`.`pcr_index_D` IS NOT NULL, `cnt`.`pcr_index_D`, `cre`.`pdc_index_D`) AS `index_D`,
    			IF(`gva`.`id` IS NULL, 0, 1) AS `giveaways`,
    			`cre`.`pdc_folder` AS `folder`
    		FROM
    			`babordb001`.`tbl_products_core` AS `cre`
    		LEFT JOIN
    			`babordb001`.`tbl_products_country_b2c` AS `cnt`
    		ON (
    			`cnt`.`id` = `cre`.`id` AND
    			`cnt`.`country` = 'US' 
    			)
    		
    		LEFT JOIN
    			`babordb001`.`tbl_products_ingredients` AS `ing1`
    		ON (
    			`ing1`.`id` = `cre`.`id` AND
    			`ing1`.`language` = 'EN'
    			)
    		LEFT JOIN
    			`babordb001`.`tbl_products_ingredients` AS `ing2`
    		ON (
    			`ing2`.`id` = `cre`.`id` AND
    			`ing2`.`language` = 'EN'
    			)
    		LEFT JOIN
    			`babordb001`.`tbl_products_giveaways` AS `gva`
    		ON (
    			`gva`.`id` = `cre`.`id` AND
    			`gva`.`country` = 'US'
    			)
    
    		LEFT JOIN
    			`babordb001`.`tbl_products_language` AS `cln`
    		ON (
    			`cln`.`id` = `cre`.`id` AND
    			`cln`.`language` = 'EN'
    			)
    		LEFT JOIN
    			`babordb001`.`tbl_products_country_language` AS `ccl`
    		ON (
    			`ccl`.`id` = `cre`.`id` AND
    			`ccl`.`country` = 'US' AND
    			`ccl`.`language` = 'EN'
    			)
    			
    		LEFT JOIN
    			`babordb001`.`tbl_products_categorize` AS `pc1` 
    		ON (
    			`pc1`.`id` = `cre`.`id` AND
    			`pc1`.`type` = 'srn' 
    			)
    		LEFT JOIN
    			`babordb001`.`tbl_products_categorize_country` AS `pcc1` 
    		ON (
    			`pcc1`.`id` = `cre`.`id` AND
    			`pcc1`.`type` = 'srn' AND 
    			`pcc1`.`country` = 'US' 
    			)
    		LEFT JOIN 
    			`babordb001`.`tbl_categorize_language` AS `cl1` 
    		ON (
    			`cl1`.`id` = IF(`pcc1`.`primary` IS NOT NULL, `pcc1`.`primary` ,`pc1`.`primary`) AND
    			`cl1`.`country` = 'US' AND
    			`cl1`.`language` = 'EN'
    			)
    		LEFT JOIN
    			`babordb001`.`tbl_products_categorize` AS `pc2` 
    		ON (
    			`pc2`.`id` = `cre`.`id` AND
    			`pc2`.`type` = 'srn' 
    			)
    		LEFT JOIN
    			`babordb001`.`tbl_products_categorize_country` AS `pcc2` 
    		ON (
    			`pcc2`.`id` = `cre`.`id` AND
    			`pcc2`.`type` = 'srn' AND 
    			`pcc2`.`country` = 'US' 
    			)
    		LEFT JOIN 
    			`babordb001`.`tbl_categorize_language` AS `cl2` 
    		ON (
    			`cl2`.`id` = IF(`pcc2`.`secondary` IS NOT NULL, `pcc2`.`secondary` ,`pc2`.`secondary`) AND
    			`cl2`.`country` = 'US' AND
    			`cl2`.`language` = 'EN'
    			)
    		LEFT JOIN
    			`babordb001`.`tbl_products_categorize` AS `pc3` 
    		ON (
    			`pc3`.`id` = `cre`.`id` AND
    			`pc3`.`type` = 'srn' 
    			)
    		LEFT JOIN
    			`babordb001`.`tbl_products_categorize_country` AS `pcc3` 
    		ON (
    			`pcc3`.`id` = `cre`.`id` AND
    			`pcc3`.`type` = 'srn' AND 
    			`pcc3`.`country` = 'US' 
    			)
    		LEFT JOIN 
    			`babordb001`.`tbl_categorize_language` AS `cl3` 
    		ON (
    			`cl3`.`id` =  IF(`pcc3`.`tertiary` IS NOT NULL, `pcc3`.`tertiary` ,`pc3`.`tertiary`) AND
    			`cl3`.`country` = 'US' AND
    			`cl3`.`language` = 'EN'
    			)
    		WHERE
    			( `cre`.`id` = 58757 ) AND
    			
    			(IF(`cnt`.`pcr_release` IS NOT NULL, `cnt`.`pcr_release`, `cre`.`pdc_release`) <= NOW() OR IF(`cnt`.`pcr_release` IS NOT NULL, `cnt`.`pcr_release`, `cre`.`pdc_release`) IS NULL) AND
    			(IF(`cnt`.`pcr_expires` IS NOT NULL, `cnt`.`pcr_expires`, `cre`.`pdc_expires`) >= NOW() OR IF(`cnt`.`pcr_expires` IS NOT NULL, `cnt`.`pcr_expires`, `cre`.`pdc_expires`) IS NULL) AND
    			(IF(`cnt`.`pcr_available` IS NOT NULL, `cnt`.`pcr_available`, `cre`.`pdc_available`) BETWEEN 1 AND 4) AND
    			(`cnt`.`pcr_available_status` BETWEEN 1 AND 2) 
    		GROUP BY
    			`cre`.`id`
    		LIMIT 1;
    
  • 101
  • SELECT
    			NOW() AS `cached`,
    			`cre`.`id`,
    			IF(`cnt`.`pcr_modified` IS NOT NULL, `cnt`.`pcr_modified`, `cre`.`pdc_modified`) AS `modified`,
    			`cre`.`pdc_parent` AS `parent`,
    			`cre`.`pdc_number` AS `number`,
    			`cre`.`pdc_package_unit` AS `package_unit`,
    			`cre`.`pdc_layout` AS `layout`,
    			IF(`cre`.`pdc_layout` = 'voucher', 'V', 'P') AS `species`,
    			`cre`.`pdc_gtin` AS `gtin`,
    			`cre`.`pdc_pzn` AS `pzn`,
    			`cre`.`pdc_ppn` AS `ppn`,
    			`cre`.`pdc_type` AS `type`,
    			`cre`.`pdc_vegan` AS `vegan`,
    			`cre`.`pdc_spf` AS `spf`,
    			`ccl`.`ptx_name_short` AS `ccl`,
    			`cln`.`ptx_name_short` AS `cln`,
    			`cre`.`pdc_color` AS `colorid`,
    			`cre`.`pdc_color_code` AS `color_code`,
    			@name:=IF(`ccl`.`ptx_name_short` IS NOT NULL, `ccl`.`ptx_name_short`, IF(`cln`.`ptx_name_short` IS NOT NULL, `cln`.`ptx_name_short`, `cre`.`pdc_name`)) AS `name`,
    			IF(`ccl`.`ptx_teaser` IS NOT NULL, `ccl`.`ptx_teaser`, `cln`.`ptx_teaser`) AS `teaser`,
    			IF(`ccl`.`ptx_caption` IS NOT NULL, `ccl`.`ptx_caption`, `cln`.`ptx_caption`) AS `caption`,
    			IF(`ccl`.`ptx_description` IS NOT NULL, `ccl`.`ptx_description`, `cln`.`ptx_description`) AS `description`,
    			IF(`ccl`.`ptx_features` IS NOT NULL, `ccl`.`ptx_features`, `cln`.`ptx_features`) AS `features`,
    			IF(`ccl`.`ptx_application` IS NOT NULL, `ccl`.`ptx_application`, `cln`.`ptx_application`) AS `application`,
    			IF(`ccl`.`ptx_color` IS NOT NULL, `ccl`.`ptx_color`, `cln`.`ptx_color`) AS `color`,
    			IF(`ccl`.`ptx_ingredient` IS NOT NULL, `ccl`.`ptx_ingredient`, `cln`.`ptx_ingredient`) AS `ingredient`,
    			IF(`ccl`.`ptx_info` IS NOT NULL, `ccl`.`ptx_info`, `cln`.`ptx_info`) AS `info`,
    
    			IF(`ing1`.`ingredients` IS NULL, `ing2`.`ingredients`, `ing1`.`ingredients`) AS `ingredient`,
    			IF(`ing1`.`mapping` IS NULL, `ing2`.`mapping`, `ing1`.`mapping`) AS `incimapping`,
    
    			IF(`ccl`.`ptx_whatis` IS NOT NULL, `ccl`.`ptx_whatis`, `cln`.`ptx_whatis`) AS `whatis`,
    			IF(`ccl`.`ptx_forwhat` IS NOT NULL, `ccl`.`ptx_forwhat`, `cln`.`ptx_forwhat`) AS `forwhat`,
    			IF(`ccl`.`ptx_why` IS NOT NULL, `ccl`.`ptx_why`, `cln`.`ptx_why`) AS `why`,
    
    			IF(`ccl`.`ptx_html` IS NOT NULL, `ccl`.`ptx_html`, `cln`.`ptx_html`) AS `html`,
    			IF(`ccl`.`ptx_seo_content1` IS NOT NULL, `ccl`.`ptx_seo_content1`, `cln`.`ptx_seo_content1`) AS `seo_content1`,
    			IF(`ccl`.`ptx_seo_content2` IS NOT NULL, `ccl`.`ptx_seo_content2`, `cln`.`ptx_seo_content2`) AS `seo_content2`,
    			IF(`ccl`.`ptx_synonym_extended` IS NOT NULL, `ccl`.`ptx_synonym_extended`, `cln`.`ptx_synonym_extended`) AS `synonyms`,
    			IF(`ccl`.`ptx_meta_title` IS NOT NULL, `ccl`.`ptx_meta_title`, `cln`.`ptx_meta_title`) AS `meta_title`,
    			IF(`ccl`.`ptx_meta_keywords` IS NOT NULL, `ccl`.`ptx_meta_keywords`, `cln`.`ptx_meta_keywords`) AS `meta_keywords`,
    			IF(`ccl`.`ptx_meta_description` IS NOT NULL, `ccl`.`ptx_meta_description`, `cln`.`ptx_meta_description`) AS `meta_description`,
    			IF(`ccl`.`ptx_video` IS NOT NULL, `ccl`.`ptx_video`, `cln`.`ptx_video`) AS `video`,
    			@available:=IF(`cnt`.`pcr_available` IS NOT NULL, `cnt`.`pcr_available`, `cre`.`pdc_available`) AS `available`,
    			@status:=`cnt`.`pcr_available_status` AS `status`,
    			@available_status:=`cnt`.`pcr_available_status` AS `available_status`,
    			@available_date:=`cnt`.`pcr_available_date` AS `available_date`,
    			IF(`cnt`.`pcr_available_status` BETWEEN 1 AND 2 AND `cnt`.`pcr_available` BETWEEN 1 AND 4, 1, 0) AS `visible`, 
    			1 AS `b2c`,
    			1 AS `b2b`, 
    			1 AS `b2d`, 
    			`cre`.`pdc_brand` AS `brand`,
    
    			@release:=IF(`cnt`.`pcr_release` IS NOT NULL, `cnt`.`pcr_release`, `cre`.`pdc_release`) AS `release`,
    			@expires:=IF(`cnt`.`pcr_expires` IS NOT NULL, `cnt`.`pcr_expires`, `cre`.`pdc_expires`) AS `expires`,
    			@new:=IF(`cnt`.`pcr_new` IS NOT NULL, `cnt`.`pcr_new`, `cre`.`pdc_new`) AS `new`,
    			@giftidea:=IF(`cnt`.`pcr_giftidea` IS NOT NULL, `cnt`.`pcr_giftidea`, `cre`.`pdc_giftidea`) AS `giftidea`,
    			@is_new:=IF((@new >= NOW() AND @new IS NOT NULL), 1, 0) AS `is_new`,
    			IF(`cnt`.`pcr_subscribable` IS NOT NULL, `cnt`.`pcr_subscribable`, 1) AS `subscribable`,
    			IF(`cnt`.`pcr_condition_type` IS NOT NULL, `cnt`.`pcr_condition_type`, 'bpoints') AS `condition_type`,
    			IF(`cnt`.`pcr_condition_value` IS NOT NULL, `cnt`.`pcr_condition_value`, 2) AS `condition_value`,
    
    			IF(`cnt`.`pcr_bpoints_multiplier` IS NOT NULL, `cnt`.`pcr_bpoints_multiplier`, 1) AS `multiplier`,
    
    			`cnt`.`pcr_vat` AS `vat`,
    
    			@is_price_discountable:=IF(`cnt`.`pcr_discountable` IS NOT NULL, `cnt`.`pcr_discountable`, `cre`.`pdc_discountable`) AS `is_price_discountable`, 
    				@price_regular:=CAST(`cnt`.`pcr_price_regular` AS DECIMAL(8,2)) AS `price_regular`,
    				@price_value:=CAST(
    					IF(`cnt`.`pcr_price_value` IS NULL, 0, `cnt`.`pcr_price_value`) AS DECIMAL(8,2)) AS `price_value`,
    				@is_promotion:=IF(`cnt`.`pcr_price_regular` < IF(`cnt`.`pcr_price_value` IS NULL, 0, `cnt`.`pcr_price_value`), 1, 0) AS `is_promotion`, 
    				@price_calculate:=CAST(
    					IF(`cnt`.`pcr_price_value` > IF(`cnt`.`pcr_price_value` IS NULL, 0, `cnt`.`pcr_price_value`), `cnt`.`pcr_price_value`, IF(`cnt`.`pcr_price_value` IS NULL, 0, `cnt`.`pcr_price_value`)) AS DECIMAL(8,2)) AS `price_calculate`,
    				@price_offer:=CAST(
    					IF(`cnt`.`pcr_price_offer` IS NULL, 0, `cnt`.`pcr_price_offer`) AS DECIMAL(8,2)) AS `price_offer`,
    				@price_unit:=`cnt`.`pcr_price_unit` AS `price_unit`,
    			
    				`cnt`.`pcr_price_offer_release` AS `price_offer_release`,
    				`cnt`.`pcr_price_offer_expires` AS `price_offer_expires`,
    
    				@is_price_offer:=IF((`cnt`.`pcr_price_offer_release` < NOW() AND `cnt`.`pcr_price_offer_expires` > NOW() AND `cnt`.`pcr_price_offer` > 0 AND `cnt`.`pcr_price_offer` < `cnt`.`pcr_price_regular`), 1, 0) AS `is_price_offer`,
    				@offer:=IF(@is_price_offer = 1, `cnt`.`pcr_price_offer_expires`, IF(`cnt`.`pcr_offer` IS NOT NULL, `cnt`.`pcr_offer`, `cre`.`pdc_offer`)) AS `offer`,
    				@is_offer:=IF(((@offer >= NOW() AND @offer IS NOT NULL) OR @is_price_offer = 1), 1, 0) AS `is_offer`,
    
    				@price:=CAST(IF(@is_price_offer = 1, @price_offer, @price_regular) AS DECIMAL(8,2)) AS `price`,
    				@sortprice:=CAST(IF(@is_price_offer = 1, @price_offer, @price_regular) AS DECIMAL(8,2) ) AS `sortprice`,
    
    				@price_advantage:=IF(((@is_price_offer = 1) OR (CAST(IF(`cnt`.`pcr_price_value` IS NULL, 0, `cnt`.`pcr_price_value`) AS DECIMAL(8,2)) > CAST(`cnt`.`pcr_price_regular` AS DECIMAL(8,2)))), 1, 0) AS `price_advantage`,
    				@price_saving_amount_order:=0 AS `price_saving_amount_order`,
    				@price_saving_amount_product:=0 AS `price_saving_amount_product`,
    				@price_saving_amount:=IF(@price_advantage = 1, IF(@is_price_offer = 1, (@price_regular - @price_offer), (@price_calculate - @price)), 0) AS `price_saving_amount`,
    				IF(@price_advantage = 1, ROUND(@price_saving_amount * 100 / IF(@is_price_offer = 1, @price_regular, @price_calculate)), 0) AS `price_saving_percent`,
    				IF(@is_price_offer = 1, 5, 0) AS `discounted`, 
    				IF(@is_price_offer = 0 AND @is_price_discountable = 1, 1, 0) AS `discountable`, 
    				
    				0 AS `freegoods_limit`, 
    				0 AS `freegoods_quantity`, 
    				NULL AS `freegoods_product`, 
    				
    				IF(@is_promotion = 1, 'promotion', IF(@is_offer = 1, 'sale', IF(@is_new = 1, 'new', IF(IF(`cnt`.`pcr_flaged` IS NOT NULL, `cnt`.`pcr_flaged`, `cre`.`pdc_flaged`) >= NOW(), IF(`cnt`.`pcr_flaged_type` IS NOT NULL, `cnt`.`pcr_flaged_type`, `cre`.`pdc_flaged_type`), NULL)))) AS `flagged`,
    
    			`cnt`.`pcr_popularity` AS `popularity`,
    			@amount:=`cre`.`pdc_amount` AS `amount`,
    			@amount AS `contents`,
    			`cre`.`pdc_measurement` AS `measurement`,
    			`cre`.`pdc_divided` AS `divided`,
    			`cre`.`pdc_weight` AS `weight`,
    			`cre`.`pdc_volume` AS `volume`,
    			`cre`.`pdc_dimensions_length` AS `dimensions_length`,
    			`cre`.`pdc_dimensions_width` AS `dimensions_width`,
    			`cre`.`pdc_dimensions_height` AS `dimensions_height`,
    
    			`cre`.`pdc_hazardous_goods` AS `hazardous_goods`,
    			`cre`.`pdc_hazardous_goods_weight` AS `hazardous_goods_weight`,
    			`cre`.`pdc_hazardous_goods_class_road` AS `hazardous_goods_class_road`,
    			`cre`.`pdc_hazardous_goods_class_sea` AS `hazardous_goods_class_sea`,
    			`cre`.`pdc_hazardous_goods_class_air` AS `hazardous_goods_class_air`,
    			`cre`.`pdc_origin_country` AS `origin_country`,
    			`cre`.`pdc_customs_tariff_number` AS `customs_tariff_number`,
    
    			@price_per_100:=ROUND(@price / @amount * 100, 2) AS `price_per_100`,
    			@price_per_100 AS `unitprice`,
    			IF(`pcc1`.`primary` IS NOT NULL, `pcc1`.`primary`, `pc1`.`primary`) AS `line`,
    			@catcaption1:=`cl1`.`ctc_caption` AS `catcaption1`,
    			@catalias1:=`cl1`.`ctc_alias` AS `catalias1`,
    			@catcaption2:=`cl2`.`ctc_caption` AS `catcaption2`,
    			@catalias2:=`cl2`.`ctc_alias` AS `catalias2`,
    			@catcaption3:=`cl3`.`ctc_caption` AS `catcaption3`,
    			@catalias3:=`cl3`.`ctc_alias` AS `catalias3`,
    			CONCAT_WS('/', @catalias1, @catalias2, @catalias3) AS `path`,
    			`cnt`.`pcr_redirect` AS `redirect`,
    			`cnt`.`pcr_garnish_blocked` AS `garnish_blocked`, 
    			`cnt`.`pcr_promos_blocked` AS `promos_blocked`, 
    			CONCAT(IF(@catcaption3 IS NOT NULL, @catcaption3, IF(@catcaption2 IS NOT NULL, @catcaption2, IF(@catcaption1 IS NOT NULL, @catcaption1, ''))), ' ', @name) AS `longname`,
    			TRIM(CONCAT_WS(' ', @catcaption1, @catcaption2, @catcaption3, @name)) AS `fullname`,
    			IF(`cnt`.`pcr_index_A` IS NOT NULL, `cnt`.`pcr_index_A`, `cre`.`pdc_index_A`) AS `index_A`,
    			IF(`cnt`.`pcr_index_B` IS NOT NULL, `cnt`.`pcr_index_B`, `cre`.`pdc_index_B`) AS `index_B`,
    			IF(`cnt`.`pcr_index_C` IS NOT NULL, `cnt`.`pcr_index_C`, `cre`.`pdc_index_C`) AS `index_C`,
    			IF(`cnt`.`pcr_index_D` IS NOT NULL, `cnt`.`pcr_index_D`, `cre`.`pdc_index_D`) AS `index_D`,
    			IF(`gva`.`id` IS NULL, 0, 1) AS `giveaways`,
    			`cre`.`pdc_folder` AS `folder`
    		FROM
    			`babordb001`.`tbl_products_core` AS `cre`
    		LEFT JOIN
    			`babordb001`.`tbl_products_country_b2c` AS `cnt`
    		ON (
    			`cnt`.`id` = `cre`.`id` AND
    			`cnt`.`country` = 'US' 
    			)
    		
    		LEFT JOIN
    			`babordb001`.`tbl_products_ingredients` AS `ing1`
    		ON (
    			`ing1`.`id` = `cre`.`id` AND
    			`ing1`.`language` = 'EN'
    			)
    		LEFT JOIN
    			`babordb001`.`tbl_products_ingredients` AS `ing2`
    		ON (
    			`ing2`.`id` = `cre`.`id` AND
    			`ing2`.`language` = 'EN'
    			)
    		LEFT JOIN
    			`babordb001`.`tbl_products_giveaways` AS `gva`
    		ON (
    			`gva`.`id` = `cre`.`id` AND
    			`gva`.`country` = 'US'
    			)
    
    		LEFT JOIN
    			`babordb001`.`tbl_products_language` AS `cln`
    		ON (
    			`cln`.`id` = `cre`.`id` AND
    			`cln`.`language` = 'EN'
    			)
    		LEFT JOIN
    			`babordb001`.`tbl_products_country_language` AS `ccl`
    		ON (
    			`ccl`.`id` = `cre`.`id` AND
    			`ccl`.`country` = 'US' AND
    			`ccl`.`language` = 'EN'
    			)
    			
    		LEFT JOIN
    			`babordb001`.`tbl_products_categorize` AS `pc1` 
    		ON (
    			`pc1`.`id` = `cre`.`id` AND
    			`pc1`.`type` = 'srn' 
    			)
    		LEFT JOIN
    			`babordb001`.`tbl_products_categorize_country` AS `pcc1` 
    		ON (
    			`pcc1`.`id` = `cre`.`id` AND
    			`pcc1`.`type` = 'srn' AND 
    			`pcc1`.`country` = 'US' 
    			)
    		LEFT JOIN 
    			`babordb001`.`tbl_categorize_language` AS `cl1` 
    		ON (
    			`cl1`.`id` = IF(`pcc1`.`primary` IS NOT NULL, `pcc1`.`primary` ,`pc1`.`primary`) AND
    			`cl1`.`country` = 'US' AND
    			`cl1`.`language` = 'EN'
    			)
    		LEFT JOIN
    			`babordb001`.`tbl_products_categorize` AS `pc2` 
    		ON (
    			`pc2`.`id` = `cre`.`id` AND
    			`pc2`.`type` = 'srn' 
    			)
    		LEFT JOIN
    			`babordb001`.`tbl_products_categorize_country` AS `pcc2` 
    		ON (
    			`pcc2`.`id` = `cre`.`id` AND
    			`pcc2`.`type` = 'srn' AND 
    			`pcc2`.`country` = 'US' 
    			)
    		LEFT JOIN 
    			`babordb001`.`tbl_categorize_language` AS `cl2` 
    		ON (
    			`cl2`.`id` = IF(`pcc2`.`secondary` IS NOT NULL, `pcc2`.`secondary` ,`pc2`.`secondary`) AND
    			`cl2`.`country` = 'US' AND
    			`cl2`.`language` = 'EN'
    			)
    		LEFT JOIN
    			`babordb001`.`tbl_products_categorize` AS `pc3` 
    		ON (
    			`pc3`.`id` = `cre`.`id` AND
    			`pc3`.`type` = 'srn' 
    			)
    		LEFT JOIN
    			`babordb001`.`tbl_products_categorize_country` AS `pcc3` 
    		ON (
    			`pcc3`.`id` = `cre`.`id` AND
    			`pcc3`.`type` = 'srn' AND 
    			`pcc3`.`country` = 'US' 
    			)
    		LEFT JOIN 
    			`babordb001`.`tbl_categorize_language` AS `cl3` 
    		ON (
    			`cl3`.`id` =  IF(`pcc3`.`tertiary` IS NOT NULL, `pcc3`.`tertiary` ,`pc3`.`tertiary`) AND
    			`cl3`.`country` = 'US' AND
    			`cl3`.`language` = 'EN'
    			)
    		WHERE
    			( `cre`.`id` = 58758 ) AND
    			
    			(IF(`cnt`.`pcr_release` IS NOT NULL, `cnt`.`pcr_release`, `cre`.`pdc_release`) <= NOW() OR IF(`cnt`.`pcr_release` IS NOT NULL, `cnt`.`pcr_release`, `cre`.`pdc_release`) IS NULL) AND
    			(IF(`cnt`.`pcr_expires` IS NOT NULL, `cnt`.`pcr_expires`, `cre`.`pdc_expires`) >= NOW() OR IF(`cnt`.`pcr_expires` IS NOT NULL, `cnt`.`pcr_expires`, `cre`.`pdc_expires`) IS NULL) AND
    			(IF(`cnt`.`pcr_available` IS NOT NULL, `cnt`.`pcr_available`, `cre`.`pdc_available`) BETWEEN 1 AND 4) AND
    			(`cnt`.`pcr_available_status` BETWEEN 1 AND 2) 
    		GROUP BY
    			`cre`.`id`
    		LIMIT 1;
    
  • 202
  • SELECT
    			NOW() AS `cached`,
    			`cre`.`id`,
    			IF(`cnt`.`pcr_modified` IS NOT NULL, `cnt`.`pcr_modified`, `cre`.`pdc_modified`) AS `modified`,
    			`cre`.`pdc_parent` AS `parent`,
    			`cre`.`pdc_number` AS `number`,
    			`cre`.`pdc_package_unit` AS `package_unit`,
    			`cre`.`pdc_layout` AS `layout`,
    			IF(`cre`.`pdc_layout` = 'voucher', 'V', 'P') AS `species`,
    			`cre`.`pdc_gtin` AS `gtin`,
    			`cre`.`pdc_pzn` AS `pzn`,
    			`cre`.`pdc_ppn` AS `ppn`,
    			`cre`.`pdc_type` AS `type`,
    			`cre`.`pdc_vegan` AS `vegan`,
    			`cre`.`pdc_spf` AS `spf`,
    			`ccl`.`ptx_name_short` AS `ccl`,
    			`cln`.`ptx_name_short` AS `cln`,
    			`cre`.`pdc_color` AS `colorid`,
    			`cre`.`pdc_color_code` AS `color_code`,
    			@name:=IF(`ccl`.`ptx_name_short` IS NOT NULL, `ccl`.`ptx_name_short`, IF(`cln`.`ptx_name_short` IS NOT NULL, `cln`.`ptx_name_short`, `cre`.`pdc_name`)) AS `name`,
    			IF(`ccl`.`ptx_teaser` IS NOT NULL, `ccl`.`ptx_teaser`, `cln`.`ptx_teaser`) AS `teaser`,
    			IF(`ccl`.`ptx_caption` IS NOT NULL, `ccl`.`ptx_caption`, `cln`.`ptx_caption`) AS `caption`,
    			IF(`ccl`.`ptx_description` IS NOT NULL, `ccl`.`ptx_description`, `cln`.`ptx_description`) AS `description`,
    			IF(`ccl`.`ptx_features` IS NOT NULL, `ccl`.`ptx_features`, `cln`.`ptx_features`) AS `features`,
    			IF(`ccl`.`ptx_application` IS NOT NULL, `ccl`.`ptx_application`, `cln`.`ptx_application`) AS `application`,
    			IF(`ccl`.`ptx_color` IS NOT NULL, `ccl`.`ptx_color`, `cln`.`ptx_color`) AS `color`,
    			IF(`ccl`.`ptx_ingredient` IS NOT NULL, `ccl`.`ptx_ingredient`, `cln`.`ptx_ingredient`) AS `ingredient`,
    			IF(`ccl`.`ptx_info` IS NOT NULL, `ccl`.`ptx_info`, `cln`.`ptx_info`) AS `info`,
    
    			IF(`ing1`.`ingredients` IS NULL, `ing2`.`ingredients`, `ing1`.`ingredients`) AS `ingredient`,
    			IF(`ing1`.`mapping` IS NULL, `ing2`.`mapping`, `ing1`.`mapping`) AS `incimapping`,
    
    			IF(`ccl`.`ptx_whatis` IS NOT NULL, `ccl`.`ptx_whatis`, `cln`.`ptx_whatis`) AS `whatis`,
    			IF(`ccl`.`ptx_forwhat` IS NOT NULL, `ccl`.`ptx_forwhat`, `cln`.`ptx_forwhat`) AS `forwhat`,
    			IF(`ccl`.`ptx_why` IS NOT NULL, `ccl`.`ptx_why`, `cln`.`ptx_why`) AS `why`,
    
    			IF(`ccl`.`ptx_html` IS NOT NULL, `ccl`.`ptx_html`, `cln`.`ptx_html`) AS `html`,
    			IF(`ccl`.`ptx_seo_content1` IS NOT NULL, `ccl`.`ptx_seo_content1`, `cln`.`ptx_seo_content1`) AS `seo_content1`,
    			IF(`ccl`.`ptx_seo_content2` IS NOT NULL, `ccl`.`ptx_seo_content2`, `cln`.`ptx_seo_content2`) AS `seo_content2`,
    			IF(`ccl`.`ptx_synonym_extended` IS NOT NULL, `ccl`.`ptx_synonym_extended`, `cln`.`ptx_synonym_extended`) AS `synonyms`,
    			IF(`ccl`.`ptx_meta_title` IS NOT NULL, `ccl`.`ptx_meta_title`, `cln`.`ptx_meta_title`) AS `meta_title`,
    			IF(`ccl`.`ptx_meta_keywords` IS NOT NULL, `ccl`.`ptx_meta_keywords`, `cln`.`ptx_meta_keywords`) AS `meta_keywords`,
    			IF(`ccl`.`ptx_meta_description` IS NOT NULL, `ccl`.`ptx_meta_description`, `cln`.`ptx_meta_description`) AS `meta_description`,
    			IF(`ccl`.`ptx_video` IS NOT NULL, `ccl`.`ptx_video`, `cln`.`ptx_video`) AS `video`,
    			@available:=IF(`cnt`.`pcr_available` IS NOT NULL, `cnt`.`pcr_available`, `cre`.`pdc_available`) AS `available`,
    			@status:=`cnt`.`pcr_available_status` AS `status`,
    			@available_status:=`cnt`.`pcr_available_status` AS `available_status`,
    			@available_date:=`cnt`.`pcr_available_date` AS `available_date`,
    			IF(`cnt`.`pcr_available_status` BETWEEN 1 AND 2 AND `cnt`.`pcr_available` BETWEEN 1 AND 4, 1, 0) AS `visible`, 
    			1 AS `b2c`,
    			1 AS `b2b`, 
    			1 AS `b2d`, 
    			`cre`.`pdc_brand` AS `brand`,
    
    			@release:=IF(`cnt`.`pcr_release` IS NOT NULL, `cnt`.`pcr_release`, `cre`.`pdc_release`) AS `release`,
    			@expires:=IF(`cnt`.`pcr_expires` IS NOT NULL, `cnt`.`pcr_expires`, `cre`.`pdc_expires`) AS `expires`,
    			@new:=IF(`cnt`.`pcr_new` IS NOT NULL, `cnt`.`pcr_new`, `cre`.`pdc_new`) AS `new`,
    			@giftidea:=IF(`cnt`.`pcr_giftidea` IS NOT NULL, `cnt`.`pcr_giftidea`, `cre`.`pdc_giftidea`) AS `giftidea`,
    			@is_new:=IF((@new >= NOW() AND @new IS NOT NULL), 1, 0) AS `is_new`,
    			IF(`cnt`.`pcr_subscribable` IS NOT NULL, `cnt`.`pcr_subscribable`, 1) AS `subscribable`,
    			IF(`cnt`.`pcr_condition_type` IS NOT NULL, `cnt`.`pcr_condition_type`, 'bpoints') AS `condition_type`,
    			IF(`cnt`.`pcr_condition_value` IS NOT NULL, `cnt`.`pcr_condition_value`, 2) AS `condition_value`,
    
    			IF(`cnt`.`pcr_bpoints_multiplier` IS NOT NULL, `cnt`.`pcr_bpoints_multiplier`, 1) AS `multiplier`,
    
    			`cnt`.`pcr_vat` AS `vat`,
    
    			@is_price_discountable:=IF(`cnt`.`pcr_discountable` IS NOT NULL, `cnt`.`pcr_discountable`, `cre`.`pdc_discountable`) AS `is_price_discountable`, 
    				@price_regular:=CAST(`cnt`.`pcr_price_regular` AS DECIMAL(8,2)) AS `price_regular`,
    				@price_value:=CAST(
    					IF(`cnt`.`pcr_price_value` IS NULL, 0, `cnt`.`pcr_price_value`) AS DECIMAL(8,2)) AS `price_value`,
    				@is_promotion:=IF(`cnt`.`pcr_price_regular` < IF(`cnt`.`pcr_price_value` IS NULL, 0, `cnt`.`pcr_price_value`), 1, 0) AS `is_promotion`, 
    				@price_calculate:=CAST(
    					IF(`cnt`.`pcr_price_value` > IF(`cnt`.`pcr_price_value` IS NULL, 0, `cnt`.`pcr_price_value`), `cnt`.`pcr_price_value`, IF(`cnt`.`pcr_price_value` IS NULL, 0, `cnt`.`pcr_price_value`)) AS DECIMAL(8,2)) AS `price_calculate`,
    				@price_offer:=CAST(
    					IF(`cnt`.`pcr_price_offer` IS NULL, 0, `cnt`.`pcr_price_offer`) AS DECIMAL(8,2)) AS `price_offer`,
    				@price_unit:=`cnt`.`pcr_price_unit` AS `price_unit`,
    			
    				`cnt`.`pcr_price_offer_release` AS `price_offer_release`,
    				`cnt`.`pcr_price_offer_expires` AS `price_offer_expires`,
    
    				@is_price_offer:=IF((`cnt`.`pcr_price_offer_release` < NOW() AND `cnt`.`pcr_price_offer_expires` > NOW() AND `cnt`.`pcr_price_offer` > 0 AND `cnt`.`pcr_price_offer` < `cnt`.`pcr_price_regular`), 1, 0) AS `is_price_offer`,
    				@offer:=IF(@is_price_offer = 1, `cnt`.`pcr_price_offer_expires`, IF(`cnt`.`pcr_offer` IS NOT NULL, `cnt`.`pcr_offer`, `cre`.`pdc_offer`)) AS `offer`,
    				@is_offer:=IF(((@offer >= NOW() AND @offer IS NOT NULL) OR @is_price_offer = 1), 1, 0) AS `is_offer`,
    
    				@price:=CAST(IF(@is_price_offer = 1, @price_offer, @price_regular) AS DECIMAL(8,2)) AS `price`,
    				@sortprice:=CAST(IF(@is_price_offer = 1, @price_offer, @price_regular) AS DECIMAL(8,2) ) AS `sortprice`,
    
    				@price_advantage:=IF(((@is_price_offer = 1) OR (CAST(IF(`cnt`.`pcr_price_value` IS NULL, 0, `cnt`.`pcr_price_value`) AS DECIMAL(8,2)) > CAST(`cnt`.`pcr_price_regular` AS DECIMAL(8,2)))), 1, 0) AS `price_advantage`,
    				@price_saving_amount_order:=0 AS `price_saving_amount_order`,
    				@price_saving_amount_product:=0 AS `price_saving_amount_product`,
    				@price_saving_amount:=IF(@price_advantage = 1, IF(@is_price_offer = 1, (@price_regular - @price_offer), (@price_calculate - @price)), 0) AS `price_saving_amount`,
    				IF(@price_advantage = 1, ROUND(@price_saving_amount * 100 / IF(@is_price_offer = 1, @price_regular, @price_calculate)), 0) AS `price_saving_percent`,
    				IF(@is_price_offer = 1, 5, 0) AS `discounted`, 
    				IF(@is_price_offer = 0 AND @is_price_discountable = 1, 1, 0) AS `discountable`, 
    				
    				0 AS `freegoods_limit`, 
    				0 AS `freegoods_quantity`, 
    				NULL AS `freegoods_product`, 
    				
    				IF(@is_promotion = 1, 'promotion', IF(@is_offer = 1, 'sale', IF(@is_new = 1, 'new', IF(IF(`cnt`.`pcr_flaged` IS NOT NULL, `cnt`.`pcr_flaged`, `cre`.`pdc_flaged`) >= NOW(), IF(`cnt`.`pcr_flaged_type` IS NOT NULL, `cnt`.`pcr_flaged_type`, `cre`.`pdc_flaged_type`), NULL)))) AS `flagged`,
    
    			`cnt`.`pcr_popularity` AS `popularity`,
    			@amount:=`cre`.`pdc_amount` AS `amount`,
    			@amount AS `contents`,
    			`cre`.`pdc_measurement` AS `measurement`,
    			`cre`.`pdc_divided` AS `divided`,
    			`cre`.`pdc_weight` AS `weight`,
    			`cre`.`pdc_volume` AS `volume`,
    			`cre`.`pdc_dimensions_length` AS `dimensions_length`,
    			`cre`.`pdc_dimensions_width` AS `dimensions_width`,
    			`cre`.`pdc_dimensions_height` AS `dimensions_height`,
    
    			`cre`.`pdc_hazardous_goods` AS `hazardous_goods`,
    			`cre`.`pdc_hazardous_goods_weight` AS `hazardous_goods_weight`,
    			`cre`.`pdc_hazardous_goods_class_road` AS `hazardous_goods_class_road`,
    			`cre`.`pdc_hazardous_goods_class_sea` AS `hazardous_goods_class_sea`,
    			`cre`.`pdc_hazardous_goods_class_air` AS `hazardous_goods_class_air`,
    			`cre`.`pdc_origin_country` AS `origin_country`,
    			`cre`.`pdc_customs_tariff_number` AS `customs_tariff_number`,
    
    			@price_per_100:=ROUND(@price / @amount * 100, 2) AS `price_per_100`,
    			@price_per_100 AS `unitprice`,
    			IF(`pcc1`.`primary` IS NOT NULL, `pcc1`.`primary`, `pc1`.`primary`) AS `line`,
    			@catcaption1:=`cl1`.`ctc_caption` AS `catcaption1`,
    			@catalias1:=`cl1`.`ctc_alias` AS `catalias1`,
    			@catcaption2:=`cl2`.`ctc_caption` AS `catcaption2`,
    			@catalias2:=`cl2`.`ctc_alias` AS `catalias2`,
    			@catcaption3:=`cl3`.`ctc_caption` AS `catcaption3`,
    			@catalias3:=`cl3`.`ctc_alias` AS `catalias3`,
    			CONCAT_WS('/', @catalias1, @catalias2, @catalias3) AS `path`,
    			`cnt`.`pcr_redirect` AS `redirect`,
    			`cnt`.`pcr_garnish_blocked` AS `garnish_blocked`, 
    			`cnt`.`pcr_promos_blocked` AS `promos_blocked`, 
    			CONCAT(IF(@catcaption3 IS NOT NULL, @catcaption3, IF(@catcaption2 IS NOT NULL, @catcaption2, IF(@catcaption1 IS NOT NULL, @catcaption1, ''))), ' ', @name) AS `longname`,
    			TRIM(CONCAT_WS(' ', @catcaption1, @catcaption2, @catcaption3, @name)) AS `fullname`,
    			IF(`cnt`.`pcr_index_A` IS NOT NULL, `cnt`.`pcr_index_A`, `cre`.`pdc_index_A`) AS `index_A`,
    			IF(`cnt`.`pcr_index_B` IS NOT NULL, `cnt`.`pcr_index_B`, `cre`.`pdc_index_B`) AS `index_B`,
    			IF(`cnt`.`pcr_index_C` IS NOT NULL, `cnt`.`pcr_index_C`, `cre`.`pdc_index_C`) AS `index_C`,
    			IF(`cnt`.`pcr_index_D` IS NOT NULL, `cnt`.`pcr_index_D`, `cre`.`pdc_index_D`) AS `index_D`,
    			IF(`gva`.`id` IS NULL, 0, 1) AS `giveaways`,
    			`cre`.`pdc_folder` AS `folder`
    		FROM
    			`babordb001`.`tbl_products_core` AS `cre`
    		LEFT JOIN
    			`babordb001`.`tbl_products_country_b2c` AS `cnt`
    		ON (
    			`cnt`.`id` = `cre`.`id` AND
    			`cnt`.`country` = 'US' 
    			)
    		
    		LEFT JOIN
    			`babordb001`.`tbl_products_ingredients` AS `ing1`
    		ON (
    			`ing1`.`id` = `cre`.`id` AND
    			`ing1`.`language` = 'EN'
    			)
    		LEFT JOIN
    			`babordb001`.`tbl_products_ingredients` AS `ing2`
    		ON (
    			`ing2`.`id` = `cre`.`id` AND
    			`ing2`.`language` = 'EN'
    			)
    		LEFT JOIN
    			`babordb001`.`tbl_products_giveaways` AS `gva`
    		ON (
    			`gva`.`id` = `cre`.`id` AND
    			`gva`.`country` = 'US'
    			)
    
    		LEFT JOIN
    			`babordb001`.`tbl_products_language` AS `cln`
    		ON (
    			`cln`.`id` = `cre`.`id` AND
    			`cln`.`language` = 'EN'
    			)
    		LEFT JOIN
    			`babordb001`.`tbl_products_country_language` AS `ccl`
    		ON (
    			`ccl`.`id` = `cre`.`id` AND
    			`ccl`.`country` = 'US' AND
    			`ccl`.`language` = 'EN'
    			)
    			
    		LEFT JOIN
    			`babordb001`.`tbl_products_categorize` AS `pc1` 
    		ON (
    			`pc1`.`id` = `cre`.`id` AND
    			`pc1`.`type` = 'srn' 
    			)
    		LEFT JOIN
    			`babordb001`.`tbl_products_categorize_country` AS `pcc1` 
    		ON (
    			`pcc1`.`id` = `cre`.`id` AND
    			`pcc1`.`type` = 'srn' AND 
    			`pcc1`.`country` = 'US' 
    			)
    		LEFT JOIN 
    			`babordb001`.`tbl_categorize_language` AS `cl1` 
    		ON (
    			`cl1`.`id` = IF(`pcc1`.`primary` IS NOT NULL, `pcc1`.`primary` ,`pc1`.`primary`) AND
    			`cl1`.`country` = 'US' AND
    			`cl1`.`language` = 'EN'
    			)
    		LEFT JOIN
    			`babordb001`.`tbl_products_categorize` AS `pc2` 
    		ON (
    			`pc2`.`id` = `cre`.`id` AND
    			`pc2`.`type` = 'srn' 
    			)
    		LEFT JOIN
    			`babordb001`.`tbl_products_categorize_country` AS `pcc2` 
    		ON (
    			`pcc2`.`id` = `cre`.`id` AND
    			`pcc2`.`type` = 'srn' AND 
    			`pcc2`.`country` = 'US' 
    			)
    		LEFT JOIN 
    			`babordb001`.`tbl_categorize_language` AS `cl2` 
    		ON (
    			`cl2`.`id` = IF(`pcc2`.`secondary` IS NOT NULL, `pcc2`.`secondary` ,`pc2`.`secondary`) AND
    			`cl2`.`country` = 'US' AND
    			`cl2`.`language` = 'EN'
    			)
    		LEFT JOIN
    			`babordb001`.`tbl_products_categorize` AS `pc3` 
    		ON (
    			`pc3`.`id` = `cre`.`id` AND
    			`pc3`.`type` = 'srn' 
    			)
    		LEFT JOIN
    			`babordb001`.`tbl_products_categorize_country` AS `pcc3` 
    		ON (
    			`pcc3`.`id` = `cre`.`id` AND
    			`pcc3`.`type` = 'srn' AND 
    			`pcc3`.`country` = 'US' 
    			)
    		LEFT JOIN 
    			`babordb001`.`tbl_categorize_language` AS `cl3` 
    		ON (
    			`cl3`.`id` =  IF(`pcc3`.`tertiary` IS NOT NULL, `pcc3`.`tertiary` ,`pc3`.`tertiary`) AND
    			`cl3`.`country` = 'US' AND
    			`cl3`.`language` = 'EN'
    			)
    		WHERE
    			( `cre`.`id` = 58759 ) AND
    			
    			(IF(`cnt`.`pcr_release` IS NOT NULL, `cnt`.`pcr_release`, `cre`.`pdc_release`) <= NOW() OR IF(`cnt`.`pcr_release` IS NOT NULL, `cnt`.`pcr_release`, `cre`.`pdc_release`) IS NULL) AND
    			(IF(`cnt`.`pcr_expires` IS NOT NULL, `cnt`.`pcr_expires`, `cre`.`pdc_expires`) >= NOW() OR IF(`cnt`.`pcr_expires` IS NOT NULL, `cnt`.`pcr_expires`, `cre`.`pdc_expires`) IS NULL) AND
    			(IF(`cnt`.`pcr_available` IS NOT NULL, `cnt`.`pcr_available`, `cre`.`pdc_available`) BETWEEN 1 AND 4) AND
    			(`cnt`.`pcr_available_status` BETWEEN 1 AND 2) 
    		GROUP BY
    			`cre`.`id`
    		LIMIT 1;
    
  • 303
No Animal Testing & Vegan

Mattifying Foundation has a 4-fold anti-aging effect and provides variable coverage for oily and combination skin. Available in 3 shades.

Mattifying Foundation conditions oily and combination skin, provides variable coverage and lends the skin a natural, well-cared-for look.

Mattifying Foundation creates an instant optical lifting effect, imparts a youthful glow, protects the cells and has a detoxifying effect.

Using the make-up brush, apply from the center of the face towards the sides. Blend carefully at the outer edges (hairline, jaw line).
Coverage can be varied by layering, i.e. by applying several very thin layers of foundation on top of each other.
Shake Mattifying Foundation before use.


This is what our customers say:

Ask Questions, Get Answers