USP_DATAFORMTEMPLATE_VIEW_CONSTITUENTPROFILETILE

create  procedure [dbo].[USP_DATAFORMTEMPLATE_VIEW_CONSTITUENTPROFILETILE]
    @ID uniqueidentifier,
    @CURRENTAPPUSERID uniqueidentifier=null,
    @DATALOADED bit = 0 output,

    @TILESTOLOAD xml = null output,

    @CONSTITUENCIESDISPLAYORDER xml = null output,
    @ADVOCATECONSTITUENCYTEXT nvarchar(100) = null output,
    @ALUMNUSENROLLMENTID uniqueidentifier = null output,
    @ALUMNUSCONSTITUENCYTEXT nvarchar(100) = null output,
    @ALUMNUSSTATUSTEXT nvarchar(100) = null output,
    @BANKCONSTITUENCYTEXT nvarchar(100) = null output,
    @BOARDMEMBERCONSTITUENCYTEXT nvarchar(100) = null output,
    @COMMITTEECONSTITUENCYTEXT nvarchar(100) = null output,
    @COMMITTEEMEMBERCONSTITUENCYTEXT nvarchar(100) = null output,
    @DONORCONSTITUENCYTEXT nvarchar(100) = null output,
    @FACULTYCONSTITUENCYTEXT nvarchar(100) = null output,
    @FUNDRAISERCONSTITUENCYTEXT nvarchar(100) = null output,
    @FUNDRAISINGGROUPCONSTITUENCYTEXT nvarchar(100) = null output,
    @GRANTORCONSTITUENCYTEXT nvarchar(100) = null output,
    @LOYALDONORCONSTITUENCYTEXT nvarchar(100) = null output,
    @MAJORDONORCONSTITUENCYTEXT nvarchar(100) = null output,
    @MATCHFINDERONLINERECORDID int = null output,
    @MATCHFINDERCONSTITUENCYTEXT nvarchar(100) = null output,
    @MEMBERCONSTITUENCYTEXT nvarchar(100) = null output,
    @NFGCONSTITUENCYTEXT nvarchar(100) = null output,
    @PATRONCONSTITUENCYTEXT nvarchar(100) = null output,
    @PLANNEDGIVERCONSTITUENCYTEXT nvarchar(100)=null output,
    @PROSPECTCONSTITUENCYTEXT nvarchar(100) = null output,
    @RECOGNITIONCONSTITUENCYTEXT nvarchar(100) = null output,
    @REGISTRANTSTATUSTEXT nvarchar(100) = null output,
    @RELATIONCONSTITUENCYTEXT nvarchar(100) = null output,
    @SPONSORCONSTITUENCYTEXT nvarchar(100) = null output,
    @STAFFCONSTITUENCYTEXT nvarchar(100) = null output,
    @STUDENTENROLLMENTID uniqueidentifier = null output,
    @STUDENTCONSTITUENCYTEXT nvarchar(100) = null output,
    @STUDENTRELATIONCONSTITUENCYTEXT nvarchar(4000) = null output,
    @STUDENTRELATIONCONSTITUENCIES xml = null output,
    @VENDORSTATUSTEXT nvarchar(100) = null output,
    @VOLUNTEERCONSTITUENCYTEXT nvarchar(100) = null output,    
    @USERDEFINEDCONSTITUENCYTEXT nvarchar(4000) = null output,
    @USERDEFINEDCONSTITUENCIES xml = null output

as
    set nocount on;

    declare @CURRENTDATE date;
    set @CURRENTDATE = dbo.UFN_DATE_GETEARLIESTTIME(getdate());

    set @DATALOADED = 1;
    set @TILESTOLOAD = (
        select 'B0AC54D7-51B4-4BDC-B317-F3B15A5684A8' TILEID
        for xml raw('ITEM'),type,elements,root('TILESTOLOAD'),BINARY BASE64
    )

    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
    )

    --Constituencies

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

    set @ALUMNUSSTATUSTEXT = dbo.UFN_CONSTITUENT_GETALUMNUSSTATUSTEXT(@ID);
    set @ALUMNUSCONSTITUENCYTEXT=
        case
            when dbo.UFN_CONSTITUENT_ISALUMNUS(@ID) = 1 
                then dbo.UFN_CONSTITUENCY_GETDESCRIPTION('46EC3424-BA54-4431-A7DC-C6CEBB3B4592') --Alumnus
            else null 
        end;        
    if @ALUMNUSCONSTITUENCYTEXT is not null 
    begin
        set @ALUMNUSENROLLMENTID = (
            select top 1 (EDUCATIONALHISTORY.ID) 
            from dbo.EDUCATIONALHISTORY 
                inner join dbo.EDUCATIONALINSTITUTION on EDUCATIONALINSTITUTION.ID = EDUCATIONALHISTORY.EDUCATIONALINSTITUTIONID
                inner join EDUCATIONALHISTORYSTATUS on EDUCATIONALHISTORYSTATUS.ID = EDUCATIONALHISTORY.EDUCATIONALHISTORYSTATUSID
            where EDUCATIONALINSTITUTION.ISAFFILIATED = 1 
                and EDUCATIONALHISTORYSTATUS.CONSTITUENCYIMPLIEDCODE = 1
                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    @BOARDMEMBERCONSTITUENCYTEXT = 
        case
            when dbo.UFN_CONSTITUENT_ISBOARDMEMBER(@ID) = 1 
                then dbo.UFN_CONSTITUENCY_GETDESCRIPTION('F828E957-5F5E-479A-8F23-2FFD6C7C68FF') --Board member
            else null 
        end;

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

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

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

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

    set @FUNDRAISINGGROUPCONSTITUENCYTEXT = 
        case
            when dbo.UFN_CONSTITUENT_ISFUNDRAISINGGROUP(@ID) = 1 
                then dbo.UFN_CONSTITUENCY_GETDESCRIPTION('264818E9-3F59-44C1-9BBF-1BB1F9C78CA5') --Fundraising Group
            else null 
        end;

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

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

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

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

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

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

    set @NFGCONSTITUENCYTEXT = 
        case
            when dbo.UFN_CONSTITUENT_ISNATIONALFUNDRAISINGGROUP(@ID) = 1 
                then dbo.UFN_CONSTITUENCY_GETDESCRIPTION('A499228A-7619-40DF-AE75-BBA1EE20FB4E') --NFG
            else null 
        end;        

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

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

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

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

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

    set @RELATIONCONSTITUENCYTEXT = null;
    select top 1 @RELATIONCONSTITUENCYTEXT = DESCRIPTION 
    from dbo.UFN_CONSTITUENT_GETALLCONSTITUENCIES(@ID)
    where CONSTITUENCYCODEID = '8B6ECCFD-6829-4FBC-B092-58CA083F9322';

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

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

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

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

    set @STUDENTRELATIONCONSTITUENCYTEXT = (
        select dbo.UDA_BUILDLIST(distinct CONSTITUENCYCODEDESCRIPTION) 
        from dbo.UFN_CONSTITUENT_STUDENTRELATIONCONSTITUENCIES() 
        where CONSTITUENTID = @ID
    )

    set @STUDENTRELATIONCONSTITUENCIES = (
        select distinct
            CONSTITUENCYCODEDESCRIPTION as DESCRIPTION,
            CONSTITUENCYCODESEQUENCE as SEQUENCE
        from dbo.UFN_CONSTITUENT_STUDENTRELATIONCONSTITUENCIES()
        where CONSTITUENTID = @ID
        order by CONSTITUENCYCODESEQUENCE
        for xml raw('ITEM'), type, elements, root('STUDENTRELATIONCONSTITUENCIES'), binary base64
    )

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

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

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

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

    return 0;