UFN_CONSTITUENT_GETCONSTITUENCYLIST

CREATE FUNCTION dbo.UFN_CONSTITUENT_GETCONSTITUENCYLIST(@ID uniqueidentifier, @CURRENTAPPUSERID uniqueidentifier)
returns nvarchar(4000)
as
begin
    declare @RETVAL nvarchar(4000);

    declare @CURRENTDATEEARLIESTTIME date;
    set @CURRENTDATEEARLIESTTIME = getdate();

    select @RETVAL = dbo.UDA_BUILDLIST(distinct CONSTITUENCY) from 
    (
    --Constituencies

    select case 
            when dbo.UFN_CONSTITUENT_ISPLANNEDGIVER(@ID) = 1 then dbo.UFN_CONSTITUENCY_GETDESCRIPTION('CEE46FE7-3FBB-4DFE-97EB-BA67DD33C634') --Planned giver

            else null end as CONSTITUENCY 
    union all
    select case 
            when dbo.UFN_CONSTITUENT_ISRECOGNITION(@ID,@CURRENTAPPUSERID) = 1 then dbo.UFN_CONSTITUENCY_GETDESCRIPTION('3dfac92e-78bd-4051-abdc-02c675deb8f6')  --Recognition

            else null end
    union all
    select case 
            when dbo.UFN_CONSTITUENT_ISMEMBER(@ID,@CURRENTAPPUSERID) = 1 then dbo.UFN_CONSTITUENCY_GETDESCRIPTION('2d11326e-8f3b-4322-9797-57c1aacfa5df')  --Member

            else null end
    union all
    select case 
            when dbo.UFN_CONSTITUENT_ISBOARDMEMBER(@ID) = 1 then dbo.UFN_CONSTITUENCY_GETDESCRIPTION('F828E957-5F5E-479A-8F23-2FFD6C7C68FF') --Board member

            else null end
    union all
    select case 
            when dbo.UFN_CONSTITUENT_ISRELATION(@ID) = 1 then dbo.UFN_CONSTITUENCY_GETDESCRIPTION('8B6ECCFD-6829-4FBC-B092-58CA083F9322') --Relation only

            else null end
    union all
    select case 
            when dbo.UFN_CONSTITUENT_ISSTAFF(@ID) = 1 then dbo.UFN_CONSTITUENCY_GETDESCRIPTION('6093915E-ADE9-42BE-88AE-304731754467') --Staff

            else null end
    union all
    select case 
            when dbo.UFN_CONSTITUENT_ISDONOR(@ID) = 1 and dbo.UFN_CONSTITUENT_ISLOYALDONOR(@ID) = 0 and dbo.UFN_CONSTITUENT_ISMAJORDONOR(@ID) = 0 then dbo.UFN_CONSTITUENCY_GETDESCRIPTION('70165682-4324-46EC-9439-83FC0CC67E7F') --Donor

            else null end
    union all
    select case 
        when dbo.UFN_CONSTITUENT_ISLOYALDONOR(@ID) = 1 then dbo.UFN_CONSTITUENCY_GETDESCRIPTION('E5A0EA42-65BA-4B25-AFE2-9B709F99E72B') --Loyal donor

        else null end
    union all
    select case 
        when dbo.UFN_CONSTITUENT_ISMAJORDONOR(@ID) = 1 then dbo.UFN_CONSTITUENCY_GETDESCRIPTION('1A9BFE80-604D-4B5B-8065-E751DDF6EF39') --Major donor

        else null end
    union all
    select case 
            when dbo.UFN_CONSTITUENT_ISFUNDRAISER(@ID) = 1 then dbo.UFN_CONSTITUENCY_GETDESCRIPTION('D2DCA06A-BE6E-40B3-B95D-59A926181923') --Fundraiser

            else null end
    union all
    select case 
            when dbo.UFN_CONSTITUENT_ISPROSPECT(@ID) = 1 then dbo.UFN_CONSTITUENCY_GETDESCRIPTION('00E748FB-940D-4A7D-A133-C148B29410A8') --Major giving prospect

            else null end
    union all
    select case 
            when dbo.UFN_CONSTITUENT_ISVOLUNTEER(@ID) = 1 then dbo.UFN_CONSTITUENCY_GETDESCRIPTION('E7489703-3D63-4017-A2BC-88C092563C5D') --Volunteer

            else null end
    union all
    select case 
            when dbo.UFN_CONSTITUENT_ISCOMMUNITYMEMBER(@ID) = 1 then dbo.UFN_CONSTITUENCY_GETDESCRIPTION('55FE8E7C-2B68-44C8-B35C-818AD1944C03') --Community member

            else null end    
    union all
    select case 
        when dbo.UFN_CONSTITUENT_ISPATRON(@ID) = 1 then dbo.UFN_CONSTITUENCY_GETDESCRIPTION('A843B859-4C6B-445B-97F3-179582E270A5') --Patron

        else null end                                                        
    union all
    select
         dbo.UFN_CONSTITUENT_GETALUMNUSSTATUSTEXT(@ID)
    union all
    select case 
        when dbo.UFN_CONSTITUENT_ISREGISTRANT(@ID) = 1 then dbo.UFN_CONSTITUENCY_GETDESCRIPTION('C49D4B46-72A7-4206-91AA-BEABA2323E3C') --Event registrant

        else null end
    union all
    select case 
        when dbo.UFN_CONSTITUENT_ISVENDOR(@ID) = 1 then dbo.UFN_CONSTITUENCY_GETDESCRIPTION('5435C96D-8617-46C3-9A62-5AFF08451A53') --Vendor

        else null end
    union all
    select case 
        when exists(select ID from dbo.MATCHFINDERCONSTITUENT where ID = @ID and MATCHFINDERRECORDID <> 0) then dbo.UFN_CONSTITUENCY_GETDESCRIPTION('2D04A9C5-27D0-4646-BF0F-6826E4C12632') --Matching gift organization

        else null end
    union all
    select case 
        when dbo.UFN_CONSTITUENT_ISBANK(@ID) = 1 then dbo.UFN_CONSTITUENCY_GETDESCRIPTION('171AB3CD-C4E1-4825-B693-10F524A7A594') --Bank

        else null end
    union all
    select case 
        when dbo.UFN_CONSTITUENT_ISCOMMITTEE(@ID) = 1 then dbo.UFN_CONSTITUENCY_GETDESCRIPTION('AC9DB5A4-14E0-416A-9FB2-04038AC66799') --Committee

        else null end
    union all
    select case 
        when dbo.UFN_CONSTITUENT_ISCOMMITTEEMEMBER(@ID) = 1 then dbo.UFN_CONSTITUENCY_GETDESCRIPTION('093A3D4F-2974-447F-AD92-870EB4A04593') --Committee member

        else null end
    union all
    select distinct  
        CC.DESCRIPTION
    from dbo.CONSTITUENCY C  
    inner join dbo.CONSTITUENCYCODE CC on C.CONSTITUENCYCODEID = CC.ID  
    where  
        (C.CONSTITUENTID = @ID) and  
        (C.DATEFROM <= @CURRENTDATEEARLIESTTIME or C.DATEFROM is null) and  
        (C.DATETO >= @CURRENTDATEEARLIESTTIME or C.DATETO is null)  
    ) a

    return @RETVAL
end