USP_DATALIST_MKTINDIRECTRESPONSES

CREATE procedure dbo.[USP_DATALIST_MKTINDIRECTRESPONSES]
(
  @SEGMENTATIONID uniqueidentifier,
  @CURRENCYCODE tinyint = 1
)
with execute as owner
as
  set nocount on;

  declare @BASECURRENCYID uniqueidentifier;
  declare @RECORDSOURCEID uniqueidentifier;
  declare @GIFTIDSETTABLE nvarchar(128);
  declare @REVENUESEGMENTTABLE nvarchar(128);
  declare @SQL nvarchar(max);

  declare @RECORDSOURCEVIEWNAME nvarchar(128);
  declare @RECORDSOURCEPRIMARYKEYFIELD nvarchar(255);
  declare @RECORDSOURCEFULLNAMEFIELD nvarchar(255);
  declare @RECORDSOURCEFIRSTNAMEFIELD nvarchar(255);
  declare @RECORDSOURCEMIDDLENAMEFIELD nvarchar(255);
  declare @RECORDSOURCELASTNAMEFIELD nvarchar(255);
  declare @RECORDSOURCEADDRESSLINE1FIELD nvarchar(255);
  declare @RECORDSOURCEADDRESSLINE2FIELD nvarchar(255);
  declare @RECORDSOURCEADDRESSLINE3FIELD nvarchar(255);
  declare @RECORDSOURCEADDRESSLINE4FIELD nvarchar(255);
  declare @RECORDSOURCEADDRESSLINE5FIELD nvarchar(255);
  declare @RECORDSOURCECITYFIELD nvarchar(255);
  declare @RECORDSOURCESTATEFIELD nvarchar(255);
  declare @RECORDSOURCEPOSTCODEFIELD nvarchar(255);
  declare @RECORDSOURCEPHONENUMBERFIELD nvarchar(255);
  declare @RECORDSOURCEEMAILADDRESSFIELD nvarchar(255);

  declare @RESULTTABLE table (
    [FULLNAME] nvarchar(255),
    [FIRSTNAME] nvarchar(100),
    [MIDDLENAME] nvarchar(100),
    [LASTNAME] nvarchar(100),
    [ADDRESSLINE1] nvarchar(255),
    [ADDRESSLINE2] nvarchar(255),
    [ADDRESSLINE3] nvarchar(255),
    [ADDRESSLINE4] nvarchar(255),
    [ADDRESSLINE5] nvarchar(255),
    [CITY] nvarchar(100),
    [STATE] nvarchar(100),
    [POSTCODE] nvarchar(30),
    [PHONENUMBER] nvarchar(50),
    [EMAILADDRESS] nvarchar(255),
    [GIFTDATE] datetime,
    [GIFTAMOUNT] money
  );


  begin try
    if @CURRENCYCODE = 1
      set @BASECURRENCYID = dbo.[UFN_CURRENCY_GETORGANIZATIONCURRENCY]();
    else
      select
        @BASECURRENCYID = [MKTSEGMENTATION].[BASECURRENCYID]
      from dbo.[MKTSEGMENTATION]
      where [ID] = @SEGMENTATIONID;


    declare RECORDSOURCECURSOR cursor local fast_forward for
      select
        [MKTSEGMENTATIONACTIVATE].[RECORDSOURCEID],
        dbo.[UFN_MKTSELECTION_GETFUNCTIONNAME]([MKTSEGMENTATIONACTIVATE].[NORMALGIFTIDSETREGISTERID]),
        dbo.[UFN_REVENUESEGMENT_MAKETABLENAME]([MKTSEGMENTATIONACTIVATE].[RECORDSOURCEID])
      from dbo.[UFN_MKTSEGMENTATION_GETDISTINCTRECORDSOURCES](@SEGMENTATIONID) as [DRS]
      inner join dbo.[MKTSEGMENTATIONACTIVATE] on [MKTSEGMENTATIONACTIVATE].[RECORDSOURCEID] = [DRS].[QUERYVIEWCATALOGID] and [MKTSEGMENTATIONACTIVATE].[SEGMENTATIONID] = @SEGMENTATIONID

    open RECORDSOURCECURSOR;
    fetch next from RECORDSOURCECURSOR into @RECORDSOURCEID, @GIFTIDSETTABLE, @REVENUESEGMENTTABLE;

    while (@@FETCH_STATUS = 0)
    begin
      -- get the record source field mappings...

      select
        @RECORDSOURCEVIEWNAME = [QUERYVIEWCATALOG].[OBJECTNAME],
        @RECORDSOURCEPRIMARYKEYFIELD = [QUERYVIEWCATALOG].[PRIMARYKEYFIELD],
        @RECORDSOURCEFULLNAMEFIELD = isnull([FULLNAMEFIELD], ''),
        @RECORDSOURCEFIRSTNAMEFIELD = isnull([FIRSTNAMEFIELD],''),
        @RECORDSOURCEMIDDLENAMEFIELD = isnull([MIDDLENAMEFIELD],''),
        @RECORDSOURCELASTNAMEFIELD = isnull([LASTNAMEFIELD],''),
        @RECORDSOURCEADDRESSLINE1FIELD = isnull([ADDRESSLINE1FIELD],''),
        @RECORDSOURCEADDRESSLINE2FIELD = isnull([ADDRESSLINE2FIELD],''),
        @RECORDSOURCEADDRESSLINE3FIELD = isnull([ADDRESSLINE3FIELD],''),
        @RECORDSOURCEADDRESSLINE4FIELD = isnull([ADDRESSLINE4FIELD],''),
        @RECORDSOURCEADDRESSLINE5FIELD = isnull([ADDRESSLINE5FIELD],''),
        @RECORDSOURCECITYFIELD = isnull([CITYFIELD],''),
        @RECORDSOURCESTATEFIELD = isnull([STATEFIELD],''),
        @RECORDSOURCEPOSTCODEFIELD = isnull([POSTCODEFIELD],''),
        @RECORDSOURCEPHONENUMBERFIELD = isnull([PHONENUMBERFIELD],''),
        @RECORDSOURCEEMAILADDRESSFIELD = isnull([EMAILADDRESSFIELD],'')
      from dbo.[MKTRECORDSOURCEFIELDMAPPINGS]
      inner join dbo.[QUERYVIEWCATALOG] on [QUERYVIEWCATALOG].[ID] = [MKTRECORDSOURCEFIELDMAPPINGS].[ID]
      where [MKTRECORDSOURCEFIELDMAPPINGS].[ID] = @RECORDSOURCEID;

      -- build the SQL to get all revenue in the normal gifts table that is not matched to a segment in the REVENUESEGMENT table...

      set @SQL = 'select' + char(13) +
                 '  ' + (case when len(@RECORDSOURCEFULLNAMEFIELD) = 0 then '''''' else '[DONORS].[' + @RECORDSOURCEFULLNAMEFIELD + ']' end) + ',' + char(13) +
                 '  ' + (case when len(@RECORDSOURCEFIRSTNAMEFIELD) = 0 then '''''' else '[DONORS].[' + @RECORDSOURCEFIRSTNAMEFIELD + ']' end) + ',' + char(13) +
                 '  ' + (case when len(@RECORDSOURCEMIDDLENAMEFIELD) = 0 then '''''' else '[DONORS].[' + @RECORDSOURCEMIDDLENAMEFIELD + ']' end) + ',' + char(13) +
                 '  ' + (case when len(@RECORDSOURCELASTNAMEFIELD) = 0 then '''''' else '[DONORS].[' + @RECORDSOURCELASTNAMEFIELD + ']' end) + ',' + char(13) +
                 '  ' + (case when len(@RECORDSOURCEADDRESSLINE1FIELD) = 0 then '''''' else '[DONORS].[' + @RECORDSOURCEADDRESSLINE1FIELD + ']' end) + ',' + char(13) +
                 '  ' + (case when len(@RECORDSOURCEADDRESSLINE2FIELD) = 0 then '''''' else '[DONORS].[' + @RECORDSOURCEADDRESSLINE2FIELD + ']' end) + ',' + char(13) +
                 '  ' + (case when len(@RECORDSOURCEADDRESSLINE3FIELD) = 0 then '''''' else '[DONORS].[' + @RECORDSOURCEADDRESSLINE3FIELD + ']' end) + ',' + char(13) +
                 '  ' + (case when len(@RECORDSOURCEADDRESSLINE4FIELD) = 0 then '''''' else '[DONORS].[' + @RECORDSOURCEADDRESSLINE4FIELD + ']' end) + ',' + char(13) +
                 '  ' + (case when len(@RECORDSOURCEADDRESSLINE5FIELD) = 0 then '''''' else '[DONORS].[' + @RECORDSOURCEADDRESSLINE5FIELD + ']' end) + ',' + char(13) +
                 '  ' + (case when len(@RECORDSOURCECITYFIELD) = 0 then '''''' else '[DONORS].[' + @RECORDSOURCECITYFIELD + ']' end) + ',' + char(13) +
                 '  ' + (case when len(@RECORDSOURCESTATEFIELD) = 0 then '''''' else '[DONORS].[' + @RECORDSOURCESTATEFIELD + ']' end) + ',' + char(13) +
                 '  ' + (case when len(@RECORDSOURCEPOSTCODEFIELD) = 0 then '''''' else '[DONORS].[' + @RECORDSOURCEPOSTCODEFIELD + ']' end) + ',' + char(13) +
                 '  ' + (case when len(@RECORDSOURCEPHONENUMBERFIELD) = 0 then '''''' else '[DONORS].[' + @RECORDSOURCEPHONENUMBERFIELD + ']' end) + ',' + char(13) +
                 '  ' + (case when len(@RECORDSOURCEEMAILADDRESSFIELD) = 0 then '''''' else '[DONORS].[' + @RECORDSOURCEEMAILADDRESSFIELD + ']' end) + ',' + char(13) +
                 '  [GIFTIDSET].[DATE],' + char(13) +
                 '  [GIFTIDSET].[' + (case when @CURRENCYCODE = 1 then 'ORGANIZATIONAMOUNT' else 'AMOUNT' end) + '] as [AMOUNT]' + char(13) +
                 'from dbo.' + @GIFTIDSETTABLE + ' as [GIFTIDSET]' + char(13) +
                 'inner join dbo.[' + @RECORDSOURCEVIEWNAME + '] as [DONORS] on [DONORS].[' + @RECORDSOURCEPRIMARYKEYFIELD + '] = [GIFTIDSET].[DONORID]' + char(13) +
                 'where not exists(select * from dbo.[' + @REVENUESEGMENTTABLE + '] where [REVENUEID] = [GIFTIDSET].[ID] and [SEGMENTID] in (select [ID] from dbo.[MKTSEGMENTATIONSEGMENT] where [SEGMENTATIONID] = @SEGMENTATIONID));';

      -- execute the SQL to get the counts/amounts for this record source

      insert into @RESULTTABLE
        exec sp_executesql @SQL, N'@SEGMENTATIONID uniqueidentifier', @SEGMENTATIONID = @SEGMENTATIONID;

      fetch next from RECORDSOURCECURSOR into @RECORDSOURCEID, @GIFTIDSETTABLE, @REVENUESEGMENTTABLE;
    end

    close RECORDSOURCECURSOR;
    deallocate RECORDSOURCECURSOR;


    -- return the values

    select 
      [FULLNAME],
      [FIRSTNAME],
      [MIDDLENAME],
      [LASTNAME],
      [ADDRESSLINE1],
      [ADDRESSLINE2],
      [ADDRESSLINE3],
      [ADDRESSLINE4],
      [ADDRESSLINE5],
      [CITY],
      [STATE],
      [POSTCODE],
      [PHONENUMBER],
      [EMAILADDRESS],
      [GIFTDATE],
      [GIFTAMOUNT],
      [CURRENCY].[ISO4217] as [CURRENCYISOCURRENCYCODE],
      [CURRENCY].[DECIMALDIGITS] as [CURRENCYDECIMALDIGITS],
      [CURRENCY].[CURRENCYSYMBOL],
      [CURRENCY].[SYMBOLDISPLAYSETTINGCODE] as [CURRENCYSYMBOLDISPLAYSETTINGCODE],
      [LASTNAME] as [LASTNAME_STRING],
      [GIFTDATE] as [GIFTDATE_DATE]
    from @RESULTTABLE as [RESULTTABLE]
    inner join dbo.[CURRENCY] on [CURRENCY].[ID] = @BASECURRENCYID
    order by [LASTNAME], [FULLNAME], [FIRSTNAME], [MIDDLENAME], [GIFTDATE];
  end try

  begin catch
    exec dbo.[USP_RAISE_ERROR];
    return 1;
  end catch

  return 0;