Update Proc

24 Monday Mar 2014

Posted by in Uncategorized

≈ Leave a comment

Tags

— =============================================
— Author: Jitendra singh
— Create date: 05/06/2013
— Description: get the user text,pic,music,video friend details shared
— =============================================
alter PROCEDURE lsp_GetUpdShredModule
@u_Id BIGINT ,
@max_RowIndex int,
@min_RowIndex int

AS

BEGIN

DECLARE @temp1 TABLE
(
[id] int IDENTITY(1,1) NOT NULL,
[usrid] [bigint] NULL,
[shrid] [bigint] NULL,
[usrname] [nvarchar](200)NULL,
[usrimage] [nvarchar](500)NULL,
[MdlTypId] [tinyint] NULL,
[stypeid] [int] NULL,
[stype] [nvarchar](50) NULL,
[title] [nvarchar](500) NULL,
[sUrl] [nvarchar](500) NULL,
[sDtl] [nvarchar](MAX) NULL,
[Level1] [nvarchar](500) NULL,
[Level2] [nvarchar](500) NULL,
[Level3] [nvarchar](500) NULL,
[Level4] [nvarchar](500) NULL,
[Level5] [nvarchar](500) NULL,
[LikeCnt] [int] NULL,
[Likests] [int] NULL,
[DisLikeCnt] [int] NULL,
[DisLikests] [int] NULL,
[cmntCnt] [int] NULL,
[AlbmImgCnt] [int] NULL,
[cdate] [datetime] NULL

)

DECLARE @temp2 TABLE
(
[id] int IDENTITY(1,1) NOT NULL,
[usrid] [bigint] NULL,
[shrid] [bigint] NULL,
[usrname] [nvarchar](200)NULL,
[usrimage] [nvarchar](500)NULL,
[MdlTypId] [tinyint] NULL,
[stypeid] [int] NULL,
[stype] [nvarchar](50) NULL,
[title] [nvarchar](500) NULL,
[sUrl] [nvarchar](500) NULL,
[sDtl] [nvarchar](MAX) NULL,
[Level1] [nvarchar](500) NULL,
[Level2] [nvarchar](500) NULL,
[Level3] [nvarchar](500) NULL,
[Level4] [nvarchar](500) NULL,
[Level5] [nvarchar](500) NULL,
[LikeCnt] [int] NULL,
[Likests] [int] NULL,
[DisLikeCnt] [int] NULL,
[DisLikests] [int] NULL,
[cmntCnt] [int] NULL,
[AlbmImgCnt] [int] NULL,
[cdate] [datetime] NULL

)

–Insert comment share in temp talbe
INSERT INTO @temp1(usrid,shrid,usrname,usrimage,sDtl,MdlTypId,stypeId,stype,LikeCnt,Likests,DisLikeCnt,DisLikests,cmntCnt,cdate)
SELECT distinct(ust.usrid) as usrid,(ust.usrShrTxtId) as shrid,(usr.usrfname+ ‘ ‘ +usr.usrlname) as usrname,
(SELECT dbo.GetUsrImgPath(ust.usrid)) as usrimage,
(ust.txtDtl) as sDtl,1 as MdlTypId,(ust.ShrComnTypId) as stypeid,(uscts.shrSubTyp) as stype,

(dbo.fn_LikeCnt(ust.usrShrTxtId,1)) As LikeCnt,
(select distinct tls.sts from tblUsrLike tls where tls.shrtypId=1 and tls.usrShrid=ust.usrShrTxtId and tls.usrid=@u_Id) as Likests,

(dbo.fn_DisLikeCnt(ust.usrShrTxtId,1)) As DisLikeCnt,
(select distinct tds.sts from tblUsrDislike tds where tds.shrtypId=1 and tds.usrShrid=ust.usrShrTxtId and tds.usrid=@u_Id) as DisLikests,

(dbo.fn_CmntCnt(ust.usrShrTxtId,1)) As cmntCnt, ust.cdate FROM tblUsrShrTxt ust
INNER JOIN tblUsrShrSubTypStatic uscts ON ust.ShrComnTypId=uscts.UsrShrSubTypStaticId
INNER JOIN tblusr usr ON ust.usrid=usr.usrid
WHERE usr.usrid in (SELECT ntwrkUsrid FROM tblUsrNtwrk where usrid=@u_Id AND sts=2 AND crntsts=1) OR usr.usrid=@u_Id

–Insert Picture share in temp talbe
INSERT INTO @temp1(usrid,shrid,usrname,usrimage,sDtl,sUrl,MdlTypId,stypeid,stype,LikeCnt,Likests,DisLikeCnt,DisLikests,cmntCnt,cdate)
SELECT distinct(usp.usrid) as usrid,(usp.usrShrPicid) as shrid,(usr.usrfname+ ‘ ‘ +usr.usrlname) as usrname,
(SELECT dbo.GetUsrImgPath(usp.usrid)) as usrimage, (usp.picDtl) as sDtl,
(‘UserFiles/100/’+cast(usp.usrid as varchar)+’/’+’SharedImages/’ + +cast(usp.usrShrPicid as varchar)+ ‘.’+ substring(usp.picUrl,charindex(‘.’, usp.picUrl)+1,len(usp.picUrl))) as sUrl,
2 as MdlTypId,(usp.ShrComnTypId) as stypeid,(uscts.shrSubTyp) as stype,

(dbo.fn_LikeCnt(usp.usrShrPicid,2)) As LikeCnt,
(select distinct tls.sts from tblUsrLike tls where tls.shrtypId=2 and tls.usrShrid=usp.usrShrPicid and tls.usrid=@u_Id) as Likests,

(dbo.fn_DisLikeCnt(usp.usrShrPicid,2)) As DisLikeCnt,
(select distinct tds.sts from tblUsrDislike tds where tds.shrtypId=2 and tds.usrShrid=usp.usrShrPicid and tds.usrid=@u_Id) as DisLikests,

(dbo.fn_CmntCnt(usp.usrShrPicid,2)) As cmntCnt,usp.cdate FROM tblUsrShrPic usp
INNER JOIN tblUsrShrSubTypStatic uscts ON usp.ShrComnTypId=uscts.UsrShrSubTypStaticId
INNER JOIN tblusr usr ON usp.usrid=usr.usrid
WHERE usr.usrid in (SELECT ntwrkUsrid FROM tblUsrNtwrk where usrid=@u_Id AND sts=2 AND crntsts=1) OR usr.usrid=@u_Id

–Insert Album share in temp talbe
INSERT INTO @temp1(usrid,shrid,usrname,usrimage,sDtl,sUrl,MdlTypId,stypeid,stype,title,LikeCnt,Likests,DisLikeCnt,DisLikests,cmntCnt,AlbmImgCnt,cdate)
SELECT distinct(usAlb.usrid) as usrid,(usAlb.UsrShrAlbmId) as shrid,(usr.usrfname+ ‘ ‘ +usr.usrlname) as usrname,
(SELECT dbo.GetUsrImgPath(usAlb.usrid)) as usrimage,(usAlb.albumdesc) as sDtl,(usAlb.titlImg) as sUrl,3 as MdlTypId,
(usAlb.ShrComnTypId) as stypeid,(uscts.shrSubTyp) as stype,(usAlb.albumname) as title,

(dbo.fn_LikeCnt(usAlb.UsrShrAlbmId,3)) As LikeCnt,
(select tls.sts from tblUsrLike tls where tls.shrtypId=3 and tls.usrShrid=usAlb.UsrShrAlbmId and tls.usrid=@u_Id) as Likests,

(dbo.fn_DisLikeCnt(usAlb.UsrShrAlbmId,3)) As DisLikeCnt,
(select tds.sts from tblUsrDislike tds where tds.shrtypId=3 and tds.usrShrid=usAlb.UsrShrAlbmId and tds.usrid=@u_Id) as DisLikests,

(dbo.fn_CmntCnt(usAlb.UsrShrAlbmId,3)) As cmntCnt,

(dbo.fn_AlbmImgCnt(usAlb.UsrShrAlbmId,3)) As AlbmImgCnt,usAlb.cdate FROM tblUsrShrAlbm usAlb

INNER JOIN tblUsrShrSubTypStatic uscts ON usAlb.ShrComnTypId=uscts.UsrShrSubTypStaticId
INNER JOIN tblusr usr ON usAlb.usrid=usr.usrid
WHERE usr.usrid in (SELECT ntwrkUsrid FROM tblUsrNtwrk where usrid=@u_Id AND sts=2 AND crntsts=1) OR usr.usrid=@u_Id

–Insert Video share in temp talbe

INSERT INTO @temp1(usrid,shrid,usrname,usrimage,sDtl,sUrl,MdlTypId,stypeid,stype,LikeCnt,Likests,DisLikeCnt,DisLikests,cmntCnt,cdate)
SELECT distinct(usv.usrid) as usrid,(usv.usrShrVidid) as shrid,(usr.usrfname+ ‘ ‘ +usr.usrlname) as usrname,
(SELECT dbo.GetUsrImgPath(usv.usrid)) as usrimage,
(usv.vidDtl) as sDtl,(usv.vidUrl) as sUrl,4 as MdlTypId,(usv.ShrComnTypId) as stypeid,(uscts.shrSubTyp) as stype,

(dbo.fn_LikeCnt(usv.usrShrVidid,4)) As LikeCnt,
(select distinct tls.sts from tblUsrLike tls where tls.shrtypId=4 and tls.usrShrid=usv.usrShrVidid and tls.usrid=@u_Id) as Likests,

(dbo.fn_DisLikeCnt(usv.usrShrVidid,4)) As DisLikeCnt,
(select distinct tds.sts from tblUsrDislike tds where tds.shrtypId=4 and tds.usrShrid=usv.usrShrVidid and tds.usrid=@u_Id) as DisLikests,

(dbo.fn_CmntCnt(usv.usrShrVidid,4)) As cmntCnt,usv.cdate FROM tblUsrShrVid usv

INNER JOIN tblUsrShrSubTypStatic uscts ON usv.ShrComnTypId=uscts.UsrShrSubTypStaticId
INNER JOIN tblusr usr ON usv.usrid=usr.usrid
WHERE usr.usrid in (SELECT ntwrkUsrid FROM tblUsrNtwrk where usrid=@u_Id AND sts=2 AND crntsts=1) OR usr.usrid=@u_Id

–Insert file share in temp talbe

INSERT INTO @temp1(usrid,shrid,usrname,usrimage,sDtl,sUrl,title,MdlTypId,stypeid,stype,LikeCnt,Likests,DisLikeCnt,DisLikests,cmntCnt,cdate)
SELECT distinct(usd.usrid) as usrid,(usd.usrShrfdataid) as shrid,(usr.usrfname+ ‘ ‘ +usr.usrlname) as usrname,
(SELECT dbo.GetUsrImgPath(usd.usrid)) as usrimage,(usd.DataDtl) as sDtl,
(‘UserFiles/100/’+cast(usd.usrid as varchar)+’/’+’Data/’ + +cast(usd.usrShrfdataid as varchar)+ ‘.’+ substring(usd.fname,charindex(‘.’, usd.fname)+1,len(usd.fname))) as sUrl,
(usd.fname) as title, 6 as MdlTypId,(usd.ShrComnTypId) as stypeid,(uscts.shrSubTyp) as stype,

(dbo.fn_LikeCnt(usd.usrShrfdataid,6)) As LikeCnt,
(select distinct tls.sts from tblUsrLike tls where tls.shrtypId=6 and tls.usrShrid=usd.usrShrfdataid and tls.usrid=@u_Id) as Likests,

(dbo.fn_DisLikeCnt(usd.usrShrfdataid,6)) As DisLikeCnt,
(select distinct tds.sts from tblUsrDislike tds where tds.shrtypId=6 and tds.usrShrid=usd.usrShrfdataid and tds.usrid=@u_Id) as DisLikests,

(dbo.fn_CmntCnt(usd.usrShrfdataid,6)) As cmntCnt,

usd.cdate FROM tblUsrShrfileData usd
INNER JOIN tblUsrShrSubTypStatic uscts ON usd.ShrComnTypId=uscts.UsrShrSubTypStaticId
INNER JOIN tblusr usr ON usd.usrid=usr.usrid
WHERE usr.usrid in (SELECT ntwrkUsrid FROM tblUsrNtwrk where usrid=@u_Id AND sts=2 AND crntsts=1) OR usr.usrid=@u_Id

–New Change for scroll using last posted data
INSERT INTO @temp2(usrid,shrid,usrname,usrimage,title,MdlTypId,stypeid,stype,sDtl,sUrl,LikeCnt,Likests,DisLikeCnt,DisLikests,cmntCnt,AlbmImgCnt,cdate)
SELECT usrid,shrid,usrname,usrimage,title,MdlTypId,stypeid,stype,sDtl,sUrl,LikeCnt,Likests,DisLikeCnt,DisLikests,cmntCnt,AlbmImgCnt,cdate FROM @temp1 ORDER BY cdate desc

SELECT id,usrid,shrid,usrname,usrimage,MdlTypId,stypeid,stype,title,sDtl,sUrl,Level1,Level2,Level3,Level4,Level5,LikeCnt,Likests,DisLikeCnt,DisLikests,
cmntCnt,AlbmImgCnt,CONVERT(VARCHAR(25),cdate,100) as cdate FROM @temp2
WHERE id BETWEEN @min_RowIndex and @max_RowIndex order by id desc

 

END

–exec lsp_GetUpdShredModule 100,10,0
–select * from tblusr
–select * from tblUsrShrfileData

 

 

 

 

 

function

CREATE FUNCTION [dbo].[fn_DisLikeCnt](@usrshrId BIGINT,@styp int) returns varchar(100)
AS
BEGIN
Declare @Dcnt NVARCHAR(100)
SELECT @Dcnt= COUNT(usrid) FROM tblUsrDislike WHERE usrShrid=@usrshrId AND ShrTypId=@styp AND sts=1
–select * from tblUsrDislike
RETURN @Dcnt

END

–SELECT dbo.fn_DisLikeCnt(100,1)