USP_REPORT_TRIBUTEREVENUE

CREATE procedure dbo.USP_REPORT_TRIBUTEREVENUE
(
    @DATETYPE tinyint = null,
    @DATERANGEDISPLAY nvarchar(100) = '',
    @STARTDATE datetime = null, 
    @ENDDATE datetime = null,
    @TRIBUTEQUERY uniqueidentifier = null,
    @APPEALID uniqueidentifier = null,
    @DESIGNATIONID uniqueidentifier = null,
    @TRIBUTEID uniqueidentifier = null,
    @TRIBUTETYPECODEID uniqueidentifier = null,
    @ISUKINSTALLED tinyint = null,
    @REPORTUSERID nvarchar(128) = null,
    @CURRENCYCODE tinyint = null, --3 = My base, (null, 1) = Organization

    @ALTREPORTUSERID nvarchar(128) = null
)
with execute as owner
as
    set nocount on;

    declare @CURRENTAPPUSERID uniqueidentifier;
    declare @ISADMIN bit;
    declare @APPUSER_IN_NONRACROLE bit;
    declare @APPUSER_IN_NOSECGROUPROLE bit;
    declare @APPUSER_IN_NONSITEROLE bit;
    declare @APPUSER_IN_NOSITEROLE bit;

    set @CURRENTAPPUSERID = dbo.UFN_APPUSER_GETREPORTAPPUSERID(@REPORTUSERID, @ALTREPORTUSERID);
    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);
    set @APPUSER_IN_NONSITEROLE = dbo.UFN_SECURITY_APPUSER_IN_NONSITEROLE(@CURRENTAPPUSERID);
    set @APPUSER_IN_NOSITEROLE = dbo.UFN_SECURITY_APPUSER_IN_NO_SITE_ROLE(@CURRENTAPPUSERID);

    declare @ORGANIZATIONCURRENCYID uniqueidentifier = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();
    declare @SELECTEDCURRENCYID uniqueidentifier;

    if @CURRENCYCODE = 3
    begin
        select @SELECTEDCURRENCYID = CURRENCYSET.BASECURRENCYID
        from dbo.CURRENCYSET
        where CURRENCYSET.ID = dbo.UFN_CURRENCYSET_GETAPPUSERCURRENCYSET(@CURRENTAPPUSERID);
    end
    else
    begin
        set @SELECTEDCURRENCYID = @ORGANIZATIONCURRENCYID;
    end

    declare @SELECTEDCURRENCYISO nvarchar(3);
    declare @SELECTEDCURRENCYDECIMALDIGITS tinyint;
    declare @SELECTEDCURRENCYSYMBOL nvarchar(5);
    declare @SELECTEDCURRENCYSYMBOLDISPLAYSETTINGCODE tinyint;
    declare @SELECTEDCURRENCYROUNDINGTYPECODE tinyint;

    select
        @SELECTEDCURRENCYISO = CURRENCYPROPERTIES.ISO4217,
        @SELECTEDCURRENCYDECIMALDIGITS = CURRENCYPROPERTIES.DECIMALDIGITS,
        @SELECTEDCURRENCYSYMBOL = CURRENCYPROPERTIES.CURRENCYSYMBOL,
        @SELECTEDCURRENCYSYMBOLDISPLAYSETTINGCODE = CURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE,
        @SELECTEDCURRENCYROUNDINGTYPECODE = CURRENCYPROPERTIES.ROUNDINGTYPECODE
    from
        dbo.UFN_CURRENCY_GETPROPERTIES(@SELECTEDCURRENCYID) CURRENCYPROPERTIES

    --Bug 130390 - AdamBu - Create a temp table for granted tributes.

    create table #TRIBUTESGRANTED(
        ID uniqueidentifier,
        TRIBUTETEXT nvarchar(255) collate DATABASE_DEFAULT,
        ISACTIVE bit,
        DATEADDED datetime,
        TRIBUTEEID uniqueidentifier
    )
    insert into #TRIBUTESGRANTED
    select
        ID,
        TRIBUTETEXT,
        ISACTIVE,
        DATEADDED,
        TRIBUTEEID
    from dbo.TRIBUTE
    where (@TRIBUTEID is null or TRIBUTE.ID = @TRIBUTEID)
        and (@TRIBUTETYPECODEID is null or TRIBUTE.TRIBUTETYPECODEID = @TRIBUTETYPECODEID)
        and (TRIBUTE.TRIBUTEEID is null
            or @ISADMIN = 1
            or(
                (@APPUSER_IN_NONRACROLE = 1
                    or dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT(@CURRENTAPPUSERID, TRIBUTE.TRIBUTEEID, @APPUSER_IN_NOSECGROUPROLE) = 1
                ) 
                and(@APPUSER_IN_NONSITEROLE = 1
                    or dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT_BYSITE(@CURRENTAPPUSERID, TRIBUTE.TRIBUTEEID, @APPUSER_IN_NOSITEROLE) = 1
                )
            )
        )

    --Bug 130390 - AdamBu - If a selection was given, remove the items in the temp table that are not in

    --    that selection.

    if @TRIBUTEQUERY is not null begin
        if not exists(select ID from dbo.IDSETREGISTER where ID = @TRIBUTEQUERY) 
            raiserror('ID set does not exist in the database.', 13, 1);

        declare @DBOBJECTNAME nvarchar(128);
        declare @DBOBJECTTYPE smallint;
        select @DBOBJECTNAME = DBOBJECTNAME, 
            @DBOBJECTTYPE = OBJECTTYPE 
        from dbo.IDSETREGISTER 
        where ID = @TRIBUTEQUERY;

        if @DBOBJECTTYPE = 1 
            set @DBOBJECTNAME = @DBOBJECTNAME + '()';
        else if @DBOBJECTTYPE = 2 
            set @DBOBJECTNAME = @DBOBJECTNAME + '(''' + convert(nvarchar(36), @TRIBUTEQUERY) + ''')';

        declare @SELECTIONSQL nvarchar(max) = '
            delete #TRIBUTESGRANTED
            where ID not in(
                select ID
                from dbo.'+@DBOBJECTNAME+'
            )'

        exec sp_executesql @SELECTIONSQL
    end

    --Bug 130390 - AdamBu - Use a temp table to hold info about granteed revenue,

    --    allowing us to cut down on the number of constituent security checks needed.

    --    Site security checks are handled by joining to the designation table variable.

    --    Joining to the tribute temp table simply to ensure we don't waste time checking 

    --    security on records that won't be used anyway.

    create table #REVENUEGRANTED(
        ID uniqueidentifier,
        DATE datetime,
        TRANSACTIONTYPE nvarchar(21) collate DATABASE_DEFAULT,
        CONSTITUENTID uniqueidentifier,
        APPEALID uniqueidentifier
    )
    insert into #REVENUEGRANTED
    select distinct
        REVENUE.ID,
        REVENUE.DATE,
        REVENUE.TRANSACTIONTYPE,
        REVENUE.CONSTITUENTID,
        REVENUE.APPEALID
    from dbo.REVENUE with (nolock)
        inner join dbo.REVENUETRIBUTE on REVENUETRIBUTE.REVENUEID=REVENUE.ID 
        inner join #TRIBUTESGRANTED TRIBUTESGRANTED on TRIBUTESGRANTED.ID = REVENUETRIBUTE.TRIBUTEID
    where 
        (
            @ISADMIN = 1
            or
            (
                (
                    (@APPUSER_IN_NONRACROLE = 1
                        or dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT(@CURRENTAPPUSERID, REVENUE.CONSTITUENTID, @APPUSER_IN_NOSECGROUPROLE) = 1
                    ) 
                    and(@APPUSER_IN_NONSITEROLE = 1 or 
                        (
                            -- Evaluate constituent site security

                            dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT_BYSITE(@CURRENTAPPUSERID, REVENUE.CONSTITUENTID, @APPUSER_IN_NOSITEROLE) = 1 and
                            -- Evaluate revenue site security

                            exists
                            (
                                select HASPERMISSION
                                from dbo.UFN_SITEID_MAPFROM_REVENUEID(REVENUE.ID) REVSITES
                                cross apply dbo.UFN_REPORT_HASUSERSITEPERMISSION(@CURRENTAPPUSERID, '479282cc-a399-48b1-a484-84e09db40548', REVSITES.SITEID)
                            )
                        )
                    )
                )
            )
        );

    --Bug 130390 - AdamBu - Call bulk functions in CTEs, to avoid calling them more than necessary.

    with CTE_REVENUETRIBUTEBULK
    as (
        select
            ID,
            REVENUEID,
            TRIBUTEID,
            AMOUNTINCURRENCY
        from 
            dbo.UFN_REVENUETRIBUTE_GETAMOUNTSINCURRENCY_BULK(
                @SELECTEDCURRENCYID, 
                @ORGANIZATIONCURRENCYID, 
                @SELECTEDCURRENCYDECIMALDIGITS, 
                @SELECTEDCURRENCYROUNDINGTYPECODE
            )
    ),
    CTE_REVENUETRIBUTEBULKTAXCLAIM
    as (
        select
            ID,
            AMOUNTINCURRENCY
        from 
            dbo.UFN_REVENUETRIBUTETAXCLAIMAMOUNT_GETAMOUNTSINCURRENCY_BULK(
                @SELECTEDCURRENCYID, 
                @ORGANIZATIONCURRENCYID, 
                @SELECTEDCURRENCYDECIMALDIGITS, 
                @SELECTEDCURRENCYROUNDINGTYPECODE
            )
    ),
    --Bug 130390 - AdamBu - Avoid using a subselect to calculate total amounts.

    CTE_TRIBUTES
    as (
        select
            TRIBUTEFILTER.ID,
            TRIBUTEFILTER.TRIBUTETEXT,
            TRIBUTEFILTER.ISACTIVE,
            TRIBUTEFILTER.DATEADDED,
            TRIBUTEFILTER.TRIBUTEEID,
            isnull(sum(CTE_REVENUETRIBUTEBULK.AMOUNTINCURRENCY), 0) as TOTALREVENUETRIBUTEAMOUNT,
            isnull(sum(CTE_REVENUETRIBUTEBULK.AMOUNTINCURRENCY + isnull(CTE_REVENUETRIBUTEBULKTAXCLAIM.AMOUNTINCURRENCY, 0)), 0) as TOTALGROSSAMOUNT
        from #TRIBUTESGRANTED TRIBUTEFILTER
            inner join CTE_REVENUETRIBUTEBULK on CTE_REVENUETRIBUTEBULK.TRIBUTEID = TRIBUTEFILTER.ID
            inner join #REVENUEGRANTED REVENUEGRANTED on CTE_REVENUETRIBUTEBULK.REVENUEID = REVENUEGRANTED.ID
            left join CTE_REVENUETRIBUTEBULKTAXCLAIM on CTE_REVENUETRIBUTEBULK.ID = CTE_REVENUETRIBUTEBULKTAXCLAIM.ID
        group by
            TRIBUTEFILTER.ID,
            TRIBUTEFILTER.TRIBUTETEXT,
            TRIBUTEFILTER.ISACTIVE,
            TRIBUTEFILTER.DATEADDED,
            TRIBUTEFILTER.TRIBUTEEID
    )
    select
        'http://www.blackbaud.com/TRIBUTEID?TRIBUTEID=' + CONVERT(nvarchar(36), CTE_TRIBUTES.ID) as TRIBUTEID,
        'http://www.blackbaud.com/DONORID?DONORID=' + CONVERT(nvarchar(36), DONOR.ID) as DONORID,
        'http://www.blackbaud.com/TRIBUTEEID?TRIBUTEEID=' + CONVERT(nvarchar(36), TRIBUTEE.ID) as TRIBUTEEID,
        'http://www.blackbaud.com/REVENUETRANSACTIONID?REVENUETRANSACTIONID=' + CONVERT(nvarchar(36), REVENUEGRANTED.ID) as REVENUETRANSACTIONID,
        CTE_TRIBUTES.ID,
        CTE_TRIBUTES.TRIBUTETEXT,
        TRIBUTEE_NF.NAME as TRIBUTEENAME,
        TRIBUTEE.KEYNAME as TRIBUTEEKEYNAME,
        TRIBUTEE.FIRSTNAME as TRIBUTEEFIRSTNAME,
        CTE_TRIBUTES.ISACTIVE,
        CTE_TRIBUTES.DATEADDED as DATECREATED,
        CTE_TRIBUTES.TOTALREVENUETRIBUTEAMOUNT,
        CTE_REVENUETRIBUTEBULK.AMOUNTINCURRENCY as REVENUETRIBUTEAMOUNT,
        CTE_TRIBUTES.TOTALGROSSAMOUNT,
        CTE_REVENUETRIBUTEBULK.AMOUNTINCURRENCY + CTE_REVENUETRIBUTEBULKTAXCLAIM.AMOUNTINCURRENCY as GROSSAMOUNT,
        REVENUEGRANTED.TRANSACTIONTYPE as REVENUETYPE,
        DONOR_NF.NAME as DONORNAME,
        DONOR.KEYNAME as DONORKEYNAME,
        DONOR.FIRSTNAME as DONORFIRSTNAME,
        REVENUEGRANTED.DATE as REVENUEDATE,
        @SELECTEDCURRENCYID SELECTEDCURRENCYID,
        @SELECTEDCURRENCYISO as SELECTEDCURRENCYISO,
        @SELECTEDCURRENCYDECIMALDIGITS as SELECTEDCURRENCYDECIMALDIGITS,
        @SELECTEDCURRENCYSYMBOL as SELECTEDCURRENCYSYMBOL,
        @SELECTEDCURRENCYSYMBOLDISPLAYSETTINGCODE as SELECTEDCURRENCYSYMBOLDISPLAYSETTINGCODE
    from CTE_TRIBUTES 
        inner join CTE_REVENUETRIBUTEBULK on CTE_TRIBUTES.ID = CTE_REVENUETRIBUTEBULK.TRIBUTEID
        inner join #REVENUEGRANTED REVENUEGRANTED on CTE_REVENUETRIBUTEBULK.REVENUEID = REVENUEGRANTED.ID 
        left join dbo.CONSTITUENT TRIBUTEE with (nolock) on CTE_TRIBUTES.TRIBUTEEID = TRIBUTEE.ID
        left join dbo.CONSTITUENT DONOR with (nolock) on REVENUEGRANTED.CONSTITUENTID = DONOR.ID
        left join CTE_REVENUETRIBUTEBULKTAXCLAIM on CTE_REVENUETRIBUTEBULK.ID = CTE_REVENUETRIBUTEBULKTAXCLAIM.ID
        outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(TRIBUTEE.ID) TRIBUTEE_NF
        outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(DONOR.ID) DONOR_NF
    where
        (@STARTDATE is null or REVENUEGRANTED.DATE >= dbo.UFN_DATE_GETEARLIESTTIME(@STARTDATE))
        and (@ENDDATE is null or REVENUEGRANTED.DATE <= dbo.UFN_DATE_GETLATESTTIME(@ENDDATE))
        and (@APPEALID is null or REVENUEGRANTED.APPEALID = @APPEALID)
        and (@DESIGNATIONID is null or exists (select ID from dbo.REVENUESPLIT where DESIGNATIONID = @DESIGNATIONID and REVENUEID = REVENUEGRANTED.ID))
    order by CTE_TRIBUTES.TRIBUTETEXT,TRIBUTEE.KEYNAME,TRIBUTEE.FIRSTNAME,CTE_TRIBUTES.DATEADDED,DONOR.KEYNAME,DONOR.FIRSTNAME

    drop table #TRIBUTESGRANTED
    drop table #REVENUEGRANTED