USP_REPORT_CONSTITUENTRECOGNITIONCREDIT

CREATE procedure dbo.USP_REPORT_CONSTITUENTRECOGNITIONCREDIT
(
    @DATETYPE tinyint = null,
    @DATERANGEDISPLAY nvarchar(100) = '',
    @STARTDATE datetime = null, 
    @ENDDATE datetime = null,
    @CONSTITUENTQUERY uniqueidentifier = null,
    @CONSTITUENTID uniqueidentifier = null,
    @REVENUERECOGNITIONTYPECODEID uniqueidentifier = null,
    @REPORTUSERID nvarchar(128) = null,
    @CAMPAIGNID uniqueidentifier = null,
    @CURRENCYCODE tinyint = null, -- (null, 1) = Organization, 0 = Base

    @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;

    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);

    declare @ORGANIZATIONCURRENCYID uniqueidentifier = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();

    set @STARTDATE = dbo.UFN_DATE_GETEARLIESTTIME(@STARTDATE);
    set @ENDDATE = dbo.UFN_DATE_GETLATESTTIME(@ENDDATE);

    declare @DBOBJECTNAME nvarchar(128);
    declare @DBOBJECTTYPE smallint;    

    declare @SITESGRANTED table(
        SITEID uniqueidentifier primary key
    )
    insert into @SITESGRANTED
    select SITEID
    from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,'8F08BE6D-D8D0-45e4-A629-25DDC268DC73', 21)
    where SITEID is not null

    create table #CONSTITUENTSGRANTED(
        ID uniqueidentifier,
        NAME nvarchar(700) collate DATABASE_DEFAULT,
        KEYNAME nvarchar(100) collate DATABASE_DEFAULT,
        FIRSTNAME nvarchar(54) collate DATABASE_DEFAULT,
        ISORGANIZATION bit,
        ISGROUP bit,
        ISCONSTITUENT bit
    )

    insert into #CONSTITUENTSGRANTED
    select
        CONSTITUENT.ID,
        DISPLAYNAME.NAME,
        CONSTITUENT.KEYNAME,
        CONSTITUENT.FIRSTNAME,
        CONSTITUENT.ISORGANIZATION,
        CONSTITUENT.ISGROUP,
        CONSTITUENT.ISCONSTITUENT
    from dbo.CONSTITUENT with (nolock)
        outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(CONSTITUENT.ID) DISPLAYNAME
    where @ISADMIN = 1 
        or @APPUSER_IN_NONRACROLE = 1
        or dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT(@CURRENTAPPUSERID, CONSTITUENT.ID, @APPUSER_IN_NOSECGROUPROLE) = 1

    create index IX_CONSTITUENTSGRANTED_ID on #CONSTITUENTSGRANTED (ID) include (NAME)

    create table #CONSTITUENTFILTER(
        ID uniqueidentifier,
        NAME nvarchar(700) collate DATABASE_DEFAULT,
        KEYNAME nvarchar(100) collate DATABASE_DEFAULT,
        FIRSTNAME nvarchar(54) collate DATABASE_DEFAULT,
        ISORGANIZATION bit,
        ISGROUP bit
    )

    declare @SQLTOEXEC nvarchar(max)='
        insert into #CONSTITUENTFILTER
        select
            CONSTITUENTSGRANTED.ID,
            CONSTITUENTSGRANTED.NAME,
            CONSTITUENTSGRANTED.KEYNAME,
            CONSTITUENTSGRANTED.FIRSTNAME,
            CONSTITUENTSGRANTED.ISORGANIZATION,
            CONSTITUENTSGRANTED.ISGROUP
        from #CONSTITUENTSGRANTED CONSTITUENTSGRANTED
    ' + nchar(13);

    if @CONSTITUENTQUERY is not null
    begin
        if not exists(select ID from dbo.IDSETREGISTER where ID = @CONSTITUENTQUERY) raiserror('ID set does not exist in the database.', 15, 1);
        select @DBOBJECTNAME = DBOBJECTNAME, @DBOBJECTTYPE = OBJECTTYPE from dbo.IDSETREGISTER where ID = @CONSTITUENTQUERY;
        if @DBOBJECTTYPE = 1 set @DBOBJECTNAME = @DBOBJECTNAME + '()';
        else if @DBOBJECTTYPE = 2 set @DBOBJECTNAME = @DBOBJECTNAME + '(''' + convert(nvarchar(36), @CONSTITUENTQUERY) + ''')';

        set @SQLTOEXEC = @SQLTOEXEC + 'inner join dbo.' + @DBOBJECTNAME + ' as SELECTION on CONSTITUENTSGRANTED.ID = SELECTION.[ID]' + nchar(13);
    end

    set @SQLTOEXEC = @SQLTOEXEC + 'where CONSTITUENTSGRANTED.ISCONSTITUENT = 1' + nchar(13);

    if @CONSTITUENTID is not null
    begin
        set @SQLTOEXEC = @SQLTOEXEC + 'and CONSTITUENTSGRANTED.ID = @CONSTITUENTID' + nchar(13);
    end

    exec sp_executesql @SQLTOEXEC,
        N'@CONSTITUENTID uniqueidentifier',
        @CONSTITUENTID=@CONSTITUENTID;

    declare @CAMPAIGNHIERARCHYPATH hierarchyid
    select @CAMPAIGNHIERARCHYPATH=HIERARCHYPATH
    from CAMPAIGN
    where ID=@CAMPAIGNID;

    with REVENUERECOGNITION_CTE as
    (
        select
            RECOGNITIONS.ID,
            REVENUE.CONSTITUENTID as REVENUECONSTITUENTID,
            REVENUERECOGNITIONTYPECODE.DESCRIPTION as RECOGNITIONTYPE,
            RECOGNITIONS.EFFECTIVEDATE,
            case @CURRENCYCODE when 0 then RECOGNITIONS.AMOUNT else RECOGNITIONS.ORGANIZATIONAMOUNT end as AMOUNT,
            case @CURRENCYCODE when 0 then RECOGNITIONS.BASECURRENCYID else @ORGANIZATIONCURRENCYID end as CURRENCYID,
            RECOGNITIONS.CONSTITUENTID as RECOGNIZEDCONSTITUENTID
        from dbo.UFN_RECOGNITIONCREDIT_GETRECOGNITIONS(1, @REVENUERECOGNITIONTYPECODEID, null) RECOGNITIONS
            inner join dbo.REVENUESPLIT on RECOGNITIONS.REVENUESPLITID = REVENUESPLIT.ID
            inner join dbo.REVENUE with (nolock) on REVENUESPLIT.REVENUEID = REVENUE.ID
            left join dbo.REVENUERECOGNITIONTYPECODE on RECOGNITIONS.REVENUERECOGNITIONTYPECODEID = REVENUERECOGNITIONTYPECODE.ID
        where (@STARTDATE is null or RECOGNITIONS.EFFECTIVEDATE >= @STARTDATE)
            and (@ENDDATE is null or RECOGNITIONS.EFFECTIVEDATE <= @ENDDATE)
            and (@REVENUERECOGNITIONTYPECODEID is null or RECOGNITIONS.REVENUERECOGNITIONTYPECODEID = @REVENUERECOGNITIONTYPECODEID)
            -- Check site security

            and(@ISADMIN = 1 
                or exists(
                    select 1
                    from dbo.UFN_SITEID_MAPFROM_REVENUESPLITID(REVENUESPLIT.ID) REVSITES
                        left join @SITESGRANTED SITESGRANTED on REVSITES.SITEID = SITESGRANTED.SITEID
                    where SITESGRANTED.SITEID is not null or REVSITES.SITEID is null
                )  
                or 
                (exists (
                    select 1 
                        from @SITESGRANTED 
                    where
                        SITEID = dbo.UFN_SITEID_MAPFROM_DESIGNATIONID(RECOGNITIONS.DESIGNATIONID) or 
                        (
                            SITEID is null and 
                            dbo.UFN_SITEID_MAPFROM_DESIGNATIONID(RECOGNITIONS.DESIGNATIONID) is null
                        ))
                )
            )
            and (@CAMPAIGNID is null 
                or REVENUESPLIT.ID in (
                    select REVENUESPLITCAMPAIGN.REVENUESPLITID 
                    from dbo.REVENUESPLITCAMPAIGN
                        inner join dbo.CAMPAIGN on CAMPAIGN.ID=REVENUESPLITCAMPAIGN.CAMPAIGNID
                    where CAMPAIGN.HIERARCHYPATH.IsDescendantOf(@CAMPAIGNHIERARCHYPATH)=1
                )
            )
    )
    select
        'http://www.blackbaud.com/CONSTITUENTID?CONSTITUENTID=' + CONVERT(nvarchar(36), CONSTITUENT.ID) as CONSTITUENTID,
        CONSTITUENT.NAME,
        CONSTITUENT.KEYNAME,
        CONSTITUENT.FIRSTNAME,
        case 
            when CONSTITUENT.ISORGANIZATION = 1 then 'Organization'
            when CONSTITUENT.ISGROUP = 1 and GROUPDATA.GROUPTYPECODE = 0 then 'Household'
            when CONSTITUENT.ISGROUP = 1 and GROUPDATA.GROUPTYPECODE = 1 then 'Group'
            else 'Individual'
        end CONSTITUENTTYPE,
        coalesce(ADDRESS.FULLADDRESS,'') ADDRESS,
        REVENUERECOGNITION_CTE.RECOGNITIONTYPE,
        REVENUERECOGNITION_CTE.EFFECTIVEDATE,
        REVENUERECOGNITION_CTE.AMOUNT,
        DONOR.NAME as DONORNAME,
        CURRENCYPROPERTIES.ISO4217 [ISOCURRENCYCODE],
        CURRENCYPROPERTIES.CURRENCYSYMBOL,
        CURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE [CURRENCYSYMBOLDISPLAYSETTINGCODE],
        CURRENCYPROPERTIES.DECIMALDIGITS
    from #CONSTITUENTFILTER CONSTITUENT 
        inner join REVENUERECOGNITION_CTE on REVENUERECOGNITION_CTE.RECOGNIZEDCONSTITUENTID = CONSTITUENT.ID
        inner join #CONSTITUENTSGRANTED DONOR on DONOR.ID = REVENUERECOGNITION_CTE.REVENUECONSTITUENTID
        outer apply(
            select dbo.UFN_BUILDFULLADDRESS(ADDRESS.ID, ADDRESS.ADDRESSBLOCK, ADDRESS.CITY, ADDRESS.STATEID, ADDRESS.POSTCODE, ADDRESS.COUNTRYID) [FULLADDRESS]
            from dbo.ADDRESS 
            where ADDRESS.CONSTITUENTID = CONSTITUENT.ID and ADDRESS.ISPRIMARY = 1
        ) ADDRESS
        left join dbo.GROUPDATA on GROUPDATA.ID = CONSTITUENT.ID
        cross apply dbo.UFN_CURRENCY_GETPROPERTIES(REVENUERECOGNITION_CTE.CURRENCYID) CURRENCYPROPERTIES 
    order by 
        CONSTITUENT.KEYNAME, 
        CONSTITUENT.FIRSTNAME, 
        REVENUERECOGNITION_CTE.RECOGNITIONTYPE, 
        REVENUERECOGNITION_CTE.EFFECTIVEDATE, 
        REVENUERECOGNITION_CTE.AMOUNT

    drop table #CONSTITUENTSGRANTED
    drop table #CONSTITUENTFILTER