CREATE PROCEDURE dbo.usp_sel_AnyList
@GroupID INT
, @StartDate DATETIME
, @EndDate DATETIME
, @ZoneID INT
, @ShowArchived BIT = 0
, @DeliveryStatus INT = 0
, @SortColumn VARCHAR(25) = ''
, @SortOrder VARCHAR(4) = 'asc'
, @StartIndex INT = 0
, @Count INT
AS
BEGIN
SET NOCOUNT ON
DECLARE @EndIndex INT
SET @EndIndex = @StartIndex + @Count - 1
IF(@SortOrder <> 'desc')
SET @SortOrder = 'asc'
DECLARE @SortPattern VARCHAR(30)
SET @SortOrder = LOWER(@SortOrder)
SET @SortColumn = LOWER(@SortColumn)
IF ( @SortColumn = '' )
SET @SortColumn = 'default'
SET @SortPattern = @SortColumn + '-' + @SortOrder
SELECT DISTINCT * FROM (
SELECT
TotalRows = COUNT(*) OVER()
, RowNumber = ROW_NUMBER() OVER
( ORDER BY SiteId )
, SiteId
, SiteName
, AdIDString
, AdName
, AdFileName
, Text1
, StartDate
, EndDate
, Days
, AccountID
, StatusID
, DownloadDate
, ZoneName
, LoginID
, SourceUrl
FROM (
SELECT
o.SiteId
, o.OrgName as SiteName
, AdIDString = CAST(a.AdID AS VARCHAR(36))
, a.Name AS AdName
, af.FileName AS AdFileName
, Text1 = ISNULL(CONVERT(VARCHAR(max),a.Text1),'')
, a.StartDate
, a.EndDate
, a.Days
, AccountID = CAST(a.AccountId AS VARCHAR(36))
, mf.StatusID
, DownloadDate = smf.TranDate
, z.ZoneName
, act.LoginID
, mf.SourceUrl
FROM dbo.Ads a (NOLOCK)
JOIN (
SELECT
AdID
, ManifestFileID , FileName
FROM dbo.AdFiles (NOLOCK)
GROUP BY
AdID
, ManifestFileID , FileName
) af
ON a.AdID = af.AdID
JOIN Programming.dbo.SiteAds b (NOLOCK) ON a.AdID = b.AdId
JOIN dbGamePlay..tbOrg o (NOLOCK) ON o.SiteId = b.SiteId
JOIN GameManagement..GroupSites gs (NOLOCK) ON gs.SiteID = b.SiteID AND gs.GroupID= @GroupID
JOIN Accounts act (NOLOCK) ON a.AccountID = act.AccountID
LEFT JOIN Programming.dbo.AdBackgrounds ab (NOLOCK) ON ab.AdBackgroundID = a.AdBackgroundID
LEFT JOIN Programming.dbo.ManifestFile mf (NOLOCK) ON mf.ManifestFileID = af.ManifestFileID
LEFT JOIN Programming.dbo.SiteManifestFile smf (NOLOCK) ON smf.ManifestFileID = af.ManifestFileID
AND smf.SiteID = b.SiteID
LEFT JOIN Programming.dbo.Zone z (NOLOCK)ON a.ZoneID = z.ZoneID
WHERE (@StartDate <= a.EndDate OR EndDate IS NULL)
AND (@EndDate >= StartDate OR StartDate IS NULL)
AND (@ZoneID = 0 OR a.ZoneID = @ZoneID)
AND ((@ShowArchived = 0 AND (a.EndDate >= GETDATE() OR a.EndDate IS NULL)) OR (@ShowArchived = 1))
AND ( @DeliveryStatus = 0 )
OR
( @DeliveryStatus = 1 AND smf.TranDate IS NOT NULL )
OR
( @DeliveryStatus = 2 AND smf.TranDate IS NULL )
) adFiles
) t
WHERE @startIndex = -1
OR RowNumber BETWEEN @startIndex AND @EndIndex
ORDER BY RowNumber
END