USP_REPORT_DESIGNATIONPROGRESSSUMMARY_WITHIDSET

/*
Generated by Blackbaud Application Framework
Date: 8/17/2011 2:36:37 PM
Assembly Version: Blackbaud.AppFx.Platform.SqlClr, Version=2.91.1535.0, Culture=neutral, PublicKeyToken=null
*/
CREATE procedure dbo.USP_REPORT_DESIGNATIONPROGRESSSUMMARY_WITHIDSET (@IDSETID uniqueidentifier = null, @DESIGNATIONLEVELID uniqueidentifier, @GOALID uniqueidentifier, @REPORTCODE1ID uniqueidentifier, @REPORTCODE2ID uniqueidentifier, @CATEGORYID uniqueidentifier, @TYPE uniqueidentifier, @GROUPBY tinyint, @CURRENCYCODE tinyint)
with execute as owner
as
  set nocount on;

  declare @r int;

  if @IDSETID is null
    begin
      exec @r = dbo.[USP_REPORT_DESIGNATIONPROGRESSSUMMARY] @DESIGNATIONLEVELID, @GOALID, @REPORTCODE1ID, @REPORTCODE2ID, @CATEGORYID, @TYPE, @GROUPBY, @CURRENCYCODE;
    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'declare @SELECTEDCURRENCYID uniqueidentifier;

                if @CURRENCYCODE = 0
                    -- use each DESIGNATIONS''s base currency

                    set @SELECTEDCURRENCYID = null;
                else
                begin
                    -- use org currency

                    set @CURRENCYCODE = 1;
                    set @SELECTEDCURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();
                end

                select 
                    ''http://www.blackbaud.com/DESIGNATIONID?DESIGNATIONID='' + CONVERT(nvarchar(36),DESIGNATIONPROGRESS.DESIGNATIONID) as [DESIGNATIONID],
                    DESIGNATIONPROGRESS.LEVEL1NAME,
                    case when DESIGNATIONPROGRESS.LEVEL2NAME is null then DESIGNATIONPROGRESS.LEVEL1NAME
                         when DESIGNATIONPROGRESS.LEVEL3NAME is null then DESIGNATIONPROGRESS.LEVEL1NAME + '' \ '' + DESIGNATIONPROGRESS.LEVEL2NAME
                         when DESIGNATIONPROGRESS.LEVEL4NAME is null then DESIGNATIONPROGRESS.LEVEL1NAME + '' \ '' + DESIGNATIONPROGRESS.LEVEL2NAME + '' \ '' + DESIGNATIONPROGRESS.LEVEL3NAME
                         when DESIGNATIONPROGRESS.LEVEL5NAME is null then DESIGNATIONPROGRESS.LEVEL1NAME + '' \ '' + DESIGNATIONPROGRESS.LEVEL2NAME + '' \ '' + DESIGNATIONPROGRESS.LEVEL3NAME + '' \ '' + DESIGNATIONPROGRESS.LEVEL4NAME
                         else DESIGNATIONPROGRESS.LEVEL1NAME + '' \ '' + DESIGNATIONPROGRESS.LEVEL2NAME + '' \ '' + DESIGNATIONPROGRESS.LEVEL3NAME + '' \ '' + DESIGNATIONPROGRESS.LEVEL4NAME + '' \ '' + DESIGNATIONPROGRESS.LEVEL5NAME
                         end DESIGNATIONNAME,
                    DESIGNATIONPROGRESS.DESIGNATIONLEVEL1ID,
                    DESIGNATIONPROGRESS.DESIGNATIONLEVEL2ID,
                    DESIGNATIONPROGRESS.DESIGNATIONLEVEL3ID,
                    DESIGNATIONPROGRESS.DESIGNATIONLEVEL4ID,
                    DESIGNATIONPROGRESS.DESIGNATIONLEVEL5ID,
                    DESIGNATIONPROGRESS.DESIGNATIONLEVELTYPE,
                    DESIGNATIONPROGRESS.REPORTCODE1,
                    DESIGNATIONPROGRESS.REPORTCODE2,
                    coalesce(DESIGNATIONPROGRESS.DESIGNATIONGOAL,0) as DESIGNATIONGOAL,
                    coalesce(DESIGNATIONPROGRESS.OVERALLGOAL,0) as OVERALLGOAL,
                    DESIGNATIONPROGRESS.GOALNAME,
                    DESIGNATIONPROGRESS.STARTDATE,
                    DESIGNATIONPROGRESS.ENDDATE,
                    coalesce(DESIGNATIONPROGRESS.TOTALEXPECTED,0) as TOTALEXPECTED,
                    coalesce(DESIGNATIONPROGRESS.TOTALRECEIVED,0) as TOTALRECEIVED,
                    coalesce(DESIGNATIONPROGRESS.TOTALPLANNEDGIFT,0) as TOTALPLANNEDGIFT,
                    coalesce(DESIGNATIONPROGRESS.TOTALGIFTS,0) as TOTALGIFTS,
                    coalesce(DESIGNATIONPROGRESS.TOTALDONORS,0) as TOTALDONORS,
                    DESIGNATIONPROGRESS.APPEALNAME,
                    coalesce(DESIGNATIONPROGRESS.APPEALRECEIVED,0) as APPEALRECEIVED,
                    coalesce(DESIGNATIONPROGRESS.APPEALEXPECTED,0) as APPEALEXPECTED,
                    coalesce(DESIGNATIONPROGRESS.APPEALNUMGIFTS,0) as APPEALNUMGIFTS,
                    coalesce(DESIGNATIONPROGRESS.APPEALNUMDONORS,0) as APPEALNUMDONORS,
                    coalesce(DESIGNATIONPROGRESS.APPEALMAXGIFT,0) as APPEALMAXGIFT,
                    coalesce(DESIGNATIONPROGRESS.APPEALPLANNEDGIFT,0) as APPEALPLANNEDGIFT,
                    DESIGNATIONPROGRESS.APPEALCURRENCYID,
                    DESIGNATIONPROGRESS.APPEALCURRENCYISO,
                    DESIGNATIONPROGRESS.APPEALCURRENCYDECIMALDIGITS,
                    DESIGNATIONPROGRESS.APPEALCURRENCYSYMBOL,
                    DESIGNATIONPROGRESS.APPEALCURRENCYSYMBOLDISPLAYSETTINGCODE,
                    DESIGNATIONPROGRESS.DESIGNATIONCURRENCYID,
                    DESIGNATIONPROGRESS.DESIGNATIONCURRENCYISO,
                    DESIGNATIONPROGRESS.DESIGNATIONCURRENCYDECIMALDIGITS,
                    DESIGNATIONPROGRESS.DESIGNATIONCURRENCYSYMBOL,
                    DESIGNATIONPROGRESS.DESIGNATIONCURRENCYSYMBOLDISPLAYSETTINGCODE,
                    DESIGNATIONPROGRESS.ISACTIVE
                from 
                    dbo.UFN_DESIGNATION_GETPROGRESSINCURRENCY(@DESIGNATIONLEVELID, @GOALID, @REPORTCODE1ID, @REPORTCODE2ID, 1, @GROUPBY, @SELECTEDCURRENCYID) DESIGNATIONPROGRESS
                     inner join dbo.' as nvarchar(max)) + @DBOBJECTNAME + cast(N' as [IDSET_299b8b95_01f4_4d9f_a403_fa88825d278c] on [DESIGNATIONPROGRESS].[DESIGNATIONID] = [IDSET_299b8b95_01f4_4d9f_a403_fa88825d278c].[ID] 
                where
                    (DESIGNATIONPROGRESS.LASTCATEGORYCODEID = @CATEGORYID or @CATEGORYID is null) and
                    (DESIGNATIONPROGRESS.LASTTYPEID = @TYPE or @TYPE is null);' as nvarchar(max));

      exec @r = sp_executesql @SQLTOEXEC, N'@DESIGNATIONLEVELID uniqueidentifier, @GOALID uniqueidentifier, @REPORTCODE1ID uniqueidentifier, @REPORTCODE2ID uniqueidentifier, @CATEGORYID uniqueidentifier, @TYPE uniqueidentifier, @GROUPBY tinyint, @CURRENCYCODE tinyint', @DESIGNATIONLEVELID = @DESIGNATIONLEVELID, @GOALID = @GOALID, @REPORTCODE1ID = @REPORTCODE1ID, @REPORTCODE2ID = @REPORTCODE2ID, @CATEGORYID = @CATEGORYID, @TYPE = @TYPE, @GROUPBY = @GROUPBY, @CURRENCYCODE = @CURRENCYCODE;
    end

  return @r;