USP_DATALIST_MKTAPPEALUNRESOLVEDRESPONSES

CREATE procedure dbo.USP_DATALIST_MKTAPPEALUNRESOLVEDRESPONSES
(
  @SEGMENTATIONID uniqueidentifier,
  @CURRENCYCODE tinyint = 1
)
as
  set nocount on;

  declare @RECORDSOURCEID uniqueidentifier;
  declare @SQL nvarchar(max);
  declare @GIFTIDSETID uniqueidentifier;
  declare @RECORDSOURCEVIEWNAME nvarchar(255);
  declare @RECORDSOURCEPRIMARYKEYFIELD nvarchar(255);
  declare @RECORDSOURCETITLEFIELD nvarchar(255);
  declare @RECORDSOURCEFIRSTNAMEFIELD nvarchar(255);
  declare @RECORDSOURCEMIDDLENAMEFIELD nvarchar(255);
  declare @RECORDSOURCELASTNAMEFIELD nvarchar(255);
  declare @RECORDSOURCESUFFIXFIELD nvarchar(255);
  declare @RECORDSOURCECOUNTRYFIELD nvarchar(255);
  declare @RECORDSOURCEADDRESSLINE1FIELD nvarchar(255);
  declare @RECORDSOURCEADDRESSLINE2FIELD nvarchar(255);
  declare @RECORDSOURCECITYFIELD nvarchar(255);
  declare @RECORDSOURCESTATEFIELD nvarchar(255);
  declare @RECORDSOURCEPOSTCODEFIELD nvarchar(255);
  declare @RECORDSOURCEPHONENUMBERFIELD nvarchar(255);
  declare @RECORDSOURCEEMAILADDRESSFIELD nvarchar(255);
  declare @RECORDSOURCEFULLNAMEFIELD nvarchar(255);

  /* Get multicurrency values for the given effort */
  declare @ISO4217 nvarchar(100);
  declare @DECIMALDIGITS tinyint;
  declare @CURRENCYSYMBOL nvarchar(5);
  declare @SYMBOLDISPLAYSETTINGCODE tinyint;

  select
    @ISO4217 = [CURRENCY].[ISO4217],
    @DECIMALDIGITS = [CURRENCY].[DECIMALDIGITS],
    @CURRENCYSYMBOL = [CURRENCY].[CURRENCYSYMBOL],
    @SYMBOLDISPLAYSETTINGCODE = [CURRENCY].[SYMBOLDISPLAYSETTINGCODE]
  from dbo.[MKTSEGMENTATION]
  inner join dbo.[CURRENCY] on [CURRENCY].[ID] = case when @CURRENCYCODE = 1 then dbo.[UFN_CURRENCY_GETORGANIZATIONCURRENCY]() else [MKTSEGMENTATION].[BASECURRENCYID] end
  where [MKTSEGMENTATION].[ID] = @SEGMENTATIONID;

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

  declare RECORDSOURCECURSOR cursor local fast_forward for
    select [QUERYVIEWCATALOGID]
    from dbo.[UFN_MKTSEGMENTATION_GETDISTINCTRECORDSOURCES](@SEGMENTATIONID);

  open RECORDSOURCECURSOR;
  fetch next from RECORDSOURCECURSOR into @RECORDSOURCEID;

  while (@@FETCH_STATUS = 0)
  begin
    /* Gather some info so we can build the SQL for each record source */
    select
      @GIFTIDSETID = [UNRESOLVEDGIFTIDSETREGISTERID]
    from dbo.[MKTSEGMENTATIONACTIVATE]
    where [SEGMENTATIONID] = @SEGMENTATIONID
    and [RECORDSOURCEID] = @RECORDSOURCEID;

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


    /* Build the SQL statement for this record source */
    set @SQL = 'select' + char(13) +
               '  ' + (case when len(@RECORDSOURCETITLEFIELD) = 0 then '''''' else '[DONORS].[' + @RECORDSOURCETITLEFIELD + ']' 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(@RECORDSOURCESUFFIXFIELD) = 0 then '''''' else '[DONORS].[' + @RECORDSOURCESUFFIXFIELD + ']' end) + ',' + char(13) +
               '  ' + (case when len(@RECORDSOURCECOUNTRYFIELD) = 0 then '''''' else '[DONORS].[' + @RECORDSOURCECOUNTRYFIELD + ']' 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(@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) +
               '  ' + (case when len(@RECORDSOURCEFULLNAMEFIELD) = 0 then '''''' else '[DONORS].[' + @RECORDSOURCEFULLNAMEFIELD + ']' end) + ',' + char(13) +
               '  [GIFTIDSET].[DATE],' + char(13) +
               '  ' + (case when @CURRENCYCODE = 1 then '[GIFTIDSET].[ORGANIZATIONAMOUNT]' else '[GIFTIDSET].[AMOUNT]' end) + ' as [AMOUNT]' + char(13) +
               'from dbo.' + dbo.[UFN_MKTSELECTION_GETFUNCTIONNAME](@GIFTIDSETID) + ' as [GIFTIDSET]' + char(13) +
               'inner join dbo.[' + @RECORDSOURCEVIEWNAME + '] as [DONORS] on [DONORS].[' + @RECORDSOURCEPRIMARYKEYFIELD + '] = [GIFTIDSET].[DONORID]';

    /* Execute the SQL to get the counts/amounts for this record source */
    insert into @RESULTTABLE
      exec (@SQL);

    fetch next from RECORDSOURCECURSOR into @RECORDSOURCEID;
  end;

  close RECORDSOURCECURSOR;
  deallocate RECORDSOURCECURSOR;

  /* Return the values */
  select 
    [TITLE],
    [FIRSTNAME],
    [MIDDLENAME],
    [LASTNAME],
    [SUFFIX],
    [COUNTRY],
    [ADDRESSLINE1],
    [ADDRESSLINE2],
    [CITY],
    [STATE],
    [POSTCODE],
    [PHONENUMBER],
    [EMAILADDRESS],
    [FULLNAME],
    [GIFTDATE],
    [GIFTAMOUNT],
    @ISO4217 as [CURRENCYISOCURRENCYCODE],
    @DECIMALDIGITS as [CURRENCYDECIMALDIGITS],
    @CURRENCYSYMBOL as [CURRENCYSYMBOL],
    @SYMBOLDISPLAYSETTINGCODE as [CURRENCYSYMBOLDISPLAYSETTINGCODE],
    [MIDDLENAME] as [MIDDLENAME_STRING],
    [LASTNAME] as [LASTNAME_STRING],
    [GIFTDATE] as [GIFTDATE_DATE],
    [GIFTAMOUNT] as [GIFTAMOUNT_MONEY]
  from @RESULTTABLE
  order by [LASTNAME], [FULLNAME], [FIRSTNAME], [MIDDLENAME], [GIFTDATE];

  return 0;