Amen/db/SP-GetPrayers.sql

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetPrayers]') AND type in (N'P'))
DROP PROCEDURE [dbo].[GetPrayers]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Than Nap
-- =============================================
CREATE PROCEDURE [dbo].[GetPrayers]
	@affiliateId int,
	@fetchCount int,
	@skipCount int = 0,
	@days int,
	@userId nvarchar(255) = null,
	@orderBy nvarchar(255) = 'newest',
	@skipToPrayerId int = null,
	@isPraise bit = 0,
	@prayerId int = null,
	@isSubmitted bit = 0,
	@isBookmarked bit = 0,
	@isPrayedFor bit = 0

AS
BEGIN

/*
select * from aspnetusers where email = 'thannap@hotmail.com'
select * from affiliate where [key] = 'amen'

	exec [GetPrayers]
		@affiliateId = 1
		, @fetchCount = 10
		, @skipCount = 0
		, @days = 360
		, @userId = '71a8f01c-8557-4ff9-91fe-471361507c4c'
		, @orderBy = 'newest'
		, @skipToPrayerId = 0
		, @isPraise = 0
		, @prayerId = null
		, @isSubmitted = 0
		, @isBookmarked = 0
		, @isPrayedFor = 0

*/

	DECLARE @includeUniversal bit

	SELECT @includeUniversal =
		CASE 
			WHEN @affiliateId = 1 THEN 1
			ELSE 0
		END

	IF (@skipToPrayerId is not null AND @prayerId is null)
	BEGIN
	
		SELECT @skipCount = rownum
		FROM (
			SELECT p.Id, row_number() OVER 
			
			(order by
			   CASE WHEN @orderBy = 'newest' THEN p.CreatedUTC END DESC,
			   CASE WHEN @orderBy != 'newest' THEN p.CountPrayed END, p.CreatedUTC DESC
			)
				 as rownum
			from Prayer p
				JOIN Affiliate a on a.Id = p.AffiliateId
				LEFT JOIN PrayerBookmark b on b.PrayerId = p.Id AND b.ApplicationUserId = @userId
				LEFT JOIN PrayerItem i on i.PrayerId = p.Id AND i.ApplicationUserId = @userId
			WHERE
			1 = 1
			AND (p.IsActive = 1 OR p.ApplicationUserId = @userId)
			AND p.IsDeleted = 0
			AND p.IsPraise = @isPraise
			AND p.CreatedUTC >= dateadd(day, -@days, getdate())
			--AND (select count(id) from PrayerFlag where prayerid = p.Id) < a.FlagThreshold
			AND (a.Id = @affiliateId
					OR ( a.IncludeInUniversal = 1
						AND @includeUniversal = 1
						)
				)
			AND (@isSubmitted = 0 OR p.ApplicationUserId = @userId)

			GROUP BY a.FlagThreshold, p.Id, p.CreatedUTC, p.CountPrayed, p.CountFlagged
			HAVING p.CountFlagged < a.FlagThreshold AND (@isBookmarked = 0 OR count(b.Id) > 0 ) AND (@isPrayedFor = 0 OR count(i.Id) > 0 )

		) As r
		WHERE id = @skipToPrayerId

	END

	SELECT
		p.Id,
		p.Summary,
		p.Content,
		cast(p.IsPraise as bit) as IsPraise,
		p.CountFlagged,
		p.CountPrayed,
		p.EnableEmailNote,
		p.CreatedUTC,
		CASE
			WHEN count(b.Id) > 0 THEN CAST(1 as bit)
			ELSE CAST(0 as bit)
		END as IsUserBookmarked,
		CASE
			WHEN count(s.Id) > 0 THEN CAST(1 as bit)
			ELSE CAST(0 as bit)
		END as IsUserSubscribed,
		CASE
			WHEN @userId is not null AND p.ApplicationUserId = @userId THEN CAST(1 as bit)
			ELSE CAST(0 as bit)
		END as EnableEdit,
		a.UrlLogo,
		CASE
			WHEN u.ShouldShowName = 1 THEN u.FullName
			ELSE ''
		END AS UserName,
		CASE
			WHEN u.ShouldShowLocation = 1 THEN u.Location
			ELSE ''
		END AS UserLocation
	from Prayer p
		JOIN Affiliate a on a.Id = p.AffiliateId
		LEFT JOIN AspNetUsers u on p.ApplicationUserId = u.Id
		LEFT JOIN PrayerBookmark b on b.PrayerId = p.Id AND b.ApplicationUserId = @userId
		LEFT JOIN PrayerSubscription s on s.PrayerId = p.Id AND s.ApplicationUserId = @userId
		LEFT JOIN PrayerItem i on i.PrayerId = p.Id AND i.ApplicationUserId = @userId
	WHERE
		(@prayerId is null OR p.Id = @prayerId)
	AND (p.IsActive = 1 OR p.ApplicationUserId = @userId)
	AND p.IsDeleted = 0
	AND p.IsPraise = @isPraise
	AND p.CreatedUTC >= dateadd(day, -@days, getdate())
	--AND (select count(id) from PrayerFlag where prayerid = p.Id) < a.FlagThreshold
	AND (a.Id = @affiliateId
			OR ( a.IncludeInUniversal = 1
				AND @includeUniversal = 1
				)
		)
	AND (@isSubmitted = 0 OR p.ApplicationUserId = @userId)
	group by 
		p.Id,
		p.Summary,
		p.Content,
		p.IsPraise,
		p.CountPrayed,
		p.EnableEmailNote,
		p.CreatedUTC,
		p.ApplicationUserId,
		a.FlagThreshold,
		a.UrlLogo,
		u.FullName,
		u.Location,
		u.ShouldShowName,
		u.ShouldShowLocation,
		p.CountFlagged

	HAVING p.CountFlagged < a.FlagThreshold AND (@isBookmarked = 0 OR count(b.Id) > 0 ) AND (@isPrayedFor = 0 OR count(i.Id) > 0 )
	order by

	   CASE WHEN @orderBy = 'newest' THEN p.CreatedUTC END DESC,
	   CASE WHEN @orderBy != 'newest' THEN p.CountPrayed END, p.CreatedUTC DESC


	OFFSET     @skipCount ROWS
	FETCH NEXT @fetchCount ROWS ONLY

END