SELECT LEFT(itemcode,12) AS itemcode, LOWER(CONCAT(CASE SUBSTRING(itemcode,5,1)
    WHEN 'M' THEN 'Men''s'
    WHEN 'W' THEN 'Women''s'
    WHEN 'B' THEN 'Boy''s'
    ELSE ''
  END,' ', CASE SUBSTRING(itemcode,2,1)
    WHEN 'A' THEN subgroup
    WHEN 'B' THEN subgroup
    WHEN 'C' THEN CONCAT(subgroup,' ', itemname_real)
    WHEN 'D' THEN CONCAT(subgroup,' ', itemname_real)
    WHEN 'E' THEN CONCAT(subgroup,' ', itemname_real)
    WHEN 'F' THEN CONCAT(subgroup,' ', itemname_real)
    WHEN 'G' THEN CONCAT(subgroup,' ', itemname_real)
    WHEN 'H' THEN CONCAT(subgroup,' ', itemname_real)
  END)) AS itemname, retailprice, itemimage, itemcolor_value, itemcolor_name, LOWER(search_color) AS search_color, 
              instore, promoprice, retailprice, promo_datestart, promo_dateend, CASE 
            WHEN promo_datestart <= NOW() AND promo_dateend >= NOW() AND promoprice > 0 THEN promoprice
            WHEN promo_datestart IS NULL AND promo_dateend IS NULL AND promoprice > 0 THEN promoprice
            ELSE retailprice
          END AS pricing, SUM(item_stock) AS stock, MIN(RIGHT(itemcode,3)) AS size, LOWER(theme) AS theme 
          FROM article 
          WHERE is_shown = 1 AND LEFT(search_tag, LENGTH('')) = ''  AND LOCATE(CONCAT('|',itemcolor_name ,'|'), '|WHITE|BEIGE|') > 0  AND LOCATE(CONCAT('|',RIGHT(itemcode,3),'|'), '|001|004|390|350|420|000|430|') > 0  AND ((
            (promo_datestart <= NOW() AND promo_dateend >= NOW() AND promoprice >= 100000 AND promoprice < 500000) OR
            (promo_datestart IS NULL AND promo_dateend IS NULL AND promoprice >= 100000 AND promoprice < 500000) OR 
            (promo_datestart IS NULL AND promo_dateend IS NULL AND promoprice = 0 AND retailprice > 0 AND retailprice < 500000)
          ) OR (
            (promo_datestart <= NOW() AND promo_dateend >= NOW() AND promoprice >= 1500000) OR
            (promo_datestart IS NULL AND promo_dateend IS NULL AND promoprice >= 1500000) OR 
            (promo_datestart IS NULL AND promo_dateend IS NULL AND promoprice = 0 AND retailprice >= 1500000)
          )) GROUP BY LEFT(itemcode,12)  ORDER BY create_date DESC LIMIT 12