/**
*
*    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 @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
GO
GRANT EXECUTE ON  [dbo].[UpsertARCCustomerContact_XLedger] TO [MssExec]
GO