-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[GetDispatchWorkflowBoardNewData](
@DetailView varchar(1),
@UserHoursFromCorporate As integer, 
@orderByClause As varchar(2000),
@filteredWhere as varchar(4000),
@RecordStart as nvarchar(50),
@RecordEnd as varchar(50),
@PageSize as varchar(50),
@Columns as varchar(2000),
@CategoryView as varchar(150)
)
AS
SET NOCOUNT ON

DECLARE @sqlTotalRows nvarchar(max)
DECLARE @sqlDisplayRows nvarchar(max)
DECLARE @InternalWhere nvarchar(75)
DECLARE @TotalRows varchar(15)
DECLARE @DisplayRows varchar(15)


DECLARE @strippedfilterWhere varchar(2000)
-- Remove where from clause
SET @strippedfilterWhere = @filteredWhere
IF (CHARINDEX('WHERE',@filteredWhere) <> 0)
	BEGIN 
		SET @strippedfilterWhere = SUBSTRING(@filteredWhere,CHARINDEX('WHERE',@filteredWhere)+5,LEN(@filteredWhere))
	END

IF @DetailView = 'Y'
	BEGIN
		SET @InternalWhere = 'APARCode=''V'''
		SET @sqlTotalRows = 'SELECT COUNT(PK_OrderNo) AS Expr1 FROM tbl_ShipmentHeader SH  WITH (NOLOCK) INNER JOIN tbl_ShipmentAPAR  WITH (NOLOCK) ON PK_OrderNo=FK_OrderNo WHERE ' + @InternalWhere
	END
ELSE
	BEGIN
		SET @InternalWhere = '1=1'
		SET @sqlTotalRows = 'SELECT COUNT(PK_OrderNo) AS Expr1 FROM tbl_ShipmentHeader SH  WITH (NOLOCK) WHERE ' + @InternalWhere
	END
SET @sqlDisplayRows = 'select COUNT(PK_OrderNo) from ' + @CategoryView + ' SH WHERE ' + @InternalWhere + ' AND ' + @strippedfilterWhere

-- You can't set the value from a dynamic sql statement into a variable.  You have to put it into a temp table and then retrieve it.
create table #tempTableTotalRows (CNT varchar(15) null)
insert into #tempTableTotalRows exec sp_executesql @sqlTotalRows
select @TotalRows = CNT from #tempTableTotalRows

-- You can't set the value from a dynamic sql statement into a variable.  You have to put it into a temp table and then retrieve it.
create table #tempTableDisplayRows (CNT varchar(15) null)
insert into #tempTableDisplayRows exec sp_executesql @sqlDisplayRows
select @DisplayRows = CNT from #tempTableDisplayRows

IF Exists(SELECT 1
FROM INFORMATION_SCHEMA.COLUMNS AS UsedColumns 
WHERE UsedColumns.TABLE_NAME= @CategoryView And COLUMN_NAME = 'NextTrackingDateTime1')
	BEGIN
		--Set @orderByClause = REPLACE(@orderByClause, 'NextTrackingDateTime', ' NextTrackingDateTime1'); 
		Set @orderByClause = REPLACE(@orderByClause, 'NextTrackingDateTime asc', 'NextTrackingDateTime1 asc , NextTrackingTime asc'); 
		Set @orderByClause = REPLACE(@orderByClause, 'NextTrackingDateTime desc', 'NextTrackingDateTime1 desc , NextTrackingTime desc');
	END

DECLARE @RankSql nvarchar(1000)
SET @RankSql = @orderByClause + ',PK_OrderNo'
IF (@DetailView = 'Y')
BEGIN
	SET @RankSQL = @RankSQL + ',SeqNo'
	SET @Columns = @Columns + ',SeqNo'
END

DECLARE @sql nvarchar(max)
SET @sql = 'Select rank() OVER (' + @RankSql + ') as RowNumber, ' + @TotalRows + ' AS TotalRows, ' + @DisplayRows + ' AS TotalDisplayRows,' + @Columns + ' From (select * from ' + @CategoryView + @filteredWhere + ')z ' + @orderByClause + ' OFFSET ' + @RecordStart + ' ROWS FETCH NEXT ' + @PageSize + ' ROWS ONLY'

 PRINT SUBSTRING(@sql,0,4000)
 PRINT SUBSTRING(@sql,4000,4000)
 PRINT SUBSTRING(@sql,8000,4000)
 PRINT SUBSTRING(@sql,12000,4000)
 PRINT SUBSTRING(@sql,16000,4000)


EXEC sp_executesql @sql













GO