USP_REPORT_REVENUEANNUALSTATEMENT

CREATE procedure [dbo].[USP_REPORT_REVENUEANNUALSTATEMENT]
            (
                @REVENUETRANSACTIONQUERY uniqueidentifier = null,
                @CONSTITUENTID uniqueidentifier = null,
                @STARTDATE datetime = null,
                @ENDDATE datetime = null,
                @REPORTUSERID nvarchar(128) = null,
                @ALTREPORTUSERID nvarchar(128) = null
            )
            with execute as owner
            as
                set nocount on;
                set transaction isolation level read uncommitted;

        declare @CURRENTAPPUSERID uniqueidentifier;
        declare @ISADMIN bit;
        declare @APPUSER_IN_NONRACROLE bit;
        declare @APPUSER_IN_NOSECGROUPROLE bit;
        declare @APPUSER_IN_NONSITEROLE 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 @STARTDATE = dbo.UFN_DATE_GETEARLIESTTIME(@STARTDATE);
        set @ENDDATE = dbo.UFN_DATE_GETLATESTTIME(@ENDDATE);

        declare @CHECKSITESECURITY bit = 1;

        declare @BASICPROGRAMSINSTALLED bit = 0;
        if dbo.UFN_INSTALLEDPRODUCTS_PRODUCTIS('BB1C17BC-9E0B-4683-B490-EE40D511FA05') = 1
            set @BASICPROGRAMSINSTALLED = 1;

        if @ISADMIN = 1 or @APPUSER_IN_NONSITEROLE = 1
            set @CHECKSITESECURITY = 0;
        else if not exists(select top 1 1 from dbo.[SITE])
            set @CHECKSITESECURITY = 0;

        declare @SQLTOEXEC nvarchar(max);

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

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

        set @SQLTOEXEC =
            ';with REVENUE_CTE as (
            select
                c.ID,
                case c.ISORGANIZATION when 1 then
                    case c.KEYNAMEPREFIX when '''' then c.KEYNAME else c.KEYNAMEPREFIX + '' '' +  c.KEYNAME end
                else
                    case c.ISGROUP when 1 then
                        case c.DISPLAYNAME when '''' then c.KEYNAME else c.DISPLAYNAME end
                    else
                        case c.FIRSTNAME when '''' then '''' else c.FIRSTNAME + '' '' end
                        +
                        case c.MIDDLENAME when '''' then '''' else LEFT(c.MIDDLENAME,1) + ''. '' end
                        + 
                        c.KEYNAME
                    end
                end as NAME,
                case when a.ID is null then null else dbo.UFN_BUILDFULLADDRESS(a.ID, a.ADDRESSBLOCK, a.CITY, a.STATEID, a.POSTCODE, a.COUNTRYID) end [ADDRESS],
                FINANCIALTRANSACTION.DATE,
                case FINANCIALTRANSACTION.TYPECODE 
                    when 0 then N''Payment'' 
                    when 1 then N''Pledge'' 
                    when 2 then N''Recurring gift'' 
                    when 3 then N''Matching gift claim'' 
                    when 4 then N''Planned gift'' 
                    when 5 then N''Order'' 
                    when 6 then N''Grant award'' 
                    when 7 then N''Auction donation'' 
                    when 8 then N''Donor challenge claim'' 
                    when 9 then N''Pending gift''
                    end as TRANSACTIONTYPE,
                FINANCIALTRANSACTION.TRANSACTIONAMOUNT,
                (select top 1 RR.RECEIPTNUMBER from dbo.REVENUERECEIPT RR where RR.REVENUEID = FINANCIALTRANSACTION.ID order by RR.RECEIPTPROCESSDATE desc) as RECEIPTNUMBER,
                REVENUE_EXT.RECEIPTAMOUNT,
                CURRENCY.ISO4217 [ISOCURRENCYCODE],
                CURRENCY.CURRENCYSYMBOL,
                CURRENCY.SYMBOLDISPLAYSETTINGCODE [CURRENCYSYMBOLDISPLAYSETTINGCODE],
                CURRENCY.DECIMALDIGITS,
                FINANCIALTRANSACTION.[ID] as [REVENUEID],
                c.KEYNAME,
                c.FIRSTNAME,
                c.MIDDLENAME
     from
                  dbo.FINANCIALTRANSACTION with (NOLOCK)
                  inner join dbo.REVENUE_EXT on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID
                inner join dbo.CONSTITUENT c with (NOLOCK) on FINANCIALTRANSACTION.CONSTITUENTID = c.ID
                left join dbo.ADDRESS a with (NOLOCK) on a.CONSTITUENTID = c.ID and a.ISPRIMARY = 1
                inner join dbo.CURRENCY on FINANCIALTRANSACTION.TRANSACTIONCURRENCYID = CURRENCY.ID' + nchar(13);

        if @REVENUETRANSACTIONQUERY is not null
                  set @SQLTOEXEC += 'inner join dbo.' + @DBOBJECTNAME + ' as SELECTION on FINANCIALTRANSACTION.[ID] = SELECTION.[ID]' + nchar(13);

        set @SQLTOEXEC += 'where (FINANCIALTRANSACTION.[TYPECODE] in (0,7))
                    and FINANCIALTRANSACTION.DELETEDON is null' + nchar(13);

        if @CHECKSITESECURITY = 1
            set @SQLTOEXEC += '
                    and exists(
                    select FINANCIALTRANSACTIONLINEITEM.ID
                    from dbo.FINANCIALTRANSACTIONLINEITEM
                        inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
                    where FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID=FINANCIALTRANSACTION.ID
                        and (
                            (REVENUESPLIT_EXT.APPLICATIONCODE=1 
                                and REVENUESPLIT_EXT.TYPECODE=1 
                                and dbo.UFN_EVENTREGISTRANTPAYMENT_USERHASSITEACCESS(FINANCIALTRANSACTIONLINEITEM.ID,@CURRENTAPPUSERID) = 1
                            )
                            or(
                                not(
                                    REVENUESPLIT_EXT.APPLICATIONCODE=1 
                                    and REVENUESPLIT_EXT.TYPECODE=1
                                )))
                            and exists
                              (
                               select HASPERMISSION
                               from dbo.UFN_SITEID_MAPFROM_REVENUEID(FINANCIALTRANSACTION.ID) REVSITES
                               cross apply dbo.UFN_REPORT_HASUSERSITEPERMISSION(@CURRENTAPPUSERID, ''FAB95CFF-3EED-4AC4-84D5-F586DDA61AB6'', REVSITES.SITEID)
                        )) ';      

        if @STARTDATE is not null 
            set @SQLTOEXEC += 'and (FINANCIALTRANSACTION.DATE >= @STARTDATE)' + nchar(13);

        if @ENDDATE is not null 
            set @SQLTOEXEC += 'and (FINANCIALTRANSACTION.DATE <= @ENDDATE)' + nchar(13);

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

        if not (@ISADMIN = 1 or @APPUSER_IN_NONRACROLE = 1 or (@CONSTITUENTID is not null and dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT(@CURRENTAPPUSERID, @CONSTITUENTID, @APPUSER_IN_NOSECGROUPROLE) = 1))
            set @SQLTOEXEC += 'and (dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT(@CURRENTAPPUSERID, c.ID, @APPUSER_IN_NOSECGROUPROLE) = 1)' + nchar(13);

        set @SQLTOEXEC += ')' + nchar(13);

        if @BASICPROGRAMSINSTALLED = 1
            set @SQLTOEXEC += ',
                REFUND_CTE as (
                    select
                        coalesce([CREDITPAYMENT].[REVENUEID], [FINANCIALTRANSACTIONLINEITEM].[FINANCIALTRANSACTIONID]) as [REVENUEID],
                        [CREDITPAYMENT].[AMOUNT],
                        [FINANCIALTRANSACTION].[CONSTITUENTID],
                        dbo.UFN_BUILDFULLADDRESS(ADDRESS.ID, ADDRESS.ADDRESSBLOCK, ADDRESS.CITY, ADDRESS.STATEID, ADDRESS.POSTCODE, ADDRESS.COUNTRYID) [ADDRESS],
                        [FINANCIALTRANSACTION].[DATEADDED] as DATE,
                        [FINANCIALTRANSACTIONLINEITEM].[ID] as [REVENUESPLITID],
                        [CREDITPAYMENT].[ID] as [CREDITPAYMENTID],
                        [CONSTITUENT].[KEYNAME],
                        [CONSTITUENT].[FIRSTNAME],
                        [CONSTITUENT].[MIDDLENAME],
                        case [CONSTITUENT].ISORGANIZATION when 1 then
                            case [CONSTITUENT].KEYNAMEPREFIX when '''' then [CONSTITUENT].KEYNAME else [CONSTITUENT].KEYNAMEPREFIX + '' '' +  [CONSTITUENT].KEYNAME end
                        else
                            case [CONSTITUENT].ISGROUP when 1 then
                                case [CONSTITUENT].DISPLAYNAME when '''' then [CONSTITUENT].KEYNAME else [CONSTITUENT].DISPLAYNAME end
                            else
                                case [CONSTITUENT].FIRSTNAME when '''' then '''' else [CONSTITUENT].FIRSTNAME + '' '' end
                                +
                                case [CONSTITUENT].MIDDLENAME when '''' then '''' else LEFT([CONSTITUENT].MIDDLENAME,1) + ''. '' end
                                + 
                                [CONSTITUENT].KEYNAME
                            end
                        end as NAME
                    from dbo.[CREDITPAYMENT]
                    inner join dbo.[FINANCIALTRANSACTION] on [CREDITPAYMENT].[CREDITID] = [FINANCIALTRANSACTION].[ID]
                    inner join dbo.[CONSTITUENT] on [CONSTITUENT].[ID] = [FINANCIALTRANSACTION].[CONSTITUENTID]
                    left join dbo.[FINANCIALTRANSACTIONLINEITEM] on [CREDITPAYMENT].[REVENUESPLITID] = [FINANCIALTRANSACTIONLINEITEM].[ID]
                    left join dbo.[ADDRESS] with (NOLOCK) on [ADDRESS].[CONSTITUENTID] = [FINANCIALTRANSACTION].[CONSTITUENTID] and [ADDRESS].[ISPRIMARY] = 1
                    where FINANCIALTRANSACTION.DELETEDON is null
                    and FINANCIALTRANSACTION.TYPECODE in (0,1,2,3,4,5,6,7,8,9)
                    and FINANCIALTRANSACTIONLINEITEM.TYPECODE != 1
                    and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null
                )' + nchar(13);

        set @SQLTOEXEC += '
            select 
                    [ID],
                    [NAME],
                    [ADDRESS],
                    convert(datetime,[DATE]) as [DATE],
                    [TRANSACTIONTYPE],
                    [TRANSACTIONAMOUNT],
                    [RECEIPTNUMBER],
                    [RECEIPTAMOUNT],
                    [ISOCURRENCYCODE],
                    [CURRENCYSYMBOL],
                    [CURRENCYSYMBOLDISPLAYSETTINGCODE],
                    [DECIMALDIGITS],
                    [KEYNAME],
                    [FIRSTNAME],
                    [MIDDLENAME]
            from REVENUE_CTE' + nchar(13);

        if @BASICPROGRAMSINSTALLED = 1
            set @SQLTOEXEC += '
                union all

                select
                    [REFUNDS].[CONSTITUENTID],
                    [REFUNDS].[NAME],
                    [REFUNDS].[ADDRESS],
                    convert(datetime,[REFUNDS].[DATE]),
                    [REVENUE].[TRANSACTIONTYPE] as [TRANSACTIONTYPE],
                    -1 * [REFUNDS].[AMOUNT],
                    null as [RECEIPTNUMBER],
                    -1 * dbo.UFN_CREDITPAYMENT_GETRECEIPTAMOUNT([REFUNDS].[CREDITPAYMENTID]) as [RECEIPTAMOUNT],
                    [REVENUE].[ISOCURRENCYCODE],
                    [REVENUE].[CURRENCYSYMBOL],
                    [REVENUE].[CURRENCYSYMBOLDISPLAYSETTINGCODE],
                    [REVENUE].[DECIMALDIGITS],
                    [REFUNDS].[KEYNAME],
                    [REFUNDS].[FIRSTNAME],
                    [REFUNDS].[MIDDLENAME]
                from REFUND_CTE [REFUNDS]
                inner join REVENUE_CTE [REVENUE] on [REFUNDS].[REVENUEID] = [REVENUE].[REVENUEID]' + nchar(13);


        set @SQLTOEXEC += 'order by KEYNAME, FIRSTNAME, MIDDLENAME, DATE asc' + nchar(13);

        exec sp_executesql @SQLTOEXEC, 
                    N'@CONSTITUENTID uniqueidentifier, @STARTDATE datetime, @ENDDATE datetime, @ISADMIN bit, @APPUSER_IN_NONRACROLE bit, @APPUSER_IN_NOSECGROUPROLE bit, @CURRENTAPPUSERID uniqueidentifier',
                    @CONSTITUENTID=@CONSTITUENTID, @STARTDATE=@STARTDATE, @ENDDATE=@ENDDATE, @ISADMIN=@ISADMIN, @APPUSER_IN_NONRACROLE=@APPUSER_IN_NONRACROLE, @APPUSER_IN_NOSECGROUPROLE=@APPUSER_IN_NOSECGROUPROLE, @CURRENTAPPUSERID=@CURRENTAPPUSERID;