USP_DATAFORMTEMPLATE_VIEW_CONSTITUENTDUPLICATEMATCHVIEW

CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_CONSTITUENTDUPLICATEMATCHVIEW (
    @ID uniqueidentifier,
    @DATALOADED bit = 0 output,
    @LOOKUPID nvarchar(50) = null output,
    @FIRSTNAME nvarchar(50) = null output,
    @MIDDLENAME nvarchar(50) = null output,
    @LASTNAME nvarchar(100) = null output,
    @SUFFIXCODEID uniqueidentifier = null output,
    @MAIDENNAME nvarchar(100) = null output,
    @NICKNAME nvarchar(50) = null output,
    @BIRTHDATE dbo.UDT_FUZZYDATE = null output,
    @ADDRESSID uniqueidentifier = null output,
    @ADDRESSTYPECODEID uniqueidentifier = null output,
    @ADDRESS_ADDRESSBLOCK nvarchar(150) = null output,
    @ADDRESS_CITY nvarchar(50) = null output,
    @ADDRESS_STATEID uniqueidentifier = null output,
    @ADDRESS_POSTCODE nvarchar(12) = null output,
    @ADDRESS_COUNTRYID uniqueidentifier = null output,
    @PHONEID uniqueidentifier = null output,
    @PHONETYPECODEID uniqueidentifier = null output,
    @PHONENUMBER nvarchar(100) = null output,
    @EMAILID uniqueidentifier = null output,
    @EMAILADDRESSTYPECODEID uniqueidentifier = null output,
    @EMAILADDRESS dbo.UDT_EMAILADDRESS = null output,
    @CREATEDON datetime = null output,
    @DATECHANGED datetime = null output,
    @ADDRESSES xml = null output,
    @PHONES xml = null output,
    @EMAILS xml = null output,
    @SPOUSENAME nvarchar(154) = null output,
    @SPOUSELOOKUPID nvarchar(50) = null output,
    @SPOUSESTARTDATE datetime = null output,
    @SPOUSEENDDATE datetime = null output,
    @HOUSEHOLDNAME nvarchar(154) = null output,
    @HOUSEHOLDLOOKUPID nvarchar(50) = null output,
    @BUSINESSNAME nvarchar(154) = null output,
    @BUSINESSLOOKUPID nvarchar(50) = null output
    )
as
declare @ISGROUP bit,
    @ISORGANIZATION bit;

set nocount on;
-- be sure to set this, in case the select returns no rows
set @DATALOADED = 0;

-- populate the output parameters, which correspond to fields on the form.  Note that
-- we set @DATALOADED = 1 to indicate that the load was successful.  Otherwise, the system
-- will display a "no data loaded" message.
--CONSTIT FIELDS
select @DATALOADED = 1,
    @ISORGANIZATION = CONSTITUENT.ISORGANIZATION,
    @ISGROUP = CONSTITUENT.ISGROUP,
    @FIRSTNAME = CONSTITUENT.FIRSTNAME,
    @MIDDLENAME = CONSTITUENT.MIDDLENAME,
    @LASTNAME = CONSTITUENT.KEYNAME,
    @SUFFIXCODEID = CONSTITUENT.SUFFIXCODEID,
    @MAIDENNAME = CONSTITUENT.MAIDENNAME,
    @NICKNAME = CONSTITUENT.NICKNAME,
    @BIRTHDATE = CONSTITUENT.BIRTHDATE,
    @LOOKUPID = CONSTITUENT.LOOKUPID,
    @CREATEDON = CONSTITUENT.DATEADDED,
    @DATECHANGED = CONSTITUENT.DATECHANGED
from dbo.CONSTITUENT
where CONSTITUENT.ID = @ID;

--EMAIL
declare @EMAILTABLE table (
    EMAILADDRESS dbo.UDT_EMAILADDRESS,
    EMAILADDRESSID uniqueidentifier,
    EMAILADDRESSTYPECODEID uniqueidentifier,
    ISPRIMARY bit,
    STARTDATE datetime,
    ENDDATE datetime
    );

insert into @EMAILTABLE (
    EMAILADDRESS,
    EMAILADDRESSID,
    EMAILADDRESSTYPECODEID,
    ISPRIMARY,
    STARTDATE,
    ENDDATE
    )
select EMAILADDRESS,
    ID,
    EMAILADDRESSTYPECODEID,
    ISPRIMARY,
    STARTDATE,
    ENDDATE
from dbo.EMAILADDRESS
where EMAILADDRESS.CONSTITUENTID = @ID;

select @EMAILID = EMAILADDRESSID,
    @EMAILADDRESSTYPECODEID = EMAILADDRESSTYPECODEID,
    @EMAILADDRESS = EMAILADDRESS
from @EMAILTABLE
where ISPRIMARY = 1;

set @EMAILS = (
        select EMAILADDRESSID,
            EMAILADDRESSTYPECODEID,
            EMAILADDRESS,
            STARTDATE,
            ENDDATE,
            ISPRIMARY
        from @EMAILTABLE
        order by ISPRIMARY desc
        for xml raw('ITEM'),
            type,
            elements,
            root('EMAILS'),
            binary BASE64
        );

--PHONE    
declare @PHONETABLE table (
    PHONENUMBER nvarchar(100),
    PHONEID uniqueidentifier,
    PHONETYPECODEID uniqueidentifier,
    ISPRIMARY bit,
    STARTDATE datetime,
    ENDDATE datetime
    );

insert into @PHONETABLE (
    PHONENUMBER,
    PHONEID,
    PHONETYPECODEID,
    ISPRIMARY,
    STARTDATE,
    ENDDATE
    )
select PHONE.NUMBER,
    PHONE.ID,
    PHONE.PHONETYPECODEID,
    PHONE.ISPRIMARY,
    PHONE.STARTDATE,
    PHONE.ENDDATE
from dbo.PHONE
where PHONE.CONSTITUENTID = @ID;

-- get primary phone
select @PHONEID = PHONEID,
    @PHONETYPECODEID = PHONETYPECODEID,
    @PHONENUMBER = PHONENUMBER
from @PHONETABLE
where ISPRIMARY = 1;

set @PHONES = (
        select PHONEID,
            PHONETYPECODEID,
            PHONENUMBER,
            STARTDATE,
            ENDDATE,
            ISPRIMARY
        from @PHONETABLE
        order by ISPRIMARY desc
        for xml raw('ITEM'),
            type,
            elements,
            root('PHONES'),
            binary BASE64
        );

--ADDRESS
declare @ADDRESSTABLE table (
    ADDRESSID uniqueidentifier,
    ADDRESSTYPECODEID uniqueidentifier,
    ADDRESSBLOCK nvarchar(100),
    CITY nvarchar(100),
    STATEID uniqueidentifier,
    POSTCODE nvarchar(12),
    ISPRIMARY bit,
    STARTDATE dbo.UDT_MONTHDAY,
    ENDDATE dbo.UDT_MONTHDAY,
    COUNTRYID uniqueidentifier,
    HISTORICALSTARTDATE datetime,
    HISTORICALENDDATE datetime
    );

insert into @ADDRESSTABLE (
    ADDRESSID,
    ADDRESSTYPECODEID,
    ADDRESSBLOCK,
    CITY,
    STATEID,
    POSTCODE,
    ISPRIMARY,
    STARTDATE,
    ENDDATE,
    COUNTRYID,
    HISTORICALSTARTDATE,
    HISTORICALENDDATE
    )
select ADDRESS.ID,
    ADDRESS.ADDRESSTYPECODEID,
    ADDRESS.ADDRESSBLOCK,
    ADDRESS.CITY,
    ADDRESS.STATEID,
    ADDRESS.POSTCODE,
    ISPRIMARY,
    STARTDATE,
    ENDDATE,
    COUNTRYID,
    HISTORICALSTARTDATE,
    HISTORICALENDDATE
from dbo.ADDRESS
where ADDRESS.CONSTITUENTID = @ID;

-- get primary address
select @ADDRESSID = ADDRESSID,
    @ADDRESSTYPECODEID = ADDRESSTYPECODEID,
    @ADDRESS_ADDRESSBLOCK = ADDRESSBLOCK,
    @ADDRESS_CITY = CITY,
    @ADDRESS_STATEID = STATEID,
    @ADDRESS_POSTCODE = POSTCODE,
    @ADDRESS_COUNTRYID = COUNTRYID
from @ADDRESSTABLE
where ISPRIMARY = 1;

set @ADDRESSES = (
        select ADDRESSID,
            ADDRESSTYPECODEID,
            ADDRESSBLOCK,
            CITY,
            STATEID,
            POSTCODE,
            STARTDATE,
            ENDDATE,
            ISPRIMARY,
            COUNTRYID,
            HISTORICALSTARTDATE,
            HISTORICALENDDATE
        from @ADDRESSTABLE
        order by ISPRIMARY desc
        for xml raw('ITEM'),
            type,
            elements,
            root('ADDRESSES'),
            binary BASE64
        );

-- spouse information
select @SPOUSENAME = CONSTITUENT.name,
    @SPOUSELOOKUPID = CONSTITUENT.LOOKUPID,
    @SPOUSESTARTDATE = RELATIONSHIP.STARTDATE,
    @SPOUSEENDDATE = RELATIONSHIP.ENDDATE
from dbo.RELATIONSHIP
inner join dbo.CONSTITUENT on RELATIONSHIP.RECIPROCALCONSTITUENTID = CONSTITUENT.ID
where RELATIONSHIP.RELATIONSHIPCONSTITUENTID = @ID and RELATIONSHIP.ISSPOUSE = 1;

-- household information
if @ISORGANIZATION = 0 and @ISGROUP = 0
    select top (1) @HOUSEHOLDNAME = CG.name,
        @HOUSEHOLDLOOKUPID = CG.LOOKUPID
    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;

-- get primary business
if @ISORGANIZATION = 0 and @ISGROUP = 0
    select @BUSINESSNAME = [ORG].KEYNAME,
        @BUSINESSLOOKUPID = [ORG].LOOKUPID
    from dbo.RELATIONSHIP
    inner join dbo.CONSTITUENT as [ORG] on [ORG].ID = RELATIONSHIP.RECIPROCALCONSTITUENTID
    where RELATIONSHIP.RELATIONSHIPCONSTITUENTID = @ID and RELATIONSHIP.ISPRIMARYBUSINESS = 1;

return 0;