USP_DATALIST_EVENTINVITATIONINVITEES

CREATE procedure dbo.[USP_DATALIST_EVENTINVITATIONINVITEES]
(
  @MAXROWS int,
  @INVITATIONID uniqueidentifier,
  @INVITEEID uniqueidentifier = null,
  @STATUSCODE tinyint = null,
  @INCLUDEINSEND tinyint = null,
  @CURRENTAPPUSERID uniqueidentifier = null
)
as
  set nocount on;

  declare @ISSYSADMIN bit = dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID);

  declare @INVITEES table
  (
    ID uniqueidentifier,
    CONSTITUENTID uniqueidentifier,
    DECLINED bit,
    EXCLUDEDFROMLASTSEND bit,
    SENTON datetime,
    INVITEERESENDID uniqueidentifier,
    REGISTRANTID uniqueidentifier
  );

  insert into @INVITEES
  select
    [INVITEE].[ID],
    [INVITEE].[CONSTITUENTID],
    [INVITEE].[DECLINED],
    [INVITEE].[EXCLUDEDFROMLASTSEND],
    --This sub-select mimics the functionality of the scalar function:  UFN_INVITATION_SENTONMOSTRECENT

    (
      select top (1)
        [INVITATIONHISTORY].[DATECHANGED]
      from dbo.[INVITATIONHISTORY]
      --Join on all 3 fields here so that it uses the index on this table...

      inner join dbo.[INVITEEHISTORY] on [INVITEEHISTORY].[INVITATIONHISTORYID] = [INVITATIONHISTORY].[ID]
        and [INVITEEHISTORY].[CONSTITUENTID] = [INVITEE].[CONSTITUENTID]
        and [INVITEEHISTORY].[EVENTID] = [INVITEE].[EVENTID]
      where [INVITATIONHISTORY].[PARAMETERSETID] = cast(@INVITATIONID as nvarchar(36))  --cast to string so that it can use the index

      order by [INVITATIONHISTORY].[DATECHANGED] desc
    ) as [SENTON],
    [INVITEERESEND].[ID] as [INVITEERESENDID],
    [REGISTRANT].[ID] as [REGISTRANTID]
  from dbo.[INVITEE]
  left join dbo.[INVITEERESEND] on [INVITEERESEND].[INVITEEID] = [INVITEE].[ID] and [INVITEERESEND].[INVITATIONID] = [INVITEE].[INVITATIONID]
  left join dbo.[REGISTRANT] on [REGISTRANT].[CONSTITUENTID] = [INVITEE].[CONSTITUENTID] and [REGISTRANT].[EVENTID] = [INVITEE].[EVENTID]
  where [INVITEE].[INVITATIONID] = @INVITATIONID
  and (@INVITEEID is null or [INVITEE].[ID] = @INVITEEID)
  and (
    @STATUSCODE is null
    or (
      @STATUSCODE = 0
      and [REGISTRANT].[ID] is not null
    )
    or (
      @STATUSCODE = 1
      and [INVITEE].[DECLINED] = 1
    )
    or (
      @STATUSCODE = 2
      and [REGISTRANT].[ID] is not null
      and [INVITEE].[DECLINED] = 1
    )
    or (
      @STATUSCODE = 3
      and [REGISTRANT].[ID] is null
      and [INVITEE].[DECLINED] = 0
    )
  );

  if @ISSYSADMIN = 0
  begin
    delete INVITEES
    from @INVITEES INVITEES
    where 
      not exists 
        ( select 1 from dbo.UFN_SITEID_MAPFROM_CONSTITUENTID(INVITEES.CONSTITUENTID) CONSTITUENTSITES 
                  where exists 
                    ( select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID, '20EF266D-A1B2-4345-947C-206ACA5A1103', 2) SITES
                                where SITES.SITEID = CONSTITUENTSITES.SITEID 
                                  or (SITES.SITEID is null and CONSTITUENTSITES.SITEID is null) 
                    ) 
        );
  end

  select top (@MAXROWS)
    [INVITEES].[ID],
    [INVITEES].[CONSTITUENTID],
    dbo.UFN_NAMEFORMAT_08(null, [CONSTITUENT].[KEYNAME], [CONSTITUENT].[FIRSTNAME], [CONSTITUENT].[MIDDLENAME], null, null, null, null, null, null, null) as [CONSTITUENTNAME],
    [INVITEES].[SENTON],
    cast((case when [INVITEES].[REGISTRANTID] is null then 0 else 1 end) as bit) as [REGISTERED],
    (case when [INVITEES].[REGISTRANTID] is null then
       N'Not registered'
     else
       N'Registered'
     end) as [REGISTEREDCAPTION],
    [INVITEES].[INVITEERESENDID],
    (case when [INVITEES].[SENTON] is null then
       'False'
     else
       'True'
     end) as [HASRECEIVEDINVITATION],
    [INVITEES].[DECLINED],
    (case when [INVITEES].[DECLINED] = 0 then
       'Not declined'
     else
       'Declined'
     end) as [DECLINEDCAPTION],
    cast
    (
      (case when ([INVITEES].[INVITEERESENDID] is not null or ([INVITEES].[DECLINED] = 0 and [INVITEES].[SENTON] is null)) then
         1
       else
         0
       end)
      as bit
    ) as [INCLUDEINSEND],
    (case when ([INVITEES].[INVITEERESENDID] is not null or ([INVITEES].[DECLINED] = 0 and [INVITEES].[SENTON] is null)) then
       N'Include in next send'
     else
       N'Do not include in next send'
     end) as [INCLUDEINSENDCAPTION],
    [INVITEES].[EXCLUDEDFROMLASTSEND],
    (case when [INVITEES].[EXCLUDEDFROMLASTSEND] = 1 then
       'Excluded from last send'
     else
       'Included in last send'
     end) as [EXCLUDEDFROMLASTSENDCAPTION]

  from @INVITEES [INVITEES]
  left join dbo.[CONSTITUENT] on [CONSTITUENT].[ID] = [INVITEES].[CONSTITUENTID]
  where (
    @INCLUDEINSEND is null
    or (
      @INCLUDEINSEND = 0
      and
      not ([INVITEES].[INVITEERESENDID] is not null or ([INVITEES].[DECLINED] = 0 and [INVITEES].[SENTON] is null))
    )
    or (
      @INCLUDEINSEND = 1
      and
      ([INVITEES].[INVITEERESENDID] is not null or ([INVITEES].[DECLINED] = 0 and [INVITEES].[SENTON] is null))
    )
  )
  order by [INCLUDEINSEND] desc, [CONSTITUENTNAME] asc;

  return 0;