UFN_SMARTQUERY_EVENTREGISTRANTATTENDANCE

create function dbo.UFN_SMARTQUERY_EVENTREGISTRANTATTENDANCE
(
    @SPECIFICEVENT bit = 1,
    @EVENTID uniqueidentifier = null,
    @DATETYPE int = 1,
    @STARTDATE datetime = null,
    @ENDDATE datetime = null,
    @ONLYATTENDED bit = 0,
    @CURRENTAPPUSERID uniqueidentifier = null,
    @MAXROWS int = 500
)
returns @RETURNTABLE table
(
    ID uniqueidentifier not null,
    NAME nvarchar(150),
    EVENTNAME nvarchar(150),
    ATTENDED bit
)
as
begin

    declare @ISADMIN bit;
    declare @APPUSER_IN_NONRACROLE bit;
    declare @APPUSER_IN_NOSECGROUPROLE bit;

    set @ISADMIN = dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID);
    set @APPUSER_IN_NONRACROLE = dbo.UFN_SECURITY_APPUSER_IN_NONRACROLE(@CURRENTAPPUSERID);
    set @APPUSER_IN_NOSECGROUPROLE = dbo.UFN_SECURITY_APPUSER_IN_NO_SECURITY_GROUP_ROLE(@CURRENTAPPUSERID);

    if @SPECIFICEVENT = 1
    begin
        insert into @RETURNTABLE
        (
            ID,
            NAME,
            EVENTNAME,
            ATTENDED
        )
        (
            select
                CONSTITUENT.ID,
                NF.NAME,
                EVENT.NAME,
                REGISTRANT.ATTENDED
            from
                dbo.CONSTITUENT
            inner join
                dbo.REGISTRANT on REGISTRANT.CONSTITUENTID = CONSTITUENT.ID
            inner join
                dbo.EVENT on EVENT.ID = REGISTRANT.EVENTID
            outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(CONSTITUENT.ID) NF
            where
                ( (@ISADMIN = 1) or (@APPUSER_IN_NONRACROLE = 1) or (dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT(@CURRENTAPPUSERID, CONSTITUENT.ID, @APPUSER_IN_NOSECGROUPROLE) = 1) )
                and ( (REGISTRANT.EVENTID = @EVENTID) or (@EVENTID is null) )
                and ( (REGISTRANT.ATTENDED = @ONLYATTENDED) or (@ONLYATTENDED = 0) )
        );
    end
    else
    begin
        declare @CURRENTDATE datetime;
        set @CURRENTDATE = GETDATE();

        if @DATETYPE = 0 begin
            -- This year

            set @STARTDATE = dbo.UFN_DATE_THISCALENDARYEAR_FIRSTDAY(@CURRENTDATE, 0);
            set @ENDDATE = dbo.UFN_DATE_THISCALENDARYEAR_LASTDAY(@CURRENTDATE, 1);
        end else if @DATETYPE = 1 begin
            -- Last year

            set @STARTDATE = dbo.UFN_DATE_LASTCALENDARYEAR_FIRSTDAY(@CURRENTDATE, 0);
            set @ENDDATE = dbo.UFN_DATE_LASTCALENDARYEAR_LASTDAY(@CURRENTDATE, 1);
        end else if @DATETYPE = 2 begin
            -- Any year

            set @STARTDATE = '1753-01-02';
            set @ENDDATE = '9999-12-30';
        end

        insert into @RETURNTABLE
        (
            ID,
            NAME,
            EVENTNAME,
            ATTENDED
        )
        (
            select
                CONSTITUENT.ID,
                NF.NAME,
                EVENT.NAME,
                REGISTRANT.ATTENDED
            from
                dbo.CONSTITUENT
            inner join
                dbo.REGISTRANT on REGISTRANT.CONSTITUENTID = CONSTITUENT.ID
            inner join
                dbo.EVENT on EVENT.ID = REGISTRANT.EVENTID
            outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(CONSTITUENT.ID) NF
            where
                ( (@ISADMIN = 1) or (@APPUSER_IN_NONRACROLE = 1) or (dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT(@CURRENTAPPUSERID, CONSTITUENT.ID, @APPUSER_IN_NOSECGROUPROLE) = 1) )
                and ( (EVENT.STARTDATE <= @ENDDATE) and (EVENT.ENDDATE >= @STARTDATE) )
                and ( (REGISTRANT.ATTENDED = @ONLYATTENDED) or (@ONLYATTENDED = 0) )
        );
    end

    return;
end