select p.prod_id, p.company_price, p.prod_name, p.friendly_name, prod_cat_name, p.cat_id, prod_maincat_name, brand_name,
p.sku, p.image_path, p.prod_shipping_cost, p.date_added,
p.price, p.avail_status , p.sort_order , p.prod_desc,
upper(p.is_active) is_active, p.custom_title ptitle, pi18n.custom_title pi18ntitle,
upper(p.is_featured) is_featured,
pc.prod_cat_friendly_name , prod_maincat_friendly_name,
p.product_rating, p.product_rating_count,
p.product_dimention_units , p.default_qty,
p.quantity_in_stock, p.is_offer, p.offer_discount_percent, p.offer_start_date, p.offer_end_date,
if ( p.offer_start_date <= now() , if( p.offer_end_date >= now() , 1, 0 ) , 0 ) is_valid_offer,
p.is_offer_active, get_prod_mrp( p.prod_id ) prod_mrp
from product_categories pc, product_maincategories pmc , products p
left join product_brands pb on p.brand_id = pb.brand_id
left join products_i18n pi18n on pi18n.prod_id = p.prod_id and pi18n.language_code = 'en'
where p.cat_id = pc.prod_cat_id and p.avail_status = 1
and upper(p.is_active) = 'Y'
and pc.pc_maincat_id = pmc.prod_maincat_id
and pc.is_active = 'Y'
and p.price > 0
and pmc.prod_maincat_is_active = 'Y'
AND ( pc.prod_cat_friendly_name = 'fresh-juices'
or pc.prod_cat_name = 'fresh-juices' ) AND ( pc.prod_cat_friendly_name = 'fresh-juices'
or pc.prod_cat_name = 'fresh-juices' ) and pmc.prod_maincat_friendly_name = 'allproducts' order by p.prod_subcat_id, p.cat_id, p.main_cat_id
