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('521', '522', '523', '524', '527', '528', '529', '530', '531', '532', '533', '534', '535', '536', '537', '538', '552', '658', '659', '660', '661', '662', '663', '665', '668', '669', '670', '671', '672', '673', '674', '699', '702', '764', '767', '769', '770', '777', '814', '815', '816', '829', '830', '831', '832', '833', '834', '835', '851', '852', '853', '854', '929', '930', '931', '932', '933', '949', '952', '953', '954')
AND `products`.`static` =0
GROUP BY `products`.`id`
ORDER BY `products`.`category_order` DESC, `discounted_price` ASC, `products`.`featured_order` DESC, `discounted_price` ASC
