Comment Quantity

---IF

============================================= Create scalar function (FN) ============================================= EXISTS (SELECT * FROM sysobjects WHERE name = N'FN_Get_Comment_Quantity') DROP FUNCTION FN_Get_Comment_Quantity

GO CREATE FUNCTION FN_Get_Comment_Quantity (@Wish_Idx bigint, @User_Idx bigint) RETURNS int AS BEGIN declare @retval int select @retval = count(*) from dbo.Comment with (nolock) where (Wish_Idx = @Wish_Idx or @Wish_Idx is null) and (User_Idx = @User_Idx or @User_Idx is null) return @retval END GO -------------------------------------------SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO

ALTER proc ST_Get_Wish @Idx bigint, ------------Error parameters--------------@ErrorCode tinyint = 0 output , @ErrorId varchar(90) = 0 output as select w.Idx, w.Text, w.Status_Idx , w.Create_Date, w.Last_Status_Update, Wish_Type ,Style ,dbo.FN_Get_Comment_Quantity(w.Idx , null) as Comment_Quantity from dbo.Wish w with (nolock) where w.Clone_Idx is null and w.Idx = @Idx

GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO

----------------------------------------------------------------------SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO

ALTER proc ST_Get_User_Wishes @User_Idx bigint, @Spectator_Idx bigint, @Is_Family tinyint , @Is_Friend tinyint , ------------Error parameters--------------@ErrorCode tinyint = 0 output , @ErrorId varchar(90) = 0 output as select w.Idx, w.Text,w.Status_Idx, w.Create_Date , w.Last_Status_Update ,w.User_Idx as Initiator, case when REL.Benefactor_Idx = @Spectator_Idx then 1 else 0 end as Spectator_Is_Benefactor ,dbo.FN_Get_Comment_Quantity(w.Idx , null) as Comment_Quantity from dbo.Wish w with (nolock) inner join dbo.VIEW_Wish_Related_Users REL on REL.Wish_Idx = w.Idx inner join W_User.dbo.[User] USR on USR.Idx = REL.Beneficiary_Idx where w.Clone_Idx is null and REL.Beneficiary_Idx = @User_Idx and (REL.Benefactor_Idx = @Spectator_Idx or Visible_2_Family * @Is_Family = 1 or Visible_2_Friend * @Is_Friend = 1 or Visible_2_Pubic = 1) order by w.Idx desc

GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO

---------------------------------------------------SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO

ALTER proc ST_Get_my_Wishes @User_Idx bigint, ------------Error parameters--------------@ErrorCode tinyint = 0 output , @ErrorId varchar(90) = 0 output as select w.Idx, w.Text, w.Status_Idx, w.Create_Date, w.Last_Status_Update , REL.Benefactor_Idx ,USR.Screen_Name ,w.User_Idx as Initiator , w.Wish_Type ,dbo.FN_Get_Comment_Quantity(w.Idx , null) as Comment_Quantity from dbo.Wish w with (nolock) inner join dbo.VIEW_Wish_Related_Users REL on REL.Wish_Idx = w.Idx left outer join W_User.dbo.[User] USR on USR.Idx = REL.Benefactor_Idx where w.Clone_Idx is null and REL.Beneficiary_Idx = @User_Idx order by w.Idx desc

GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO ---------------------------------------------------------------SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO

ALTER proc ST_Get_All_My_Wishes_V2 @User_Idx bigint, ------------Error parameters--------------@ErrorCode tinyint = 0 output , @ErrorId varchar(90) = 0 output as set nocount on select w.Idx, w.Text, w.Status_Idx, w.Create_Date, w.Last_Status_Update , case @User_Idx when REL.Benefactor_Idx then REL.Beneficiary_Idx when REL.Beneficiary_Idx then REL.Benefactor_Idx end as Interlocutor , USR.Screen_Name ,w.User_Idx as Initiator , case @User_Idx when REL.Benefactor_Idx then 'Benefactor' when REL.Beneficiary_Idx then 'Beneficiary' end as User_Role_In_Wish ,w.Wish_Type ,dbo.FN_Get_Comment_Quantity(w.Idx , null) as Comment_Quantity from dbo.Wish w with (nolock) inner join dbo.VIEW_Wish_Related_Users REL on REL.Wish_Idx = w.Idx left outer join W_User.dbo.[User] USR on (USR.Idx = REL.Benefactor_Idx ) or (USR.Idx = REL.Beneficiary_Idx ) where w.Clone_Idx is null and (REL.Beneficiary_Idx = @User_Idx or REL.Benefactor_Idx = @User_Idx and usr.Idx != @User_Idx order by w.Idx desc

GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO --------------------------------------SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO

)

ALTER proc ST_Get_All_My_Wishes @User_Idx bigint, @User_Role_In_Wish varchar(15) , ------------Error parameters--------------@ErrorCode tinyint = 0 output , @ErrorId varchar(90) = 0 output as set nocount on select w.Idx, w.Text, w.Status_Idx, w.Create_Date, w.Last_Status_Update , case @User_Role_In_Wish when 'Benefactor' then REL.Beneficiary_Idx when 'Beneficiary' then REL.Benefactor_Idx end as Interlocutor , USR.Screen_Name ,w.User_Idx as Initiator ,dbo.FN_Get_Comment_Quantity( w.Idx , null) as Comment_Quantity from dbo.Wish w with (nolock) inner join dbo.VIEW_Wish_Related_Users REL on REL.Wish_Idx = w.Idx left outer join W_User.dbo.[User] USR on (USR.Idx = REL.Benefactor_Idx and @User_Role_In_Wish = 'Beneficiary') or (USR.Idx = REL.Beneficiary_Idx and @User_Role_In_Wish = 'Benefactor') where w.Clone_Idx is null and ( (REL.Beneficiary_Idx = @User_Idx and @User_Role_In_Wish ='Beneficiary' ) or ( REL.Benefactor_Idx = @User_Idx and @User_Role_In_Wish = 'Benefactor') ) order by w.Idx desc

GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO --------------------------------