USP_PARTIALINFORMATIONSEARCH_ADDREALESTATERECORDTOCONSTITUENT

create procedure dbo.USP_PARTIALINFORMATIONSEARCH_ADDREALESTATERECORDTOCONSTITUENT
(
    @ID uniqueidentifier = null output,
    @CHANGEAGENTID uniqueidentifier = null,
    @RECORDID uniqueidentifier,
    @CONSTITUENTID uniqueidentifier = null,
    @PARTIALHASH nvarchar(32) = null
)
as

set nocount on;

if @ID is null
    set @ID = newid()

if @CHANGEAGENTID is null  
    exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output

declare @CURRENTDATE datetime
set @CURRENTDATE = getdate()

begin try

    if not exists(select 1 from dbo.WEALTH where ID = @CONSTITUENTID)
        insert into dbo.WEALTH
        (
            ID,
            ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
        )
        values
        (
            @CONSTITUENTID,
            @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
        )

    -- handle inserting the data - fields listed in the order they appear in the

    -- WPREALESTATE table spec for convenience

    insert into dbo.WPREALESTATE
    (
        ID, 
        WEALTHID, 
        SOURCE,
        CNOTES,
        BUYERMAILINGADDRESS,
        BUYER,
        OWNER,
        MAILINGADDRESS,
        ASSESSORSPARCELNUMBER, 
        TOTALMARKETVALUE, 
        MARKETVALUEYEAR,
        ASSESSMENTYEAR, 
        ASSESSEDTOTALVALUE,
        PROPADDRESS, 
        PROPCITY, 
        PROPSTATE, 
        PROPZIP,
        OWNERCAREOFNAME,
        ASSESSEENAME, 
        ASSESSEENAME2, 
        ASSESSEECAREOFNAME,
        ASSESSEEMAILINGADDRESS,
        BUYER2,
        BUYERCAREOFNAME,
        ESTIMATEDVALUE, 
        ESTIMATEDVALUEDATE, 
        CONFIDENCESCORE, 
        CONFIDENCESCOREDATE, 
        FIPSCODE,
        PARTIALHASH,
        ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
    )
    select
        @ID, 
        @CONSTITUENTID, 
        'Fidelity Data Services',
        'Added by Prospect Quick Search',
        coalesce(dbo.UFN_PARTIALINFORMATIONSEARCH_BUILDADDRESS(RD.BUYERMAILINGADDRESSBLOCK, RD.BUYERMAILINGADDRESSUNITNUMBER, RD.BUYERMAILINGCITY, RD.BUYERMAILINGSTATE, RD.BUYERMAILINGPOSTCODE), ''),
        coalesce(dbo.UFN_PARTIALINFORMATIONSEARCH_BUILDNAME(RD.BUYERFIRSTNAME, RD.BUYERLASTNAME), ''),
        coalesce(R.OWNERNAME, ''),
        coalesce(dbo.UFN_PARTIALINFORMATIONSEARCH_BUILDADDRESS(R.MAILINGADDRESSBLOCK, R.MAILINGUNITNUMBER, R.MAILINGCITY, R.MAILINGSTATE, R.MAILINGPOSTCODE), ''),
        coalesce(R.ASSESSORSPARCELNUMBER, ''),
        coalesce(RD.TOTALMARKETVALUE, 0), 
        coalesce(RD.MARKETVALUEYEAR, 0),
        coalesce(RD.ASSESSMENTYEAR, 0), 
        coalesce(RD.ASSESSEDTOTALVALUE, 0), 
        case 
            when R.PROPERTYUNITNUMBER is null then coalesce(R.PROPERTYADDRESSBLOCK, '')
            else coalesce(R.PROPERTYADDRESSBLOCK + ' ' + R.PROPERTYUNITNUMBER, '')
        end, 
        coalesce(R.PROPERTYCITY, ''), 
        coalesce(R.PROPERTYSTATE, ''),
        coalesce(R.PROPERTYPOSTCODE, ''),
        coalesce(RD.OWNERCAREOFNAME, ''),
        coalesce(RD.ASSESSEENAME, ''),
        coalesce(RD.ASSESSEENAME2, ''),
        coalesce(RD.ASSESSEECAREOFNAME, ''),
        coalesce(dbo.UFN_PARTIALINFORMATIONSEARCH_BUILDADDRESS(RD.ASSESSEEMAILINGADDRESSBLOCK, RD.ASSESSEEMAILINGADDRESSUNITNUMBER, RD.ASSESSEEMAILINGCITY, RD.ASSESSEEMAILINGSTATE, RD.ASSESSEEMAILINGPOSTCODE), ''),
        coalesce(dbo.UFN_PARTIALINFORMATIONSEARCH_BUILDNAME(RD.BUYER2FIRSTNAME, RD.BUYER2LASTNAME), ''),
        coalesce(RD.BUYERCAREOFNAME, ''),
        coalesce(RD.ESTIMATEDVALUE, 0),
        RD.ESTIMATEDVALUEDATE, 
        coalesce(RD.CONFIDENCESCORE, 0),
        RD.CONFIDENCESCOREDATE, 
        coalesce(R.FIPSCODE, ''),
        @PARTIALHASH,
        @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
    from
        dbo.PARTIALINFORMATIONSEARCHRESULT_REALESTATE R
    left join
        dbo.PARTIALINFORMATIONSEARCHRESULT_REALESTATEDETAIL RD on RD.PARTIALINFORMATIONSEARCHRESULT_REALESTATEID = R.ID
    where
        R.ID = @RECORDID

end try

begin catch
    exec dbo.USP_RAISE_ERROR
    return 1
end catch

return 0