USP_REPORT_APPEALPROGRESSSUMMARY_MAIN_WITHIDSET

/*
Generated by Blackbaud Application Framework
Date: 12/17/2008 2:13:48 AM
Assembly Version: Blackbaud.AppFx.Platform.SqlClr, Version=1.7.1271.0, Culture=neutral, PublicKeyToken=null
*/
create procedure dbo.USP_REPORT_APPEALPROGRESSSUMMARY_MAIN_WITHIDSET (@IDSETID uniqueidentifier = null)
with execute as owner
as
  set nocount on;

  declare @r int;

  if @IDSETID is null
    begin
      exec @r = dbo.[USP_REPORT_APPEALPROGRESSSUMMARY_MAIN];
    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 = N'select 
                APPEAL.NAME,
                APPEAL.GOAL,
                (
                    select count(distinct CONSTITUENTAPPEAL.CONSTITUENTID) 
                    from dbo.CONSTITUENTAPPEAL
                    where CONSTITUENTAPPEAL.APPEALID = APPEAL.ID
                ) as [NUMBERSOLICITED],
                coalesce(( --Get gifts, pledges, and recurring gift payments

                    select coalesce(sum(AMOUNT), 0) 
                    from dbo.REVENUE 
                     inner join dbo.' + @DBOBJECTNAME + ' as [IDSET_249b5c4a_6315_4f95_9799_3f56d9bd65c6] on [APPEAL].[ID] = [IDSET_249b5c4a_6315_4f95_9799_3f56d9bd65c6].[ID] 
                    where REVENUE.APPEALID = APPEAL.ID
                          and REVENUE.TYPECODE in (0, 1, 3, 5)
                ), 0)
                - 
                coalesce(( --Subtract write-offs of the above pledges

                    select (select sum(AMOUNT) from dbo.INSTALLMENTWRITEOFF where WRITEOFFID = WRITEOFF.ID)
                    from dbo.WRITEOFF
                    left join dbo.REVENUE on REVENUE.ID = WRITEOFF.REVENUEID
                    where REVENUE.APPEALID = APPEAL.ID
                ), 0) as [TOTALREVENUE],
                coalesce(( --Get gifts, pledge payments, and recurring gift payments

                    select coalesce(sum(AMOUNT), 0)
                    from dbo.REVENUE
                    where REVENUE.APPEALID = APPEAL.ID
                          and REVENUE.TYPECODE in (0, 4, 5)
                ), 0) as [ACTUALREVENUE],
                (
                    select count(REVENUE.ID)
                    from dbo.REVENUE 
                    where REVENUE.APPEALID = APPEAL.ID
                          and REVENUE.TYPECODE in (0, 1, 3, 5)
                ) as [NUMBEROFGIFTS],
                (
                    select count(distinct CONSTITUENTID)
                    from dbo.REVENUE
                    where REVENUE.APPEALID = APPEAL.ID
                          and REVENUE.TYPECODE in (0, 1, 3, 5)
                ) as [NUMBEROFDONORS],
                (
                    select coalesce(avg(AMOUNT), 0)
                    from dbo.REVENUE 
                    where REVENUE.APPEALID = APPEAL.ID
                          and REVENUE.TYPECODE in (0, 1, 3, 5)
                ) as [AVGGIFTAMOUNT]    
            from dbo.APPEAL 
             inner join dbo.' + @DBOBJECTNAME + ' as [IDSET_249b5c4a_6315_4f95_9799_3f56d9bd65c6] on [APPEAL].[ID] = [IDSET_249b5c4a_6315_4f95_9799_3f56d9bd65c6].[ID] 
            where APPEAL.ISACTIVE = 1';

      exec @r = sp_executesql @SQLTOEXEC;
    end

  return @r;