USP_DATALIST_APPUSERWORKSPACEDONOR

CREATE procedure dbo.USP_DATALIST_APPUSERWORKSPACEDONOR
(
    @CURRENTAPPUSERID uniqueidentifier,
    @DATEFILTER tinyint = 0,
    @AMOUNT money = 0
)
as
    set nocount on;

    declare @ISSYSADMIN bit = dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID);
    declare @SITEREQUIREDFORUSER bit;
    declare @USERSITES table (SITEID uniqueidentifier);

    if @ISSYSADMIN = 0
    begin
        set @SITEREQUIREDFORUSER = dbo.UFN_SITEREQUIREDFORUSER(@CURRENTAPPUSERID);

        if @SITEREQUIREDFORUSER = 1
        begin
            insert into @USERSITES
            select SITEID from dbo.UFN_SITESFORUSER(@CURRENTAPPUSERID);
        end
    end

    declare @DATE datetime;
    set @DATE = getDate();

    declare @STARTDATE datetime;
    declare @ENDDATE datetime;

    if @DATEFILTER = 0    -- today

    begin
        set @STARTDATE = dbo.UFN_DATE_GETEARLIESTTIME(@DATE);
        set @ENDDATE = dbo.UFN_DATE_GETLATESTTIME(@DATE);
    end
    if @DATEFILTER = 1    -- yesterday

    begin
        set @STARTDATE = dbo.UFN_DATE_GETEARLIESTTIME(dateadd(day, -1, @DATE));
        set @ENDDATE = dbo.UFN_DATE_GETLATESTTIME(dateadd(day, -1, @DATE));
    end
    if @DATEFILTER = 2    -- this week

    begin
        set @STARTDATE = dbo.UFN_DATE_THISWEEK_FIRSTDAY(@DATE, 0);
        set @ENDDATE = dbo.UFN_DATE_THISWEEK_LASTDAY(@DATE, 1);
    end
    if @DATEFILTER = 3    -- this month

    begin
        set @STARTDATE = dbo.UFN_DATE_THISMONTH_FIRSTDAY(@DATE, 0);
        set @ENDDATE = dbo.UFN_DATE_THISMONTH_LASTDAY(@DATE, 1);
    end
    if @DATEFILTER = 4    -- last month

    begin
        set @STARTDATE = dbo.UFN_DATE_LASTMONTH_FIRSTDAY(@DATE, 0);
        set @ENDDATE = dbo.UFN_DATE_LASTMONTH_LASTDAY(@DATE, 1);
    end

    select
        REVENUE.ID,
        REVENUE.CONSTITUENTID,
        REVENUE.DATE,
        NF.NAME,
        REVENUESPLIT.AMOUNT,
        DESIGNATION.NAME,
        REVENUE.TRANSACTIONTYPE,
        REVENUESPLIT.BASECURRENCYID
    from
        dbo.REVENUE
    inner join
        dbo.REVENUESPLIT on REVENUESPLIT.REVENUEID = REVENUE.ID
    cross apply dbo.UFN_CONSTITUENT_DISPLAYNAME(REVENUE.CONSTITUENTID) NF
    inner join
        dbo.DESIGNATION on DESIGNATION.ID = REVENUESPLIT.DESIGNATIONID
    where
        (REVENUE.TRANSACTIONTYPECODE in (0, 1, 3, 4) and REVENUESPLIT.APPLICATIONCODE in (0, 2, 3, 6, 7))
        and REVENUESPLIT.AMOUNT > @AMOUNT
        and (REVENUE.DATE between @STARTDATE and @ENDDATE)
        and
        (
            @ISSYSADMIN = 1
            or
            (
                dbo.UFN_SECURITY_APPUSER_GRANTED_DATALIST_FORCONSTIT(@CURRENTAPPUSERID,'1d812f19-dce2-4fea-ae33-b16e47c29ae6', REVENUE.CONSTITUENTID) = 1
                and
                (
                    @SITEREQUIREDFORUSER = 0
                    or
                    exists(select 1 from dbo.UFN_SITEID_MAPFROM_CONSTITUENTID(REVENUE.CONSTITUENTID) CONSTITSITES
                            inner join @USERSITES USERSITES on CONSTITSITES.SITEID = USERSITES.SITEID)
                )
            )
        )
    order by
        REVENUESPLIT.AMOUNT desc, NF.NAME;

    return 0;