USP_DATALIST_MKTSEGMENTREVENE_MEMBER

CREATE procedure dbo.[USP_DATALIST_MKTSEGMENTREVENE_MEMBER]
(
  @SEGMENTID uniqueidentifier,
  @TOTALROWS int = 500,
  @FILTER nvarchar(253) = null
)
as
  set nocount on;

  declare @PARENTTABLENAME nvarchar(255); 
  declare @PARENTID nvarchar(255); 
  declare @SEGMENTVIEWNAME nvarchar(255); 
  declare @RECORDSOURCEID uniqueidentifier;
  declare @ISBBEC bit;

  begin try
    select
      @RECORDSOURCEID = [MKTSEGMENT].[QUERYVIEWCATALOGID],
      @PARENTTABLENAME = [QUERYVIEWCATALOG].[OBJECTNAME],
      @PARENTID = [QUERYVIEWCATALOG].[PRIMARYKEYFIELD],
      @SEGMENTVIEWNAME = dbo.[UFN_MKTSEGMENT_MAKEVIEWNAME]([MKTSEGMENT].[ID]),
      @ISBBEC = dbo.[UFN_MKTRECORDSOURCE_VALIDFORBBEC]([MKTSEGMENT].[QUERYVIEWCATALOGID])
    from dbo.[MKTSEGMENT]
    inner join dbo.[QUERYVIEWCATALOG] on [QUERYVIEWCATALOG].[ID] = [MKTSEGMENT].[QUERYVIEWCATALOGID]
    where [MKTSEGMENT].[ID] = @SEGMENTID;

    -- these represent the mapped fields --

    declare @FULLNAMEFIELDNAME nvarchar(255);
    declare @FIRSTNAMEFIELDNAME nvarchar(255);
    declare @MIDDLENAMEFIELDNAME nvarchar(255);
    declare @LASTNAMEFIELDNAME nvarchar(255);
    declare @TITLEFIELDNAME nvarchar(255);
    declare @SUFFIXFIELDNAME nvarchar(255);
    declare @COUNTRYFIELDNAME nvarchar(255);
    declare @ADDRESSLINE1FIELDNAME nvarchar(255);
    declare @ADDRESSLINE2FIELDNAME nvarchar(255);
    declare @CITYFIELDNAME nvarchar(255);
    declare @STATEFIELDNAME nvarchar(255);
    declare @POSTCODEFIELDNAME nvarchar(255);
    declare @CARTFIELDNAME nvarchar(255);
    declare @DPCFIELDNAME nvarchar(255);
    declare @LOTFIELDNAME nvarchar(255);
    declare @PHONENUMBERFIELDNAME nvarchar(255);
    declare @EMAILADDRESSFIELDNAME nvarchar(255);
    declare @FIRSTGIFTDATEFIELDNAME nvarchar(255);
    declare @LOOKUPIDFIELDNAME nvarchar(255);

    declare @GIFTVIEWNAME nvarchar(128);
    declare @GIFTVIEWPRIMARYKEYFIELD nvarchar(255)
    declare @GIFTVIEWDONORIDFIELD nvarchar(255);
    declare @GIFTAMOUNTFIELDNAME nvarchar(255);
    declare @GIFTDATEFIELDNAME nvarchar(255);
    declare @GIFTLOOKUPIDFIELDNAME nvarchar(255);

    declare @BASECURRENCYIDFIELDNAME nvarchar(255);

    -- grab the field names --

    select
        @FULLNAMEFIELDNAME = [MKTRECORDSOURCEFIELDMAPPINGS].[FULLNAMEFIELD] 
        ,@FIRSTNAMEFIELDNAME = [MKTRECORDSOURCEFIELDMAPPINGS].[FIRSTNAMEFIELD]
        ,@MIDDLENAMEFIELDNAME = [MKTRECORDSOURCEFIELDMAPPINGS].[MIDDLENAMEFIELD]
        ,@LASTNAMEFIELDNAME = [MKTRECORDSOURCEFIELDMAPPINGS].[LASTNAMEFIELD]
        ,@TITLEFIELDNAME = [MKTRECORDSOURCEFIELDMAPPINGS].[TITLEFIELD]
        ,@SUFFIXFIELDNAME = [MKTRECORDSOURCEFIELDMAPPINGS].[SUFFIXFIELD]
        ,@COUNTRYFIELDNAME = [MKTRECORDSOURCEFIELDMAPPINGS].[COUNTRYFIELD]
        ,@ADDRESSLINE1FIELDNAME = [MKTRECORDSOURCEFIELDMAPPINGS].[ADDRESSLINE1FIELD]
        ,@ADDRESSLINE2FIELDNAME = [MKTRECORDSOURCEFIELDMAPPINGS].[ADDRESSLINE2FIELD]
        ,@CITYFIELDNAME = [MKTRECORDSOURCEFIELDMAPPINGS].[CITYFIELD]
        ,@STATEFIELDNAME = [MKTRECORDSOURCEFIELDMAPPINGS].[STATEFIELD]
        ,@POSTCODEFIELDNAME = [MKTRECORDSOURCEFIELDMAPPINGS].[POSTCODEFIELD]
        ,@CARTFIELDNAME = [MKTRECORDSOURCEFIELDMAPPINGS].[CARTFIELD]
        ,@DPCFIELDNAME = [MKTRECORDSOURCEFIELDMAPPINGS].[DPCFIELD]
        ,@LOTFIELDNAME = [MKTRECORDSOURCEFIELDMAPPINGS].[LOTFIELD]
        ,@PHONENUMBERFIELDNAME = [MKTRECORDSOURCEFIELDMAPPINGS].[PHONENUMBERFIELD]
        ,@EMAILADDRESSFIELDNAME = [MKTRECORDSOURCEFIELDMAPPINGS].[EMAILADDRESSFIELD]
        ,@FIRSTGIFTDATEFIELDNAME = [MKTRECORDSOURCEFIELDMAPPINGS].[FIRSTGIFTDATEFIELD]
        ,@GIFTVIEWNAME = [QUERYVIEWCATALOG].[OBJECTNAME]
        ,@GIFTVIEWPRIMARYKEYFIELD = [QUERYVIEWCATALOG].[PRIMARYKEYFIELD]
        ,@GIFTVIEWDONORIDFIELD = [MKTGIFTRECORDSOURCE].[DONORIDFIELD]
        ,@GIFTAMOUNTFIELDNAME = [MKTGIFTRECORDSOURCE].[AMOUNTFIELD]
        ,@GIFTDATEFIELDNAME = [MKTGIFTRECORDSOURCE].[DATEFIELD]
  ,@BASECURRENCYIDFIELDNAME = [MKTGIFTRECORDSOURCE].[BASECURRENCYIDFIELD]
        ,@LOOKUPIDFIELDNAME = [MKTRECORDSOURCEFIELDMAPPINGS].[LOOKUPIDFIELD]
        ,@GIFTLOOKUPIDFIELDNAME = [MKTGIFTRECORDSOURCE].[LOOKUPIDFIELD]
    from dbo.[MKTRECORDSOURCEFIELDMAPPINGS]
    inner join dbo.[MKTGIFTRECORDSOURCE] on [MKTGIFTRECORDSOURCE].[ID] = [MKTRECORDSOURCEFIELDMAPPINGS].[ID]
    inner join dbo.[QUERYVIEWCATALOG] on [QUERYVIEWCATALOG].[ID] = [MKTGIFTRECORDSOURCE].[QUERYVIEWCATALOGID]
      where [MKTRECORDSOURCEFIELDMAPPINGS].[ID] = @RECORDSOURCEID;

      if @ISBBEC = 1
        -- go straight to the FINANCIALTRANSACTION table - the gift record source query view filters out some gifts by gift type

        -- (export does something analogous to this)

        begin
          set @GIFTVIEWNAME = 'FINANCIALTRANSACTION';
          set @GIFTVIEWPRIMARYKEYFIELD = 'ID';
          set @GIFTAMOUNTFIELDNAME = 'BASEAMOUNT';
          set @GIFTDATEFIELDNAME = 'DATE';
          set @BASECURRENCYIDFIELDNAME = 'BASECURRENCYID';
          set @GIFTLOOKUPIDFIELDNAME = 'CALCULATEDUSERDEFINEDID';
        end

    -- see if the fields exist in the table/view and clear the name if necessary --

    if exists (select * from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @PARENTTABLENAME and [COLUMN_NAME] = @FULLNAMEFIELDNAME)
      set @FULLNAMEFIELDNAME = '[P].[' + @FULLNAMEFIELDNAME + ']';
    else
      set @FULLNAMEFIELDNAME = '''''';

    if exists (select * from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @PARENTTABLENAME and [COLUMN_NAME] = @FIRSTNAMEFIELDNAME)
      set @FIRSTNAMEFIELDNAME = '[P].[' + @FIRSTNAMEFIELDNAME + ']';
    else
      set @FIRSTNAMEFIELDNAME = '''''';

    if exists (select * from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @PARENTTABLENAME and [COLUMN_NAME] = @MIDDLENAMEFIELDNAME)
      set @MIDDLENAMEFIELDNAME = '[P].[' + @MIDDLENAMEFIELDNAME + ']';
    else
      set @MIDDLENAMEFIELDNAME = '''''';

    if exists (select * from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @PARENTTABLENAME and [COLUMN_NAME] = @LASTNAMEFIELDNAME)
      set @LASTNAMEFIELDNAME = '[P].[' + @LASTNAMEFIELDNAME + ']';
    else
      set @LASTNAMEFIELDNAME = '''''';

    if exists (select * from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @PARENTTABLENAME and [COLUMN_NAME] = @TITLEFIELDNAME) 
      set @TITLEFIELDNAME = '[P].[' + @TITLEFIELDNAME + ']';
    else
      set @TITLEFIELDNAME = '''''';

    if exists (select * from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @PARENTTABLENAME and [COLUMN_NAME] = @SUFFIXFIELDNAME)
      set @SUFFIXFIELDNAME = '[P].[' + @SUFFIXFIELDNAME + ']';
    else
      set @SUFFIXFIELDNAME = '''''';

    if exists (select * from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @PARENTTABLENAME and [COLUMN_NAME] = @COUNTRYFIELDNAME)
      set @COUNTRYFIELDNAME = '[P].[' + @COUNTRYFIELDNAME + ']';
    else
      set @COUNTRYFIELDNAME = '''''';

    if exists (select * from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @PARENTTABLENAME and [COLUMN_NAME] = @ADDRESSLINE1FIELDNAME)
      set @ADDRESSLINE1FIELDNAME = '[P].[' + @ADDRESSLINE1FIELDNAME + ']';
    else
      set @ADDRESSLINE1FIELDNAME = '''''';

    if exists (select * from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @PARENTTABLENAME and [COLUMN_NAME] = @ADDRESSLINE2FIELDNAME)
      set @ADDRESSLINE2FIELDNAME = '[P].[' + @ADDRESSLINE2FIELDNAME + ']';
    else
      set @ADDRESSLINE2FIELDNAME = '''''';

    if exists (select * from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @PARENTTABLENAME and [COLUMN_NAME] = @CITYFIELDNAME)
      set @CITYFIELDNAME = '[P].[' + @CITYFIELDNAME + ']';
    else
      set @CITYFIELDNAME = '''''';

    if exists (select * from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @PARENTTABLENAME and [COLUMN_NAME] = @STATEFIELDNAME)
      set @STATEFIELDNAME = '[P].[' + @STATEFIELDNAME + ']';
  else
      set @STATEFIELDNAME = '''''';

    if exists (select * from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @PARENTTABLENAME and [COLUMN_NAME] = @POSTCODEFIELDNAME)
      set @POSTCODEFIELDNAME = '[P].[' + @POSTCODEFIELDNAME + ']';
    else
      set @POSTCODEFIELDNAME = '''''';

    if exists (select * from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @PARENTTABLENAME and [COLUMN_NAME] = @CARTFIELDNAME)
      set @CARTFIELDNAME = '[P].[' + @CARTFIELDNAME + ']';
    else
      set @CARTFIELDNAME = '''''';

    if exists (select * from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @PARENTTABLENAME and [COLUMN_NAME] = @DPCFIELDNAME)
      set @DPCFIELDNAME = '[P].[' + @DPCFIELDNAME + ']';
    else
      set @DPCFIELDNAME = '''''';

    if exists (select * from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @PARENTTABLENAME and [COLUMN_NAME] = @LOTFIELDNAME)
      set @LOTFIELDNAME = '[P].[' + @LOTFIELDNAME + ']';
    else
      set @LOTFIELDNAME = '''''';

    if exists (select * from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @PARENTTABLENAME and [COLUMN_NAME] = @PHONENUMBERFIELDNAME)
      set @PHONENUMBERFIELDNAME = '[P].[' + @PHONENUMBERFIELDNAME + ']';
    else
      set @PHONENUMBERFIELDNAME = '''''';

    if exists (select * from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @PARENTTABLENAME and [COLUMN_NAME] = @EMAILADDRESSFIELDNAME)
      set @EMAILADDRESSFIELDNAME = '[P].[' + @EMAILADDRESSFIELDNAME + ']';
    else
      set @EMAILADDRESSFIELDNAME = '''''';

    if exists (select * from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @PARENTTABLENAME and [COLUMN_NAME] = @FIRSTGIFTDATEFIELDNAME)
      set @FIRSTGIFTDATEFIELDNAME = '[P].[' + @FIRSTGIFTDATEFIELDNAME + ']';
    else
      set @FIRSTGIFTDATEFIELDNAME = '''''';

    if exists (select * from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @GIFTVIEWNAME and [COLUMN_NAME] = @GIFTAMOUNTFIELDNAME)
      set @GIFTAMOUNTFIELDNAME = '[GIFTS].[' + @GIFTAMOUNTFIELDNAME + ']';
    else
      set @GIFTAMOUNTFIELDNAME = '''''';

    if exists (select * from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @GIFTVIEWNAME and [COLUMN_NAME] = @GIFTDATEFIELDNAME)
      set @GIFTDATEFIELDNAME = '[GIFTS].[' + @GIFTDATEFIELDNAME + ']';
    else
      set @GIFTDATEFIELDNAME = '''''';

    if @ISBBEC = 1
    begin
      if exists (select * from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = 'CURRENCYSET' and [COLUMN_NAME] = @BASECURRENCYIDFIELDNAME)
        set @BASECURRENCYIDFIELDNAME = '[CURRENCYSET].[' + @BASECURRENCYIDFIELDNAME + ']';
      else
        set @BASECURRENCYIDFIELDNAME = '''''';
    end
    else
    begin
      if exists (select * from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @GIFTVIEWNAME and [COLUMN_NAME] = @BASECURRENCYIDFIELDNAME)
        set @BASECURRENCYIDFIELDNAME = '[GIFTS].[' + @BASECURRENCYIDFIELDNAME + ']';
      else
        set @BASECURRENCYIDFIELDNAME = '''''';
    end

    if exists (select * from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @GIFTVIEWNAME and [COLUMN_NAME] = @GIFTLOOKUPIDFIELDNAME)
      set @GIFTLOOKUPIDFIELDNAME = '[GIFTS].[' + @GIFTLOOKUPIDFIELDNAME + ']';
    else
      set @GIFTLOOKUPIDFIELDNAME = '''''';

    if exists (select * from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @PARENTTABLENAME and [COLUMN_NAME] = @LOOKUPIDFIELDNAME)
      set @LOOKUPIDFIELDNAME = '[P].[' + @LOOKUPIDFIELDNAME + ']';
    else
      set @LOOKUPIDFIELDNAME = '''''';

    --Build the SQL

    declare @SQL nvarchar(max);
    set @SQL = 'select top ' + cast(@TOTALROWS as nvarchar(10)) + char(13) +
               '  ' + @FULLNAMEFIELDNAME + ',' + char(13) +
               '  ' + @FIRSTNAMEFIELDNAME + ',' + char(13) +
               '  ' + @MIDDLENAMEFIELDNAME + ',' + char(13) +
               '  ' + @LASTNAMEFIELDNAME + ',' + char(13) +
               '  ' + @TITLEFIELDNAME + ',' + char(13) +
 '  ' + @SUFFIXFIELDNAME + ',' + char(13) +
               '  ' + @GIFTAMOUNTFIELDNAME + ',' + char(13) +
               '  ' + @GIFTDATEFIELDNAME + ',' + char(13) +
               '  ' + @COUNTRYFIELDNAME + ',' + char(13) +
               '  ' + @ADDRESSLINE1FIELDNAME + ',' + char(13) +
               '  ' + @ADDRESSLINE2FIELDNAME + ',' + char(13) +
               '  ' + @CITYFIELDNAME + ',' + char(13) +
               '  ' + @STATEFIELDNAME + ',' + char(13) +
               '  ' + @POSTCODEFIELDNAME + ',' + char(13) +
               '  ' + @CARTFIELDNAME + ',' + char(13) +
               '  ' + @DPCFIELDNAME + ',' + char(13) +
               '  ' + @LOTFIELDNAME + ',' + char(13) +
               '  ' + @PHONENUMBERFIELDNAME + ',' + char(13) +
               '  ' + @EMAILADDRESSFIELDNAME + ',' + char(13) +
               '  ' + @FIRSTGIFTDATEFIELDNAME + ',' + char(13) +
               '  ' + @BASECURRENCYIDFIELDNAME + ',' + char(13) +
               '  ' + @LOOKUPIDFIELDNAME + ',' + char(13) +
               '  ' + @GIFTLOOKUPIDFIELDNAME + ' [REVENUELOOKUPID],' + char(13) +
               '  [P].[' + @PARENTID + '],' + char(13) +
               '  [GIFTS].[' + @GIFTVIEWPRIMARYKEYFIELD + ']' + char(13) +
               'from dbo.[' + @GIFTVIEWNAME + '] as [GIFTS]' + char(13);

    if @ISBBEC = 1
      set @SQL = @SQL +
              'left outer join dbo.[PDACCOUNTSYSTEM] on [PDACCOUNTSYSTEM].[ID] = [GIFTS].[PDACCOUNTSYSTEMID]' + char(13) +
              'left outer join dbo.[CURRENCYSET] on [CURRENCYSET].[ID] = [PDACCOUNTSYSTEM].[CURRENCYSETID]' + char(13);

    set @SQL = @SQL +
               'inner join dbo.[' + @PARENTTABLENAME + '] as [P] on [P].[' + @PARENTID + '] = [GIFTS].[' + @GIFTVIEWDONORIDFIELD + ']' + char(13) +
               'inner join dbo.[' + @SEGMENTVIEWNAME + '] as [V] on [GIFTS].[' + @GIFTVIEWPRIMARYKEYFIELD + '] = [V].[ID]' + char(13);

    --Filter the records, if specified

    declare @TEMPFILTER nvarchar(255);
    if @FILTER is not null and len(@FILTER) > 0
      begin
        set @TEMPFILTER = '%' + @FILTER + '%';
        set @SQL = @SQL + 'where (' + @FULLNAMEFIELDNAME + ' like @FILTER' + char(13) +
                          '    or ' + @FIRSTNAMEFIELDNAME + ' like @FILTER' + char(13) +
                          '    or ' + @MIDDLENAMEFIELDNAME + ' like @FILTER' + char(13) +
                          '    or ' + @LASTNAMEFIELDNAME + ' like @FILTER' + char(13) +
                          '    or ' + @TITLEFIELDNAME + ' like @FILTER' + char(13) +
                          '    or ' + @SUFFIXFIELDNAME + ' like @FILTER' + char(13) +
                          '    or ' + @COUNTRYFIELDNAME + ' like @FILTER' + char(13) +
                          '    or ' + @ADDRESSLINE1FIELDNAME + ' like @FILTER' + char(13) +
                          '    or ' + @ADDRESSLINE2FIELDNAME + ' like @FILTER' + char(13) +
                          '    or ' + @CITYFIELDNAME + ' like @FILTER' + char(13) +
                          '    or ' + @STATEFIELDNAME + ' like @FILTER' + char(13) +
                          '    or ' + @POSTCODEFIELDNAME + ' like @FILTER' + char(13) +
                          '    or ' + @CARTFIELDNAME + ' like @FILTER' + char(13) +
                          '    or ' + @DPCFIELDNAME + ' like @FILTER' + char(13) +
                          '    or ' + @LOTFIELDNAME + ' like @FILTER' + char(13) +
                          '    or ' + @PHONENUMBERFIELDNAME + ' like @FILTER' + char(13) +
                          '    or ' + @EMAILADDRESSFIELDNAME + ' like @FILTER' + char(13) +
                          '    or ' + @FIRSTGIFTDATEFIELDNAME + ' like @FILTER' + char(13) +
                          '    or ' + @LOOKUPIDFIELDNAME + ' like @FILTER' + char(13) +
                          '    or ' + @GIFTLOOKUPIDFIELDNAME + ' like @FILTER)' + char(13);
      end

    --Do NOT order the results here, for large amounts of data it takes too long for not much benefit.


    exec sp_executesql @SQL, N'@FILTER nvarchar(255)', @FILTER = @TEMPFILTER;
  end try

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

  return 0;