USP_DATALIST_COMMITTEDBATCHES_2

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

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

  select @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 OWNER,                            
      BATCH.STATUS,
      BATCHTYPECATALOG.CATEGORY,
      [BATCH].[DATECHANGED],
      coalesce(BATCHTYPECATALOG.BATCHDELETERECORDOPERATIONID, ''5345164C-DD4F-4B6E-9CF1-10E1B25E3F4F'') BATCHDELETERECORDOPERATIONID,
      BATCH.STATUSCODE,
      case
      when exists (select 1 
        from dbo.BUSINESSPROCESSOUTPUT
        inner join dbo.BATCHSTATUS on BATCHSTATUS.PARAMETERSETID = BATCH.ID
        and BUSINESSPROCESSOUTPUT.BUSINESSPROCESSSTATUSID = BATCHSTATUS.ID
        and BUSINESSPROCESSOUTPUT.TABLEKEY = ''CONTROL'') 
        and BATCH.STATUSCODE > 0 and len(BATCHTYPECATALOG.CONTROLREPORTREPORTSPECID) > 0 then 1
      else 0
      end CONTROLREPORTVISIBLE,
      case
      when exists (select 1 
        from dbo.BUSINESSPROCESSOUTPUT
        inner join dbo.BATCHSTATUS on BATCHSTATUS.PARAMETERSETID = BATCH.ID
        and BUSINESSPROCESSOUTPUT.BUSINESSPROCESSSTATUSID = BATCHSTATUS.ID
        and BUSINESSPROCESSOUTPUT.TABLEKEY = ''EXCEPTION'') 
        and BATCH.STATUSCODE > 0 and len(BATCHTYPECATALOG.EXCEPTIONREPORTREPORTSPECID) > 0 then
        1
      else 0
      end EXCEPTIONREPORTVISIBLE,
      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 BATCHDESIGN,
      [BATCH].[DATECOMMITTED],          
      case when BATCHTEMPLATE.CUSTOM = 1 then 1 else null end ISCUSTOM,
      coalesce([BATCHTEMPLATESITE].NAME, ''All sites'') as BATCHTEMPLATESITE
    from dbo.BATCH
    left join dbo.BATCH [CHILDBATCH] on BATCH.ID = [CHILDBATCH].ORIGINATINGBATCHID
    inner join BATCHTEMPLATE on BATCH.BATCHTEMPLATEID = BATCHTEMPLATE.ID
    inner join BATCHTYPECATALOG on BATCHTEMPLATE.BATCHTYPECATALOGID = BATCHTYPECATALOG.ID
    left outer join [dbo].[UFN_BATCHTEMPLATE_GETORIGINATINGTEMPLATE]() ORIGINATINGTEMPLATE on ORIGINATINGTEMPLATE.ID = BATCH.BATCHTEMPLATEID
    left join dbo.SITE [BATCHTEMPLATESITE] on BATCHTEMPLATE.SITEID = [BATCHTEMPLATESITE].ID
    left join BATCHWORKFLOWSTATUS [STATUSOWNER] on [STATUSOWNER].BATCHID = BATCH.ID and [STATUSOWNER].APPUSERID = @CURRENTAPPUSERID
    where
      BATCH.STATUSCODE IN (1, 1 + @INCLUDEDELETED)
      and 
      not BATCH.DATECOMMITTED is null
      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.DATECOMMITTED as date) as datetime))) > dateadd(dd, -@NUMDAYS, getdate()))
      )
      and
      (@SITEFILTERMODE = 0 or BATCHTEMPLATE.SITEID in (select SITEID from dbo.UFN_SITE_BUILDDATALISTSITEFILTER (@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED)))
      and
      (
        [STATUSOWNER].BATCHID is not null
        or 
        BATCH.APPUSERID = @CURRENTAPPUSERID
        or     
        dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1
        or
        (
          dbo.UFN_SECURITY_APPUSER_GRANTED_BATCHOWNER(@CURRENTAPPUSERID,BATCH.ID) = 1
          and 
          (
            BATCHTEMPLATE.SITEID is null
            or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[BATCHTEMPLATE].[SITEID] or (SITEID is null and [BATCHTEMPLATE].[SITEID] is null))
          )
        )
      )';

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

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

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

  select @sql = @sql + ' order by BATCH.DATECOMMITTED DESC; ';

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

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

  return 0;