-- 1.1.16.137 SP1 update scripts

IF NOT EXISTS(SELECT * FROM sys.indexes WHERE Name = 'Ix_EDate_Pending')
BEGIN
CREATE NONCLUSTERED INDEX [Ix_EDate_Pending] ON [dbo].[Event]
(
	[EDate] ASC,
	[Seqno] ASC
)
INCLUDE ([Facno],
	[Badge], [caObjectID],	[Priority],	[Cat],	[PnlNo], [DeviceNo], [Status],	[Class],	[Description],
	[Name],	[HasPhoto],	[HasVideo],	[Arch],	[AckTStamp], [Tag], [UTCOffset], [RespReq],	[AckOpr],
	[Pending]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
END
GO
-----------ca_sp_VHistChunkResultSet -----------------
ALTER PROCEDURE [dbo].[ca_sp_VHistChunkResultSet](
    @PageSize int,
    @CurrentPage int,
	@OrderBy varchar(1000),
	@FinalOrderBy varchar(1000),
	@Select varchar(1000),
	@FinalSelect varchar(1000),
	@Where varchar(1000),
	@Table varchar(3000),
	@ObjectID varchar(30),
	@OperatorID UniqueIdentifier,
	@EventDBServer varchar(3000),
	@EventDB varchar(3000),
    @ConfigDb varchar(3000),
	@Partitioned bit
)
WITH EXECUTE AS CALLER
AS
BEGIN
SET NOCOUNT ON;
    DECLARE	  @sql nVarChar(MAX)
			, @sqlFinal nVarChar(MAX)
			, @sqlSelect nVarChar(4000)
			, @sqlStmt nVarChar(1000)
			, @vWhere nVarChar(1000)
			, @vTable nVarChar(1000)
			, @Partitionstr varchar(3000)
	
	IF @Where <> ''
	BEGIN
		IF @Partitioned = 1
		BEGIN
			SET @vWhere = ' AND ' + @Where;
		END
		ELSE
		BEGIN
		  SET @vWhere = ' WHERE ' + @Where;
		END
	END
	ELSE
	BEGIN
	  SET @vWhere = '';
	END

	IF @Partitioned = 1
	BEGIN
		SET @Partitionstr=' WHERE (EXISTS (SELECT 1 FROM ['+@ConfigDb+'].dbo.OperatorPartitions AS OP INNER JOIN ['+@ConfigDb+'].dbo.devicepartitions AS DP on DP.PartitionId= OP.Partitionid WHERE OP.OperatorID = @pOperatorID AND DP.caObjectID= [' + @Table + '].' + @ObjectID + ') ';
		SET @Partitionstr= @Partitionstr + ' OR EXISTS (SELECT 1 FROM ['+@ConfigDb+'].dbo.OperatorPartitions AS OPar INNER JOIN ['+@ConfigDb+'].dbo.devicepartitions AS DPar ON DPar.PartitionId= OPar.Partitionid INNER JOIN ['+@ConfigDb+'].[dbo].[Badge] AS B ON B.PersonID= DPar.caObjectID WHERE OPar.OperatorID = @pOperatorID AND ' + @Table + '.Badge > 0 AND ' + @Table + '.Badge= B.Badge AND ' + @Table + '.Facno= B.Facility) ';
		
		IF @OperatorID = '1EEB696C-0E28-4702-8598-C379C95B45E1'
		BEGIN
			SET @Partitionstr= @Partitionstr + ' OR NOT EXISTS (SELECT 1 FROM ['+@ConfigDb+'].dbo.devicepartitions AS devPar WHERE devPar.caObjectID = ['+@Table+'].caObjectID)';
		END		
		SET @Partitionstr = @Partitionstr + ') '
	END
	ELSE
	BEGIN
		SET @Partitionstr = '';
	END
	SET @sql = ''
	SET @sql = @sql + 'SELECT ' + @Select + CHAR(10)
	SET @sql = @sql + 'FROM ( ' + CHAR(10)
	SET @sql = @sql + 'SELECT ROW_NUMBER() OVER (ORDER BY ' + @OrderBy +') AS rownum1, '  + @Select + CHAR(10)
	SET @sql = @sql + 'FROM [' + @EventDBServer + '].[' + @EventDB + '].[dbo].[' + @Table + ']' + CHAR(10)
	SET @sql = @sql + CHAR(10) + @Partitionstr + CHAR(10) + @vWhere
	SET @sql = @sql + ') AS TAB1' + CHAR(10)
	SET @sql = @sql + 'WHERE rownum1 BETWEEN (@pCurrentPage-1) * @pPageSize+1 AND @pCurrentPage * @pPageSize' + CHAR(10)

	SET @sqlFinal = CHAR(10) + N'SELECT TOP ' + CAST(@PageSize AS VARCHAR) + ' ' + @FinalSelect + CHAR(10)
	SET @sqlFinal = @sqlFinal + N'FROM ( ' + @sql + ') AS TAB2 ORDER BY ' + @FinalOrderBy + CHAR(10)
	EXEC sp_executesql @sqlFinal, N'@pPageSize int, @pCurrentPage int, @pOperatorID UniqueIdentifier', @pPageSize= @PageSize, @pCurrentPage= @CurrentPage, @pOperatorID= @OperatorID; 
END

-----------------------------
GO

ALTER PROCEDURE [dbo].[ca_sp_VHistChunkResultSetCount]( 
	@Where varchar(1000) ,
	@Table varchar(3000) ,
	@ObjectID varchar(30) ,
	@OperatorID UniqueIdentifier ,
	@Partitioned bit ,
    @ConfigDb varchar(3000),
	@EventDBServer varchar(3000),
	@EventDB varchar(3000),
	@TotalCount int OUTPUT
	)	
AS
BEGIN
SET NOCOUNT ON;
	DECLARE	  @sql nVarChar(4000)
			, @sqlStmt nVarChar(4000)
			, @vWhere nVarChar(1000)
			, @Partitionstr varchar(3000)	
	
	IF @Where <> ''
	BEGIN
		IF @Partitioned = 1
		BEGIN
			SET @vWhere = ' AND ' + @Where;
		END
		ELSE
		BEGIN
		  SET @vWhere = ' WHERE ' + @Where;
		END
	END
	ELSE
	BEGIN
	  SET @vWhere = '';
	END

	IF @Partitioned = 1
	BEGIN
		SET @Partitionstr=' where (('+@ObjectID+' in (SELECT ['+@ConfigDb+'].dbo.devicepartitions.caObjectID FROM ['+@ConfigDb+'].dbo.OperatorPartitions  inner join  ['+@ConfigDb+'].dbo.devicepartitions   on ['+@ConfigDb+'].dbo.devicepartitions.PartitionId = ['+@ConfigDb+'].dbo.OperatorPartitions.Partitionid where ['+@ConfigDb+'].dbo.OperatorPartitions.OperatorID =@pOperatorID) ) ';
		SET @Partitionstr= @Partitionstr + ' OR EXISTS (SELECT 1 FROM ['+@ConfigDb+'].dbo.OperatorPartitions INNER JOIN ['+@ConfigDb+'].dbo.DevicePartitions ON ['+@ConfigDb+'].dbo.devicepartitions.PartitionId = ['+@ConfigDb+'].dbo.OperatorPartitions.Partitionid INNER JOIN ['+@ConfigDb+'].[dbo].[Badge] AS B ON B.PersonID = ['+@ConfigDb+'].dbo.devicepartitions.caObjectID where ['+@ConfigDb+'].dbo.OperatorPartitions.OperatorID =@pOperatorID AND ' + @Table + '.Badge > 0 AND ' + @Table + '.Badge = B.Badge AND ' + @Table + '.Facno = B.Facility) ';
		SET @Partitionstr= @Partitionstr + ' OR NOT EXISTS (SELECT 1 FROM ['+@ConfigDb+'].dbo.DevicePartitions WHERE ['+@ConfigDb+'].dbo.DevicePartitions.caObjectID = '+@Table+'.caObjectID UNION SELECT 1 FROM ['+@ConfigDb+'].dbo.Operators WHERE IsGlobalAdministrator = 0 AND OperatorID =@pOperatorID)) ';
	END
	ELSE
	BEGIN
		SET @Partitionstr = '';
	END
	IF (LEN(ISNULL(@EventDBServer, '')) > 0 OR LEN(ISNULL(@EventDB, '')) > 0)
	BEGIN
		SET @sql = N'SELECT @TotalCount = COUNT(1) FROM [' + @EventDBServer + '].[' + @EventDB + '].[dbo].' + @Table + @Partitionstr + @vWhere;
		EXEC sp_executesql @sql, N'@TotalCount INT OUTPUT, @pOperatorID UniqueIdentifier',  @TotalCount OUTPUT, @pOperatorID = @OperatorID;
	END
	ELSE
	BEGIN
		SET @TotalCount = 0;
	END
END

--------------------------------
GO
ALTER PROCEDURE [dbo].[ca_sp_GetEvents] (
		@SvrName varchar(250),
        @DBName varchar(250),
		@OperatorID UniqueIdentifier,
		@AckOperatorID UniqueIdentifier,
		@SelectQuery nvarchar(max),
		@TableName varchar(50),
		@IsPending bit,
		@Count int,
		@Cat int,
		@Status int,
		@OrderBy varchar(250),
		@caObjectId UniqueIdentifier,
		@Badge bigint,
		@Facility int,
		@Type int)
AS
declare	
		@sql nVarChar(max),
		@partition nvarchar(max),
		@where nvarchar(500),
		@svrstr varchar(500),
		@MachineInstance varchar(150),
        @InstanceName varchar(50)

BEGIN
	SET NOCOUNT ON;

	SET @MachineInstance = Convert(varchar(150),SERVERPROPERTY ('machinename'));
	SET @InstanceName = Convert(varchar(50),SERVERPROPERTY ('instancename'));

	IF @InstanceName <> '' 
    BEGIN
		SET @MachineInstance = @MachineInstance + '\' + @InstanceName;
    END

	IF UPPER(Convert(varchar(150), @MachineInstance)) = UPPER(@SvrName)
	BEGIN
		SELECT @svrstr = ' ['+@DBName+'].'
    END
	ELSE
    BEGIN
		SELECT @svrstr = ' ['+@SvrName+'].['+@DBName+'].'
	END


	-- Partition Query
	SET @partition = ' ';
	SET @partition = @partition + ' ( ';
	SET @partition = @partition + ' EXISTS (SELECT 1 FROM '+ @svrstr +'dbo.OperatorPartitions op inner join  '+ @svrstr +'dbo.devicepartitions dp  on dp.PartitionId = op.Partitionid 
	 where '+ @TableName +'.caObjectID = dp.caObjectID AND op.OperatorID = ''' + CAST(@OperatorID as varchar(36)) + ''') OR (Cat = 50 OR Cat = 48 OR Cat = 29)  ';
	IF @OperatorID = '1EEB696C-0E28-4702-8598-C379C95B45E1'
	BEGIN
		 SET @partition = @partition + ' OR NOT EXISTS ( SELECT 1 FROM '+ @svrstr +'dbo.devicepartitions WHERE '+ @TableName +'.caObjectID = devicepartitions.caObjectID ) '
	END
	SET @partition = @partition + ' ) ';
	-- End Partition


	--WHERE QUERY
	SET @where = ' WHERE ';

	--Type 1 : ShowThisEvents
	IF @Type = 1 AND @caObjectId <> '00000000-0000-0000-0000-000000000000'
	BEGIN
		SET @where = @where + ' caObjectId = ''' + CAST(@caObjectId as varchar(36)) +''' ';
		SET @where = @where + ' AND ';
	END
	--End Type 1

	--Type 2 : ShowThisEventsBadge
	IF @Type = 2
	BEGIN
		SET @where = @where + ' Badge = '+ CAST(@Badge as nvarchar(30)) +' AND Facno = '+ CAST(@Facility as nvarchar(10)) +' ';
		SET @where = @where + ' AND ';
	END
	--End Type 2

	--Type 3 : AutoAckedEvents
	IF @Type = 3 
	BEGIN
		SET @where = @where + ' ( Arch = 2 or Arch=16386 ) ';
		SET @where = @where + ' AND ';
	END
	ELSE
	BEGIN
		SET @where = @where + ' (Pending = '+ CAST(@IsPending as varchar(1)) +' OR (Pending = '+ CAST(@IsPending as varchar(1)) +' AND (Cat = 29))) ';
		SET @where = @where + ' AND ';
	END
	--End Type 3

	IF @Cat <> -1
	BEGIN
		SET @where = @where + ' Cat = '+  CAST(@Cat as nvarchar(10));
		SET @where = @where + ' AND ';
	END

	IF @Status <> -1
	BEGIN
		SET @where = @where + ' Status = '+  CAST(@Status as nvarchar(10));
		SET @where = @where + ' AND ';
	END
	
	IF @AckOperatorID <> '00000000-0000-0000-0000-000000000000'
	BEGIN
		SET @where = @where + ' AckOpr = ''' + CAST(@AckOperatorID as varchar(36)) + '''';
		SET @where = @where + ' AND ';
	END
	--END WHERE

	-- SQL Query
	SET @sql = ' ';

	IF (@Type = 1 OR @Type = 2) AND @Count > 0
	BEGIN 
		SET @sql = @sql + ' SELECT TOP '+ CAST(@Count as nvarchar(10)) +' * FROM ( ';
	END
	SET @sql = @sql + ' SELECT '
	IF @Count > 0
	BEGIN
		SET @sql = @sql + ' TOP ' + CAST(@Count as nvarchar(10));
	END
	SET @sql = @sql + @SelectQuery;
    SET @sql = @sql + ' FROM ' + @TableName + ' WITH (NOLOCK) ';

	SET @sql = @sql + @where + @partition + @OrderBy;

	--Type 1 : ShowThisEvents to get data from Archive Database
	IF (@Type = 1 OR @Type = 2) AND @Count > 0
	BEGIN
		CREATE TABLE #TmpDatabaseList
		(
  			ServerName nvarchar(200),
			DatabaseName nvarchar(200)
		)
	
		EXEC(' INSERT INTO #TmpDatabaseList SELECT ServerName, DatabaseName FROM '+ @svrstr +'dbo.DatabaseList WHERE DatabaseType = 1');

		IF EXISTS(SELECT 1 FROM #TmpDatabaseList)
		BEGIN
			DECLARE @ServerName AS nvarchar(200);
			DECLARE @DatabaseName AS nvarchar(200);

			DECLARE DBList_Cursor CURSOR 
			FOR 
  			SELECT ServerName, DatabaseName FROM #TmpDatabaseList
  			OPEN DBList_Cursor  
	
			FETCH NEXT FROM DBList_Cursor INTO @ServerName, @DatabaseName  

			WHILE @@FETCH_STATUS = 0  
  			BEGIN
				SET @sql =  @sql + ' UNION ';
				SET @sql = @sql + ' SELECT  TOP ' + CAST(@Count as varchar(10)) + ' ';
				SET @sql = @sql + @SelectQuery;
				SET @sql = @sql + ' FROM ' + ' ['+@ServerName+'].['+@DatabaseName+'].dbo.'+@TableName;
				SET @sql = @sql + @where + @partition + @OrderBy;

				FETCH NEXT FROM DBList_Cursor INTO @ServerName, @DatabaseName
			END

			CLOSE DBList_Cursor
			DEALLOCATE DBList_Cursor

			SET @sql = @sql + ') AS TAB ';
			SET @sql = @sql + @OrderBy;
		END

		DROP TABLE #TmpDatabaseList
	END

	EXEC sp_executesql @sql;
END

GO
------------------------------
GO
