/*
*    Creates a synonym for getting the customer phone information by customer number and address code
*    broken down into area code, local number and optional extension information.
*    The GetCustomerAddressInformation_Synonym will be set to use the appropriate function for the current
*    system, based upon if they have XLedger, GP, QuickBooks or no accounting system.
*
*/
CREATE PROCEDURE [dbo].[GetCustomerPhone1AreaCodeLocalNbrExt_XLedger]
    @inCustomerNumber varchar(15),
    @inCustomerAddressCode varchar(15)
as
set nocount on

declare @thePhone varchar(30)
declare @theAreaCode PhoneAreaCode
declare @theLocalNumber PhoneLocalNumber
declare @thePhoneExtension PhoneExtension
declare @theIndex int
declare @theIndex2 int

-- In XLedger, phones only exist on the AccountingCustomer
-- so the address code doesn't play a role.
select
    @thePhone =  rtrim( ltrim( AccountingCustomer.Phone1 ) )
from AccountingCustomer
where AccountingCustomer.CustomerNumber = @inCustomerNumber

if( @thePhone != '' )
begin
    set @theIndex = charindex( 'ext', @thePhone )
    if( @theIndex > 7 )
    begin
        set @thePhoneExtension = ltrim( rtrim( substring( @thePhone, @theIndex + 3, 30 ) ) )
        set @thePhone = ltrim( rtrim( substring( @thePhone, 1, @theIndex - 1 ) ) )
    end
    else
    begin
        set @theIndex = charindex( 'ex', @thePhone )
        if( @theIndex > 7 )
        begin
            set @thePhoneExtension = ltrim( rtrim( substring( @thePhone, @theIndex + 2, 30 ) ) )
            set @thePhone = ltrim( rtrim( substring( @thePhone, 1, @theIndex - 1 ) ) )
        end
        else
        begin
            set @theIndex = charindex( 'x', @thePhone )
            if( @theIndex > 7 )
            begin
                set @thePhoneExtension = ltrim( rtrim( substring( @thePhone, @theIndex + 1, 30 ) ) )
                set @thePhone = ltrim( rtrim( substring( @thePhone, 1, @theIndex - 1 ) ) )
            end
        end
    end

    if( len( @thePhone ) = 10 )
    begin
        set @theAreaCode = substring( @thePhone, 1, 3 )
        set @theLocalNumber = concat( substring( @thePhone, 4, 3 ), '-', substring( @thePhone, 7, 4 ) )
        set @thePhone = ''
    end
    else if( len( @thePhone ) = 7 )
    begin
        set @theAreaCode = null
        set @theLocalNumber = concat( substring( @thePhone, 1, 3 ), '-', substring( @thePhone, 4, 4 ) )
        set @thePhone = ''
    end
    else if( len( @thePhone ) = 8 and substring( @thePhone, 4, 1 ) = '-' )
    begin
        set @theAreaCode = null
        set @theLocalNumber = @thePhone
        set @thePhone = ''
    end
    else if( len( @thePhone ) >= 12 and substring( @thePhone, 4, 1 ) = '-' and substring( @thePhone, 8, 1 ) = '-' )
    begin
        set @theAreaCode = substring( @thePhone, 1, 3 )
        set @theLocalNumber = substring( @thePhone, 5, 8 )
        set @thePhone = ltrim( rtrim( substring( @thePhone, 13, 30 ) ) )
    end
    else
    begin
        set @theIndex = charindex( '(', @thePhone )
        set @theIndex2 = charindex( ')', @thePhone )
        if( @theIndex > 0 and @theIndex2 > @theIndex )
        begin
            set @theAreaCode = ltrim( rtrim( substring( @thePhone, @theIndex + 1, @theIndex2 - @theIndex - 1 ) ) )
            set @thePhone = ltrim( rtrim( substring( @thePhone, @theIndex2 + 1, 30 ) ) )
        end
        else
        begin
            set @theIndex = charindex( '-', @thePhone )
            set @theIndex2 = charindex( '-', @thePhone, @theIndex + 1 )
            if( @theIndex > 0 and @theIndex2 > @theIndex )
            begin
                set @theAreaCode = ltrim( rtrim( substring( @thePhone, 1, @theIndex - 1 ) ) )
                set @thePhone = ltrim( rtrim( substring( @thePhone, @theIndex + 1, 30 ) ) )
            end
        end


        if( len( @thePhone ) >= 8 and substring( @thePhone, 4, 1 ) = '-' )
        begin
            set @theLocalNumber = substring( @thePhone, 1, 8 )
            set @thePhone = ltrim( rtrim( substring( @thePhone, 9, 30 ) ) )
        end
        else if( len( @thePhone ) >= 7 )
        begin
            set @theLocalNumber = concat( substring( @thePhone, 1, 3 ), '-', substring( @thePhone, 4, 4 ) )
            set @thePhone = ltrim( rtrim( substring( @thePhone, 8, 30 ) ) )
        end
    end

    if( isnull( @theLocalNumber, '' ) = '' )
    begin
        set @theLocalNumber = @thePhone
    end
    else if( isnull( @thePhoneExtension, '' ) = '' )
    begin
        set @thePhoneExtension = @thePhone
    end

    if( isnull( @thePhoneExtension, '' ) != '' )
    begin
        if( substring( @thePhoneExtension, 1, 1 ) = '.' )
        begin
            set @thePhoneExtension = ltrim( rtrim( substring( @thePhoneExtension, 2, 30 ) ) )
        end
        if( substring( @thePhoneExtension, 1, 1 ) = 'x' )
        begin
            set @thePhoneExtension = ltrim( rtrim( substring( @thePhoneExtension, 2, 30 ) ) )
        end
        if( substring( @thePhoneExtension, 1, 1 ) = 'x' )
        begin
            set @thePhoneExtension = ltrim( rtrim( substring( @thePhoneExtension, 2, 30 ) ) )
        end
    end
    set @thePhoneExtension = ltrim( rtrim( case
        when isnull( @thePhoneExtension, '' ) = '' then null
        when substring( @thePhoneExtension, 1, 1 ) = 'x' then substring( @thePhoneExtension, 2, 30 )
        else @thePhoneExtension
    end ) )
end

-- Return the specified Phone1 in its parts (as best we can)
select
    AreaCode = @theAreaCode,
    LocalNumber = @theLocalNumber,
    Extension = @thePhoneExtension
GO
GRANT EXECUTE ON  [dbo].[GetCustomerPhone1AreaCodeLocalNbrExt_XLedger] TO [MssExec]
GO