USP_DATAFORMTEMPLATE_VIEW_PROSPECTSIDEPROFILE

CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_PROSPECTSIDEPROFILE
(
    @ID uniqueidentifier,
  @CURRENTAPPUSERID uniqueidentifier = null,
    @DATALOADED bit = 0 output,
  @PICTURE varbinary(max) = null output,
    @ISINACTIVE bit = null output,
    @ISDECEASED bit = null output,
    @LOOKUPID nvarchar(100) = null output,
    @NAME nvarchar(400) = null output,
  @ISORGANIZATION bit = null output,
  @ISGROUP bit = null output,
    @ADDRESS nvarchar(300) = null output,
    @ADDRESSID uniqueidentifier = null output,
  @ADDRESSISCONFIDENTIAL bit = null output,
  @DONOTMAIL bit = null output,
    @EMAILADDRESS dbo.UDT_EMAILADDRESS = null output,
    @EMAILADDRESSWEBUI dbo.UDT_EMAILADDRESS = null output,    --Copied functionality from WealthInformationPage
    @EMAILADDRESSID uniqueidentifier = null output,
  @DONOTEMAIL bit = null output,
    @PHONENUMBER nvarchar(100) = null output,
    @PHONENUMBERID uniqueidentifier = null output,
  @PHONEISCONFIDENTIAL bit = null output,
  @DONOTPHONE bit = null output,
  @DATELASTRESEARCHREQUESTCOMPLETED datetime = null output,
    @SPOUSE_E nvarchar(400) = null output,
    @SPOUSE_RP nvarchar(400) = null output,
    @SPOUSEID uniqueidentifier = null output,
  @ISSPOUSEDECEASED bit = null output,
    @HOUSEHOLD_E nvarchar(400) = null output,
    @HOUSEHOLD_RP nvarchar(400) = null output,
    @HOUSEHOLDID uniqueidentifier = null output,
    @PRIMARYBUSINESS_E nvarchar(100) = null output,
    @PRIMARYBUSINESS_RP nvarchar(100) = null output,
    @PRIMARYBUSINESSID uniqueidentifier = null output,
    @PRIMARYEDUCATION_E nvarchar(100) = null output,
    @PRIMARYEDUCATION_RP nvarchar(100) = null output,
    @PRIMARYEDUCATIONID uniqueidentifier = null output,
    @SUBSCRIPTION integer = null output,
    @ENTERPRISE_FLAG bit = null output,
    @SOCIALMEDIAACCOUNTS xml = null output
)
as
    set nocount on;

    --CONSTITUENT FIELDS
    select
        @DATALOADED = 1,
        @PICTURE = CONSTITUENT.PICTURETHUMBNAIL,
        @ISINACTIVE = CONSTITUENT.ISINACTIVE,
        @LOOKUPID = CONSTITUENT.LOOKUPID,
    @NAME = CONSTITUENT.NAME,
        @ISORGANIZATION = CONSTITUENT.ISORGANIZATION,
        @ISGROUP = CONSTITUENT.ISGROUP
    from
        dbo.CONSTITUENT
    where
        CONSTITUENT.ID = @ID;

    --DECEASED
    select
        @ISDECEASED = 1
    from
        dbo.DECEASEDCONSTITUENT
    where
        DECEASEDCONSTITUENT.ID = @ID;

    --EMAIL FIELDS
    select
        @EMAILADDRESS = EMAILADDRESS.EMAILADDRESS,
        @EMAILADDRESSWEBUI = EMAILADDRESS.EMAILADDRESS,
    @DONOTEMAIL = DONOTEMAIL,
    @EMAILADDRESSID = ID
    from
        dbo.EMAILADDRESS
    where
        EMAILADDRESS.CONSTITUENTID = @ID and 
        EMAILADDRESS.ISPRIMARY = 1;

    --PHONE FIELDS
    select
        @PHONENUMBER = dbo.UFN_PHONE_GETINTERNATIONALNUMBER(PHONE.COUNTRYID, PHONE.NUMBER),
    @PHONEISCONFIDENTIAL = PHONE.ISCONFIDENTIAL,
    @DONOTPHONE = PHONE.DONOTCALL,
    @PHONENUMBERID = PHONE.ID
    from
        dbo.PHONE
    where 
        PHONE.CONSTITUENTID = @ID and
        PHONE.ISPRIMARY = 1;

    --ADDRESS FIELDS
    select 
        @ADDRESS = dbo.UFN_BUILDFULLADDRESS(ADDRESS.ID, ADDRESS.ADDRESSBLOCK, ADDRESS.CITY, ADDRESS.STATEID, ADDRESS.POSTCODE, ADDRESS.COUNTRYID),
        @DONOTMAIL = ADDRESS.DONOTMAIL,
    @ADDRESSISCONFIDENTIAL = ADDRESS.ISCONFIDENTIAL,
    @ADDRESSID = ADDRESS.ID
    from
        dbo.ADDRESS
    where
        ADDRESS.CONSTITUENTID = @ID and
        ADDRESS.ISPRIMARY = 1;                    

    --SPOUSE FIELDS
    set @ISSPOUSEDECEASED = 0;

    select
        @SPOUSE_E =   NF_SPOUSE.[NAME],
        @SPOUSE_RP =   NF_SPOUSE.[NAME],
        @SPOUSEID = SPOUSE.ID,
        @ISSPOUSEDECEASED = case when dbo.DECEASEDCONSTITUENT.ID is null then 0 else 1 end
    from
        dbo.RELATIONSHIP
    left outer join
        dbo.CONSTITUENT as SPOUSE
    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(SPOUSE.ID) NF_SPOUSE
    on
        SPOUSE.ID = RELATIONSHIP.RECIPROCALCONSTITUENTID
    left join
      dbo.DECEASEDCONSTITUENT
    on
      SPOUSE.ID = dbo.DECEASEDCONSTITUENT.ID
    where 
        RELATIONSHIP.RELATIONSHIPCONSTITUENTID = @ID and
        RELATIONSHIP.ISSPOUSE = 1;

    --HOUSEHOLD FIELDS
    if @ISORGANIZATION = 0 and @ISGROUP = 0
    begin
        select top(1)
            @HOUSEHOLDID = CG.ID,
            @HOUSEHOLD_E = CG.NAME,
            @HOUSEHOLD_RP = CG.NAME
        from dbo.GROUPMEMBER as GM
        join dbo.CONSTITUENT as CG on GM.GROUPID = CG.ID
        join dbo.GROUPDATA as GD on GD.ID = CG.ID
        where GM.MEMBERID = @ID
          and dbo.UFN_GROUPMEMBER_ISCURRENTMEMBER(GM.ID) = 1
          and GD.GROUPTYPECODE = 0;
    end

    --PRIMARY EDUCATION FIELDS
    if @ISORGANIZATION = 0 and @ISGROUP = 0
    begin
        select
            @PRIMARYEDUCATIONID = EDUCATIONALHISTORY.ID,
            @PRIMARYEDUCATION_E = EDUCATIONALINSTITUTION.NAME,
            @PRIMARYEDUCATION_RP = EDUCATIONALINSTITUTION.NAME
        from
            dbo.EDUCATIONALHISTORY 
        inner join dbo.EDUCATIONALINSTITUTION on
            EDUCATIONALINSTITUTION.ID = EDUCATIONALHISTORY.EDUCATIONALINSTITUTIONID
        where EDUCATIONALHISTORY.ISPRIMARYRECORD = 1 and EDUCATIONALHISTORY.CONSTITUENTID = @ID;
    end

    --BUSINESS FIELDS
    if @ISORGANIZATION = 1
    begin
        select 
            @PRIMARYBUSINESS_E =   NF_CONTACT.[NAME],
            @PRIMARYBUSINESS_RP =   NF_CONTACT.[NAME],
            @PRIMARYBUSINESSID = CONTACT.ID
        from
            dbo.RELATIONSHIP
        left outer join
            dbo.CONSTITUENT as CONTACT
        outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(CONTACT.ID) NF_CONTACT
        on
            CONTACT.ID = RELATIONSHIP.RECIPROCALCONSTITUENTID
        where 
            RELATIONSHIP.RELATIONSHIPCONSTITUENTID = @ID and
            RELATIONSHIP.ISPRIMARYCONTACT = 1;
    end
    else 
    begin
        select 
            @PRIMARYBUSINESS_E =   BUSINESS.[NAME],
            @PRIMARYBUSINESS_RP =   BUSINESS.[NAME],
            @PRIMARYBUSINESSID = BUSINESS.ID
        from
            dbo.RELATIONSHIP
        left outer join
            dbo.CONSTITUENT as BUSINESS
        on
            BUSINESS.ID = RELATIONSHIP.RECIPROCALCONSTITUENTID
        where 
            RELATIONSHIP.RELATIONSHIPCONSTITUENTID = @ID and
            RELATIONSHIP.ISPRIMARYBUSINESS = 1;
    end

    --LAST PROSPECT RESEARCH REQUEST COMPLETED DATE
    select top(1)
        @DATELASTRESEARCHREQUESTCOMPLETED = PRRC.DATECOMPLETED
    from
        dbo.PROSPECTRESEARCHREQUESTCONSTITUENT PRRC
    where    
        PRRC.CONSTITUENTID = @ID
    order by DATECOMPLETED desc

    select
        @SUBSCRIPTION = ACCOUNTTYPECODE
    from
        dbo.WEALTHPOINTCONFIGURATION        

  set @ENTERPRISE_FLAG = 0;
    Select @ENTERPRISE_FLAG = 1 from INSTALLEDPRODUCTLIST where ID='3117D2C8-7F46-42F2-ABEB-B654F2F63046'

    --SOCIAL MEDIA ACCOUNTS
    select @SOCIALMEDIAACCOUNTS = dbo.UFN_CONSTITUENT_GETSOCIALMEDIAACCOUNTS_TOITEMLISTXML(@ID);

  return 0;