UFN_MKTRECORDSOURCE_GETREQUIREDFIELDSSET

CREATE function dbo.[UFN_MKTRECORDSOURCE_GETREQUIREDFIELDSSET]
(
  @RECORDSOURCEID uniqueidentifier,
  @FIELDSTYPE tinyint = 0 -- 0 = All, 1 = Person, 2 = Gift, 3 = Appeal, 4 = Membership, 5 = Sponsorship

)
returns bit
as begin

  declare @ISBBEC bit;
  declare @FIELDSMAPPED bit;
  declare @PARENTTABLENAME nvarchar(255);
  declare @PEOPLEFIELDSMAPPED bit;
  declare @GIFTFIELDSMAPPED bit;
  declare @APPEALFIELDSMAPPED bit;
  declare @MEMBERSHIPFIELDSMAPPED bit;
  declare @SPONSORSHIPFIELDSMAPPED bit;
  declare @QUERYVIEWCATALOGID uniqueidentifier;

  set @ISBBEC = (case when dbo.[UFN_INSTALLEDPRODUCTS_PRODUCTIS]('BB9873D7-F1ED-430A-8AB4-F09F47056538') = 0 then 1 else 0 end);

  -- PEOPLE FIELDS --

  set @PEOPLEFIELDSMAPPED = 1;

  -- SEE IF WE ARE DEALING WITH CONSOLIDATED --

  select
    @QUERYVIEWCATALOGID = [ID]
  from dbo.[MKTCONSOLIDATEDQUERYVIEWSPEC]
  where [CONSOLIDATEDQUERYVIEWCATALOGID] = @RECORDSOURCEID;

  if @QUERYVIEWCATALOGID is null set @QUERYVIEWCATALOGID = @RECORDSOURCEID;

  -- GIFT FIELDS --

  select
    @PARENTTABLENAME = [Q].[OBJECTNAME]
  from dbo.[QUERYVIEWCATALOG] as [Q]
  inner join dbo.[MKTGIFTRECORDSOURCE] as [M] on [M].[QUERYVIEWCATALOGID] = [Q].[ID]
  where [M].[ID] = @QUERYVIEWCATALOGID;

  select
    @GIFTFIELDSMAPPED =
      (case when
        coalesce((select [COLUMN_NAME] from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @PARENTTABLENAME and [COLUMN_NAME] = [APPEALSYSTEMIDFIELD]), '') = '' or
        coalesce((select [COLUMN_NAME] from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @PARENTTABLENAME and [COLUMN_NAME] = [APPEALIDFIELD]), '') = '' or
        coalesce((select [COLUMN_NAME] from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @PARENTTABLENAME and [COLUMN_NAME] = [APPEALDESCRIPTIONFIELD]), '') = '' or
        coalesce((select [COLUMN_NAME] from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @PARENTTABLENAME and [COLUMN_NAME] = [AMOUNTFIELD]), '') = '' or
        coalesce((select [COLUMN_NAME] from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @PARENTTABLENAME and [COLUMN_NAME] = [DONORIDFIELD]), '') = '' or
        coalesce((select [COLUMN_NAME] from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @PARENTTABLENAME and [COLUMN_NAME] = [SOURCECODEFIELD]), '') = '' or
        coalesce((select [COLUMN_NAME] from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @PARENTTABLENAME and [COLUMN_NAME] = [FINDERNUMBERFIELD]), '') = '' or
        coalesce((select [COLUMN_NAME] from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @PARENTTABLENAME and [COLUMN_NAME] = [MAILINGIDFIELD]), '') = '' then 0 else 1 end)
  from
    dbo.[MKTGIFTRECORDSOURCE]
  where
    [MKTGIFTRECORDSOURCE].[ID] = @QUERYVIEWCATALOGID;

  -- APPEAL FIELDS --

  select
    @PARENTTABLENAME = [Q].[OBJECTNAME]
  from dbo.[QUERYVIEWCATALOG] as [Q]
  inner join dbo.[MKTAPPEALRECORDSOURCE] as [M] on [M].[QUERYVIEWCATALOGID] = [Q].[ID]
  where [M].[ID] = @QUERYVIEWCATALOGID;

  select @APPEALFIELDSMAPPED =
    (case when
      coalesce((select [COLUMN_NAME] from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @PARENTTABLENAME and [COLUMN_NAME] = [APPEALIDFIELD]), '') = '' or
      coalesce((select [COLUMN_NAME] from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @PARENTTABLENAME and [COLUMN_NAME] = [DESCRIPTIONFIELD]), '') = '' then 0 else 1 end)
  from
    [dbo].[MKTAPPEALRECORDSOURCE]
  where
    [MKTAPPEALRECORDSOURCE].[ID] = @QUERYVIEWCATALOGID;

  -- MEMBERSHIP FIELDS --

  if @ISBBEC = 1 
    begin
      select
        @PARENTTABLENAME = [Q].[OBJECTNAME]
      from dbo.[QUERYVIEWCATALOG] as [Q]
      inner join dbo.[MKTMEMBERSHIPRECORDSOURCE] as [M] on [M].[QUERYVIEWCATALOGID] = [Q].[ID]
      where [M].[ID] = @QUERYVIEWCATALOGID;

      select @MEMBERSHIPFIELDSMAPPED =
        (case when
          coalesce((select [COLUMN_NAME] from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @PARENTTABLENAME and [COLUMN_NAME] = [MEMBERSHIPSYSTEMIDFIELD]), '') = '' or
          coalesce((select [COLUMN_NAME] from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @PARENTTABLENAME and [COLUMN_NAME] = [MEMBERIDFIELD]), '') = '' or
          coalesce((select [COLUMN_NAME] from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @PARENTTABLENAME and [COLUMN_NAME] = [GIVENBYIDFIELD]), '') = '' or
          coalesce((select [COLUMN_NAME] from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @PARENTTABLENAME and [COLUMN_NAME] = [EXPIRATIONDATEFIELD]), '') = '' or
          coalesce((select [COLUMN_NAME] from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @PARENTTABLENAME and [COLUMN_NAME] = [ISGIFTFIELD]), '') = '' or
          coalesce((select [COLUMN_NAME] from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @PARENTTABLENAME and [COLUMN_NAME] = [JOINDATEFIELD]), '') = '' or
          coalesce((select [COLUMN_NAME] from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @PARENTTABLENAME and [COLUMN_NAME] = [LASTRENEWEDONDATEFIELD]), '') = '' or
          coalesce((select [COLUMN_NAME] from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @PARENTTABLENAME and [COLUMN_NAME] = [LEVELFIELD]), '') = '' or
          coalesce((select [COLUMN_NAME] from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @PARENTTABLENAME and [COLUMN_NAME] = [PROGRAMFIELD]), '') = '' or
          coalesce((select [COLUMN_NAME] from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @PARENTTABLENAME and [COLUMN_NAME] = [SENDRENEWALFIELD]), '') = '' or
          coalesce((select [COLUMN_NAME] from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @PARENTTABLENAME and [COLUMN_NAME] = [STATUSFIELD]), '') = '' or
          coalesce((select [COLUMN_NAME] from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @PARENTTABLENAME and [COLUMN_NAME] = [TERMFIELD]), '') = '' or
          coalesce((select [COLUMN_NAME] from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @PARENTTABLENAME and [COLUMN_NAME] = [TYPEFIELD]), '') = '' then 0 else 1 end)
      from
        [dbo].[MKTMEMBERSHIPRECORDSOURCE]
      where
        [MKTMEMBERSHIPRECORDSOURCE].[ID] = @QUERYVIEWCATALOGID;
    end;

   -- SPONSORSHIP FIELDS --

  if @ISBBEC = 1 
    begin
      select
        @PARENTTABLENAME = [Q].[OBJECTNAME]
      from dbo.[QUERYVIEWCATALOG] as [Q]
      inner join dbo.[MKTSPONSORSHIPRECORDSOURCE] as [M] on [M].[QUERYVIEWCATALOGID] = [Q].[ID]
      where [M].[ID] = @QUERYVIEWCATALOGID;

      select @SPONSORSHIPFIELDSMAPPED =
        (case when
          coalesce((select [COLUMN_NAME] from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @PARENTTABLENAME and [COLUMN_NAME] = [SPONSORSHIPSYSTEMIDFIELD]), '') = '' or
          coalesce((select [COLUMN_NAME] from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @PARENTTABLENAME and [COLUMN_NAME] = [SPONSORIDFIELD]), '') = '' or
          coalesce((select [COLUMN_NAME] from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @PARENTTABLENAME and [COLUMN_NAME] = [ACTIONFIELD]), '') = '' or
          coalesce((select [COLUMN_NAME] from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @PARENTTABLENAME and [COLUMN_NAME] = [REASONFIELD]), '') = '' or
          coalesce((select [COLUMN_NAME] from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @PARENTTABLENAME and [COLUMN_NAME] = [GIFTSPONSORSHIPFIELD]), '') = '' or
          coalesce((select [COLUMN_NAME] from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @PARENTTABLENAME and [COLUMN_NAME] = [SPONSORSHIPOPPORTUNITYIDCHILDFIELD]), '') = '' or
          coalesce((select [COLUMN_NAME] from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @PARENTTABLENAME and [COLUMN_NAME] = [SPONSORSHIPOPPORTUNITYIDPROJECTFIELD]), '') = '' or
          coalesce((select [COLUMN_NAME] from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @PARENTTABLENAME and [COLUMN_NAME] = [SPONSORSHIPCHILDPROGRAMIDFIELD]), '') = '' or
          coalesce((select [COLUMN_NAME] from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @PARENTTABLENAME and [COLUMN_NAME] = [SPONSORSHIPPROJECTPROGRAMIDFIELD]), '') = '' or
          coalesce((select [COLUMN_NAME] from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @PARENTTABLENAME and [COLUMN_NAME] = [SPONSORSHIPLOCATIONIDFIELD]), '') = '' then 0 else 1 end)
      from
        [dbo].[MKTSPONSORSHIPRECORDSOURCE]
      where
        [MKTSPONSORSHIPRECORDSOURCE].[ID] = @QUERYVIEWCATALOGID;
    end;  

  -- RETURN VALUE --

  if @FIELDSTYPE = 0
    set @FIELDSMAPPED = (case when @PEOPLEFIELDSMAPPED = 0 or @GIFTFIELDSMAPPED = 0 or @APPEALFIELDSMAPPED = 0 or (@ISBBEC = 1 and @MEMBERSHIPFIELDSMAPPED = 0) then 0 else 1 end)
  else if @FIELDSTYPE = 1
    set @FIELDSMAPPED = @PEOPLEFIELDSMAPPED
  else if @FIELDSTYPE = 2
    set @FIELDSMAPPED = @GIFTFIELDSMAPPED
  else if @FIELDSTYPE = 3
    set @FIELDSMAPPED = @APPEALFIELDSMAPPED
  else if @FIELDSTYPE = 4
    set @FIELDSMAPPED = @MEMBERSHIPFIELDSMAPPED
  else if @FIELDSTYPE = 5
    set @FIELDSMAPPED = @SPONSORSHIPFIELDSMAPPED  

  return @FIELDSMAPPED;
end