CREATE procedure [dbo].[BcImportCustomers]
    @items BcCustomerImportItems readonly,
    @addresses BcAddressImportItems readonly
as
begin
set nocount on

    --[BcCustomerId] int not null identity(1,1),
    --[BcId] uniqueidentifier not null,
    --[AccountingCustomerFid] int not null,
    --[CreatedOn] datetimeoffset not null, -- the date on which we first imported it.
    --[LastImportedOn] datetimeoffset not null,
    --[LastUpdatedInBcOn] datetimeoffset not null, -- last modified date in Bc



    --BCTD:  Possibly follow XLedger's approach of importing payment terms etc here.

    declare @AccountingCustomerIdHolder table
    (
        AccountingCustomerFid int not null,
        BcId uniqueidentifier not null,
        LastUpdatedInBcOn datetimeoffset not null
    )

    declare @AccountingCustomerAddressIdHolder table
    (
        AccountingCustomerAddressFid int not null,
        AddressBcId uniqueidentifier not null,
        AddressCode varchar(64) null
    )

    --declare @bob IntList


    --select Item42 from @bob

    ;with
    MergeData as
    (
        select
            BcCustomer.AccountingCustomerFid,
            --BcCustomerClass.AccountingCustomerClassFid,
            --BcCustomerPaymentTerms.AccountingCustomerPaymentTermsFid,
            Items.BcId,
            --Items.SubLedgerGroupExternalId,
            Items.[Name],
            Items.CustomerNumber,
            Items.Email,
            --Items.OnHold,
            Items.CreditLimit,
            Items.LastModified
            --Items.NoCreditFlag,
            --Items.Phone1,
            --Items.Phone2,
            --Items.Phone3,
            --Items.Fax,
            --Items.Contact,
            --Items.TaxExempt
        from @items Items
        left outer join BcCustomer on Items.BcId = BcCustomer.BcId
        --left outer join BcCustomerClass on Items.SubLedgerGroupExternalId = BcCustomerClass.BcId
        --left outer join BcCustomerPaymentTerms on Items.PaymentTermsExternalId = BcCustomerPaymentTerms.BcId
    )
    merge AccountingCustomer with (tablock) using MergeData on
        MergeData.AccountingCustomerFid = AccountingCustomer.AccountingCustomerId
    when matched then update set
        [Name] = MergeData.[Name],
        CustomerNumber = MergeData.CustomerNumber,
        Email = MergeData.Email,
        --OnHold = MergeData.OnHold,
        OnHold = 0, -- BCTD:  How are we going to interact with on hold?
        CreditLimit = MergeData.CreditLimit,
        --NoCreditFlag = MergeData.NoCreditFlag,
        NoCreditFlag = 0
        --Phone1 = MergeData.Phone1,
        --Phone2 = MergeData.Phone2,
        --Phone3 = MergeData.Phone3,
        --Fax = MergeData.Fax,
        --Contact = MergeData.Contact,
        --TaxExempt = MergeData.TaxExempt,
        --AccountingCustomerClassFid = MergeData.AccountingCustomerClassFid,
        --AccountingCustomerPaymentTermsFid = MergeData.AccountingCustomerPaymentTermsFid
    when not matched then
        insert
        (
            [Name],
            CustomerNumber,
            Email,
            OnHold,
            CreditLimit,
            NoCreditFlag
            --Phone1,
            --Phone2,
            --Phone3,
            --Fax,
            --Contact,
            --TaxExempt,
            --AccountingCustomerClassFid,
            --AccountingCustomerPaymentTermsFid
        )
        values
        (
            MergeData.[Name],
            MergeData.CustomerNumber,
            MergeData.Email,
            0,--OnHold - (BCTD:  deal with OnHold)
            MergeData.CreditLimit,
            --MergeData.NoCreditFlag,
            0 -- NoCreditFlag

            --MergeData.Phone1,
            --MergeData.Phone2,
            --MergeData.Phone3,
            --MergeData.Fax,
            --MergeData.Contact,
            --MergeData.TaxExempt,
            --MergeData.AccountingCustomerClassFid,
            --MergeData.AccountingCustomerPaymentTermsFid
        )
    output
        inserted.AccountingCustomerId,
        MergeData.BcId,
        MergeData.LastModified
    into @AccountingCustomerIdHolder;


    declare @now datetimeoffset = sysdatetimeoffset()

    --select Bob from Orders
        -- Step 3b: Use the 3a merge output table to update XLedgerCustomer table.
    ;with
    MergeData as
    (
        select
            AccountingCustomerIdHolder.BcId,
            AccountingCustomerIdHolder.AccountingCustomerFid,
            LastUpdatedInBcOn

        from @AccountingCustomerIdHolder as AccountingCustomerIdHolder
    )
    merge BcCustomer with (tablock) using MergeData on
        MergeData.BcId = BcCustomer.BcId
    when matched then update set
        LastImportedOn = @now
    when not matched then
        insert
        (
            BcId,
            AccountingCustomerFid,
            CreatedOn,
            LastImportedOn,
            LastUpdatedInBcOn
        )
        values
        (
            MergeData.BcId,
            MergeData.AccountingCustomerFid,
            @now, -- CreatedOn
            @now, -- LastImportedOn
            MergeData.LastUpdatedInBcOn
        );


            -- Step 4: Deal with AccountingCustomerAddress (multiple addresses per customer)


    insert into AccountingCustomerAddressType(TypeName)
    select Addresses.Code
    from @addresses Addresses
    except
    select AccountingCustomerAddressType.TypeName
    from AccountingCustomerAddressType

    ;with MergeData as
    (
        select
            AccountingCustomer.AccountingCustomerId,
            AccountingCustomerAddressType.AccountingCustomerAddressTypeId,
            AddressDeleted = convert(bit, case when Addresses.ParentBcId is null then 1 else 0 end ),
            AddressBcId = Addresses.BcId,
            AddressCode = Addresses.Code,
            Addresses.Address1,
            Addresses.Address2,
            Addresses.City,
            Addresses.State,
            Addresses.PostalCode,
            BcCustomerAddress.AccountingCustomerAddressFid,
            CountryCodeStandard.CountryCodeStandardID
        from @items Items
        inner join BcCustomer on Items.BcId = BcCustomer.BcId
        inner join AccountingCustomer on BcCustomer.AccountingCustomerFid = AccountingCustomer.AccountingCustomerId
        left outer join @addresses Addresses on BcCustomer.BcId = Addresses.ParentBcId
        left outer join BcCustomerAddress on Addresses.BcId = BcCustomerAddress.BcId
        inner join AccountingCustomerAddressType on Addresses.Code = AccountingCustomerAddressType.TypeName
        left outer join CountryCodeStandard on Addresses.CountryAlpha2Code = CountryCodeStandard.Alpha2Code
    ) merge AccountingCustomerAddress with (tablock) using MergeData ON
        MergeData.AccountingCustomerAddressFid = AccountingCustomerAddressId
    when matched and MergeData.AddressDeleted = 0 then update SET
        AccountingCustomerAddressTypeFid = MergeData.AccountingCustomerAddressTypeId,
        Address1 = MergeData.Address1,
        Address2 = MergeData.Address2,
        City = MergeData.City,
        [State] = MergeData.State,
        [PostalCode] = MergeData.PostalCode,
        [CountryCodeStandardFID] = MergeData.CountryCodeStandardID
    when not matched and AddressDeleted = 0 then insert (
            AccountingCustomerFid,
            AccountingCustomerAddressTypeFid,
            Address1,
            Address2,
            City,
            State,
            PostalCode,
            CountryCodeStandardFID
    ) values (
        MergeData.AccountingCustomerId,
        MergeData.AccountingCustomerAddressTypeId,
        MergeData.Address1,
        MergeData.Address2,
        MergeData.City,
        MergeData.State,
        MergeData.PostalCode,
        MergeData.CountryCodeStandardID
    )
    output
        inserted.AccountingCustomerAddressId,
        MergeData.AddressBcId,
        MergeData.AddressCode
    into @AccountingCustomerAddressIdHolder;

    ;with MergeData as
    (
        select
            AccountingCustomerAddressIdHolder.AccountingCustomerAddressFid,
            AccountingCustomerAddressIdHolder.AddressBcId,
            AccountingCustomerAddressIdHolder.AddressCode
        from @AccountingCustomerAddressIdHolder AccountingCustomerAddressIdHolder
    )
    merge BcCustomerAddress with(tablock) using MergeData on
        MergeData.AddressBcId = BcCustomerAddress.BcId
    when matched then update
        set LastImportedOn = @now
    when not matched then
        insert
        (
            BcId,
            AccountingCustomerAddressFid,
            CreatedOn,
            LastImportedOn
        )
        values
        (
            MergeData.AddressBcId,
            MergeData.AccountingCustomerAddressFid,
            @now,
            @now
        );

    -- Step 5: Make sure every AccountingCustomer has an AccountingCustomerAddress of the default type.
    -- This ensures that every customer has a default address, which is an assurance that Dynamics
    -- GP gave us.
    insert into AccountingCustomerAddress
    (
        AccountingCustomerFid,
        AccountingCustomerAddressTypeFid,
        CountryCodeStandardFID
    )
    select
        AccountingCustomerFid = BcCustomer.AccountingCustomerFid,
        AccountingCustomerAddressTypeFid = AccountingCustomerAddressType.AccountingCustomerAddressTypeId,
        CountryCodeStandardFID = dbo.udfGetDefaultCountryCodeStandardID()
    from @items as Items
    inner join BcCustomer on Items.BcId = BcCustomer.BcId
    inner join AccountingCustomerAddressType on AccountingCustomerAddressType.TypeName = dbo.GetDefaultAccountingCustomerAddressTypeName()
    left outer join AccountingCustomerAddress on AccountingCustomerAddress.AccountingCustomerFid = BcCustomer.AccountingCustomerFid and
        AccountingCustomerAddress.AccountingCustomerAddressTypeFid = AccountingCustomerAddressType.AccountingCustomerAddressTypeId
    where AccountingCustomerAddress.AccountingCustomerAddressId is null

end
GO
GRANT EXECUTE ON  [dbo].[BcImportCustomers] TO [MssExec]
GO