UFN_SMARTYQUERY_CONSTITUENTTRIBUTE

Return

Return Type
table
Parameters

Parameter	Parameter Type	Mode
@TRIBUTETYPECODEID	uniqueidentifier	IN
@DESIGNATIONID	uniqueidentifier	IN
@INCLUDEINACTIVE	bit	IN
@TRIBUTELETTERCODEID	uniqueidentifier	IN
@ASSOCIATIONTYPE	tinyint	IN
@SELECTIONID	uniqueidentifier	IN
@CURRENTAPPUSERID	uniqueidentifier	IN
@MAXROWS	int	IN
Definition

Copy


create function dbo.UFN_SMARTYQUERY_CONSTITUENTTRIBUTE
(
  @TRIBUTETYPECODEID uniqueidentifier = null,
  @DESIGNATIONID uniqueidentifier = null,
  @INCLUDEINACTIVE bit = null,
  @TRIBUTELETTERCODEID uniqueidentifier = null,
  @ASSOCIATIONTYPE tinyint = null,
  @SELECTIONID uniqueidentifier = null,
  @CURRENTAPPUSERID uniqueidentifier = null,
  @MAXROWS int = 500    
)
returns @T table 
(
  ID uniqueidentifier not null, 
  NAME nvarchar(150),
  ADDRESSBLOCK nvarchar(150),
  CITY nvarchar(50),
  STATE nvarchar(100),
  POSTCODE nvarchar(12),
  LOOKUPID nvarchar(100),
  ASSOCIATION nvarchar(30),
  TRIBUTETYPE nvarchar(100),
  TRIBUTETEXT nvarchar(255),
  DEFAULTDESIGNATION nvarchar(512),
  ISACTIVE nvarchar(3),
  TRIBUTELETTER nvarchar(100)
)
as
begin                     
  declare @ISADMIN bit;
  set @ISADMIN = dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID);

  if @SELECTIONID is null
  begin 
    if @ASSOCIATIONTYPE = 0
    begin
      insert into @T
        select top (@MAXROWS)
          C.ID,
          C.NAME,
          A.ADDRESSBLOCK,
          A.CITY,
          STATE.DESCRIPTION,
          A.POSTCODE,
          C.LOOKUPID,
          'Tributee' as ASSOCIATION,
          TT.DESCRIPTION as TRIBUTETYPE,
          TRIBUTE.TRIBUTETEXT,
          dbo.UFN_DESIGNATION_BUILDNAME(TRIBUTE.DESIGNATIONID) as DEFAULTDESIGNATION,
          case TRIBUTE.ISACTIVE when 1 then 'Yes' else 'No' end as ISACTIVE,
          null
        from
          dbo.CONSTITUENT AS C
          inner join dbo.TRIBUTE on TRIBUTE.TRIBUTEEID = C.ID
          inner join dbo.TRIBUTETYPECODE TT on TRIBUTE.TRIBUTETYPECODEID = TT.ID
          left join dbo.ADDRESS A on C.ID = A.CONSTITUENTID and A.ISPRIMARY = 1
          left join dbo.STATE on STATE.ID = A.STATEID
        where
          (@TRIBUTETYPECODEID is null or TRIBUTE.TRIBUTETYPECODEID = @TRIBUTETYPECODEID) and
          (@DESIGNATIONID is null or TRIBUTE.DESIGNATIONID = @DESIGNATIONID) and
          (@INCLUDEINACTIVE = 1 or TRIBUTE.ISACTIVE = 1) and    
          (@ISADMIN = 1 or
            (
              (dbo.UFN_SECURITY_APPUSER_GRANTED_SMARTQUERY_FORCONSTIT(@CURRENTAPPUSERID, 'bec1e7ec-e8f9-4c62-a5e9-75dc19cc69f3', C.ID) = 1)
              and
              (dbo.UFN_SECURITY_APPUSER_GRANTED_SMARTQUERY_FORCONSTIT_BYSITE(@CURRENTAPPUSERID, 'bec1e7ec-e8f9-4c62-a5e9-75dc19cc69f3', C.ID) = 1)
            )
          )
        order by 
          C.NAME

    end
    else if @ASSOCIATIONTYPE = 1
    begin
      insert into @T
        select top (@MAXROWS)
          C.ID,
          C.NAME,
          A.ADDRESSBLOCK,
          A.CITY,
          STATE.DESCRIPTION,
          A.POSTCODE,
          C.LOOKUPID,
          'Acknowledgee' as ASSOCIATION,
          TT.DESCRIPTION as TRIBUTETYPE,
          TRIBUTE.TRIBUTETEXT,
          dbo.UFN_DESIGNATION_BUILDNAME(TRIBUTE.DESIGNATIONID) as DEFAULTDESIGNATION,
          case TRIBUTE.ISACTIVE when 1 then 'Yes' else 'No' end as ISACTIVE,
          TRIBUTELETTERCODE.NAME
        from
          dbo.CONSTITUENT AS C
          inner join dbo.TRIBUTEACKNOWLEDGEE TA on TA.CONSTITUENTID = C.ID
          inner join dbo.TRIBUTE on TA.TRIBUTEID = TRIBUTE.ID
          inner join dbo.TRIBUTETYPECODE TT on TRIBUTE.TRIBUTETYPECODEID = TT.ID
          left join dbo.TRIBUTELETTERCODE on TA.TRIBUTELETTERCODEID = TRIBUTELETTERCODE.ID
          left join dbo.ADDRESS A on C.ID = A.CONSTITUENTID and A.ISPRIMARY = 1
          left join dbo.STATE on STATE.ID = A.STATEID
        where
          (@TRIBUTETYPECODEID is null or TRIBUTE.TRIBUTETYPECODEID = @TRIBUTETYPECODEID) and
          (@DESIGNATIONID is null or TRIBUTE.DESIGNATIONID = @DESIGNATIONID) and
          (@TRIBUTELETTERCODEID is null or TA.TRIBUTELETTERCODEID = @TRIBUTELETTERCODEID) and
          (@INCLUDEINACTIVE = 1 or TRIBUTE.ISACTIVE = 1) and
          (@ISADMIN = 1 or
            (
              (dbo.UFN_SECURITY_APPUSER_GRANTED_SMARTQUERY_FORCONSTIT(@CURRENTAPPUSERID, 'bec1e7ec-e8f9-4c62-a5e9-75dc19cc69f3', C.ID) = 1)
              and
              (dbo.UFN_SECURITY_APPUSER_GRANTED_SMARTQUERY_FORCONSTIT_BYSITE(@CURRENTAPPUSERID, 'bec1e7ec-e8f9-4c62-a5e9-75dc19cc69f3', C.ID) = 1)
            )
          )
        order by 
          C.NAME
    end
    else
    begin

      with TRIBUTES_CTE(CONSTITUENTID,NAME,LOOKUPID,ASSOCIATION,TRIBUTETYPE,TRIBUTETEXT,DEFAULTDESIGNATION,ISACTIVE,TRIBUTELETTER) as (
        select top (@MAXROWS)
          C.ID as CONSTITUENTID,
          C.NAME,
          C.LOOKUPID,
          'Tributee' as ASSOCIATION,
          TT.DESCRIPTION as TRIBUTETYPE,
          TRIBUTE.TRIBUTETEXT,
          dbo.UFN_DESIGNATION_BUILDNAME(TRIBUTE.DESIGNATIONID) as DEFAULTDESIGNATION,
          case TRIBUTE.ISACTIVE when 1 then 'Yes' else 'No' end as ISACTIVE,
          null
        from
          dbo.CONSTITUENT AS C
          inner join dbo.TRIBUTE on TRIBUTE.TRIBUTEEID = C.ID
          inner join dbo.TRIBUTETYPECODE TT on TRIBUTE.TRIBUTETYPECODEID = TT.ID
        where
          (@TRIBUTETYPECODEID is null or TRIBUTE.TRIBUTETYPECODEID = @TRIBUTETYPECODEID) and
          (@DESIGNATIONID is null or TRIBUTE.DESIGNATIONID = @DESIGNATIONID) and
          (@INCLUDEINACTIVE = 1 or TRIBUTE.ISACTIVE = 1) and
          (@ISADMIN = 1 or
            (
              (dbo.UFN_SECURITY_APPUSER_GRANTED_SMARTQUERY_FORCONSTIT(@CURRENTAPPUSERID, 'bec1e7ec-e8f9-4c62-a5e9-75dc19cc69f3', C.ID) = 1)
              and
              (dbo.UFN_SECURITY_APPUSER_GRANTED_SMARTQUERY_FORCONSTIT_BYSITE(@CURRENTAPPUSERID, 'bec1e7ec-e8f9-4c62-a5e9-75dc19cc69f3', C.ID) = 1)
            )
          )                                
        union all                                
        select top (@MAXROWS)
          C.ID as CONSTITUENTID,
          C.NAME,
          C.LOOKUPID,
          'Acknowledgee' as ASSOCIATION,
          TT.DESCRIPTION as TRIBUTETYPE,
          TRIBUTE.TRIBUTETEXT,
          dbo.UFN_DESIGNATION_BUILDNAME(TRIBUTE.DESIGNATIONID) as DEFAULTDESIGNATION,
          case TRIBUTE.ISACTIVE when 1 then 'Yes' else 'No' end as ISACTIVE,
          TRIBUTELETTERCODE.NAME as TRIBUTELETTER
        from
          dbo.CONSTITUENT AS C
          inner join dbo.TRIBUTEACKNOWLEDGEE TA on TA.CONSTITUENTID = C.ID
          inner join dbo.TRIBUTE on TA.TRIBUTEID = TRIBUTE.ID
          inner join dbo.TRIBUTETYPECODE TT on TRIBUTE.TRIBUTETYPECODEID = TT.ID
          left join dbo.TRIBUTELETTERCODE on TA.TRIBUTELETTERCODEID = TRIBUTELETTERCODE.ID
        where
          (@TRIBUTETYPECODEID is null or TRIBUTE.TRIBUTETYPECODEID = @TRIBUTETYPECODEID) and
          (@DESIGNATIONID is null or TRIBUTE.DESIGNATIONID = @DESIGNATIONID) and
          (@TRIBUTELETTERCODEID is null or TA.TRIBUTELETTERCODEID = @TRIBUTELETTERCODEID) and
          (@INCLUDEINACTIVE = 1 or TRIBUTE.ISACTIVE = 1) and
          (@ISADMIN = 1 or
            (
              (dbo.UFN_SECURITY_APPUSER_GRANTED_SMARTQUERY_FORCONSTIT(@CURRENTAPPUSERID, 'bec1e7ec-e8f9-4c62-a5e9-75dc19cc69f3', C.ID) = 1)
              and
              (dbo.UFN_SECURITY_APPUSER_GRANTED_SMARTQUERY_FORCONSTIT_BYSITE(@CURRENTAPPUSERID, 'bec1e7ec-e8f9-4c62-a5e9-75dc19cc69f3', C.ID) = 1)
            )
          )
      )                                
      insert into @T
        select top (@MAXROWS)
          CTE.CONSTITUENTID,
          CTE.NAME,
          A.ADDRESSBLOCK,
          A.CITY,
          STATE.DESCRIPTION,
          A.POSTCODE,
          CTE.LOOKUPID,
          CTE.ASSOCIATION,
          CTE.TRIBUTETYPE,
          CTE.TRIBUTETEXT,
          CTE.DEFAULTDESIGNATION,
          CTE.ISACTIVE,
          CTE.TRIBUTELETTER
        from
          TRIBUTES_CTE CTE
          left join dbo.ADDRESS A on CTE.CONSTITUENTID = A.CONSTITUENTID and A.ISPRIMARY = 1
          left join dbo.STATE on STATE.ID = A.STATEID
        order by
          CTE.NAME                                
    end
  end

  else -- @SELECTION is not null

  begin
    if @ASSOCIATIONTYPE = 0
    begin
      insert into @T
        select distinct top (@MAXROWS)
          C.ID,
          C.NAME,
          A.ADDRESSBLOCK,
          A.CITY,
          STATE.DESCRIPTION,
          A.POSTCODE,
          C.LOOKUPID,
          'Tributee' as ASSOCIATION,
          TT.DESCRIPTION as TRIBUTETYPE,
          TRIBUTE.TRIBUTETEXT,
          dbo.UFN_DESIGNATION_BUILDNAME(TRIBUTE.DESIGNATIONID) as DEFAULTDESIGNATION,
          case TRIBUTE.ISACTIVE when 1 then 'Yes' else 'No' end as ISACTIVE,
          null
        from
          dbo.CONSTITUENT AS C
          inner join dbo.UFN_IDSETREADER_GETRESULTS_GUID(@SELECTIONID) SELECTION on C.ID = SELECTION.ID and @SELECTIONID is not null
          inner join dbo.TRIBUTE on TRIBUTE.TRIBUTEEID = C.ID
          inner join dbo.TRIBUTETYPECODE TT on TRIBUTE.TRIBUTETYPECODEID = TT.ID
          left join dbo.ADDRESS A on C.ID = A.CONSTITUENTID and A.ISPRIMARY = 1
          left join dbo.STATE on STATE.ID = A.STATEID
        where
          (@TRIBUTETYPECODEID is null or TRIBUTE.TRIBUTETYPECODEID = @TRIBUTETYPECODEID) and
          (@DESIGNATIONID is null or TRIBUTE.DESIGNATIONID = @DESIGNATIONID) and
          (@INCLUDEINACTIVE = 1 or TRIBUTE.ISACTIVE = 1) and
          (@ISADMIN = 1 or
            (
              (dbo.UFN_SECURITY_APPUSER_GRANTED_SMARTQUERY_FORCONSTIT(@CURRENTAPPUSERID, 'bec1e7ec-e8f9-4c62-a5e9-75dc19cc69f3', C.ID) = 1)
              and
              (dbo.UFN_SECURITY_APPUSER_GRANTED_SMARTQUERY_FORCONSTIT_BYSITE(@CURRENTAPPUSERID, 'bec1e7ec-e8f9-4c62-a5e9-75dc19cc69f3', C.ID) = 1)
            )
          )
        order by 
          C.NAME                                    
    end
    else if @ASSOCIATIONTYPE = 1
    begin
      insert into @T
        select distinct top (@MAXROWS)
          C.ID,
          C.NAME,
          A.ADDRESSBLOCK,
          A.CITY,
          STATE.DESCRIPTION,
          A.POSTCODE,
          C.LOOKUPID,
          'Acknowledgee' as ASSOCIATION,
          TT.DESCRIPTION as TRIBUTETYPE,
          TRIBUTE.TRIBUTETEXT,
          dbo.UFN_DESIGNATION_BUILDNAME(TRIBUTE.DESIGNATIONID) as DEFAULTDESIGNATION,
          case TRIBUTE.ISACTIVE when 1 then 'Yes' else 'No' end as ISACTIVE,
          TRIBUTELETTERCODE.NAME
        from
          dbo.CONSTITUENT AS C
          inner join dbo.UFN_IDSETREADER_GETRESULTS_GUID(@SELECTIONID) SELECTION on C.ID = SELECTION.ID and @SELECTIONID is not null
          inner join dbo.TRIBUTEACKNOWLEDGEE TA on TA.CONSTITUENTID = C.ID
          inner join dbo.TRIBUTE on TA.TRIBUTEID = TRIBUTE.ID
          inner join dbo.TRIBUTETYPECODE TT on TRIBUTE.TRIBUTETYPECODEID = TT.ID
          left join dbo.TRIBUTELETTERCODE on TA.TRIBUTELETTERCODEID = TRIBUTELETTERCODE.ID
          left join dbo.ADDRESS A on C.ID = A.CONSTITUENTID and A.ISPRIMARY = 1
          left join dbo.STATE on STATE.ID = A.STATEID
        where
          (@TRIBUTETYPECODEID is null or TRIBUTE.TRIBUTETYPECODEID = @TRIBUTETYPECODEID) and
          (@DESIGNATIONID is null or TRIBUTE.DESIGNATIONID = @DESIGNATIONID) and
          (@TRIBUTELETTERCODEID is null or TA.TRIBUTELETTERCODEID = @TRIBUTELETTERCODEID) and
          (@INCLUDEINACTIVE = 1 or TRIBUTE.ISACTIVE = 1) and
          (@ISADMIN = 1 or
            (
              (dbo.UFN_SECURITY_APPUSER_GRANTED_SMARTQUERY_FORCONSTIT(@CURRENTAPPUSERID, 'bec1e7ec-e8f9-4c62-a5e9-75dc19cc69f3', C.ID) = 1)
              and
              (dbo.UFN_SECURITY_APPUSER_GRANTED_SMARTQUERY_FORCONSTIT_BYSITE(@CURRENTAPPUSERID, 'bec1e7ec-e8f9-4c62-a5e9-75dc19cc69f3', C.ID) = 1)
            )
          )
        order 
          by C.NAME
    end
    else
    begin                        
      with TRIBUTES_CTE(CONSTITUENTID,NAME,LOOKUPID,ASSOCIATION,TRIBUTETYPE,TRIBUTETEXT,DEFAULTDESIGNATION,ISACTIVE,TRIBUTELETTER) as (
        select top (@MAXROWS)
          C.ID as CONSTITUENTID,
          C.NAME,
          C.LOOKUPID,
          'Tributee' as ASSOCIATION,
          TT.DESCRIPTION as TRIBUTETYPE,
          TRIBUTE.TRIBUTETEXT,
          dbo.UFN_DESIGNATION_BUILDNAME(TRIBUTE.DESIGNATIONID) as DEFAULTDESIGNATION,
          case TRIBUTE.ISACTIVE when 1 then 'Yes' else 'No' end as ISACTIVE,
          null
        from
          dbo.CONSTITUENT AS C
          inner join dbo.TRIBUTE on TRIBUTE.TRIBUTEEID = C.ID
          inner join dbo.TRIBUTETYPECODE TT on TRIBUTE.TRIBUTETYPECODEID = TT.ID
        where
          (@TRIBUTETYPECODEID is null or TRIBUTE.TRIBUTETYPECODEID = @TRIBUTETYPECODEID) and
          (@DESIGNATIONID is null or TRIBUTE.DESIGNATIONID = @DESIGNATIONID) and
          (@INCLUDEINACTIVE = 1 or TRIBUTE.ISACTIVE = 1) and
          (@ISADMIN = 1 or
            (
              (dbo.UFN_SECURITY_APPUSER_GRANTED_SMARTQUERY_FORCONSTIT(@CURRENTAPPUSERID, 'bec1e7ec-e8f9-4c62-a5e9-75dc19cc69f3', C.ID) = 1)
              and
              (dbo.UFN_SECURITY_APPUSER_GRANTED_SMARTQUERY_FORCONSTIT_BYSITE(@CURRENTAPPUSERID, 'bec1e7ec-e8f9-4c62-a5e9-75dc19cc69f3', C.ID) = 1)
            )
          )                                    
        union all

        select top (@MAXROWS)
          C.ID as CONSTITUENTID,
          C.NAME,
          C.LOOKUPID,
          'Acknowledgee' as ASSOCIATION,
          TT.DESCRIPTION as TRIBUTETYPE,
          TRIBUTE.TRIBUTETEXT,
          dbo.UFN_DESIGNATION_BUILDNAME(TRIBUTE.DESIGNATIONID) as DEFAULTDESIGNATION,
          case TRIBUTE.ISACTIVE when 1 then 'Yes' else 'No' end as ISACTIVE,
          TRIBUTELETTERCODE.NAME
        from
          dbo.CONSTITUENT AS C
          inner join dbo.TRIBUTEACKNOWLEDGEE TA on TA.CONSTITUENTID = C.ID
          inner join dbo.TRIBUTE on TA.TRIBUTEID = TRIBUTE.ID
          inner join dbo.TRIBUTETYPECODE TT on TRIBUTE.TRIBUTETYPECODEID = TT.ID
          left join dbo.TRIBUTELETTERCODE on TA.TRIBUTELETTERCODEID = TRIBUTELETTERCODE.ID
        where
          (@TRIBUTETYPECODEID is null or TRIBUTE.TRIBUTETYPECODEID = @TRIBUTETYPECODEID) and
          (@DESIGNATIONID is null or TRIBUTE.DESIGNATIONID = @DESIGNATIONID) and
          (@TRIBUTELETTERCODEID is null or TA.TRIBUTELETTERCODEID = @TRIBUTELETTERCODEID) and
          (@INCLUDEINACTIVE = 1 or TRIBUTE.ISACTIVE = 1) and
          (@ISADMIN = 1 or
            (
              (dbo.UFN_SECURITY_APPUSER_GRANTED_SMARTQUERY_FORCONSTIT(@CURRENTAPPUSERID, 'bec1e7ec-e8f9-4c62-a5e9-75dc19cc69f3', C.ID) = 1)
              and
              (dbo.UFN_SECURITY_APPUSER_GRANTED_SMARTQUERY_FORCONSTIT_BYSITE(@CURRENTAPPUSERID, 'bec1e7ec-e8f9-4c62-a5e9-75dc19cc69f3', C.ID) = 1)
            )
          )                                    
      )                                
      insert into @T
        select distinct top (@MAXROWS)
          CTE.CONSTITUENTID,
          CTE.NAME,
          A.ADDRESSBLOCK,
          A.CITY,
          STATE.DESCRIPTION,
          A.POSTCODE,
          CTE.LOOKUPID,
          CTE.ASSOCIATION,
          CTE.TRIBUTETYPE,
          CTE.TRIBUTETEXT,
          CTE.DEFAULTDESIGNATION,
          CTE.ISACTIVE,
          CTE.TRIBUTELETTER
        from
          TRIBUTES_CTE CTE
          inner join dbo.UFN_IDSETREADER_GETRESULTS_GUID(@SELECTIONID) SELECTION on CTE.CONSTITUENTID = SELECTION.ID and @SELECTIONID is not null
          left join dbo.ADDRESS A on CTE.CONSTITUENTID = A.CONSTITUENTID and A.ISPRIMARY = 1
          left join dbo.STATE on STATE.ID = A.STATEID
        order by
          CTE.NAME                                
    end
  end

  return;
end;