USP_DATAFORMTEMPLATE_VIEW_CONSTITUENTMEMBERSHIPMEMBERINFORMATION

                CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_CONSTITUENTMEMBERSHIPMEMBERINFORMATION
                (
                    @ID uniqueidentifier,
                    @CURRENTAPPUSERID uniqueidentifier,
                    @DATALOADED bit = 0 output,
                    @PRIMARYMEMBERNAME nvarchar(100) = null output,
                    @ADDRESSID uniqueidentifier = null output,
                    @ADDRESS nvarchar(300) = null output,
                    @ADDRESSTYPE nvarchar(100) = null output,
                    @PHONENUMBERID uniqueidentifier = null output,
                    @PHONENUMBER nvarchar(100) = null output,
                    @PHONENUMBERTYPE nvarchar(100) = null output,
                    @EMAILADDRESSID uniqueidentifier = null output,
                    @EMAILADDRESS dbo.UDT_EMAILADDRESS = null output,
                    @SCHOOLNAME nvarchar(100) = null output,
                    @CLASSYEAR nvarchar(4) = null output,
                    @PROSPECTMANAGERID uniqueidentifier = null output,
                    @PROSPECTMANAGER nvarchar(100) = null output,
                    @ADVOCATECONSTITUENCYTEXT nvarchar(100) = null output,
                    @BOARDMEMBERCONSTITUENCYTEXT nvarchar(100) = null output,
                    @STAFFCONSTITUENCYTEXT nvarchar(100) = null output,
                    @DONORCONSTITUENCYTEXT nvarchar(100) = null output,
                    @FUNDRAISERCONSTITUENCYTEXT nvarchar(100) = null output,
                    @PROSPECTCONSTITUENCYTEXT nvarchar(100) = null output,
                    @VOLUNTEERCONSTITUENCYTEXT nvarchar(100) = null output,
                    @ALUMNUSSTATUSTEXT nvarchar(100) = null output,
                    @REGISTRANTSTATUSTEXT nvarchar(100) = null output,
                    @VENDORSTATUSTEXT nvarchar(100) = null output,
                    @MEMBERCONSTITUENCYTEXT nvarchar(100) = null output,
                    @RECOGNITIONCONSTITUENCYTEXT nvarchar(100) = null output,
                    @MATCHFINDERCONSTITUENCYTEXT nvarchar(100) = null output,
                    @SPONSORTYPECODE tinyint = null output,
                    @SPONSORCONSTITUENCYTEXT nvarchar(100) = null output,
                    @PLANNEDGIVERCONSTITUENCYTEXT nvarchar(100) = null output,
                    @PATRONCONSTITUENCYTEXT nvarchar(100) = null output,
                    @COMMITTEECONSTITUENCYTEXT nvarchar(100) = null output,
                    @GRANTORCONSTITUENCYTEXT nvarchar(100) = null output,
                    @COMMITTEEMEMBERCONSTITUENCYTEXT nvarchar(100) = null output,
                    @FACULTYCONSTITUENCYTEXT nvarchar(100) = null output,
                    @STUDENTCONSTITUENCYTEXT nvarchar(100) = null output,
                    @ALUMNUSENROLLMENTID uniqueidentifier = null output,
                    @STUDENTENROLLMENTID uniqueidentifier = null output,
                    @ALUMNUSCONSTITUENCYTEXT nvarchar(100) = null output,
                    @BANKCONSTITUENCYTEXT nvarchar(100) = null output,
                    @LOYALDONORCONSTITUENCYTEXT nvarchar(100) = null output,
                    @MAJORDONORCONSTITUENCYTEXT nvarchar(100) = null output,
                    @USERDEFINEDCONSTITUENCYTEXT nvarchar(4000) = null output,
                    @USERDEFINEDCONSTITUENCIES xml = null output,
                    @CONSTITUENCIESDISPLAYORDER xml = null output
                )
                as
                    set nocount on;

                    set @DATALOADED = 0;

                    -- Find name
                    select
                        @PRIMARYMEMBERNAME = NAME
                    from
                        dbo.UFN_CONSTITUENT_DISPLAYNAME(@ID)

                    declare @SEASONALITEMS table
                    (
                        CONSTITUENTID uniqueidentifier,
                        ITEMID uniqueidentifier
                    )

                    -- Find seasonal addresses
                    insert into @SEASONALITEMS
                    select
                        CONSTITUENTID,
                        ADDRESSID
                    from
                        dbo.UFN_ADDRESSPROCESS_SEASONALADDRESSES(GETDATE())
                    where
                        CONSTITUENTID = @ID

                    -- Address
                    if exists (select * from @SEASONALITEMS)
                    begin
                        -- Address is seasonal
                        select
                            @ADDRESSID = SI.ITEMID,
                            @ADDRESS = dbo.UFN_BUILDFULLADDRESS(SI.ITEMID, A.ADDRESSBLOCK, A.CITY, A.STATEID, A.POSTCODE, A.COUNTRYID),
                            @ADDRESSTYPE = ATC.DESCRIPTION
                        from
                            @SEASONALITEMS SI
                            inner join dbo.ADDRESS A on SI.ITEMID = A.ID
                            inner join dbo.ADDRESSTYPECODE ATC on A.ADDRESSTYPECODEID = ATC.ID
                    end
                    else
                    begin
                        -- Address is not seasonal
                        select
                            @ADDRESSID = A.ID,
                            @ADDRESS = dbo.UFN_BUILDFULLADDRESS(A.ID, A.ADDRESSBLOCK, A.CITY, A.STATEID, A.POSTCODE, A.COUNTRYID),
                            @ADDRESSTYPE = ATC.DESCRIPTION
                        from
                            dbo.ADDRESS A
                            inner join dbo.ADDRESSTYPECODE ATC on A.ADDRESSTYPECODEID = ATC.ID
                        where
                            A.CONSTITUENTID = @ID
                    end

                    -- Prepare for re-use
                    delete from @SEASONALITEMS

                    -- Find seasonal phone numbers
                    insert into @SEASONALITEMS
                    select
                        CONSTITUENTID,
                        ID
                    from
                        dbo.PHONE P
                    where
                        CONSTITUENTID = @ID and
                        ((cast((right('0' + cast(month(GETDATE()) as varchar(2)), 2) + right('0' + cast(day(GETDATE()) as varchar(2)), 2)) as int) - cast(P.SEASONALSTARTDATE as int)) + 1231) % 1231 between 0 and ((cast(P.SEASONALENDDATE as int) - cast(P.SEASONALSTARTDATE as int)) + 1231) % 1231

                    -- Phone number
                    if exists (select * from @SEASONALITEMS)
                    begin
                        -- Phone number is seasonal
                        select
                            @PHONENUMBERID = SI.ITEMID,
                            @PHONENUMBER = dbo.UFN_PHONE_GETINTERNATIONALNUMBER(P.COUNTRYID, P.NUMBER),
                            @PHONENUMBERTYPE = PTC.DESCRIPTION
                        from
                            @SEASONALITEMS SI
                            inner join dbo.PHONE P on SI.ITEMID = P.ID
                            inner join dbo.PHONETYPECODE PTC on P.PHONETYPECODEID = PTC.ID
                    end
                    else
                    begin
                        -- Phone number is not seasonal
                        select
                            @PHONENUMBERID = P.ID,
                            @PHONENUMBER = dbo.UFN_PHONE_GETINTERNATIONALNUMBER(P.COUNTRYID, P.NUMBER),
                            @PHONENUMBERTYPE = PTC.DESCRIPTION
                        from
                            dbo.PHONE P
                            inner join dbo.PHONETYPECODE PTC on P.PHONETYPECODEID = PTC.ID
                        where
                            P.CONSTITUENTID = @ID
                    end

                    -- Email address
                    select
                        @EMAILADDRESSID = E.ID,
                        @EMAILADDRESS = EMAILADDRESS
                    from
                        dbo.EMAILADDRESS E
                    where
                        E.CONSTITUENTID = @ID and
                        E.ISPRIMARY = 1

                    -- Education info
                    select
                        @SCHOOLNAME = EI.NAME,
                        @CLASSYEAR = EH.CLASSOF
                    from
                        dbo.EDUCATIONALHISTORY EH
                        left join dbo.EDUCATIONALINSTITUTION EI on EH.EDUCATIONALINSTITUTIONID = EI.ID
                    where
                        EH.CONSTITUENTID = @ID and
                        EH.ISPRIMARYRECORD = 1

                    -- Prospect manager
                    select
                        @PROSPECTMANAGERID = P.PROSPECTMANAGERFUNDRAISERID,
                        @PROSPECTMANAGER = NF.NAME
                    from
                        dbo.PROSPECT P
                            inner join dbo.CONSTITUENT C on P.PROSPECTMANAGERFUNDRAISERID = C.ID
                        cross apply dbo.UFN_CONSTITUENT_DISPLAYNAME(P.PROSPECTMANAGERFUNDRAISERID) NF
                    where
                        P.ID = @ID

                    --
                    -- Constituencies
                    --

                    declare @MATCHFINDERONLINERECORDID int
                    set @MATCHFINDERONLINERECORDID = 0;
                    select top 1
                        @MATCHFINDERONLINERECORDID = MATCHFINDERRECORDID
                    from
                        dbo.MATCHFINDERCONSTITUENT
                    where
                        ID = @ID;

                    set    @ADVOCATECONSTITUENCYTEXT =
                    case
                        when dbo.UFN_CONSTITUENT_ISADVOCATE(@ID) = 1 then dbo.UFN_CONSTITUENCY_GETDESCRIPTION('F828E957-5F5E-479A-8F23-2FFD6C7C6899')
                        else null
                    end;

                    set    @BOARDMEMBERCONSTITUENCYTEXT =
                    case
                        when dbo.UFN_CONSTITUENT_ISBOARDMEMBER(@ID) = 1 then dbo.UFN_CONSTITUENCY_GETDESCRIPTION('F828E957-5F5E-479A-8F23-2FFD6C7C68FF')
                        else null
                    end;

                    set @STAFFCONSTITUENCYTEXT =
                    case
                        when dbo.UFN_CONSTITUENT_ISSTAFF(@ID) = 1 then dbo.UFN_CONSTITUENCY_GETDESCRIPTION('6093915E-ADE9-42BE-88AE-304731754467')
                        else null
                    end;

                    set @DONORCONSTITUENCYTEXT =
                    case
                        when dbo.UFN_CONSTITUENT_ISDONOR(@ID) = 1 then dbo.UFN_CONSTITUENCY_GETDESCRIPTION('70165682-4324-46EC-9439-83FC0CC67E7F')
                        else null
                    end;

                    set @FUNDRAISERCONSTITUENCYTEXT =
                    case
                        when dbo.UFN_CONSTITUENT_ISFUNDRAISER(@ID) = 1 then dbo.UFN_CONSTITUENCY_GETDESCRIPTION('D2DCA06A-BE6E-40B3-B95D-59A926181923')
                        else null
                    end;

                    set @PROSPECTCONSTITUENCYTEXT =
                    case
                        when dbo.UFN_CONSTITUENT_ISPROSPECT(@ID) = 1 then dbo.UFN_CONSTITUENCY_GETDESCRIPTION('00E748FB-940D-4A7D-A133-C148B29410A8')
                        else null
                    end;

                    set @VOLUNTEERCONSTITUENCYTEXT =
                    case
                        when dbo.UFN_CONSTITUENT_ISVOLUNTEER(@ID) = 1 then dbo.UFN_CONSTITUENCY_GETDESCRIPTION('E7489703-3D63-4017-A2BC-88C092563C5D')
                        else null
                    end;

                    set @ALUMNUSSTATUSTEXT = dbo.UFN_CONSTITUENT_GETALUMNUSSTATUSTEXT(@ID);

                    set @REGISTRANTSTATUSTEXT =
                    case
                        when dbo.UFN_CONSTITUENT_ISREGISTRANT(@ID) = 1 then dbo.UFN_CONSTITUENCY_GETDESCRIPTION('C49D4B46-72A7-4206-91AA-BEABA2323E3C')
                        else null
                    end;

                    set @VENDORSTATUSTEXT =
                    case
                        when dbo.UFN_CONSTITUENT_ISVENDOR(@ID) = 1 then dbo.UFN_CONSTITUENCY_GETDESCRIPTION('5435C96D-8617-46C3-9A62-5AFF08451A53')
                        else null
                    end;

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

                    set @RECOGNITIONCONSTITUENCYTEXT =
                    case
                        when dbo.UFN_CONSTITUENT_ISRECOGNITION(@ID, @CURRENTAPPUSERID) = 1 then dbo.UFN_CONSTITUENCY_GETDESCRIPTION('3dfac92e-78bd-4051-abdc-02c675deb8f6')
                        else null
                    end;

                    set @MATCHFINDERCONSTITUENCYTEXT =
                    case
                        when not @MATCHFINDERONLINERECORDID is null and @MATCHFINDERONLINERECORDID <> 0 then dbo.UFN_CONSTITUENCY_GETDESCRIPTION('2D04A9C5-27D0-4646-BF0F-6826E4C12632')
                        else null
                    end;

                    set @SPONSORTYPECODE = dbo.UFN_CONSTITUENT_SPONSORTYPECODE(@ID)
                    set @SPONSORCONSTITUENCYTEXT =
                    case @SPONSORTYPECODE
                        when 0 then dbo.UFN_CONSTITUENCY_GETDESCRIPTION('4D746A03-A0AB-45F3-A30B-1AD4F304E622')
                        when 1 then dbo.UFN_CONSTITUENCY_GETDESCRIPTION('F89E03BC-E724-4e5d-943B-72D4D1E1E916')
                        when 2 then dbo.UFN_CONSTITUENCY_GETDESCRIPTION('908E521C-B0A5-4832-B664-7D7B079D77C2')
                        else null
                    end;

                    set @PLANNEDGIVERCONSTITUENCYTEXT =
                    case
                        when dbo.UFN_CONSTITUENT_ISPLANNEDGIVER(@ID) = 1 then dbo.UFN_CONSTITUENCY_GETDESCRIPTION('CEE46FE7-3FBB-4DFE-97EB-BA67DD33C634')
                        else null
                    end;

                    set @PATRONCONSTITUENCYTEXT =
                    case
                        when dbo.UFN_CONSTITUENT_ISPATRON(@ID) = 1 then dbo.UFN_CONSTITUENCY_GETDESCRIPTION('A843B859-4C6B-445B-97F3-179582E270A5')
                        else null
                    end;

                    set @COMMITTEECONSTITUENCYTEXT =
                    case
                        when dbo.UFN_CONSTITUENT_ISCOMMITTEE(@ID) = 1 then dbo.UFN_CONSTITUENCY_GETDESCRIPTION('AC9DB5A4-14E0-416A-9FB2-04038AC66799')
                        else null
                    end;

                    set @GRANTORCONSTITUENCYTEXT =
                    case
                        when dbo.UFN_CONSTITUENT_ISGRANTOR(@ID) = 1 then dbo.UFN_CONSTITUENCY_GETDESCRIPTION('D9982C99-15C1-4C90-873E-56FD4B164056')
                        else null
                    end;

                    set @COMMITTEEMEMBERCONSTITUENCYTEXT =
                    case
                        when dbo.UFN_CONSTITUENT_ISCOMMITTEEMEMBER(@ID) = 1 then dbo.UFN_CONSTITUENCY_GETDESCRIPTION('093A3D4F-2974-447F-AD92-870EB4A04593')
                        else null
                    end;

                    set @FACULTYCONSTITUENCYTEXT =
                    case
                        when dbo.UFN_CONSTITUENT_ISFACULTY(@ID) = 1 then dbo.UFN_CONSTITUENCY_GETDESCRIPTION('08D55D6A-10C8-4a72-92A0-EF87033AD7B6')
                        else null
                    end;

                    set @STUDENTCONSTITUENCYTEXT =
                    case
                        when dbo.UFN_CONSTITUENT_ISSTUDENT(@ID) = 1 then dbo.UFN_CONSTITUENCY_GETDESCRIPTION('4DB8F4FC-BC43-421D-B592-69BEF109B5FC')
                        else null
                    end;

                    set @ALUMNUSCONSTITUENCYTEXT =
                    case
                        when dbo.UFN_CONSTITUENT_ISALUMNUS(@ID) = 1 then dbo.UFN_CONSTITUENCY_GETDESCRIPTION('46EC3424-BA54-4431-A7DC-C6CEBB3B4592')
                        else null
                    end;

                    if @ALUMNUSCONSTITUENCYTEXT is not null
                    begin
                        set @ALUMNUSENROLLMENTID =
                            (
                                select top 1
                                    EDUCATIONALHISTORY.ID
                                from dbo.EDUCATIONALHISTORY
                                    inner join dbo.EDUCATIONALINSTITUTION on EDUCATIONALHISTORY.EDUCATIONALINSTITUTIONID = EDUCATIONALINSTITUTION.ID
                                    inner join EDUCATIONALHISTORYSTATUS on EDUCATIONALHISTORY.EDUCATIONALHISTORYSTATUSID = EDUCATIONALHISTORYSTATUS.ID
                                where
                                    (EDUCATIONALINSTITUTION.ISAFFILIATED = 1) and
                                    (EDUCATIONALHISTORYSTATUS.CONSTITUENCYIMPLIEDCODE = 1) and
                                    (EDUCATIONALHISTORY.CONSTITUENTID = @ID)
                                order by dbo.EDUCATIONALHISTORY.DATEADDED
                            )
                    end

                    if @STUDENTCONSTITUENCYTEXT is not null
                    begin
                        set @STUDENTENROLLMENTID =
                        (
                            select top 1
                                EDUCATIONALHISTORY.ID
                            from dbo.EDUCATIONALHISTORY
                                inner join dbo.EDUCATIONALINSTITUTION on EDUCATIONALHISTORY.EDUCATIONALINSTITUTIONID = EDUCATIONALINSTITUTION.ID
                                inner join EDUCATIONALHISTORYSTATUS on EDUCATIONALHISTORY.EDUCATIONALHISTORYSTATUSID = EDUCATIONALHISTORYSTATUS.ID
                            where
                                (EDUCATIONALINSTITUTION.ISAFFILIATED = 1) and
                                (EDUCATIONALHISTORYSTATUS.CONSTITUENCYIMPLIEDCODE = 0) and
                                (EDUCATIONALHISTORY.CONSTITUENTID = @ID)
                            order by dbo.EDUCATIONALHISTORY.DATEADDED
                        )
                    end

                    set @BANKCONSTITUENCYTEXT =
                    case
                        when dbo.UFN_CONSTITUENT_ISBANK(@ID) = 1 then dbo.UFN_CONSTITUENCY_GETDESCRIPTION('171AB3CD-C4E1-4825-B693-10F524A7A594')
                        else null
                    end;

                    set @LOYALDONORCONSTITUENCYTEXT =
                    case
                        when dbo.UFN_CONSTITUENT_ISLOYALDONOR(@ID) = 1 then dbo.UFN_CONSTITUENCY_GETDESCRIPTION('E5A0EA42-65BA-4B25-AFE2-9B709F99E72B')
                        else null
                    end;

                    set @MAJORDONORCONSTITUENCYTEXT =
                    case
                        when dbo.UFN_CONSTITUENT_ISMAJORDONOR(@ID) = 1 then dbo.UFN_CONSTITUENCY_GETDESCRIPTION('1A9BFE80-604D-4B5B-8065-E751DDF6EF39')
                        else null
                    end;

                    if len(@LOYALDONORCONSTITUENCYTEXT) > 0 or len(@MAJORDONORCONSTITUENCYTEXT) > 0 
                        set @DONORCONSTITUENCYTEXT = null;

                    set @USERDEFINEDCONSTITUENCYTEXT = dbo.UFN_CONSTITUENCIES_USERDEFINED(@ID);

                    set @USERDEFINEDCONSTITUENCIES = dbo.UFN_CONSTITUENT_GETCONSTITUENCIES_USERDEFINED_TOITEMLISTXML(@ID, dbo.UFN_DATE_GETEARLIESTTIME(getdate()));

                    set @CONSTITUENCIESDISPLAYORDER = 
                        (
                            select 
                                ID,
                                DESCRIPTION,
                                SEQUENCE,
                                ISSYSTEM
                            from 
                                dbo.V_CONSTITUENCYDISPLAYORDER
                            order by 
                                SEQUENCE, DESCRIPTION
                            for xml raw('ITEM'),type,elements,root('CONSTITUENCIESDISPLAYORDER'),BINARY BASE64
                        )

                    set @DATALOADED = 1;

                    return 0;