USP_DATALIST_MKTREVENUEREPORTSUMMARY

CREATE procedure dbo.[USP_DATALIST_MKTREVENUEREPORTSUMMARY]
(
  @SEARCHRESULT nvarchar(64),
  @CURRENCYCODE tinyint = 1 /* 0 = base, 1 = organization */
)
as
  set nocount on;

  declare @IDTYPE nvarchar(16);
  declare @ID nvarchar(36);
  declare @ISAPPEALMAILING bit;

  set @IDTYPE = upper(substring(@SEARCHRESULT,1,charindex('|',@SEARCHRESULT)-1));
  set @ID = substring(@SEARCHRESULT,charindex('|',@SEARCHRESULT)+1,len(@SEARCHRESULT));
  set @ISAPPEALMAILING = 0;

  -- Get currency fields.

  declare @CURRENCYISOCURRENCYCODE nvarchar(6);
  declare @CURRENCYDECIMALDIGITS tinyint;
  declare @CURRENCYSYMBOL nvarchar(10);
  declare @CURRENCYSYMBOLDISPLAYSETTINGCODE tinyint;

  select 
    @CURRENCYISOCURRENCYCODE = [ISO4217],
    @CURRENCYDECIMALDIGITS = [DECIMALDIGITS],
    @CURRENCYSYMBOL = [CURRENCYSYMBOL],
    @CURRENCYSYMBOLDISPLAYSETTINGCODE = [SYMBOLDISPLAYSETTINGCODE]
  from dbo.[CURRENCY]
  where [ID] = dbo.[UFN_CURRENCY_GETORGANIZATIONCURRENCY]();

  declare @SEGMENTATIONID uniqueidentifier;
  declare @MAILINGS table (
    [ID] uniqueidentifier,
    [NAME] nvarchar(100),
    [DESCRIPTION] nvarchar(255),
    [MAILDATE] datetime
    );

  --Get all mailings

  if @IDTYPE = 'PLANITEM'
  begin
    with [MAILING]([ID], [NAME], [STARTDATE]) as
    (
      select 
        [MKTMARKETINGPLANITEM].[ID], 
        [MKTMARKETINGPLANITEM].[NAME], 
        [MKTMARKETINGPLANITEM].[STARTDATE]
      from 
        dbo.[MKTMARKETINGPLANITEM]            
      where 
        [MKTMARKETINGPLANITEM].[ID] = @ID
      union all
      select 
        [MKTMARKETINGPLANITEM].[ID],
        [MAILING].[NAME],
        [MAILING].[STARTDATE]
      from 
        dbo.[MKTMARKETINGPLANITEM]
        inner join [MAILING]
        on [MKTMARKETINGPLANITEM].[PARENTMARKETINGPLANITEMID] = [MAILING].[ID]
    )
    insert into @MAILINGS
      select [MKTSEGMENTATION].[ID], [MAILING].[NAME], '', [MAILING].[STARTDATE] 
      from dbo.[MKTSEGMENTATION]
        inner join  [MAILING]
        on [MKTSEGMENTATION].[MARKETINGPLANITEMID] = [MAILING].[ID];            
  end
  else
  begin
    if @IDTYPE = 'MARKETING EFFORT'
    begin
      insert into @MAILINGS
        select 
          [ID], 
          [NAME], 
          [DESCRIPTION], 
          isnull([MAILDATE],[ACTIVATEDATE])
        from dbo.[MKTSEGMENTATION]
        where [ID] = @ID;

      set @ISAPPEALMAILING = dbo.[UFN_MKTSEGMENTATION_ISAPPEALMAILING](@ID);
    end
    else
    begin
      if @IDTYPE = 'APPEAL'
      begin
        insert into @MAILINGS
          select 
            [MKTSEGMENTATION].[ID], 
            [MKTSEGMENTATIONACTIVATE].[APPEALDESCRIPTION], 
            [MKTSEGMENTATIONACTIVATE].[APPEALDESCRIPTION], 
            isnull([MKTSEGMENTATION].[MAILDATE], [MKTSEGMENTATION].[ACTIVATEDATE])
          from dbo.[MKTSEGMENTATIONACTIVATE]
          inner join dbo.[MKTSEGMENTATION] on [MKTSEGMENTATIONACTIVATE].[SEGMENTATIONID] = [MKTSEGMENTATION].[ID]
          where [MKTSEGMENTATION].[COMMUNICATIONTYPECODE] = 0  --Exclude appeal mailings

          and [MKTSEGMENTATIONACTIVATE].[APPEALID] = @ID;
      end
      else
      begin
        raiserror('Invalid ID type.',13,1);
        return 1;
      end
    end
  end

  --Display

  select 
    dbo.[UFN_TRANSLATIONFUNCTION_MKTMAILINGHIERACHRY](@SEARCHRESULT) as [NAME],
    [MAILINGS].[DESCRIPTION] as [DESCRIPTION],
    min([MAILINGS].[MAILDATE]) as [START_DATE],
    isnull(sum([MKTSEGMENTATIONACTIVE].[QUANTITY]),0) as [TOTAL_QUANTITY],
    isnull(sum([MKTSEGMENTATIONBUDGET].[ORGANIZATIONBUDGETAMOUNT]),0) as [TOTAL_BUDGET],
    isnull(sum([MKTSEGMENTATIONACTIVE].[ORGANIZATIONEXPECTEDTOTALGIFTAMOUNT]),0) as [PROJECTED_REVENUE],
    isnull(sum([MKTSEGMENTATIONACTIVE].[ORGANIZATIONTOTALCOST]),0) as [FINAL_COST],
    isnull(sum([MKTSEGMENTATIONACTIVE].[ORGANIZATIONTOTALGIFTAMOUNT]),0) as [REVENUE_TO_DATE],
    @ISAPPEALMAILING as [ISAPPEALMAILING],
    @CURRENCYISOCURRENCYCODE as [CURRENCYISOCURRENCYCODE],
    @CURRENCYDECIMALDIGITS as [CURRENCYDECIMALDIGITS],
    @CURRENCYSYMBOL as [CURRENCYSYMBOL],
    @CURRENCYSYMBOLDISPLAYSETTINGCODE as [CURRENCYSYMBOLDISPLAYSETTINGCODE]
  from 
    @MAILINGS [MAILINGS]
    left join dbo.[MKTSEGMENTATIONACTIVE]
    on [MAILINGS].[ID] = [MKTSEGMENTATIONACTIVE].[ID]
    left join dbo.[MKTSEGMENTATIONBUDGET]
    on [MAILINGS].[ID] = [MKTSEGMENTATIONBUDGET].[ID]
  group by 
    [MAILINGS].[NAME],
    [MAILINGS].[DESCRIPTION];

return 0;