USP_DATALIST_OPENBATCHES_2

CREATE procedure dbo.USP_DATALIST_OPENBATCHES_2
(
  @CURRENTAPPUSERID uniqueidentifier,
  @BATCHDESIGN uniqueidentifier = null,
  @CATEGORY nvarchar(100) = '', 
  @OWNER uniqueidentifier = null, 
  @SITEFILTERMODE tinyint = 0, 
  @SITESSELECTED xml = null, 
  @SECURITYFEATUREID uniqueidentifier = null,
  @SECURITYFEATURETYPE tinyint = null,
  @SPECIFICBATCHTYPEID uniqueidentifier = null, 
  @NUMDAYS int = 7,
  @ONLYEXCEPTIONBATCHES bit = 0
)
with execute as owner
as                
  set nocount on;

  declare @sql nvarchar(max),
  @paramlist nvarchar(4000);

  set @sql = 
  ' 
    declare @DATE datetime
    set @DATE = getdate(); 

    select 
      [BATCH].[ID],
      [BATCH].[BATCHNUMBER],
      [BATCH].[DESCRIPTION],
      (select case when DISPLAYNAME = '''' then USERNAME else DISPLAYNAME end from dbo.APPUSER where ID = BATCH.APPUSERID) as PROCESSOR,
      case
        when [BATCH].[STATUSCODE] = 3 then [BATCH].[STATUS]
        else BATCHWORKFLOWSTATE.NAME
      end as STATUS,
      [BATCHTYPECATALOG].[CATEGORY],
      [BATCH].[COMMENTS],
      [BATCH].[DATECHANGED],
      dbo.UFN_BATCH_GETVALIDATIONREPORTID(BATCH.ID) REPORTID,
      dbo.UFN_BATCHWORKFLOWSTATE_TASKSAVAILABLE(BATCH.BATCHWORKFLOWSTATEID) TASKSAVAILABLE,
      (select ALLOWCOMMIT from dbo.BATCHWORKFLOWSTATE where BATCHWORKFLOWSTATE.ID = BATCH.BATCHWORKFLOWSTATEID) ENABLECOMMIT,
      (select ALLOWEDIT from dbo.BATCHWORKFLOWSTATE where BATCHWORKFLOWSTATE.ID = BATCH.BATCHWORKFLOWSTATEID) ENABLEEDIT,
      coalesce(BATCHTYPECATALOG.BATCHDELETERECORDOPERATIONID, ''5345164C-DD4F-4B6E-9CF1-10E1B25E3F4F'') BATCHDELETERECORDOPERATIONID,
      case 
        when BATCHWORKFLOWSTATE.OVERDUEIN > 0 and datediff(day, (select MAX(BATCHWORKFLOWSTATUS.DATEADDED) from dbo.BATCHWORKFLOWSTATUS where BATCHWORKFLOWSTATUS.BATCHID = BATCH.ID and BATCH.BATCHWORKFLOWSTATEID=BATCHWORKFLOWSTATUS.BATCHWORKFLOWSTATEID), @DATE) >= BATCHWORKFLOWSTATE.OVERDUEIN 
          then 1
        else 0
      end,
      case 
        when BATCHWORKFLOWSTATE.OVERDUEIN > 0 and datediff(day, (select MAX(BATCHWORKFLOWSTATUS.DATEADDED) from dbo.BATCHWORKFLOWSTATUS where BATCHWORKFLOWSTATUS.BATCHID = BATCH.ID and BATCH.BATCHWORKFLOWSTATEID=BATCHWORKFLOWSTATUS.BATCHWORKFLOWSTATEID), @DATE) >= BATCHWORKFLOWSTATE.OVERDUEIN 
          then  ''RES:warning''
        else ''RES:lv_spacer''
      end,
      case
        when (BATCH.ORIGINATINGBATCHID is not null) or ([CHILDBATCH].ID is not null) 
          then 1
        else 0
      end INEXCEPTIONCHAIN,
      case 
        when CUSTOM = 1 
          then ORIGINATINGTEMPLATE.ORIGINATINGTEMPLATENAME 
        else BATCHTEMPLATE.NAME 
      end BATCHTEMPLATE,
      case 
        when BATCHTEMPLATE.CUSTOM = 1 
          then 1 
        else null 
      end ISCUSTOM,
      [BATCH].[DATEADDED],
      [BATCH].STATUSCODE as BATCHSTATUSCODE,
      applock_test(''public'', convert(nvarchar(36), upper(BATCH.ID)), ''Exclusive'', ''Session'') as APPLOCKFREE,
      case when PARENTBATCH.STATUSCODE not in (1, 2) then 0 else 1 end as PARENTCOMMITTED,
      coalesce([BATCHTEMPLATESITE].NAME, ''All sites'') as BATCHTEMPLATESITE,
      BATCHTYPECATALOG.ID as BATCHTYPECATALOGID
    from dbo.BATCH
    left join dbo.BATCH [CHILDBATCH] on BATCH.ID = [CHILDBATCH].ORIGINATINGBATCHID
    left join dbo.BATCH PARENTBATCH on PARENTBATCH.ID = BATCH.ORIGINATINGBATCHID
    inner join dbo.BATCHTEMPLATE on BATCH.BATCHTEMPLATEID = BATCHTEMPLATE.ID
    inner join dbo.BATCHTYPECATALOG on BATCHTEMPLATE.BATCHTYPECATALOGID = BATCHTYPECATALOG.ID
    left join dbo.BATCHWORKFLOWSTATE on BATCH.BATCHWORKFLOWSTATEID = BATCHWORKFLOWSTATE.ID 
    left outer join [dbo].[UFN_BATCHTEMPLATE_GETORIGINATINGTEMPLATE]() ORIGINATINGTEMPLATE on ORIGINATINGTEMPLATE.ID = BATCH.BATCHTEMPLATEID
    left join dbo.SITE [BATCHTEMPLATESITE] on BATCHTEMPLATE.SITEID = [BATCHTEMPLATESITE].ID
    where
      [BATCH].[STATUSCODE] in (0, 3)
      and (@OWNER is null or BATCH.APPUSERID = @OWNER)
      and 
      (    
        BATCH.APPUSERID = @CURRENTAPPUSERID 
        or
        (
          (dbo.UFN_SECURITY_APPUSER_GRANTED_BATCHOWNER(@CURRENTAPPUSERID,BATCH.ID) = 1) 
          and (BATCHTEMPLATE.SITEID is null or (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[BATCHTEMPLATE].[SITEID] or (SITEID is null and [BATCHTEMPLATE].[SITEID] is null))))

        )
      )
      and 
      (
        @SITEFILTERMODE = 0 
        or BATCHTEMPLATE.SITEID in (select SITEID from dbo.UFN_SITE_BUILDDATALISTSITEFILTER (@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED))
      )
      and 
      (
        @NUMDAYS is null
        --The "GETLATESTTIME" date function has been inlined here for performance (the part with "dateadd(ms, -003...")...

        or (@NUMDAYS is not null and dateadd(ms, -003, dateadd(d, 1, cast(cast(BATCH.DATEADDED as date) as datetime))) > dateadd(dd, -@NUMDAYS, getdate()))
      )
  ';

  if @BATCHDESIGN is not null
    set @sql = @sql + ' and (BATCHTEMPLATE.ID = @BATCHDESIGN or ORIGINATINGTEMPLATE.ORIGINATINGTEMPLATEID = @BATCHDESIGN) '

  if @CATEGORY <> '' and @CATEGORY is not null
    set @sql = @sql + ' and BATCHTYPECATALOG.CATEGORY = @CATEGORY'

  if @SPECIFICBATCHTYPEID is not null
    set @sql = @sql + ' and (BATCHTYPECATALOG.ID = @SPECIFICBATCHTYPEID)'

  if @ONLYEXCEPTIONBATCHES = 1 begin
      set @sql = @sql + ' and (dbo.UFN_BATCH_HASBATCHEXCEPTION(BATCH.ID) = 1)'
  end 

  set @sql = @sql + ' order by [BATCH].[DATEADDED];';

  set @paramlist = 
    '@CURRENTAPPUSERID uniqueidentifier,
    @BATCHDESIGN uniqueidentifier = null,
    @OWNER uniqueidentifier = null,
    @CATEGORY nvarchar(100) = '''',
    @SITEFILTERMODE tinyint = 0, 
    @SITESSELECTED xml = null, 
    @SECURITYFEATUREID uniqueidentifier = null, 
    @SECURITYFEATURETYPE tinyint = null,
    @SPECIFICBATCHTYPEID uniqueidentifier = null,
    @NUMDAYS int';

  exec sp_executesql @sql, @paramlist, @CURRENTAPPUSERID, @BATCHDESIGN, @OWNER, @CATEGORY, @SITEFILTERMODE, @SITESSELECTED, @SECURITYFEATUREID, @SECURITYFEATURETYPE, @SPECIFICBATCHTYPEID, @NUMDAYS;

  return 0;