SELECT   
  concat(p.code,' - ',p.name) Product_style_45_left
,tbl_productcategory.category as  Category_style_10_left
,(ifnull( pi.qty_in_stock,0))   Qty_sum_2_style_15_right  
,ifnull(pbc.cost,0) as Cost_sum_2_style_15_right
,round(ifnull(pbc.cost,0)*ifnull( pi.qty_in_stock,0) ,2)as Value_sum_2_style_15_right
 

, ifnull(bl.branch_id,0) as category_id
,concat(ifnull(b.Code,''),' ',ifnull(b.Name,'')) as category


FROM tbl_products p
inner join tbl_productcategory on tbl_productcategory.productCategory_id = p.category_id
inner JOIN  tbl_inventory pi ON p.product_id = pi.product_id
inner JOIN  tbl_branch_inventory_locations bl ON bl.id = pi.location_id
inner JOIN  tbl_branch b ON b.branch_id = bl.branch_id
and (b.branch_id = {branch_id} or {branch_id} = 0)
inner JOIN  tbl_product_branch_cost pbc ON pbc.product_id = p.product_id
and pbc.location_id = pi.location_id
where (({available_stock} = 1 and ifnull( pi.qty_in_stock,0) > 0 ) or ({available_stock} = 0)) 

and (  {category_id} = 0  or  p.category_id  =   {category_id} )
 
and ( p.code like case when  ltrim(rtrim('{product_code}' ))= '0' then p.code else CONCAT(  ltrim(rtrim( ' {product_code} ')) , '%' )end ) 

ORDER BY   b.branch_id,p.category_id,p.name, p.code