SELECT `products`.*, `pt`.`name` as `prodName`, `pi`.`image`, `pp`.`price`, `pp`.`discount`, `pp`.`discount_type`, `pp`.`vat`, `country`.`currency`, `pi`.`comments` as `comments`, `pi`.`description` as `description`, `pi`.`tags` as `tags`, `ptt`.`name` as `product_type`, CASE WHEN discount_type = 0 THEN price - discount ELSE (price -(discount * price)/100) END as `discounted_price`
FROM `products`
LEFT JOIN `product_types_translation` as `ptt` ON `products`.`product_type`=`ptt`.`row_id` and `ptt`.`lang_id` =1
JOIN `products_translation` as `pt` ON `pt`.`row_id`=`products`.`id` AND `pt`.`lang_id`=1
LEFT JOIN `product_images` as `pi` ON `pi`.`product_id`=`products`.`id` AND `pi`.`deleted`=0
JOIN `product_prices` as `pp` ON `pp`.`product_id`=`products`.`id` and `pp`.`deleted` = 0
JOIN `country` ON `country`.`id`=`pp`.`country_id`
JOIN `product_cities` as `pc` ON `pc`.`city_id`=1 and `pc`.`deleted`=0 and `pc`.`product_id`=`products`.`id`
JOIN `florist_products` as `fp` ON `fp`.`status`=1 and `fp`.`product_id`=`products`.`id`
JOIN `florist_areas_charges` as `fac` ON `fac`.`district`=1 and `fac`.`deleted`=0 and `fac`.`can_deliver`=1 and `fp`.`user_id`=`fac`.`florist_id`
WHERE `pi`.`primary_image` = 1
AND `products`.`deleted` =0
AND `products`.`status` = 1
AND `products`.`id` IN('30', '69', '202', '203', '204', '205', '206', '207', '257', '258', '259', '536', '537', '538', '558', '559', '583', '584', '798', '814', '815', '816')
AND `products`.`static` =0
GROUP BY `products`.`id`
ORDER BY `products`.`category_order` DESC, `discounted_price` ASC, `products`.`featured_order` DESC, `discounted_price` ASC
