show visiting card for evisiting card

 =============================================                      

                                                           

-- Create date: 09/04/2013                                      

-- Description: Show visiting card for multiple profile                                      

-- =============================================                                      

CREATE proc [dbo].[lsp_MultPrflVisitingcard]                                              

@usrid bigint                                             

as                                                 

                                      

begin                                  

  select  top 100   

 (select COUNT(*) from tblusrNoOfViews where mdlid=21 and shrid=p.usrprflid) as totalview,        

(select count(tmp.usrid) from       

(select distinct usrid from tblUsrShrNtwrk where shrType=21 and shrid=p.usrprflId      

 union      

select distinct usrid from tblUsrShrGrpNtwrk where shrType=21 and shrid=p.usrprflId) as tmp) as shared,      

(select ReportTypStId from tblUsrReport where  mdlid=21 and ShrId=p.usrprflId and usrid=@usrid) as reportsts,            

(select count(*) from tblusrrating where  mdlid=21 and ShrId=p.usrprflId) as raters,            

(select count(*) from tblusrrating where  mdlid=21 and ShrId=p.usrprflId and usrid=@usrid) as ratests ,             

(select (cast(max(rating)as float))/2  from tblusrrating where  mdlid=21 and ShrId=p.usrprflId and usrid=@usrid) as rated ,             

(select (sum((cast(rating as float))/2))/(select COUNT(usrid) from tblusrrating where  mdlid=21 and ShrId=p.usrprflId) from tblusrrating where  mdlid=21 and ShrId=p.usrprflId) as rating,           

(select COUNT(usrid) from tblUsrShrCmntOn where UsrShrId=p.usrprflId and ShrTypId=21) as commntd,           

(select COUNT (tl.usrid) from tblUsrLike tl where tl.shrtypId=21 and tl.usrShrid=p.usrprflId and tl.sts=1) as liked,           

(select tls.sts from tblUsrLike tls where tls.shrtypId=21 and tls.usrShrid=p.usrprflId and tls.usrid=@usrid) as likedsts,           

(select COUNT(td.usrid) from tblUsrDislike td where td.shrtypId=21 and td.usrShrid=p.usrprflId and td.sts=1) as disliked,          

(select tds.sts from tblUsrDislike tds where tds.shrtypId=21 and tds.usrShrid=p.usrprflId and tds.usrid=@usrid) as dislikedsts,          

          

          

          

   vw.name, ([dbo].[fn_subsubcat](p.usrprflId)) as subsubctgryname, cm.compnyname,p.estb, desg.desg, d.usrprflctgryId,  c.ctgname, p.usrprflid, p.usrid, prfname.usrprfltypeid, prfname.typename,sc.usrprflsubctgryId, sc.subctgryname, ('UserFiles/100/'      

  

+cast(p.usrid as varchar)+'/Evisitingcard/'+cast(prfname.usrprflTypeId as varchar)+'/' + cast(p.usrprflId as varchar)+'.'+RIGHT(p.imgurl, 3)) as imgurl, p.discriptn,p.InspBy,p.Achvmnts,p.Planing,p.Addrs1,p.contct1,p.Email,p.Websit from tblusrprfl p       

  

    

      

                        

          

  inner join (select distinct usrprflctgryId, usrprflSubctgryId , usrprflid from tblusrprfldtl) d on p.usrprflid=d.usrprflid                                 

  left join  tblUsrPrflSubCtgry  sc on sc.usrprflsubctgryId = d.usrprflSubctgryId                                

  left join tblusrprflctgry c on c.usrprflcatgryid=d.usrprflctgryid                    

  left join tblusrcompnystatic cm on cm.usrcompnyId=p.compnyid                   

  left join tblusrPrflDesgStatic desg on desg.usrjobDesgntnStaticId=p.desgid                           

  inner join vwfrndlistfld vw on p.usrId=vw.usrid               

                               

  inner join tblusrprfltypeStatic prfname on prfname.usrprflTypeId= p.usrprfltypeId where p.isdel !=1 and p.usrid=@usrid                            

  order by p.cdate desc                    

end