sp_get_checkDataList

-- exec [sp_get_checkDataList] 2,2,50,1    
-- =============================================    
ALTER PROCEDURE [dbo].[sp_get_checkDataList]    
 -- Add the parameters for the stored procedure here    
     
 @deviceTypeId int ,    
 @businessUnitId int,    
 @pageCount int = 50,    
 @page int = 1,    
 @filterByAssetTypeId int = 0,    
 @filterByAssetId int = 0,    
 @orderBy nvarchar(100) = '',    
 @orderDirection nvarchar(10) = 'asc'    
    
AS    
BEGIN    
 -- SET NOCOUNT ON added to prevent extra result sets from    
 -- interfering with SELECT statements.    
 SET NOCOUNT ON;    
    
     
    
 declare @sql nvarchar(max);    
    
    -- Insert statements for procedure here    
 declare @colname nvarchar(max);    
 declare @colnameDefine nvarchar(max);    
     
 declare @declareString nvarchar(max);    
 declare @selectString nvarchar(max);    
 declare @checkItemName nvarchar(150);    
 declare @resetString nvarchar(max);    
 declare @updateString nvarchar(max);    
     
 declare @deviceI int;    
 set @deviceI = 1;    
     
 declare @start int;     
 declare @end int;    
     
 select @colname = COALESCE (@colname+',','')+'['+convert(nvarchar(500),name)+']'     
 from checkitems where devicetypeid = @deviceTypeId order by name;    
     
 select @colnameDefine = COALESCE (@colnameDefine+',','')+'['+convert(nvarchar(500),name)+'] nvarchar(max) null'     
 from checkitems where devicetypeid = @deviceTypeId order by name;    
   
    
    
 declare devNames cursor for select name from checkitems where devicetypeid = @deviceTypeId order by name;    
 open devNames;    
     
 while 1=1    
 begin    
  fetch next from  devNames into @checkItemName;    
  if @@fetch_status <> 0     
  begin    
   break;    
  end;    
      
      
  set @declareString = COALESCE(@declareString,'') + ' declare @a'+convert(nvarchar(10),@deviceI)+' nvarchar(max);';    
  set @selectString = COALESCE(@selectString+',','') + ' @a'+convert(nvarchar(10),
  @deviceI)+' = COALESCE(@a'+convert(nvarchar(10),@deviceI)+'+''|'','''') + convert(nvarchar(20),['+@checkItemName+'])';    
  set @resetString = Coalesce(@resetString,'') + 'set @a'+convert(nvarchar(10),@deviceI)+'=null;';    
   
      
      
  set @updateString = Coalesce(@updateString+',','') + '['+@checkItemName+'] = @a'+convert(nvarchar(10),@deviceI)+' ';    
      
  set @deviceI = @deviceI + 1;    
 end;    
     
 close devNames;    
 deallocate devNames;    
    
    
 --select @declareString,@selectString;    
     
    
 set @sql = '';    
     
 -- create templary table     
 set @sql = @sql + N' if object_id (N''#tempdata'',N''U'') is not null    
   begin    
   drop table #tempdata;    
   end;';    
      
 /*    
 if object_id (N'#tempdata',N'U') is not null    
   begin    
   drop table #tempdata;    
   end;     
 */    
 set @sql = @sql + N'if object_id (N''#tempdata2'',N''U'') is not null    
   begin    
   drop table #tempdata2;    
   end;';    
     
     
 set @sql = @sql + N'    
  create table #tempdata(    
   CheckItemID int null,    
   AssetID int null,    
   AssetName nvarchar(500) null,    
   AssetTypeID int null,    
   AssetTypeName nvarchar(500) null,    
   '+@colnameDefine+'    
  );    
  create table #tempdata2(    
   CheckItemID int null,    
   AssetID int null,    
   AssetName nvarchar(500) null,    
   AssetTypeID int null,    
   AssetTypeName nvarchar(500) null,    
   '+@colnameDefine+'    
  );    
 ';    
     
     
 --select * from #tempdata;    
     
 --insert into #tempdata    
 set @sql = @sql +  N'     
  insert into #tempdata     
  select CheckItemId,AssetId,AssetName,AssetTypeId,AssetTypeName,'+@colname+' from    
  (    
  select a.id, a.name ,    
  b.id as checkItemId, b.name as checkItemName, b.statutory, b.enabled,    
  c.id as conditionId, c.isCycleCheck,c.StartDate,c.Highest,c.A,c.Two,c.Three,    
  e.id as dataId, e.checkdate, e.data, e.examiner ,    
  f.id as AssetId, f.assetName ,    
  g.id as AssetTypeId, g.Type as AssetTypeName     
  from devicetypes a     
  left join checkitems b on a.id = b.devicetypeid and b.enabled = 1    
  left join checkitemconditions c on b.id = c.checkitemid    
  left join checkdatalatests e on e.checkitemid = b.id and e.checkitemconditionid = c.id    
  left join assets f on e.assetid = f.id    
  left join assetTypes g on f.assetTypeId = g.id    
  where a.businessUnitId = '+convert(nvarchar(11),@businessUnitId)+'    
  and b.id is not null    
  and a.id = '+convert(nvarchar(11),@deviceTypeId)+'    
  ) as p    
  pivot (    
   min(data) for checkItemName in ('+@colname+')    
  ) as pvt    
  where pvt.assetId is not null    
  order by pvt.assetTypeId, pvt.assetName;';    
      
      
  set @sql = @sql + N'    
   insert into #tempdata2     
   select * from #tempdata;    
       
   '+ @declareString +'     
       
   declare @checkItemId2 int;    
   declare @assetId2 int;    
       
   declare ccData1 cursor for select distinct checkItemId, assetId from #tempdata;    
   open ccData1;    
        
   while 1=1    
   begin    
    fetch next from ccData1 into @checkItemId2, @assetId2;    
    if @@fetch_status <> 0    
    begin    
     break;    
    end;    
        
    '+@resetString+'     
              
    select  '+@selectString+'     
    from #tempdata where assetId = @assetId2 and checkItemId = @checkItemId2 ;     
    update #tempdata2 set '+@updateString+' where   assetId = @assetId2 and checkItemId = @checkItemId2;     
        
   end;    
        
   close ccData1;    
   deallocate ccData1; ';    
        
  if @orderDirection <> 'asc' and @orderDirection <> 'desc'     
   begin     
    set @orderDirection = 'asc';     
   end;     
       
  set @start = (@page -1) * @pageCount + 1;    
  set @end = @page * @pageCount ;    
       
  set @sql = @sql + N' select * from (    
   select t.*,row_number() over (order by assetTypeName '+@orderDirection+', assetName '+@orderDirection+') as rw  from (     
   select distinct *     
  from #tempdata2     
   where 1=1 ';    
  if @filterByAssetTypeId <> 0    
  begin    
   set @sql = @sql + ' and assetTypeId = ' + convert(nvarchar(11), @filterByAssetTypeId) + ' ' ;    
  end;    
      
  if @filterByAssetId <> 0    
  begin    
   set @sql = @sql + ' and assetId = ' + convert(nvarchar(11), @filterByAssetId) + ' ' ;    
  end;     
      
  set @sql = @sql + N') t ) as tt    
  where tt. rw between '+convert(nvarchar(11),@start)+' and '+convert(nvarchar(11),@end)+' ';    
      
  if @orderBy <> ''    
  begin     
   set @sql = @sql + N'    
    order by tt.'+@orderBy+' '+@orderDirection+';    
   ';    
  end    
  else    
  begin    
   set @sql = @sql + N'    
    order by tt.assetTypeName, tt.assetName;    
   ';    
  end;    
      
      
      
  set @sql =  @sql + N'     
       
   select count(distinct assetId ) as totalCount    
  from #tempdata2     
   where 1=1 ';    
  if @filterByAssetTypeId <> 0    
  begin    
   set @sql = @sql + ' and assetTypeId = ' + convert(nvarchar(11), @filterByAssetTypeId) + ' ' ;    
  end;    
      
  if @filterByAssetId <> 0    
  begin    
   set @sql = @sql + ' and assetId = ' + convert(nvarchar(11), @filterByAssetId) + ' ' ;    
  end;     
      
      
      
  print @sql  
 --select @sql;    
 exec(@sql);    
     
     
END