USP_CONSTITUENT_GETCONSTITUENCYTEXT

CREATE procedure dbo.USP_CONSTITUENT_GETCONSTITUENCYTEXT
(
  @CONSTITUENTID uniqueidentifier,
  @CURRENTAPPUSERID uniqueidentifier = null,
  @BOARDMEMBERCONSTITUENCYTEXT nvarchar(100) = null output,
  @RELATIONCONSTITUENCYTEXT 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,
  @COMMUNITYMEMBERCONSTITUENCYTEXT nvarchar(100) = null output,
  @USERDEFINEDCONSTITUENCYTEXT nvarchar(4000) = null output,
  @ALUMNUSSTATUSTEXT nvarchar(100) = null output,
  @REGISTRANTSTATUSTEXT nvarchar(100) = null output,
  @VENDORSTATUSTEXT nvarchar(100) = null output,
  @MATCHFINDERCONSTITUENCYTEXT nvarchar(100) = null output,
  @MATCHFINDERONLINERECORDID int = null output,
  @MEMBERCONSTITUENCYTEXT nvarchar(100) = null output,
  @RECOGNITIONCONSTITUENCYTEXT nvarchar(100) = null output,
  @BANKCONSTITUENCYTEXT nvarchar(100) = null output,
  @COMMITTEECONSTITUENCYTEXT nvarchar(100) = null output,
  @COMMITTEEMEMBERCONSTITUENCYTEXT nvarchar(100) = null output,
  @PLANNEDGIVERCONSTITUENCYTEXT nvarchar(100) = null output,
  @GRANTORCONSTITUENCYTEXT nvarchar(100) = null output,
  @SPONSORCONSTITUENCYTEXT nvarchar(100) = null output,
  @LOYALDONORCONSTITUENCYTEXT nvarchar(100) = null output,
  @MAJORDONORCONSTITUENCYTEXT nvarchar(100) = null output
)
as
  set nocount on;

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

  set @BANKCONSTITUENCYTEXT = case
    when dbo.UFN_CONSTITUENT_ISBANK(@CONSTITUENTID) = 1 then dbo.UFN_CONSTITUENCY_GETDESCRIPTION('171AB3CD-C4E1-4825-B693-10F524A7A594') --Bank

    else null
  end;

  set @BOARDMEMBERCONSTITUENCYTEXT = case
    when dbo.UFN_CONSTITUENT_ISBOARDMEMBER(@CONSTITUENTID) = 1 then dbo.UFN_CONSTITUENCY_GETDESCRIPTION('F828E957-5F5E-479A-8F23-2FFD6C7C68FF') --Board member

    else null
  end;

  set @COMMITTEECONSTITUENCYTEXT = case
    when dbo.UFN_CONSTITUENT_ISCOMMITTEE(@CONSTITUENTID) = 1 then dbo.UFN_CONSTITUENCY_GETDESCRIPTION('AC9DB5A4-14E0-416A-9FB2-04038AC66799') --Committee

    else null
  end;

  set @COMMITTEEMEMBERCONSTITUENCYTEXT = case
    when dbo.UFN_CONSTITUENT_ISCOMMITTEEMEMBER(@CONSTITUENTID) = 1 then dbo.UFN_CONSTITUENCY_GETDESCRIPTION('093A3D4F-2974-447F-AD92-870EB4A04593') --Committee Member

    else null
  end;

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

  --set @COMMUNITYMEMBERCONSTITUENCYTEXT = case

  --  when dbo.UFN_CONSTITUENT_ISCOMMUNITYMEMBER(@CONSTITUENTID) = 1 then dbo.UFN_CONSTITUENCY_GETDESCRIPTION('55FE8E7C-2B68-44C8-B35C-818AD1944C03') --Community member

  --  else null

  --end;        


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

    else null
  end;

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

  else null end;

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

    else null
  end;

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

    else null
  end;

  select top 1
    @MATCHFINDERONLINERECORDID = MATCHFINDERRECORDID 
  from
    dbo.MATCHFINDERCONSTITUENT
  where
    ID = @CONSTITUENTID;

  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;

  if len(@LOYALDONORCONSTITUENCYTEXT) > 0 or len(@MAJORDONORCONSTITUENCYTEXT) > 0
  begin
    set @DONORCONSTITUENCYTEXT = null;
  end
  else
  begin
    set @DONORCONSTITUENCYTEXT = case
      when dbo.UFN_CONSTITUENT_ISDONOR(@CONSTITUENTID) = 1 then dbo.UFN_CONSTITUENCY_GETDESCRIPTION('70165682-4324-46EC-9439-83FC0CC67E7F') --Donor

      else null
    end;
  end

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

    else null
  end;

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

    else null
  end;

  set @PROSPECTCONSTITUENCYTEXT = case
    when dbo.UFN_CONSTITUENT_ISPROSPECT(@CONSTITUENTID) = 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(@CONSTITUENTID, @CURRENTAPPUSERID) = 1 then dbo.UFN_CONSTITUENCY_GETDESCRIPTION('3dfac92e-78bd-4051-abdc-02c675deb8f6')  --Recognition

    else null
  end;

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

    else null
  end;

  set @RELATIONCONSTITUENCYTEXT = case
    when dbo.UFN_CONSTITUENT_ISRELATION(@CONSTITUENTID) = 1 then dbo.UFN_CONSTITUENCY_GETDESCRIPTION('8B6ECCFD-6829-4FBC-B092-58CA083F9322') --Relation only

    else null
  end;

  set @SPONSORCONSTITUENCYTEXT = case dbo.UFN_CONSTITUENT_SPONSORTYPECODE(@CONSTITUENTID)
    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(@CONSTITUENTID) = 1 then dbo.UFN_CONSTITUENCY_GETDESCRIPTION('6093915E-ADE9-42BE-88AE-304731754467') --Staff

    else null
  end;

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

    else null
  end;

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

    else null
  end;

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

  return 0;