USP_REPORT_DESIGNATIONLEVELREVENUE_BYPURPOSE_WITHIDSET

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

  declare @r int;

  if @IDSETID is null
    begin
      exec @r = dbo.[USP_REPORT_DESIGNATIONLEVELREVENUE_BYPURPOSE] @START, @END, @CATEGORYCODEID, @DESIGNATIONLEVELTYPEID, @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 = N'begin
                    set @START = dbo.UFN_DATE_GETEARLIESTTIME(@START);
                    set @END = dbo.UFN_DATE_GETLATESTTIME(@END);

                    if @END < @START
                        raiserror(''The start date must be on or before the end date.'',13,1);                        

                    select 
                        ''http://www.blackbaud.com?DESIGNATIONLEVELID='' + CONVERT(nvarchar(36),DL.ID) as [DESIGNATIONLEVELID],
                        DL.NAME,
                        0 as SEQUENCE,
                        YEAR(@END) as PERIODYEAR,
                        DLINFO.NUMGIFTS as TOTALNUMGIFTS,
                        DLINFO.NUMDONORS TOTALDONORS,
                        (coalesce(DLRECEIVED.TOTALRECEIVED, 0) + 
                        coalesce(DLPLEDGE.TOTALPLEDGED - (DLPLEDGE.TOTALPAID + DLPLEDGE.TOTALWRITEOFFS), 0)) TOTALRAISED,
                        0 as PERIODNUMGIFTS,
                        coalesce(DLINFO.MAXGIFT, 0) MAXGIFT
                    from dbo.DESIGNATIONLEVEL DL 
                        left join dbo.UFN_DESIGNATIONLEVEL_REVENUECOUNTS(@START, @END) as DLINFO on DL.ID = DLINFO.DESIGNATIONLEVELID
                        left join dbo.UFN_DESIGNATIONLEVEL_REVENUERECEIVED(@START, @END) as DLRECEIVED on DL.ID = DLRECEIVED.DESIGNATIONLEVELID
                        left join dbo.UFN_DESIGNATIONLEVEL_REVENUEPLEDGED(@START, @END) as DLPLEDGE on DL.ID = DLPLEDGE.DESIGNATIONLEVELID
                         inner join dbo.' + @DBOBJECTNAME + ' as [IDSET_c372ab6f_046d_4bc5_b110_d39324a0433b] on [DL].[ID] = [IDSET_c372ab6f_046d_4bc5_b110_d39324a0433b].[ID] 
                    where
                        (DL.DESIGNATIONLEVELCATEGORYCODEID = @CATEGORYCODEID or @CATEGORYCODEID is null) and
                        (DL.DESIGNATIONLEVELTYPEID = @DESIGNATIONLEVELTYPEID or @DESIGNATIONLEVELTYPEID IS NULL)
                    order by DL.DESIGNATIONLEVELTYPEID, DL.NAME
                end';

      exec @r = sp_executesql @SQLTOEXEC, N'@START datetime, @END datetime, @CATEGORYCODEID uniqueidentifier, @DESIGNATIONLEVELTYPEID uniqueidentifier, @GROUPBY tinyint', @START = @START, @END = @END, @CATEGORYCODEID = @CATEGORYCODEID, @DESIGNATIONLEVELTYPEID = @DESIGNATIONLEVELTYPEID, @GROUPBY = @GROUPBY;
    end

  return @r;