Featured brands

SELECT `pinfo`.`sku`,`pinfo`.`vendor`, IF(`pinfo`.`multi_level_pricing_check`=1,(`mlrp`.`cost`/`mlrp`.`min_qty`),IF(CURDATE() >= `ds`.`expiration_date`, `ds`.`price`, `crp`.`contract_price`)) AS `price`, CONCAT(IF(`pinfo`.`vendor`=2,`desc_long`,`desc_short`),' ',`pinfo`.`sku`,' ',`category_1`,' ',`category_2`,' ',`category_3`,' ',`category_4`,' ',IF(`pinfo`.`vendor`=2,`sales_copy`,`desc_long`),' ',IF(`dealer_sku`!='',`dealer_sku`,''),' ',`manufacturer_name`,' ',`color`) AS `search_string` FROM `product_info` `pinfo` INNER JOIN `vendor_base_costs` `vbcc` ON `pinfo`.`sku`=`vbcc`.`sku` LEFT JOIN `contract_price_13` AS `crp` ON `crp`.`sku`=`pinfo`.`sku` LEFT JOIN `dealer_special` AS `ds` ON `ds`.`sku`=`pinfo`.`sku` AND CURDATE() BETWEEN `ds`.`start_date` AND `ds`.`expiration_date` LEFT JOIN `multi_level_retail_price` AS `mlrp` ON `mlrp`.`sku`=`pinfo`.`sku` AND `mlrp`.`preferred_vendor`=`pinfo`.`preferred_vendor` AND (`mlrp`.`cost`/`mlrp`.`min_qty`)>0 LEFT JOIN `bestseller_priority_detail` AS `bpd` ON `pinfo`.`sku`=`bpd`.`sku` LEFT JOIN `bestseller_priority_master` AS `bpm` ON `bpm`.`list_id`=`bpd`.`list_id` WHERE (`pinfo`.`sku` IN ('AVE95945','AVE95940','AVE95935','AVE95930','AVE95920','AVE95915','AVE95910','AVE95905','AVE95900','AVE8987','AVE8986','AVE88127','AVE8667','AVE8665','AVE8663','AVE8662','AVE8660','AVE85783','AVE8465','AVE8464','AVE8463','AVE8462','AVE8461','AVE8460','AVE8257','AVE8255','AVE8254','AVE8253','AVE8252','AVE8250','AVE8217','AVE8215','AVE8195','AVE8168','AVE8167','AVE8165','AVE8164','AVE8163','AVE8162','AVE8161','AVE8160','AVE8127','AVE8126','AVE80509','AVE6879','AVE6878','AVE6876','AVE6874','AVE6873','AVE6871','AVE6870','AVE5980','AVE5979','AVE5978','AVE5976','AVE5975','AVE5974','AVE5972','AVE5971','AVE5970','AVE5967','AVE5964','AVE5963','AVE5962','AVE5961','AVE5960','AVE5956','AVE5952','AVE5948','AVE5946','AVE5944','AVE5940','AVE5936','AVE5935','AVE5934','AVE5912','AVE58164','AVE58163','AVE58160','AVE5783','AVE5667','AVE5664','AVE5663','AVE5662','AVE5661','AVE5660','AVE5630','AVE5526','AVE5524','AVE5523','AVE5522','AVE5520','AVE55164','AVE55163','AVE55160','AVE5429','AVE5363','AVE5360','AVE5353','AVE5352','AVE5351','AVE5334','AVE5332','AVE5311','AVE5292','AVE5286','AVE5278','AVE5267','AVE5265','AVE5264','AVE5263','AVE5262','AVE5261','AVE5260','AVE5195','AVE5168','AVE5167','AVE5165','AVE5164','AVE5163','AVE5162','AVE5161','AVE5160','AVE5155','AVE5127','AVE5126','AVE48960','AVE48467','AVE48464','AVE48462','AVE48460','AVE48267','AVE48263','AVE48163','AVE48160','AVE45280','AVE45160','AVE4157','AVE4156','AVE30623','AVE30620','AVE30617','AVE30609','AVE30606','AVE30605','AVE30604','AVE30603','AVE30602','AVE30601','AVE30600','AVE30586','AVE30584','AVE30583','AVE22838','AVE22837','AVE2163','AVE2162','AVE2160','AVE18695','AVE18667','AVE18665','AVE18664','AVE18663','AVE18662','AVE18661','AVE18660','AVE18167','AVE18163','AVE18160','AVE15695','AVE15667','AVE15664','AVE15663','AVE15661','AVE15660','AVE08460','AVE05248','AVE05247','AVE05165','')) AND `pinfo`.`status` IN ('active','discontinued') AND `pinfo`.`desc_title` != '' AND `online_status`=1 AND `custom_sku_enable`=1 AND CASE WHEN `pinfo`.`multi_level_pricing_check`=1 THEN TRUE ELSE `crp`.`contract_price`>0 END AND `pinfo`.`sku` NOT IN (SELECT `alias_sku` FROM `internal_alias_detail`) GROUP BY `pinfo`.`sku` ORDER BY ISNULL(`bpm`.`list_priority`) ASC, `bpm`.`list_priority` ASC, ISNULL(`bpd`.`priority`) ASC, `bpd`.`priority` ASC, CASE WHEN MATCH(`desc_short`) AGAINST ('AveryLabel' IN BOOLEAN MODE) THEN 1 ELSE 0 END DESC, CASE WHEN `pinfo`.`vendor`=2 THEN IF(MATCH(`desc_long`, `manufacturer_name`, `color`) AGAINST ('+AveryLabel' IN BOOLEAN MODE),4,0) ELSE CASE WHEN MATCH (`keywords`) AGAINST ('"AveryLabel"' IN BOOLEAN MODE) AND MATCH (`desc_title`) AGAINST ('"AveryLabel"' IN BOOLEAN MODE) THEN 4 ELSE IF(MATCH (`desc_title`) AGAINST ('"AveryLabel"' IN BOOLEAN MODE),3,IF(MATCH (`keywords`) AGAINST ('"AveryLabel"' IN BOOLEAN MODE),2,IF(MATCH(`desc_title`, `manufacturer_name`, `color`) AGAINST ('+AveryLabel' IN BOOLEAN MODE),1,0))) END END DESC,CASE WHEN IF(`pinfo`.`vendor`=2,`category_3` ='AveryLabel',`category_4` ='AveryLabel') THEN 1 ELSE 0 END DESC, CASE WHEN `desc_short` LIKE 'AveryLabel,%' THEN 1 ELSE 0 END DESC, SUBSTR(`pinfo`.`desc_title`, 1, LOCATE('AveryLabel', `pinfo`.`desc_title`)) DESC