Select Proc For Business Offer

-- =============================================                                  
-- Author:  Javed khan                                  
-- Create date: 29/10/2013                                
-- Description: To select the value from tblbusoffers table        
--Alter date:29/11/2013        
--Altered by: Me  
--Alter date:28/02/2014        
--Altered by: Me                                
-- =============================================                                  
CREATE PROCEDURE [dbo].[lsp_BusSelectoffer]              
(                                  
 @OpType tinyint,                        
 @offerId bigint,                  
 @usrid bigint                          
)                                  
AS                                  
begin                      
if(@optype=1)                      
select    
(select COUNT (tl.usrid) from tblUsrLike tl where tl.shrtypId=25 and tl.usrShrid=bo.offerId and tl.sts=1) as liked,    
(select tls.sts from tblUsrLike tls where tls.shrtypId=25 and tls.usrShrid=bo.offerId and tls.usrid=@usrid) as likedsts,    
(select COUNT(td.usrid) from tblUsrDislike td where td.shrtypId=25 and td.usrShrid=bo.offerId and td.sts=1) as disliked,    
(select tds.sts from tblUsrDislike tds where tds.shrtypId=25 and tds.usrShrid=bo.offerId and tds.usrid=@usrid) as dislikedsts,    
   
bo.offerid,left(bo.productName,18) as name,left(oc.compaddrs,22) as compaddrs,bo.discount,left(bo.offertitle,23) as offertitle,left(bo.offerdescrptn,185) as offerdescrptn,'UserFiles/100/'+convert(varchar,vw.usrid)+'/DealsOffer/'+convert(varchar,bo.  
     
       
         
offerid)+'.'+right(bo.pimg,3)+'' as imgPath from vwfrndlistfld vw                
inner join tblbusofferComp oc                  
on oc.usrid=vw.usrid                      
inner join tblbusoffers bo on bo.compid=oc.compid where bo.usrid=@usrid and bo.isdel=0 order by bo.cdate desc                  
               
if(@optype=2)                      
select    
(select COUNT (tl.usrid) from tblUsrLike tl where tl.shrtypId=25 and tl.usrShrid=bo.offerId and tl.sts=1) as liked,    
(select tls.sts from tblUsrLike tls where tls.shrtypId=25 and tls.usrShrid=bo.offerId and tls.usrid=@usrid) as likedsts,    
(select COUNT(td.usrid) from tblUsrDislike td where td.shrtypId=25 and td.usrShrid=bo.offerId and td.sts=1) as disliked,    
(select tds.sts from tblUsrDislike tds where tds.shrtypId=25 and tds.usrShrid=bo.offerId and tds.usrid=@usrid) as dislikedsts,    
     
 bo.offerid,oc.compid,oc.compName,oc.estblst,oc.compaddrs,oc.compweb,oc.compcontact,              
'UserFiles/100/'+convert(varchar,@usrid)+'/CompDealsOffer/'+convert(varchar,bo.compid)+'.'+right(oc.imgurl,3)+'' as compImg              
,              
[dbo].[fn_Bussubsubcat](bo.compid)as subsubctgry,              
dtl.compctgryid,dc.subctgryname,ctg.ctgname,dtl.compctgryid,              
bo.offerType,bo.productname,pct.prdctName as prdcat,psct.prdctsubName as prdsubcat,bo.offerPrice,                      
cntry.crncyName as currencyname,bo.discount,'UserFiles/100/'+convert(varchar,@usrid)+'/DealsOffer/'+convert(varchar,bo.offerid)+'.'+right(bo.pimg,3)+'' as imgPath              
,bo.offertitle,              
bo.offerFromTime,bo.offerToTime,bo.offertitle,bo.offerdescrptn                        
,ct.cityname,bo.noOfCouphons,convert(varchar(12), bo.offerVtill,101) as validDate ,bo.offerDescrptn,bo.trmsCondton                      
from tblbusofferComp oc                      
inner join tblbusoffers bo on bo.compid=oc.compid              
inner join (select distinct compid, compsubctgryid,compctgryid from tblbusoffercompDtl) dtl on oc.compid=dtl.compid                
inner join tblUsrPrflSubCtgry dc on dtl.compsubctgryid=dc.usrprflsubctgryid                
inner join tblusrprflctgry ctg on dtl.compctgryid=ctg.usrprflcatgryid              
inner join tblcurrency cntry on bo.curnytype=cntry.crncyid                
inner join tblcity ct on bo.offerLoctn=ct.cityid              
inner join tblbusprdctCtgry pct on bo.productcat=pct.prdctid              
inner join tblbusprdctSubCtgry psct on bo.productsubcat=psct.prdsubid              
where offerid=@offerId            
if(@optype=3)        
select top 3 bo.offerid,oc.compid,'UserFiles/100/'+convert(varchar,bo.usrid)+'/DealsOffer/'+convert(varchar,bo.offerid)+'.'+right(bo.pimg,3)+'' as imgPath        
,bo.productname,ct.cityname,bo.discount,oc.compcontact from tblbusoffers bo        
inner join tblbusofferComp oc on oc.compid=bo.compid        
inner join tblcity ct on bo.offerLoctn=ct.cityid        
where bo.isdel=0 and  bo.offerLoctn=(select city from tblusrdtl where usrid=@usrid)        
and bo.offerid not in (select top (@offerId) bo.offerid from tblbusoffers bo where bo.isdel=0 and bo.offerLoctn=(select city from tblusrdtl where usrid=@usrid))      
order by bo.cdate        
end