USP_DATALIST_CONFIRMATIONEMAIL

CREATE procedure dbo.USP_DATALIST_CONFIRMATIONEMAIL
(
  @EVENTID uniqueidentifier,
  @CURRENTAPPUSERID uniqueidentifier,
  @TODISPLAYNAME nvarchar(100) = '',
  @TOADDRESS nvarchar(100) = '',
  @CONFIRMATIONTYPECODE tinyint = null,
  @DATEADDED datetime = null,
  @SITEFILTERMODE tinyint = 0,
  @SITESSELECTED xml = null,
  @SECURITYFEATUREID uniqueidentifier = null,
  @SECURITYFEATURETYPE tinyint = null,
  @MAXROWS int = 1000
)
as
  set nocount on;

  select top (@MAXROWS) m.EmailJobID, m.ToDisplayName, m.ToAddress, j.Subject, r.Amount, t.ConfirmationType, m.DateAdded, t.ConfirmationTypeCode
    from EmailJobExtension m (nolock)
    inner join emailjob j (nolock) on m.emailjobid = j.id
    inner join email em (nolock) on j.emailid = em.id
    left outer join eventemailtemplate t (nolock) on em.emailtemplateid = t.emailtemplateid
    left outer join event e (nolock) on m.eventid= e.id 
    left outer join revenue r (nolock) on m.revenueid = r.id
  where m.eventid = @eventid
    and (m.ToDisplayName like @TODISPLAYNAME or @TODISPLAYNAME = '')
    and (m.ToAddress like @TOADDRESS or @TOADDRESS = '')
    and (t.ConfirmationTypeCode <> 100) -- remove participant communication
    and (t.ConfirmationTypeCode = @CONFIRMATIONTYPECODE or @CONFIRMATIONTYPECODE is null)
    and (datediff(day, @DATEADDED, m.DateAdded) = 0 or @DATEADDED is null)
    --site security
    and
    (
      exists
      (
        select 1 from UFN_SITEID_MAPFROM_EVENTID(m.eventid) EVENTSITE
        where (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[EVENTSITE].[SITEID] or (SITEID is null and [EVENTSITE].[SITEID] is null)))
      )
    )
    --site filter
    and
    (
      @SITEFILTERMODE = 0
      or
      exists
      (
        select 1 from UFN_SITEID_MAPFROM_EVENTID(m.eventid) EVENTSITE
          where EVENTSITE.SITEID in
          (
            select SITEID from dbo.UFN_SITE_BUILDDATALISTSITEFILTER(@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED)
          )
      )
    )