USP_REPORT_MEMBERSHIPAPPEAL_WITHIDSET

/*
Generated by Blackbaud Application Framework
Date: 3/19/2013 1:33:34 AM
Assembly Version: Blackbaud.AppFx.Platform.SqlClr, Version=3.0.504.0, Culture=neutral, PublicKeyToken=null
*/
CREATE procedure dbo.USP_REPORT_MEMBERSHIPAPPEAL_WITHIDSET (@IDSETID uniqueidentifier = null, @STARTDATE datetime, @ENDDATE datetime, @CATEGORYID uniqueidentifier, @BUSINESSUNITID uniqueidentifier, @REPORTCODEID uniqueidentifier, @SITEID uniqueidentifier, @GROUPBY tinyint)
with execute as owner
as
  set nocount on;

  declare @r int;

  if @IDSETID is null
    begin
      exec @r = dbo.[USP_REPORT_MEMBERSHIPAPPEAL] @STARTDATE, @ENDDATE, @CATEGORYID, @BUSINESSUNITID, @REPORTCODEID, @SITEID, @GROUPBY;
    end
  else
    begin
      if not exists(select [ID] from dbo.[IDSETREGISTER] where [ID] = @IDSETID)
        raiserror('ID set does not exist in the database.', 15, 1);

      declare @DBOBJECTNAME nvarchar(max);
      declare @DBOBJECTTYPE smallint;
      declare @SQLTOEXEC nvarchar(max);

      select
        @DBOBJECTNAME = [DBOBJECTNAME],
        @DBOBJECTTYPE = [OBJECTTYPE]
      from dbo.[IDSETREGISTER]
      where [ID] = @IDSETID;

      if left(@DBOBJECTNAME, 1) <> '['
        set @DBOBJECTNAME = '[' + @DBOBJECTNAME + ']';

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

      set @SQLTOEXEC = cast(N'set nocount on;

                    -- Total variables

                    declare @TOTALDONORS int
                    declare @TOTALGOAL int
                    declare @TOTALRAISED int
                    declare @TOTALRECEIVED int
                    declare @TOTALSOLICITED int
                    declare @TOTALMEMBERS int
                    declare @TOTALDONATIONS int

                    -- Calculating totals for report.

                    select 
                        @TOTALMEMBERS = sum(coalesce(marcs.DONORCOUNT, 0)),
                        @TOTALDONATIONS = sum(coalesce(marcs.GIFTCOUNT, 0)),
                        @TOTALSOLICITED = sum(coalesce(mascs.SOLICITEDCOUNT,0)),
                        @TOTALGOAL = sum(coalesce(a.GOAL,0)),
                        @TOTALRECEIVED = sum(coalesce(mra.TOTALRECEIVED, 0)),
                        @TOTALRAISED = sum(coalesce(MRA.TOTALRECEIVED,0) + coalesce((mpe.TOTALPLEDGED - (mpe.PLEDGESPAID + mpe.PLEDGESWRITTENOFF)),0))
                    from dbo.UFN_MEMBER_RAISEDBYAPPEAL(@STARTDATE,@ENDDATE) MRA
                        full join dbo.UFN_MEMBER_PLEDGEDBYAPPEAL(@STARTDATE,@ENDDATE) mpe on (mpe.APPEALID=MRA.APPEALID)
                        right join dbo.APPEAL a on a.ID=MRA.APPEALID or a.ID=mpe.APPEALID
                     inner join dbo.' as nvarchar(max)) + @DBOBJECTNAME + cast(N' as [IDSET_6ceda48d_b2e4_49c1_932b_2da2446296f2] on [a].[ID] = [IDSET_6ceda48d_b2e4_49c1_932b_2da2446296f2].[ID] 
                        left join dbo.UFN_MEMBER_APPEALREVENUECOUNTS(@STARTDATE,@ENDDATE) marcs on marcs.APPEALID=a.ID
                        left join dbo.UFN_MEMBER_APPEALSOLICITEDCOUNTS(@STARTDATE,@ENDDATE) mascs on mascs.APPEALID=a.ID                        where
                        (a.APPEALCATEGORYCODEID = @CATEGORYID or @CATEGORYID is null) and 
                        (a.BUSINESSUNITCODEID = @BUSINESSUNITID or @BUSINESSUNITID is null) and 
                        (a.APPEALREPORT1CODEID = @REPORTCODEID or @REPORTCODEID is null) and 
                        (a.SITEID = @SITEID or @SITEID is null) and
                        (a.MEMBERSHIPPROGRAMID is not null)

                    select
                         @TOTALDONORS=count(distinct(r.CONSTITUENTID))
                    from dbo.FINANCIALTRANSACTION r
                        inner join REVENUE_EXT RE on RE.ID = r.ID
                        inner join APPEAL a on a.ID = RE.APPEALID
                        inner join dbo.FINANCIALTRANSACTIONLINEITEM RS on RS.FINANCIALTRANSACTIONID = r.ID
                        inner join dbo.REVENUESPLIT_EXT RSE on RSE.ID = RS.ID
                     inner join dbo.' as nvarchar(max)) + @DBOBJECTNAME + cast(N' as [IDSET_6ceda48d_b2e4_49c1_932b_2da2446296f2] on [a].[ID] = [IDSET_6ceda48d_b2e4_49c1_932b_2da2446296f2].[ID] 
                    where RS.TYPECODE <> 1
                        and R.DELETEDON is null 
                        and RS.DELETEDON is null and
                        (RE.APPEALID is not null) and
                        (r.TYPECODE in (1, 3) or --Pledge/MG Pledge

                        (r.TYPECODE = 0 and RSE.APPLICATIONCODE in (0, 3))) and --Payment (Gift or Recurring gift payment)

                        (cast(r.DATE as datetime) >= dbo.UFN_DATE_GETLATESTTIME(@STARTDATE) or @STARTDATE is null) and
                        (cast(r.DATE as datetime) <= dbo.UFN_DATE_GETLATESTTIME(@ENDDATE) or @ENDDATE is null) and
                        (a.APPEALCATEGORYCODEID = @CATEGORYID or @CATEGORYID is null) and 
                        (a.BUSINESSUNITCODEID = @BUSINESSUNITID or @BUSINESSUNITID is null) and 
                        (a.APPEALREPORT1CODEID = @REPORTCODEID or @REPORTCODEID is null) and 
                        (a.SITEID = @SITEID or @SITEID is null) and
                        (a.MEMBERSHIPPROGRAMID is not null)

                    -- No grouping

                    if @GROUPBY = 0
                    begin
                        select 
                            ''http://www.blackbaud.com/APPEALID?APPEALID='' + CONVERT(nvarchar(36),a.ID) as APPEALID,
                            a.[NAME],
                            a.GOAL,
                            coalesce(mascs.SOLICITEDCOUNT,0) SOLICITEDCOUNT,
                            coalesce(marcs.DONORCOUNT, 0) DONORCOUNT,
                            coalesce(marcs.GIFTCOUNT,0) GIFTCOUNT,
                            coalesce(@TOTALDONORS,0) TOTALDONORS,
                            null DESNAME,
                            coalesce(MRA.TOTALRECEIVED,0) + coalesce((mpe.TOTALPLEDGED - (mpe.PLEDGESPAID + mpe.PLEDGESWRITTENOFF)),0) APPEALTOTALRAISED,                        
                            coalesce(MRA.TOTALRECEIVED,0) APPEALTOTALRECEIVED,
                            null TRANSACTIONTOTALRECEIVED,
                            null TRANSACTIONTOTALRAISED,
                            coalesce(@TOTALGOAL, 0) TOTALGOAL,
                            coalesce(@TOTALRAISED,0) TOTALRAISED,
                            coalesce(@TOTALRECEIVED, 0) TOTALRECEIVED,
                            coalesce(@TOTALSOLICITED, 0) TOTALSOLICITED,
                            coalesce(@TOTALMEMBERS, 0) TOTALMEMBERS,
                            coalesce(@TOTALDONATIONS, 0) TOTALDONATIONS
                        from dbo.UFN_MEMBER_RAISEDBYAPPEAL(@STARTDATE,@ENDDATE) MRA
                            full join dbo.UFN_MEMBER_PLEDGEDBYAPPEAL(@STARTDATE,@ENDDATE) mpe on (mpe.APPEALID=MRA.APPEALID)
                            right join dbo.APPEAL a on a.ID=MRA.APPEALID or a.ID=mpe.APPEALID
                         inner join dbo.' as nvarchar(max)) + @DBOBJECTNAME + cast(N' as [IDSET_6ceda48d_b2e4_49c1_932b_2da2446296f2] on [a].[ID] = [IDSET_6ceda48d_b2e4_49c1_932b_2da2446296f2].[ID] 
                            left join dbo.UFN_MEMBER_APPEALREVENUECOUNTS(@STARTDATE,@ENDDATE) marcs on marcs.APPEALID=a.ID
                            left join dbo.UFN_MEMBER_APPEALSOLICITEDCOUNTS(@STARTDATE,@ENDDATE) mascs on mascs.APPEALID=a.ID
                        where
                            (a.APPEALCATEGORYCODEID = @CATEGORYID or @CATEGORYID is null) and 
                            (a.BUSINESSUNITCODEID = @BUSINESSUNITID or @BUSINESSUNITID is null) and 
                            (a.APPEALREPORT1CODEID = @REPORTCODEID or @REPORTCODEID is null) and 
                            (a.SITEID = @SITEID or @SITEID is null) and
                            (a.MEMBERSHIPPROGRAMID is not null)
                        order by a.NAME                
                    end
                    -- Group by activity (join, renew, rejoin, etc...)

                    else if @GROUPBY = 1
                    begin
                        select 
                            ''http://www.blackbaud.com/APPEALID?APPEALID='' + CONVERT(nvarchar(36),a.ID) as APPEALID,
                            a.[NAME],
                            a.GOAL,
                            coalesce(mascs.SOLICITEDCOUNT,0) SOLICITEDCOUNT,
                            --coalesce(arcs.DONORCOUNT,0) DONORCOUNT,

                            coalesce(marcs.DONORCOUNT, 0) DONORCOUNT,
                            coalesce(marcs.GIFTCOUNT,0) GIFTCOUNT,
                            coalesce(@TOTALDONORS,0) TOTALDONORS,
                            dbo.UFN_MEMBERSHIPTRANSACTION_ACTIONCODE_GETDESCRIPTION(MTRA.MTACTION) DESNAME,
                            coalesce(MRA.TOTALRECEIVED,0) + coalesce((mpe.TOTALPLEDGED - (mpe.PLEDGESPAID + mpe.PLEDGESWRITTENOFF)),0) APPEALTOTALRAISED,                        
                            coalesce(MRA.TOTALRECEIVED,0) APPEALTOTALRECEIVED,
                            coalesce(MTRA.MTTOTALRECEIVED, 0) TRANSACTIONTOTALRECEIVED,
                            coalesce(MTRA.MTTOTALRECEIVED, 0) TRANSACTIONTOTALRAISED,
                            coalesce(@TOTALGOAL, 0) TOTALGOAL,
                            coalesce(@TOTALRAISED,0) TOTALRAISED,
                            coalesce(@TOTALRECEIVED, 0) TOTALRECEIVED,
                            coalesce(@TOTALSOLICITED, 0) TOTALSOLICITED,
                            coalesce(@TOTALMEMBERS, 0) TOTALMEMBERS,
                            coalesce(@TOTALDONATIONS, 0) TOTALDONATIONS
                        from dbo.UFN_MEMBER_RAISEDBYAPPEAL(@STARTDATE,@ENDDATE) MRA
                            full join dbo.UFN_MEMBER_PLEDGEDBYAPPEAL(@STARTDATE,@ENDDATE) mpe on (mpe.APPEALID=MRA.APPEALID)
                            full join dbo.UFN_MEMBERSHIPTRANSACTION_RAISEDBYAPPEAL(@STARTDATE, @ENDDATE) MTRA on (MTRA.APPEALID = MPE.APPEALID)
                            right join dbo.APPEAL a on a.ID=MRA.APPEALID or a.ID=mpe.APPEALID or a.ID=MTRA.APPEALID
                         inner join dbo.' as nvarchar(max)) + @DBOBJECTNAME + cast(N' as [IDSET_6ceda48d_b2e4_49c1_932b_2da2446296f2] on [a].[ID] = [IDSET_6ceda48d_b2e4_49c1_932b_2da2446296f2].[ID] 
                            left join dbo.UFN_MEMBER_APPEALREVENUECOUNTS(@STARTDATE,@ENDDATE) marcs on marcs.APPEALID=a.ID
                            left join dbo.UFN_MEMBER_APPEALSOLICITEDCOUNTS(@STARTDATE,@ENDDATE) mascs on mascs.APPEALID=a.ID
                        where
                            (a.APPEALCATEGORYCODEID = @CATEGORYID or @CATEGORYID is null) and 
                            (a.BUSINESSUNITCODEID = @BUSINESSUNITID or @BUSINESSUNITID is null) and 
                            (a.APPEALREPORT1CODEID = @REPORTCODEID or @REPORTCODEID is null) and 
                            (a.SITEID = @SITEID or @SITEID is null) and
                            (a.MEMBERSHIPPROGRAMID is not null)
                        order by a.NAME    
                    end
                    else if @GROUPBY = 2
                    begin 
                        select 
                            ''http://www.blackbaud.com/APPEALID?APPEALID='' + CONVERT(nvarchar(36),a.ID) as APPEALID,
                            a.[NAME],
                            a.GOAL,
                            coalesce(mascs.SOLICITEDCOUNT,0) SOLICITEDCOUNT,
                            coalesce(marcs.DONORCOUNT, 0) DONORCOUNT,
                            coalesce(marcs.GIFTCOUNT,0) GIFTCOUNT,
                            coalesce(@TOTALDONORS,0) TOTALDONORS,
                            case 
                                when isnull(MCPA.CHANNELID, ''00000000-0000-0000-0000-000000000000'') = ''00000000-0000-0000-0000-000000000000'' 
                                    and isnull(MCRA.CHANNELCODE, ''00000000-0000-0000-0000-000000000000'') = ''00000000-0000-0000-0000-000000000000''
                                    and (coalesce(MCRA.CHANNELTOTALRECEIVED, 0) <> 0
                                    or (coalesce(MCRA.CHANNELTOTALRECEIVED,0) + coalesce((MCPA.TOTALPLEDGED - (MCPA.PLEDGESPAID + MCPA.PLEDGESWRITTENOFF)),0)) <> 0) then ''(no channel)''
                                when isnull(MCPA.CHANNELID, ''00000000-0000-0000-0000-000000000000'') = ''00000000-0000-0000-0000-000000000000'' 
                                    and isnull(MCRA.CHANNELCODE, ''00000000-0000-0000-0000-000000000000'') <>  ''00000000-0000-0000-0000-000000000000'' then dbo.UFN_CHANNELCODE_GETDESCRIPTION(MCRA.CHANNELCODE)
                                when isnull(MCPA.CHANNELID, ''00000000-0000-0000-0000-000000000000'') <> ''00000000-0000-0000-0000-000000000000'' 
                                    and isnull(MCRA.CHANNELCODE, ''00000000-0000-0000-0000-000000000000'') =  ''00000000-0000-0000-0000-000000000000'' then dbo.UFN_CHANNELCODE_GETDESCRIPTION(MCPA.CHANNELID)
                                else  dbo.UFN_CHANNELCODE_GETDESCRIPTION(MCPA.CHANNELID)
                            end as DESNAME,
                            coalesce(MRA.TOTALRECEIVED,0) + coalesce((mpe.TOTALPLEDGED - (mpe.PLEDGESPAID + mpe.PLEDGESWRITTENOFF)),0) APPEALTOTALRAISED,                        
                            coalesce(MRA.TOTALRECEIVED,0) APPEALTOTALRECEIVED,
                            coalesce(MCRA.CHANNELTOTALRECEIVED, 0) TRANSACTIONTOTALRECEIVED,
                            coalesce(MCRA.CHANNELTOTALRECEIVED,0) + coalesce((MCPA.TOTALPLEDGED - (MCPA.PLEDGESPAID + MCPA.PLEDGESWRITTENOFF)),0) TRANSACTIONTOTALRAISED,                        
                            coalesce(@TOTALGOAL, 0) TOTALGOAL,
                            coalesce(@TOTALRAISED,0) TOTALRAISED,
                            coalesce(@TOTALRECEIVED, 0) TOTALRECEIVED,
                            coalesce(@TOTALSOLICITED, 0) TOTALSOLICITED,
                            coalesce(@TOTALMEMBERS, 0) TOTALMEMBERS,
                            coalesce(@TOTALDONATIONS, 0) TOTALDONATIONS
                        from dbo.UFN_MEMBER_RAISEDBYAPPEAL(@STARTDATE,@ENDDATE) MRA
                            full join dbo.UFN_MEMBER_PLEDGEDBYAPPEAL(@STARTDATE,@ENDDATE) mpe on (mpe.APPEALID=MRA.APPEALID)
                            full join dbo.UFN_MEMBER_CHANNELRAISEDBYAPPEAL(@STARTDATE, @ENDDATE) MCRA on (MCRA.APPEALID = MPE.APPEALID)
                            full join dbo.UFN_MEMBER_CHANNELPLEDGEDBYAPPEAL(@STARTDATE, @ENDDATE) MCPA on (MCPA.CHANNELID = MCRA.CHANNELCODE and MCPA.APPEALID = MCRA.APPEALID)
                            right join dbo.APPEAL a on a.ID=MRA.APPEALID or a.ID=mpe.APPEALID or a.ID=MCRA.APPEALID or a.ID = MCPA.APPEALID
                         inner join dbo.' as nvarchar(max)) + @DBOBJECTNAME + cast(N' as [IDSET_6ceda48d_b2e4_49c1_932b_2da2446296f2] on [a].[ID] = [IDSET_6ceda48d_b2e4_49c1_932b_2da2446296f2].[ID] 
                            left join dbo.UFN_MEMBER_APPEALREVENUECOUNTS(@STARTDATE,@ENDDATE) marcs on marcs.APPEALID=a.ID
                            left join dbo.UFN_MEMBER_APPEALSOLICITEDCOUNTS(@STARTDATE,@ENDDATE) mascs on mascs.APPEALID=a.ID
                        where
                            (a.APPEALCATEGORYCODEID = @CATEGORYID or @CATEGORYID is null) and 
                            (a.BUSINESSUNITCODEID = @BUSINESSUNITID or @BUSINESSUNITID is null) and 
                            (a.APPEALREPORT1CODEID = @REPORTCODEID or @REPORTCODEID is null) and 
                            (a.SITEID = @SITEID or @SITEID is null) and
                            (a.MEMBERSHIPPROGRAMID is not null)
                        order by a.NAME    

                    end' as nvarchar(max));

      exec @r = sp_executesql @SQLTOEXEC, N'@STARTDATE datetime, @ENDDATE datetime, @CATEGORYID uniqueidentifier, @BUSINESSUNITID uniqueidentifier, @REPORTCODEID uniqueidentifier, @SITEID uniqueidentifier, @GROUPBY tinyint', @STARTDATE = @STARTDATE, @ENDDATE = @ENDDATE, @CATEGORYID = @CATEGORYID, @BUSINESSUNITID = @BUSINESSUNITID, @REPORTCODEID = @REPORTCODEID, @SITEID = @SITEID, @GROUPBY = @GROUPBY;
    end

  return @r;