UFN_CONSTITUENT_GETCONSTITUENCIES_SPECIFIED

CREATE function dbo.UFN_CONSTITUENT_GETCONSTITUENCIES_SPECIFIED(@CONSTITUENCIES xml, @CURRENTAPPUSERID uniqueidentifier)
returns @RETURN_TABLE table (
    CONSTITUENTID uniqueidentifier,
    CONSTITUENCYID uniqueidentifier
)
as
    begin
        --declare @RETURN_TABLE table (CONSTITUENTID uniqueidentifier, CONSTITUENCYID uniqueidentifier);

        declare @CONSTITUENCIES_TABLE as table(ID uniqueidentifier);

        insert into @CONSTITUENCIES_TABLE (ID)
            select
                T.c.value('CONSTITUENCYID[1]', 'uniqueidentifier')
            from
                @CONSTITUENCIES.nodes('/CONSTITUENCIES/ITEM') T(c)

        -- User defined constituencies

        insert into @RETURN_TABLE 
            select
                CONSTITUENTID,
                CONSTITUENCYCODEID 
            from
                dbo.CONSTITUENCY
            where
                CONSTITUENCYCODEID in (select * from @CONSTITUENCIES_TABLE)

        -- Alumnus

        if exists (select ID from @CONSTITUENCIES_TABLE where ID = '46EC3424-BA54-4431-A7DC-C6CEBB3B4592')
            insert into @RETURN_TABLE
                select
                    ID,
                    '46EC3424-BA54-4431-A7DC-C6CEBB3B4592'
                from
                    dbo.CONSTITUENT 
                where
                    dbo.UFN_CONSTITUENT_ISALUMNUS(ID) = 1

        -- Board member

        if exists (select ID from @CONSTITUENCIES_TABLE where ID = 'F828E957-5F5E-479A-8F23-2FFD6C7C68FF')
            insert into @RETURN_TABLE 
                select
                    ID,
                    'F828E957-5F5E-479A-8F23-2FFD6C7C68FF'
                from
                    dbo.CONSTITUENT 
                where
                    dbo.UFN_CONSTITUENT_ISBOARDMEMBER(ID) = 1

        -- Community member

        -- no longer return 'Community member' as a constituency

        --if exists (select ID from @CONSTITUENCIES_TABLE where ID = '55FE8E7C-2B68-44C8-B35C-818AD1944C03')

        --    insert into @RETURN_TABLE

        --        select

        --            ID,

        --            '55FE8E7C-2B68-44C8-B35C-818AD1944C03'

        --        from

        --            dbo.CONSTITUENT 

        --        where

        --            dbo.UFN_CONSTITUENT_ISCOMMUNITYMEMBER(ID) = 1


        -- Donor

        if exists (select ID from @CONSTITUENCIES_TABLE where ID = '70165682-4324-46EC-9439-83FC0CC67E7F')
            insert into @RETURN_TABLE
                select
                    ID,
                    '70165682-4324-46EC-9439-83FC0CC67E7F'
                from
                    dbo.CONSTITUENT 
                where
                    dbo.UFN_CONSTITUENT_ISDONOR(ID) = 1

        -- Event registrant

        if exists (select ID from @CONSTITUENCIES_TABLE where ID = 'C49D4B46-72A7-4206-91AA-BEABA2323E3C')
            insert into @RETURN_TABLE
                select
                    ID,
                    'C49D4B46-72A7-4206-91AA-BEABA2323E3C'
                from
                    dbo.CONSTITUENT 
                where
                    dbo.UFN_CONSTITUENT_ISREGISTRANT(ID) = 1

        -- Fundraiser

        if exists (select ID from @CONSTITUENCIES_TABLE where ID = 'D2DCA06A-BE6E-40B3-B95D-59A926181923')
            insert into @RETURN_TABLE 
                select
                    ID,
                    'D2DCA06A-BE6E-40B3-B95D-59A926181923'
                from
                    dbo.CONSTITUENT
                where
                    dbo.UFN_CONSTITUENT_ISFUNDRAISER(ID) = 1

        -- Major giving prospect

        if exists (select ID from @CONSTITUENCIES_TABLE where ID = '00E748FB-940D-4A7D-A133-C148B29410A8')
            insert into @RETURN_TABLE 
                select
                    ID,
                    '00E748FB-940D-4A7D-A133-C148B29410A8'
                from
                    dbo.CONSTITUENT
                where
                    dbo.UFN_CONSTITUENT_ISPROSPECT(ID) = 1

        -- Matching gift organization

        if exists (select ID from @CONSTITUENCIES_TABLE where ID = '2D04A9C5-27D0-4646-BF0F-6826E4C12632')
            insert into @RETURN_TABLE
                select
                    ID,
                    '2D04A9C5-27D0-4646-BF0F-6826E4C12632'
                from
                    dbo.CONSTITUENT 
                where
                    exists (select ID from dbo.MATCHINGGIFTCONDITION where MATCHINGGIFTCONDITION.ORGANIZATIONID = CONSTITUENT.ID)

        -- Member

        if exists (select ID from @CONSTITUENCIES_TABLE where ID = '2D11326E-8F3B-4322-9797-57C1AACFA5DF')
            insert into @RETURN_TABLE
                select
                    ID,
                    '2D11326E-8F3B-4322-9797-57C1AACFA5DF'
                from
                    dbo.CONSTITUENT
                where
                    dbo.UFN_CONSTITUENT_ISMEMBER(ID, @CURRENTAPPUSERID) = 1

        -- Recognized donor

        if exists (select ID from @CONSTITUENCIES_TABLE where ID = '3DFAC92E-78BD-4051-ABDC-02C675DEB8F6')
            insert into @RETURN_TABLE
                select
                    ID,
                    '3DFAC92E-78BD-4051-ABDC-02C675DEB8F6'
                from
                    dbo.CONSTITUENT
                where
                    dbo.UFN_CONSTITUENT_ISRECOGNITION(ID, @CURRENTAPPUSERID) = 1

        -- Committee

        if exists (select ID from @CONSTITUENCIES_TABLE where ID = 'AC9DB5A4-14E0-416A-9FB2-04038AC66799')
            insert into @RETURN_TABLE
                select
                    ID,
                    'AC9DB5A4-14E0-416A-9FB2-04038AC66799'
                from
                    dbo.CONSTITUENT
                where
                    dbo.UFN_CONSTITUENT_ISCOMMITTEE(ID) = 1

        -- Relation only

        -- This returns "Relation", NOT "Relation only"

        if exists (select ID from @CONSTITUENCIES_TABLE where ID = '8B6ECCFD-6829-4FBC-B092-58CA083F9322')
            insert into @RETURN_TABLE
                select
                    ID,
                    '8B6ECCFD-6829-4FBC-B092-58CA083F9322'
                from
                    dbo.CONSTITUENT
                where
                    dbo.UFN_CONSTITUENT_ISRELATION(ID) = 1

        -- Staff

        if exists (select ID from @CONSTITUENCIES_TABLE where ID = '6093915E-ADE9-42BE-88AE-304731754467')
            insert into @RETURN_TABLE 
                select
                    ID,
                    '6093915E-ADE9-42BE-88AE-304731754467'
                from
                    dbo.CONSTITUENT
                where
                    dbo.UFN_CONSTITUENT_ISSTAFF(ID) = 1

        -- Student

        if exists (select ID from @CONSTITUENCIES_TABLE where ID = '4DB8F4FC-BC43-421D-B592-69BEF109B5FC')
            insert into @RETURN_TABLE
                select
                    ID,
                    '4DB8F4FC-BC43-421D-B592-69BEF109B5FC'
                from
                    dbo.CONSTITUENT
                where
                    dbo.UFN_CONSTITUENT_ISSTUDENT(ID) = 1

        -- Vendor

        if exists (select ID from @CONSTITUENCIES_TABLE where ID = '5435C96D-8617-46C3-9A62-5AFF08451A53')
            insert into @RETURN_TABLE
                select
                    ID,
                    '5435C96D-8617-46C3-9A62-5AFF08451A53'
                from
                    dbo.CONSTITUENT
                where
                    dbo.UFN_CONSTITUENT_ISVENDOR(ID) = 1

        -- Volunteer

        if exists (select ID from @CONSTITUENCIES_TABLE where ID = 'E7489703-3D63-4017-A2BC-88C092563C5D')
            insert into @RETURN_TABLE 
                select
                    ID,
                    'E7489703-3D63-4017-A2BC-88C092563C5D'
                from
                    dbo.CONSTITUENT
                where
                    dbo.UFN_CONSTITUENT_ISVOLUNTEER(ID) = 1

        -- Patron

        if exists (select ID from @CONSTITUENCIES_TABLE where ID = 'E7489703-3D63-4017-A2BC-88C092563C5D')
            insert into @RETURN_TABLE 
                select
                    ID,
                    'E7489703-3D63-4017-A2BC-88C092563C5D'
                from
                    dbo.CONSTITUENT
                where
                    dbo.UFN_CONSTITUENT_ISPATRON(ID) = 1

        -- Committee member

        if exists (select ID from @CONSTITUENCIES_TABLE where ID = '093A3D4F-2974-447F-AD92-870EB4A04593')
            insert into @RETURN_TABLE
                select
                    ID,
                    '093A3D4F-2974-447F-AD92-870EB4A04593'
                from
                    dbo.CONSTITUENT 
                where
                    dbo.UFN_CONSTITUENT_ISCOMMITTEEMEMBER(ID) = 1
        return
    end