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('516', '517', '518', '519', '520', '793', '794', '795', '796', '797', '803', '804', '805', '808', '809', '810', '811', '812', '917', '922', '923')
AND `products`.`static` =0
GROUP BY `products`.`id`
ORDER BY `products`.`category_order` DESC, `discounted_price` ASC, `products`.`featured_order` DESC, `discounted_price` ASC
