USP_REPORT_DESIGNATIONLEVELREVENUE_BYDATE_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_BYDATE_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_BYDATE] @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,
                        DLINFO.SEQUENCE,
                        DLINFO.PERIODYEAR,
                        DLOVERALLINFO.NUMGIFTS as TOTALNUMGIFTS,
                        DLINFO.NUMDONORS as TOTALDONORS,
                        (coalesce(RECEIVED.TOTALRECEIVED, 0) + coalesce(PLEDGE.TOTALPLEDGED - (PLEDGE.TOTALPAID + PLEDGE.TOTALWRITEOFFS), 0)) as TOTALRAISED,
                        DLINFO.NUMGIFTS as PERIODNUMGIFTS,
                        DLINFO.MAXGIFT 
                    from dbo.DESIGNATIONLEVEL DL
                        left join dbo.UFN_DESIGNATIONLEVEL_REVENUECOUNTS_BYPERIOD(@START, @END, @GROUPBY) DLINFO on DL.ID = DLINFO.DESIGNATIONLEVELID
                        left join dbo.UFN_DESIGNATIONLEVEL_REVENUERECEIVED_BYPERIOD(@START, @END, @GROUPBY) RECEIVED on DL.ID = RECEIVED.DESIGNATIONLEVELID and DLINFO.STARTDATE = RECEIVED.STARTDATE
                        left join dbo.UFN_DESIGNATIONLEVEL_REVENUEPLEDGED_BYPERIOD(@START, @END, @GROUPBY) PLEDGE on DL.ID = PLEDGE.DESIGNATIONLEVELID and DLINFO.STARTDATE = PLEDGE.STARTDATE
                        left join dbo.UFN_DESIGNATIONLEVEL_REVENUECOUNTS(@START, @END) DLOVERALLINFO on DL.ID = DLOVERALLINFO.DESIGNATIONLEVELID
                         inner join dbo.' + @DBOBJECTNAME + ' as [IDSET_d74719c1_63f1_4533_ac31_0899c36f62da] on [DL].[ID] = [IDSET_d74719c1_63f1_4533_ac31_0899c36f62da].[ID] 
                    where
                        (DL.DESIGNATIONLEVELCATEGORYCODEID = @CATEGORYCODEID or @CATEGORYCODEID is null) and
                        (DL.DESIGNATIONLEVELTYPEID = @DESIGNATIONLEVELTYPEID or @DESIGNATIONLEVELTYPEID IS NULL)
                    order by DL.DESIGNATIONLEVELTYPEID, DL.NAME, DLINFO.PERIODYEAR, DLINFO.SEQUENCE
                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;