/**
*
*    Inserts or updates a single customer contact for a specific customer number.  This is used by the
*    AR Credit and Collections module to add or modify customer contacts.
*
*    Do NOT call this SP directly.  Instead, call UpsertARCCustomerContact_Synonym and then that
*    synonym knows to call this SP or another one based upon that system's active accounting
*    system type.
*/

CREATE procedure [dbo].[UpsertARCCustomerContact_XLedger]
    @inCustomerNumber varchar(15),
    @inCustomerAddressCode varchar(15),
    @inARCCustomerContactId int,
    @inContactPerson nvarchar(64),
    @inEmailAddress nvarchar(256),
    @inAddress1 dbo.Address,
    @inAddress2 dbo.Address,
    @inAddress3 dbo.Address,
    @inCity dbo.AddressCity,
    @inState dbo.AddressState,
    @inPostalCode dbo.AddressPostalCode,
    @inAddressCountryCodeStandardFID int,
    @inPhone1CountryCodeStandardFID int,
    @inPhone1AreaCode dbo.PhoneAreaCode,
    @inPhone1LocalNumber dbo.PhoneLocalNumber,
    @inPhone1Extension dbo.PhoneExtension,
    @inPhone2CountryCodeStandardFID int,
    @inPhone2AreaCode dbo.PhoneAreaCode,
    @inPhone2LocalNumber dbo.PhoneLocalNumber,
    @inPhone2Extension dbo.PhoneExtension,
    @inPhone3CountryCodeStandardFID int,
    @inPhone3AreaCode dbo.PhoneAreaCode,
    @inPhone3LocalNumber dbo.PhoneLocalNumber,
    @inPhone3Extension dbo.PhoneExtension,
    @inFaxCountryCodeStandardFID int,
    @inFaxAreaCode dbo.PhoneAreaCode,
    @inFaxLocalNumber dbo.PhoneLocalNumber
as
begin
    set nocount on

    declare @theErrorMessage varchar(max)
    set xact_abort on
    begin try
        declare @theARCCustomerContactId int
        declare @theAccountingCustomerId int = (
            select AccountingCustomer.AccountingCustomerId
            from AccountingCustomer
            where AccountingCustomer.CustomerNumber = @inCustomerNumber
        )
        declare @theTypeId int
        declare @thePhoneIndex int = 0
        declare @theMaxPhoneIndex int = 4
        declare @theRecordCount int

        declare @thePhoneType varchar(8)
        declare @theCountryCodeStandardFID int
        declare @theAreaCode dbo.PhoneAreaCode
        declare @theLocalNumber dbo.PhoneLocalNumber
        declare @theExtension dbo.PhoneExtension

        -- We can't save anything unless there is a valid AccountingCustomer to link the
        -- contact to.
        if( @theAccountingCustomerId is not null )
        begin

            -- Deal with Customer Contact first.
            if( isnull( @inARCCustomerContactId, 0 ) = 0 )
            begin
                insert into ARCCustomerContact
                (
                    AccountingCustomerFid,
                    ContactPerson,
                    EmailAddress
                )
                select
                    AccountingCustomerFid = AccountingCustomer.AccountingCustomerId,
                    ContactPerson = isnull( @inContactPerson, '' ),
                    EmailAddress = @inEmailAddress
                from AccountingCustomer
                where AccountingCustomer.AccountingCustomerId = @theAccountingCustomerId

                set @theRecordCount = @@ROWCOUNT

                if( @theRecordCount = 1 )
                begin
                    set @theARCCustomerContactId = scope_identity()
                end
            end
            else --if( @inARCCustomerContactId > 0 )
            begin
                update ARCCustomerContact set
                    ContactPerson = isnull( @inContactPerson, '' ),
                    EmailAddress = @inEmailAddress
                from ARCCustomerContact
                where ARCCustomerContact.AccountingCustomerFid = @theAccountingCustomerId and
                    ARCCustomerContact.ARCCustomerContactId = @inARCCustomerContactId

                set @theRecordCount = @@ROWCOUNT

                if( @theRecordCount = 1 )
                begin
                    set @theARCCustomerContactId = @inARCCustomerContactId
                end
            end

            -- Now, deal with the Customer Contact Address
            if( @theARCCustomerContactId is not null )
            begin
                set @theTypeId = ( select AddressTypeId from AddressType where AddressType.TypeName = 'Main' )

                update ARCCustomerContactAddress set
                    Address1 = @inAddress1,
                    Address2 = @inAddress2,
                    Address3 = @inAddress3,
                    City = @inCity,
                    [State] = @inState,
                    PostalCode = @inPostalCode,
                    CountryCodeStandardFid = @inAddressCountryCodeStandardFID
                from ARCCustomerContactAddress
                where ARCCustomerContactAddress.ARCCustomerContactFid = @theARCCustomerContactId and
                    ARCCustomerContactAddress.AddressTypeFid = @theTypeId

                set @theRecordCount = @@ROWCOUNT

                if( @theRecordCount = 0 )
                begin
                    insert into ARCCustomerContactAddress
                    (
                        ARCCustomerContactFid,
                        AddressTypeFid,
                        Address1,
                        Address2,
                        Address3,
                        City,
                        [State],
                        PostalCode,
                        CountryCodeStandardFid
                    )
                    select
                        ARCCustomerContactFid = @theARCCustomerContactId,
                        AddressTypeFid = @theTypeId,
                        Address1 = @inAddress1,
                        Address2 = @inAddress2,
                        Address3 = @inAddress3,
                        City = @inCity,
                        [State] = @inState,
                        PostalCode = @inPostalCode,
                        CountryCodeStandardFid = @inAddressCountryCodeStandardFID
                    where not exists (
                        select top 1 1
                        from ARCCustomerContactAddress
                        where ARCCustomerContactFid = @theARCCustomerContactId and
                            AddressTypeFid = @theTypeId
                    )
                end

                -- Loop through the four possible phone types
                while( @thePhoneIndex < @theMaxPhoneIndex )
                begin
                    select
                        @thePhoneType = case @thePhoneIndex
                            when 0 then 'Phone1'
                            when 1 then 'Phone2'
                            when 2 then 'Phone3'
                            else 'Fax'
                        end,
                        @theCountryCodeStandardFID = case @thePhoneIndex
                            when 0 then @inPhone1CountryCodeStandardFID
                            when 1 then @inPhone2CountryCodeStandardFID
                            when 2 then @inPhone3CountryCodeStandardFID
                            else @inFaxCountryCodeStandardFID
                        end,
                        @theAreaCode = case @thePhoneIndex
                            when 0 then @inPhone1AreaCode
                            when 1 then @inPhone2AreaCode
                            when 2 then @inPhone3AreaCode
                            else @inFaxAreaCode
                        end,
                        @theLocalNumber = case @thePhoneIndex
                            when 0 then @inPhone1LocalNumber
                            when 1 then @inPhone2LocalNumber
                            when 2 then @inPhone3LocalNumber
                            else @inFaxLocalNumber
                        end,
                        @theExtension = case @thePhoneIndex
                            when 0 then @inPhone1Extension
                            when 1 then @inPhone2Extension
                            when 2 then @inPhone3Extension
                            else null
                        end

                    set @theTypeId = ( select ARCCustomerContactPhoneTypeId from ARCCustomerContactPhoneType where TypeName = @thePhoneType )

                    update ARCCustomerContactPhoneNumber set
                        CountryCodeStandardFid = @theCountryCodeStandardFID,
                        AreaCode = @theAreaCode,
                        LocalNumber = @theLocalNumber,
                        Extension = rtrim( ltrim( @theExtension ) )
                    from ARCCustomerContactPhoneNumber
                    where ARCCustomerContactPhoneNumber.ARCCustomerContactFid = @theARCCustomerContactId and
                        ARCCustomerContactPhoneNumber.ARCCustomerContactPhoneTypeFid = @theTypeId

                    set @theRecordCount = @@ROWCOUNT

                    -- We must have at least a local number in order to add a new phone number.
                    if( @theRecordCount = 0 and isnull( @theLocalNumber, '' ) != '' )
                    begin
                        insert into ARCCustomerContactPhoneNumber
                        (
                            ARCCustomerContactFid,
                            ARCCustomerContactPhoneTypeFid,
                            CountryCodeStandardFid,
                            AreaCode,
                            LocalNumber,
                            Extension
                        )
                        select
                            ARCCustomerContactFid = @theARCCustomerContactId,
                            ARCCustomerContactPhoneTypeFid = @theTypeId,
                            CountryCodeStandardFid = @theCountryCodeStandardFID,
                            AreaCode = @theAreaCode,
                            LocalNumber = @theLocalNumber,
                            Extension = rtrim( ltrim( @theExtension ) )
                        where not exists (
                            select top 1 1
                            from ARCCustomerContactPhoneNumber
                            where ARCCustomerContactFid = @theARCCustomerContactId and
                                ARCCustomerContactPhoneTypeFid = @theTypeId
                        )
                    end

                    set @thePhoneIndex = @thePhoneIndex + 1
                end
            end
        end
    end try
    begin catch
        set @theErrorMessage = concat( 'There was a problem running UpsertARCCustomerContact_XLedger. ',
            'Error: ', ltrim( str( error_number() ) ), ' ', error_message(), ' ',
            'Line(', ltrim( str( error_line() ) ), ')' )

        raiserror ( @theErrorMessage, 16, 1 )
    end catch
end
GO
GRANT EXECUTE ON  [dbo].[UpsertARCCustomerContact_XLedger] TO [MssExec]
GO