SELECT (SELECT GROUP_CONCAT(DISTINCT StandardColor,',',SKU,',',SUBSTRING(large,35),',',CONCAT(UCASE(SUBSTRING(ColorDescription, 1, 1)),LCASE(SUBSTRING(ColorDescription, 2))) SEPARATOR '|') FROM tbl_products WHERE tbl_products.RootModelNumber = p.RootModelNumber) AS AvailableColors, IF(p.thumb LIKE 'http%%',p.thumb,concat( 'https://www.kellyshomecenter.com/uploads/products/', p.thumb )) AS thumb, IF(p.large LIKE 'http%%',p.large, concat( 'https://www.kellyshomecenter.com/uploads/products/', p.large )) AS large, p.SKU, p.ModelStatus, p.mhSlug, p.mhDescription, p.cgSlug, p.cSlug, p.ModelDescription, p.id, IF(p.CommonBrandName = 'LG Appliances','LG',IF(p.CommonBrandName = 'Jenn-Air','JennAir',IF(p.CommonBrandName = 'Dcs','DCS',p.CommonBrandName))) AS CommonBrandName, p.KeyFeature1, p.KeyFeature2, p.KeyFeature3, p.KeyFeature4, p.KeyFeature5, bd.CommonBrandID, IF(p.StandardColor = 'Other',CONCAT(UCASE(SUBSTRING(p.ColorDescription, 1, 1)),LCASE(SUBSTRING(p.ColorDescription, 2))),p.StandardColor) AS ModelColor, mp.MSRP, mp.Sale, mp.Available, mp.in_store, mp.model_comments, mp.Featured, IF((mp.Sale = 0), mp.MSRP, IF( (mp.MSRP = 0), mp.Sale, IF((mp.Sale > mp.MSRP), mp.MSRP, mp.Sale))) AS calcPrice, IF(mp.MSRP > 0 AND mp.MSRP > mp.Sale, mp.MSRP, '0.00') AS RegPrice, mp.OutletPrice, mp.quick_ship, mp.PowerBuy, if(bd.cart_price_message = '' OR bd.cart_price_message IS NULL,'See price in cart', cart_price_message) AS cart_message, (mp.MSRP - mp.Sale) AS Discount, IF(bd.MAP = 1 OR p.is_package = 1, 1, .90) AS MapDiscount, mp.in_stock, mp.free_shipping, p.Sale AS MinTargetPrice, p.RootModelNumber, mp.FreeShip, bd.UMRP, (SELECT COUNT(pbm.pkID) FROM PromotionsByModel AS pbm INNER JOIN Rebates ON (Rebates.PromotionName = pbm.PromotionName AND Rebates.FileName <> '') WHERE pbm.pkProduct = p.pkID AND pbm.EffectiveDate <= CURDATE() AND pbm.ExpirationDate > CURDATE() GROUP BY pbm.pkProduct) AS rwsPromotion, (SELECT COUNT(rbm.pkID) FROM rebatesbymodel AS rbm INNER JOIN Rebates AS r ON r.pkID = rbm.pkRebates WHERE rbm.pkProduct = p.pkID AND r.ExpirationDate > CURDATE() AND r.EffectiveDate < CURDATE() GROUP BY rbm.PromotionName) AS rwsRebates, (p.Sale * (IF(bd.MAP = 1 OR p.is_package = 1, 1,.90))) AS MAP, c.ConsumerDescription AS cDescription, IF(bd.no_cart =1 OR mp.on_line = 0,1,0) AS no_cart FROM Categories AS c INNER JOIN Category_Groups AS cg ON (c.Category_Group = cg.CategoryCode) INNER JOIN menu_heading AS mh ON (cg.MenuHeading = mh.id) INNER JOIN tbl_products AS p ON (p.cSlug = c.slug AND p.cgSlug = cg.slug AND p.mhSlug = mh.slug) INNER JOIN Brand_Data AS bd ON (bd.pkID = p.pkBrand) INNER JOIN tbl_store_brands AS sb ON (sb.brandID = bd.pkID AND sb.categoryID = c.pkID) INNER JOIN ModelPrice AS mp ON (mp.pkProduct = p.pkID AND mp.SKU = p.SKU AND mp.Available = 1 ) WHERE ((`p`.`mhSlug` = 'refrigeration') and (`p`.`cgSlug` = 'built-in')) GROUP BY p.RootModelNumber ORDER BY in_stock DESC, if(mp.featured = 1 , mp.display_sequence, 9999) ASC, PowerBuy DESC, preferred DESC, p.CommonBrandName, p.SKU LIMIT 15
Store Specials
Rebates Available check_circle In Stock - Fast Delivery AvailableIf an item is currently in stock at our warehouse it qualifies for Fast Local Delivery. These products will ship out once your order is processed, usually in 2 to 3 business days.
Store Specials
Rebates Available check_circle In Stock - Fast Delivery AvailableIf an item is currently in stock at our warehouse it qualifies for Fast Local Delivery. These products will ship out once your order is processed, usually in 2 to 3 business days.
Store Specials
Rebates Available check_circle In Stock - Fast Delivery AvailableIf an item is currently in stock at our warehouse it qualifies for Fast Local Delivery. These products will ship out once your order is processed, usually in 2 to 3 business days.
Store Specials
Rebates Available check_circle In Stock - Fast Delivery AvailableIf an item is currently in stock at our warehouse it qualifies for Fast Local Delivery. These products will ship out once your order is processed, usually in 2 to 3 business days.
Store Specials
Rebates Available
Store Specials
Rebates Available
Store Specials
Rebates Available
Store Specials
Rebates Available
Store Specials
Rebates Available
Store Specials
Rebates Available
Store Specials
Rebates Available
Store Specials
Rebates Available
Store Specials
Rebates Available
Store Specials
Rebates Available
Store Specials
Rebates Available