USP_DATALIST_SOURCEANALYSISRESPONSE

CREATE procedure dbo.USP_DATALIST_SOURCEANALYSISRESPONSE
(
  @SELECTIONID uniqueidentifier = null,
  @RECORDSOURCEID uniqueidentifier = null,
  @CURRENTAPPUSERID uniqueidentifier = null,
  @SECURITYFEATUREID uniqueidentifier = null,
  @SECURITYFEATURETYPE tinyint = null,
  @CURRENCYCODE tinyint = 1 /* 0 = base, 1 = organization */
)
with execute as owner
as
  set nocount on;

  declare @GIFTIDSET uniqueidentifier;
  declare @SELECTIONNAME nvarchar(300);
  declare @RECORDSOURCE nvarchar(255);
  declare @FIELDNAME nvarchar(255);
  declare @DISPLAYFIELDNAME nvarchar(255);
  declare @TYPE nvarchar(128);
  declare @SQL nvarchar(max);
  declare @RETURN int;

  set @RETURN = 0;

  --Validation

  if @SELECTIONID is null
  begin
    raiserror('Invalid Selection ID',13,1);
    --return 1;

  end
  if @RECORDSOURCEID is null
  begin
    raiserror('Invalid Record Source ID',13,1);
    --return 1;

  end

  /* Set currency symbols using the organization currency */
  /* Note, for now this report will only display in the organization currency.  Since this report
     sums efforts that could have different base currencies, it was decided the amount of processing it would take to 
     convert different base currencies on the fly is too much and a different approach would be needed. */
  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]();

  --Table names

  declare @ALLTABLE nvarchar(50);
  declare @PIVOTTABLE nvarchar(50);
  declare @SARTABLE nvarchar(66);
  set @PIVOTTABLE = '##PIVOT_' + replace(cast(newid() as nvarchar(36)),'-','_');
  set @SARTABLE = dbo.UFN_MKTSOURCEANALYSISRULE_MAKETABLENAME(@RECORDSOURCEID)

  begin try
    --Create pivot table 

    set @SQL = 'create table dbo.' + @PIVOTTABLE +' (' + char(13) +
               '  [SAR_NAME] nvarchar(255) collate database_default,' + char(13) +
               '  [SAR_VALUE] nvarchar(255) collate database_default,' + char(13) +
               '  [SAR_TYPE] nvarchar(128) collate database_default,' + char(13) +
               '  [QUANTITY] int,' + char(13) +
               '  [GIFTS] int,' + char(13) +
               '  [REVENUE] money,' + char(13) +
               '  [COST] money,' + char(13) +
               '  [RESPONSE_RATE] decimal(19,4),' + char(13) +
               '  [AVG_GIFT] money,' + char(13) +
               '  [REV/M] money,' + char(13) +
               '  [COST/M] money,' + char(13) +
               '  [NET/M] money,' + char(13) +
               '  [CPDR] money,' + char(13) +
               '  [SELECTIONNAME] nvarchar(300) collate database_default,' + char(13) +
               '  [RECORDSOURCE] nvarchar(255) collate database_default,' + char(13) +
               '  [SAR_DISPLAYNAME] nvarchar(255) collate database_default' + char(13) +
               ')';
    exec sp_executesql @SQL;


    --Build SAR fields and types

    declare @SARFIELDS table (
      [FIELDNAME] nvarchar(255),
      [TYPE] nvarchar(128),
      [DISPLAYFIELDNAME] nvarchar(255)
      );

    insert into @SARFIELDS
      select 
        sys.columns.[NAME], sys.types.[NAME], [MKTSOURCEANALYSISRULEFIELDS].[NAME]
      from
        dbo.[MKTSOURCEANALYSISRULES]
        inner join dbo.[MKTSOURCEANALYSISRULEFIELDS] 
          on [MKTSOURCEANALYSISRULEFIELDS].[SOURCEANALYSISRULEID] = [MKTSOURCEANALYSISRULES].[ID] 
          and [MKTSOURCEANALYSISRULEFIELDS].[ISACTIVE] = 1
          and [MKTSOURCEANALYSISRULES].[RECORDSOURCEID] = @RECORDSOURCEID
        inner join sys.columns 
          on sys.columns.[NAME] = [MKTSOURCEANALYSISRULEFIELDS].[CACHETABLECOLUMNNAME]
          and sys.columns.[OBJECT_ID] = object_id(@SARTABLE)
        inner join sys.types  
          on sys.types.[USER_TYPE_ID] = sys.columns.[USER_TYPE_ID];

    --Get returned parameter translations

    select @SELECTIONNAME = [NAME] from dbo.[IDSETREGISTER] where [ID] = @SELECTIONID;
    select @RECORDSOURCE = dbo.[UFN_QUERYVIEW_GETNAME](@RECORDSOURCEID);

    --Build SEGMENTATIONCURSOR using only mailings for the @RECORDSOURCE

    declare @SEGMENTATIONCURSOR cursor;  
    set @SQL = 'set @SEGMENTATIONCURSOR = cursor local fast_forward for' + char(13) + 
               '  select [IDSET].[ID]' + char(13) + 
               '  from dbo.' + dbo.[UFN_MKTSELECTION_GETFUNCTIONNAME](@SELECTIONID) + ' as [IDSET]' + char(13) + 
               '  inner join dbo.[MKTSEGMENTATIONACTIVATE] on  [IDSET].[ID] = [MKTSEGMENTATIONACTIVATE].[SEGMENTATIONID] and [MKTSEGMENTATIONACTIVATE].[RECORDSOURCEID] = cast(@RECORDSOURCEID as nvarchar(36))' + char(13) + 
               '  inner join dbo.[MKTSEGMENTATION] on [MKTSEGMENTATION].[ID] = [MKTSEGMENTATIONACTIVATE].[SEGMENTATIONID]' + char(13) +
               '  where dbo.UFN_SITEALLOWEDFORUSER(''' + convert(nvarchar(36), @CURRENTAPPUSERID) + ''', [MKTSEGMENTATION].[SITEID]) = 1;' + char(13) +
               'open @SEGMENTATIONCURSOR;';  

    -- might as well leave UFN_SITEALLOWEDFORUSER up there, to cut down on the number of rows the real site security has to check

    -- they still need to be checked, though, because of the link between site security and the role in which access to a datalist is granted


    exec sp_executesql @SQL, N'@SEGMENTATIONCURSOR cursor output,@RECORDSOURCEID uniqueidentifier', @SEGMENTATIONCURSOR = @SEGMENTATIONCURSOR output, @RECORDSOURCEID = @RECORDSOURCEID; 

    --Loop through each Mailing

    declare @SEGMENTATIONID uniqueidentifier;
    fetch next from @SEGMENTATIONCURSOR into @SEGMENTATIONID;
    while (@@FETCH_STATUS = 0)
    begin
      if ( -- check site security

           select count(*) 
           from (select [SITEID]
                 from dbo.[MKTSEGMENTATION]
                 where [ID] = @SEGMENTATIONID) 
           as [SEGMENTATIONSITE]
           where (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[SEGMENTATIONSITE].[SITEID] or (SITEID is null and [SEGMENTATIONSITE].[SITEID] is null)))
         ) > 0
        begin
          --Find List of gifts for mailing

          select 
            @GIFTIDSET = [NORMALGIFTIDSETREGISTERID] 
          from 
            dbo.[MKTSEGMENTATIONACTIVATE] 
          where 
            [SEGMENTATIONID] = @SEGMENTATIONID 
            and [RECORDSOURCEID] = @RECORDSOURCEID;

          --Start ALL table SQL statements

          set @ALLTABLE = '##ALL_' + replace(cast(newid() as nvarchar(36)),'-','_');
          set @SQL = 'select' + char(13) +
                     '  [SAR].[DONORID],' + char(13) +
                     '  [GIFTIDSET].[ORGANIZATIONAMOUNT] as [AMOUNT],' + char(13) +
                     '  (case when [MKTSEGMENTATIONSEGMENTACTIVE].[QUANTITY] > 0 then ([MKTSEGMENTATIONSEGMENTACTIVE].[ORGANIZATIONTOTALCOST] / [MKTSEGMENTATIONSEGMENTACTIVE].[QUANTITY]) else 0 end) as [COST]'

          --Build remaining ALL table select SQL in loop

          declare SARFIELDSCURSOR1 cursor local fast_forward for
            select [FIELDNAME] from @SARFIELDS;

          open SARFIELDSCURSOR1;
          fetch next from SARFIELDSCURSOR1 into @FIELDNAME;
          while (@@FETCH_STATUS = 0)
          begin
            set @SQL = @SQL + ', [SAR].[' + @FIELDNAME +']';
            fetch next from SARFIELDSCURSOR1 into @FIELDNAME;
          end 
          close SARFIELDSCURSOR1;
          deallocate SARFIELDSCURSOR1;

          --Finish ALL table SQL

set @SQL = @SQL + char(13) + 
                      'into dbo.' + @ALLTABLE + char(13) +
                      'from ' + dbo.[UFN_MKTSELECTION_GETFUNCTIONNAME](@GIFTIDSET) + ' as [GIFTIDSET]' + char(13) +            
                      'right join dbo.' + @SARTABLE + ' as [SAR]' + char(13) +
                      'on [SAR].[DONORID] = [GIFTIDSET].[DONORID]'  + char(13) +
                      'inner join dbo.[MKTSEGMENTATIONSEGMENTACTIVE]' + char(13) +
                      'on [MKTSEGMENTATIONSEGMENTACTIVE].[SEGMENTID] = [SAR].[SEGMENTID]' + char(13) +
                      '  and ([MKTSEGMENTATIONSEGMENTACTIVE].[TESTSEGMENTID] = [SAR].[TESTSEGMENTID]' + char(13) +
                      '    or ([MKTSEGMENTATIONSEGMENTACTIVE].[TESTSEGMENTID] is null and [SAR].[TESTSEGMENTID] is null))' + char(13) +
                      'where [SAR].[MAILINGID] = @SEGMENTATIONID';

          --Build ALL table

          exec sp_executesql @SQL, N'@SEGMENTATIONID uniqueidentifier', @SEGMENTATIONID = @SEGMENTATIONID;

          if exists(select 1 from [TEMPDB].[INFORMATION_SCHEMA].[TABLES] where [TABLE_SCHEMA] = 'dbo' and [TABLE_NAME] = @ALLTABLE)
            begin
              --Build Pivot table

              declare @PIVOTSQL nvarchar(max);
              declare SARFIELDSCURSOR2 cursor local fast_forward for
                select 
                  [FIELDNAME], 
                  upper([TYPE]),
                  [DISPLAYFIELDNAME]
                from @SARFIELDS;

              open SARFIELDSCURSOR2;
              fetch next from SARFIELDSCURSOR2 into @FIELDNAME, @TYPE, @DISPLAYFIELDNAME;
              while (@@FETCH_STATUS = 0)
              begin
                --Make rows in pivot table if none there

                set @PIVOTSQL = 
                      'insert into dbo.' + @PIVOTTABLE + char(13) +
                      '  select distinct @FIELDNAME ,cast([' + @FIELDNAME + '] as nvarchar(255)), @TYPE, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, @SELECTIONNAME, @RECORDSOURCE, @DISPLAYFIELDNAME' + char(13) +
                      '  from dbo.'+ @ALLTABLE + char(13) +
                      '  where not exists (select 1 from dbo.' + @PIVOTTABLE + ' where [SAR_NAME] = @FIELDNAME  and [SAR_VALUE] = cast([' + @FIELDNAME + '] as nvarchar(255)))' + char(13) +
                      '  and [' + @FIELDNAME + '] is not null';

                exec sp_executesql @PIVOTSQL,N'@SELECTIONNAME nvarchar(300), @FIELDNAME nvarchar(255), @TYPE nvarchar(128), @RECORDSOURCE nvarchar(255), @DISPLAYFIELDNAME nvarchar(255)', @SELECTIONNAME = @SELECTIONNAME, @RECORDSOURCE = @RECORDSOURCE, @FIELDNAME = @FIELDNAME, @TYPE = @TYPE, @DISPLAYFIELDNAME = @DISPLAYFIELDNAME;

                --Update the quantity, gifts, and revenue in pivot table

                set @PIVOTSQL = 
                      'with [NUMBERS]([NAME], [VALUE], [QUANTITY], [GIFTS], [REVENUE]) as (' + char(13) +
                      '  select @FIELDNAME ,cast([' + @FIELDNAME + '] as nvarchar(255)), isnull(count(distinct [DONORID]),0), isnull(count(all [AMOUNT]),0), isnull(sum([AMOUNT]),0)' + char(13) +
                      '  from dbo.'+ @ALLTABLE + char(13) +
                      '  group by [' + @FIELDNAME + '] )' + char(13) +
                      'update dbo.' + @PIVOTTABLE + char(13) +
                      'set ' + @PIVOTTABLE + '.[QUANTITY] = ' + @PIVOTTABLE + '.[QUANTITY] + [NUMBERS].[QUANTITY],' + char(13) +
                      @PIVOTTABLE + '.[GIFTS] = ' + @PIVOTTABLE + '.[GIFTS] + [NUMBERS].[GIFTS],' + char(13) +
                      @PIVOTTABLE + '.[REVENUE] = ' + @PIVOTTABLE + '.[REVENUE] + [NUMBERS].[REVENUE]' + char(13) +
                      'from dbo.' + @PIVOTTABLE + char(13) +
                      'inner join [NUMBERS] on [NUMBERS].[NAME] = ' + @PIVOTTABLE + '.[SAR_NAME]' + char(13) +
                      'and [NUMBERS].[VALUE] = ' + @PIVOTTABLE + '.[SAR_VALUE]'; 
                exec sp_executesql @PIVOTSQL, N'@FIELDNAME nvarchar(255)', @FIELDNAME = @FIELDNAME;

                --Update the cost in pivot table

                set @PIVOTSQL = 
                      'with [NUMBERS]([NAME], [VALUE], [COST]) as (' + char(13) +
                      '  select @FIELDNAME ,[SARFIELD], isnull(sum([COST]),0)' + char(13) +
                      '  from (' + char(13) +
                      '    select distinct cast([' + @FIELDNAME + '] as nvarchar(255)) as [SARFIELD], [DONORID], [COST]' + char(13) +
                      '    from dbo.' + @ALLTABLE + ') as [DISTINCT]' + char(13) +
                      '  group by [SARFIELD] )' + char(13) +
                      'update dbo.' + @PIVOTTABLE + char(13) +
                      'set ' + @PIVOTTABLE + '.[COST] = ' + @PIVOTTABLE + '.[COST] + [NUMBERS].[COST]' + char(13) +
                      'from dbo.' + @PIVOTTABLE + char(13) +
                      'inner join [NUMBERS] on [NUMBERS].[NAME] = ' + @PIVOTTABLE + '.[SAR_NAME]' + char(13) +
                      'and [NUMBERS].[VALUE] = ' + @PIVOTTABLE + '.[SAR_VALUE]'; 
                exec sp_executesql @PIVOTSQL, N'@FIELDNAME nvarchar(255)', @FIELDNAME = @FIELDNAME;

                fetch next from SARFIELDSCURSOR2 into @FIELDNAME, @TYPE, @DISPLAYFIELDNAME;
              end 
              close SARFIELDSCURSOR2;
              deallocate SARFIELDSCURSOR2;

              --Drop temp table

              set @SQL = 'drop table dbo.' + @ALLTABLE;
              exec (@SQL);
            end;
        end;

      fetch next from @SEGMENTATIONCURSOR into @SEGMENTATIONID;
    end;

    close @SEGMENTATIONCURSOR;
    deallocate @SEGMENTATIONCURSOR;

    --Update the response rate, average gift, and cost per dollar raised in pivot table

    set @PIVOTSQL = 
          'update dbo.' + @PIVOTTABLE + char(13) +
          'set ' + @PIVOTTABLE + '.[RESPONSE_RATE] = cast((case when ' + @PIVOTTABLE + '.[QUANTITY] <> 0 then cast(' + @PIVOTTABLE + '.[GIFTS] as decimal(19,4)) / cast(' + @PIVOTTABLE + '.[QUANTITY] as decimal(19,4)) else 0 end) as decimal(19,4)),' + char(13) +
          @PIVOTTABLE + '.[AVG_GIFT] = cast((case when ' + @PIVOTTABLE + '.[GIFTS] <> 0 then ' + @PIVOTTABLE + '.[REVENUE] / ' + @PIVOTTABLE + '.[GIFTS] else 0 end) as money),' + char(13) +
          @PIVOTTABLE + '.[REV/M] = cast((case when ' + @PIVOTTABLE + '.[QUANTITY] <> 0 then (' + @PIVOTTABLE + '.[REVENUE] / ' + @PIVOTTABLE + '.[QUANTITY]) * 1000 else 0 end) as money),' + char(13) +
          @PIVOTTABLE + '.[COST/M] = cast((case when ' + @PIVOTTABLE + '.[QUANTITY] <> 0 then (' + @PIVOTTABLE + '.[COST] / ' + @PIVOTTABLE + '.[QUANTITY]) * 1000 else 0 end) as money),' + char(13) +
          @PIVOTTABLE + '.[NET/M] = cast((case when ' + @PIVOTTABLE + '.[QUANTITY] <> 0 then ((' + @PIVOTTABLE + '.[REVENUE] - ' + @PIVOTTABLE + '.[COST]) / ' + @PIVOTTABLE + '.[QUANTITY]) * 1000 else 0 end) as money),' + char(13) +
          @PIVOTTABLE + '.[CPDR] = cast((case when ' + @PIVOTTABLE + '.[REVENUE] <> 0 then ' + @PIVOTTABLE + '.[COST] / ' + @PIVOTTABLE + '.[REVENUE] else 0 end) as money)'      
    exec sp_executesql @PIVOTSQL;

    --Guarantee one returned row

    set @PIVOTSQL = 'if (select count(*) from ' + @PIVOTTABLE + ' where [SAR_VALUE] is not null) = 0 ' + char(13) + 
                    'begin'  + char(13) + 
                    '  insert into ' + @PIVOTTABLE +' select null, ''no value'', null, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, @SELECTIONNAME, @RECORDSOURCE, null;' + char(13) + 
                    'end';
    exec sp_executesql @PIVOTSQL,N'@SELECTIONNAME nvarchar(300), @RECORDSOURCE nvarchar(255)', @SELECTIONNAME = @SELECTIONNAME, @RECORDSOURCE = @RECORDSOURCE;

    --Display temp table

    set @PIVOTSQL = 
               'select ' + char(13) +
               '  [SAR_NAME], ' + char(13) +
               '  [SAR_VALUE], ' + char(13) +
               '  [SAR_TYPE], ' + char(13) +
               '  [QUANTITY], ' + char(13) +
               '  [GIFTS], ' + char(13) +
               '  [REVENUE], ' + char(13) +
               '  [COST],' + char(13) +
               '  [RESPONSE_RATE],' + char(13) +
               '  [AVG_GIFT],' + char(13) +
               '  [REV/M],' + char(13) +
               '  [COST/M],' + char(13) +
               '  [NET/M],' + char(13) +
               '  [CPDR],' + char(13) +
               '  [SELECTIONNAME],' + char(13) +
               '  [RECORDSOURCE],' + char(13) +
               '  ''' + @CURRENCYISOCURRENCYCODE + ''' as [CURRENCYISOCURRENCYCODE],' + char(13) +
               '  ''' + convert(nvarchar(10), @CURRENCYDECIMALDIGITS) + ''' as [CURRENCYDECIMALDIGITS],' + char(13) +
               '  ''' + @CURRENCYSYMBOL + ''' as [CURRENCYSYMBOL],' + char(13) +
               '  ''' + convert(nvarchar(10), @CURRENCYSYMBOLDISPLAYSETTINGCODE) + ''' as [CURRENCYSYMBOLDISPLAYSETTINGCODE],' + char(13) +
               '  [SAR_DISPLAYNAME] ' + char(13) +
               'from dbo.' + @PIVOTTABLE;
    exec sp_executesql @PIVOTSQL;
  end try
  begin catch
    exec dbo.[USP_RAISE_ERROR];
    set @RETURN = 1;
  end catch

  --Check for and drop temp tables

  if exists (select 1 from [TEMPDB].sys.[SYSOBJECTS] where [TYPE] ='U' and [NAME] = @ALLTABLE)
  begin
    set @SQL = 'drop table dbo.' + @ALLTABLE;
    exec (@SQL);
  end
  if exists (select 1 from [TEMPDB].sys.[SYSOBJECTS] where [TYPE] ='U' and [NAME] = @PIVOTTABLE)
  begin
    set @SQL = 'drop table dbo.' + @PIVOTTABLE;
    exec (@SQL);
  end

  return @RETURN;