USP_DATAFORMTEMPLATE_EDIT_ORDERPATRONCONTACTINFO_2

CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_ORDERPATRONCONTACTINFO_2
(
    @ID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier,

    @ADDRESS_ID uniqueidentifier,
    @ADDRESS_ADDRESSTYPECODEID uniqueidentifier,
    @ADDRESS_COUNTRYID uniqueidentifier,
    @ADDRESS_STATEID uniqueidentifier,
    @ADDRESS_ADDRESSBLOCK nvarchar(150),
    @ADDRESS_CITY nvarchar(50),
    @ADDRESS_POSTCODE nvarchar(12),

    @PHONE_ID uniqueidentifier,
    @PHONE_PHONETYPECODEID uniqueidentifier,
    @PHONE_NUMBER nvarchar(100),

    @EMAILADDRESS_ID uniqueidentifier,
    @EMAILADDRESS_EMAILADDRESSTYPECODEID uniqueidentifier,
    @EMAILADDRESS_EMAILADDRESS dbo.UDT_EMAILADDRESS,

    @CONSTITUENTID uniqueidentifier,
    @ISHOUSEHOLDMEMBER bit,
    @UPDATEMATCHINGHOUSEHOLDADDRESSES bit
)
as
    set nocount on;

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

    -- Determine if the records still exist

    if not exists (select 1 from dbo.ADDRESS where ID = @ADDRESS_ID)
        set @ADDRESS_ID = null;

    if not exists (select 1 from dbo.PHONE where ID = @PHONE_ID)
        set @PHONE_ID = null;

    if not exists (select 1 from dbo.EMAILADDRESS where ID = @EMAILADDRESS_ID)
        set @EMAILADDRESS_ID = null;

    declare @CURRENTPRIMARYADDRESSID uniqueidentifier;
    declare @CURRENTPRIMARYPHONEID uniqueidentifier;
    declare @CURRENTPRIMARYEMAILADDRESSID uniqueidentifier;

    select @CURRENTPRIMARYADDRESSID = ID from dbo.ADDRESS where ISPRIMARY = 1 and CONSTITUENTID = @CONSTITUENTID;
    select @CURRENTPRIMARYPHONEID = ID from dbo.PHONE where ISPRIMARY = 1 and CONSTITUENTID = @CONSTITUENTID;
    select @CURRENTPRIMARYEMAILADDRESSID = ID from dbo.EMAILADDRESS where ISPRIMARY = 1 and CONSTITUENTID = @CONSTITUENTID;

    declare @CURRENTDATE datetime;
    set @CURRENTDATE = getdate();

    begin try
        if @ADDRESS_ID is not null begin
            if @UPDATEMATCHINGHOUSEHOLDADDRESSES = 1 begin
                exec dbo.USP_ADDRESS_UPDATEMATCHINGADDRESSES 
                    @ADDRESSID = @ADDRESS_ID,
                    @CHANGEAGENTID = @CHANGEAGENTID,
                    @ADDRESSTYPECODEID =  @ADDRESS_ADDRESSTYPECODEID,
                    @PRIMARY = 1,
                    @DONOTMAIL = 0,
                    @STARTDATE = '0000',
                    @ENDDATE = '0000',
                    @COUNTRYID = @ADDRESS_COUNTRYID,
                    @STATEID = @ADDRESS_STATEID,
                    @ADDRESSBLOCK = @ADDRESS_ADDRESSBLOCK,
                    @CITY = @ADDRESS_CITY,
                    @POSTCODE =  @ADDRESS_POSTCODE,
                    @CART = '',
                    @DPC = '',
                    @LOT = '',
                    @OMITFROMVALIDATION = 0,
                    @COUNTYCODEID = null,
                    @CONGRESSIONALDISTRICTCODEID = null,
                    @STATEHOUSEDISTRICTCODEID = null,
                    @STATESENATEDISTRICTCODEID = null,
                    @LOCALPRECINCTCODEID = null,
                    @INFOSOURCECODEID = null,
                    @REGIONCODEID = null,
                    @LASTVALIDATIONATTEMPTDATE = null,
                    @VALIDATIONMESSAGE = '',
                    @CERTIFICATIONDATA = 0,
                    @DONOTMAILREASONCODEID = null,
                    @HISTORICALSTARTDATE = null,
                    @HISTORICALENDDATE = null,
                    @INFOSOURCECOMMENTS = '',
                    @ISCONFIDENTIAL = 0
            end

            if @ADDRESS_ID <> @CURRENTPRIMARYADDRESSID begin
                update dbo.ADDRESS set
                    ISPRIMARY = 0,
                    CHANGEDBYID = @CHANGEAGENTID,
                    DATECHANGED = @CURRENTDATE
                where
                    ISPRIMARY = 1
                    and CONSTITUENTID = @CONSTITUENTID;
            end

            update dbo.ADDRESS set
                ADDRESSTYPECODEID = @ADDRESS_ADDRESSTYPECODEID,
                COUNTRYID = @ADDRESS_COUNTRYID,
                STATEID = @ADDRESS_STATEID,
                ADDRESSBLOCK = @ADDRESS_ADDRESSBLOCK,
                CITY = @ADDRESS_CITY,
                POSTCODE = @ADDRESS_POSTCODE,
                ISPRIMARY = 1,
                CHANGEDBYID = @CHANGEAGENTID,
                DATECHANGED = @CURRENTDATE
            where
                ID = @ADDRESS_ID;
        end

        else begin
            exec dbo.USP_ADDRESS_ADD 
                @ADDRESS_ID output, 
                @CHANGEAGENTID = @CHANGEAGENTID, 
                @CONSTITUENTID = @CONSTITUENTID, 
                @ADDRESSTYPECODEID = @ADDRESS_ADDRESSTYPECODEID, 
                @PRIMARY  = 1, 
                @DONOTMAIL = 0, 
                @STARTDATE = '0000', 
                @ENDDATE = '0000',
                @COUNTRYID = @ADDRESS_COUNTRYID, 
                @STATEID = @ADDRESS_STATEID, 
                @ADDRESSBLOCK = @ADDRESS_ADDRESSBLOCK, 
                @CITY = @ADDRESS_CITY, 
                @POSTCODE = @ADDRESS_POSTCODE, 
                @UPDATEMATCHINGHOUSEHOLDADDRESSES = @UPDATEMATCHINGHOUSEHOLDADDRESSES;
        end

        if @PHONE_ID is not null begin
            if @PHONE_ID <> @CURRENTPRIMARYPHONEID begin
                update dbo.PHONE set
                    ISPRIMARY = 0,
                    CHANGEDBYID = @CHANGEAGENTID,
                    DATECHANGED = @CURRENTDATE
                where
                    ISPRIMARY = 1
                    and CONSTITUENTID = @CONSTITUENTID;
            end

            update dbo.PHONE set
                PHONETYPECODEID = @PHONE_PHONETYPECODEID,
                NUMBER = @PHONE_NUMBER,
                ISPRIMARY = 1,
                CHANGEDBYID = @CHANGEAGENTID,
                DATECHANGED = @CURRENTDATE
            where
                ID = @PHONE_ID;
        end

        else if (@PHONE_PHONETYPECODEID is not null) or (isnull(@PHONE_NUMBER, '') <> '') begin
            exec dbo.USP_DATAFORMTEMPLATE_ADD_PHONE @PHONE_ID output, @CHANGEAGENTID, @CONSTITUENTID, @PHONE_PHONETYPECODEID,
            @PHONE_NUMBER, 1, 0;
        end

        if @EMAILADDRESS_ID is not null begin
            if @EMAILADDRESS_ID <> @CURRENTPRIMARYEMAILADDRESSID begin
                update dbo.EMAILADDRESS set
                    ISPRIMARY = 0,
                    CHANGEDBYID = @CHANGEAGENTID,
                    DATECHANGED = @CURRENTDATE
                where
                    ISPRIMARY = 1
                    and CONSTITUENTID = @CONSTITUENTID;
            end

            update dbo.EMAILADDRESS set
                EMAILADDRESSTYPECODEID = @EMAILADDRESS_EMAILADDRESSTYPECODEID,
                EMAILADDRESS = @EMAILADDRESS_EMAILADDRESS,
                CHANGEDBYID = @CHANGEAGENTID,
                DATECHANGED = @CURRENTDATE
            where
                ID = @EMAILADDRESS_ID;
        end

        else if (@EMAILADDRESS_EMAILADDRESSTYPECODEID is not null) or (isnull(@EMAILADDRESS_EMAILADDRESS, '') <> '') begin
            exec dbo.USP_DATAFORMTEMPLATE_ADD_EMAILADDRESS @EMAILADDRESS_ID output, @CHANGEAGENTID, @CONSTITUENTID,
            @EMAILADDRESS_EMAILADDRESSTYPECODEID, @EMAILADDRESS_EMAILADDRESS, 1, 0, 0, 0;
        end

        declare @RECIPIENTID uniqueidentifier = null;
        select @RECIPIENTID = [RECIPIENTID] from dbo.[SALESORDER] where [ID] = @ID;

        --Apply this contact information to blank delivery fields on the order if the recipient is the constituent

        if @CONSTITUENTID = @RECIPIENTID begin
            update dbo.[SALESORDER] set
                [ADDRESSID] = case when [ADDRESSID] is null then @ADDRESS_ID else [ADDRESSID] end,
                [PHONEID] = case when [PHONEID] is null then @PHONE_ID else [PHONEID] end,
                [EMAILADDRESSID] = case when [EMAILADDRESSID] is null then @EMAILADDRESS_ID else [EMAILADDRESSID] end,
                [CHANGEDBYID] = @CHANGEAGENTID,
                [DATECHANGED] = @CURRENTDATE
            where
                [ID] = @ID;
        end

        -- recalculate discounts and taxes in accordance with the address updates

        exec dbo.USP_SALESORDER_RECALCULATETAXESFEESDISCOUNTS @ID, @CHANGEAGENTID
    end try

    begin catch
        exec dbo.USP_RAISE_ERROR;
        return 1;
    end catch

    return 0;