USE [viberu.ru.data01]
GO
/****** Object:  StoredProcedure [dbo].[MergeOffices]    Script Date: 8/15/2019 3:10:38 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[MergeOffices]

@bankRegNumber int

as
declare @bankId uniqueidentifier;
    select @bankId = Id
    from [viberu.ru].[dbo].Banks b
    where
    b.RegNumber = @bankRegNumber and
    b.NotActive = 0

declare @outputResult table
    (
        [action] nvarchar(30),
        sNotActive bit,
        rNotActive bit,
        IsCashDispenser bit
    )

MERGE [viberu.ru].[dbo].Offices o

--В качестве источника мы берём записи сгруппированные по адресу, признаку банкомата, характеристикам банкомата
--и имени. При этом запись для группы берётся первая попавшаяся и выставляется количество элементов в группе.
--Считаем, что остальные поля не так важны, если это допустим банкомат со снятием наличных, то нам важнее знать что их в этом месте просто 2

USING
    (select d.[Id]
           ,d.[BankRegNumber]
           ,d.[Name]
           ,d.[Address]
           ,d.[CanonizedAddress]
           ,d.[Work]
           ,d.[Phone]
           ,d.[IsCashDispenser]
           ,d.[Latitude]
           ,d.[Longitude]
           ,d.[Updated]
           ,d.[UpdatedWithGeocoder]
           ,d.[RoundTheClockAccess]
           ,d.[CashDeposit]
           ,d.[CashWithdrawal]
           ,d.[Payments]
           ,d.[AdditionalInformation]
           ,d.[Province]
           ,d.[City]
           ,d.[Street]
           ,d.[GeocodingFailed]
           ,d.[IsTerminal]
           ,rowNumber
           ,d2.count as count
           , isnull(d.CashWithdrawal, 0) + isnull(d.CashDeposit, 0) * 2 + isnull(d.Payments, 0) * 4 as AtmOperations
           ,d.[Alias]
           ,d.[RegionId]
    from
    (
         select *,
             ROW_NUMBER() over
             (
                 partition by [CanonizedAddress],
                 [IsCashDispenser],
                 [CashDeposit],
                 [CashWithdrawal],
                 [Payments]
                 order by id
             ) as rowNumber
         from  [viberu.ru.data01].[dbo].[BankOffices] bo
         where ISNULL(bo.UpdatedWithGeocoder, 0) = 1 and
              bo.CanonizedAddress LIKE '%[0-9]%' and
              bo.BankRegNumber = @bankRegNumber
    ) as d
    cross apply
        (
            select count(*) count
            from [viberu.ru.data01].[dbo].[BankOffices] _bo
            where
                  _bo.[CanonizedAddress] = d.CanonizedAddress and
                  _bo.[IsCashDispenser] = d.[IsCashDispenser] and
                  _bo.[CashDeposit] = d.[CashDeposit] and
                  _bo.[CashWithdrawal] = d.[CashWithdrawal] and
                  _bo.[Payments] = d.[Payments] and
                  _bo.[BankRegNumber] = @bankRegNumber
         ) d2
        where d.rowNumber = 1
    ) bo

    --главное условие разделения

    ON
        o.[Address] = bo.CanonizedAddress and
        o.[BankId] = @bankId and
        bo.[IsCashDispenser] = o.IsCashDispenser and
        bo.AtmOperations = o.AtmOperations and
        o.NotActive = 0

    --записи удовлетворяющие главному условию

    WHEN MATCHED THEN
        update set
            [Address] = bo.CanonizedAddress,
            [ExportDbOfficeId] = bo.Id,
            [Name] = bo.Name,
            [WorkInterval] = bo.Work,
            [RoundTheClock] = bo.RoundTheClockAccess,
            [AdditionalInformation] = bo.AdditionalInformation,
            [Updated] = GETDATE(),
            [AtmType] = bo.IsTerminal,
            [Lattitude] = bo.Latitude,
            [Longitude] = bo.Longitude,
            [Count] = bo.count,
            [RegionId] = bo.RegionId

    --записи, которые остались в источнике добавляются в основную базу

    WHEN NOT MATCHED BY TARGET THEN
        insert(
            [Id],
            [BankId],
            [Name],
            [Address],
            [Phone],
            [IsCashDispenser],
            [WorkInterval],
            [Lattitude],
            [Longitude],
            [Alias],
            [Created],
            [Updated],
            [RoundTheClock],
            [AtmOperations],
            [AtmType],
            [CanonizedAddress],
            [ExportDbOfficeId],
            [AdditionalInformation],
            [TempStreet],
            [Count],
            [RegionId]
        )
        values
        (
            NEWID(),
            @bankId,
            bo.Name,
            bo.CanonizedAddress,
            bo.Phone,
            bo.IsCashDispenser,
            bo.Work,
            bo.Latitude,
            bo.Longitude,
            bo.Alias,
            GETDATE(),
            GETDATE(),
            bo.RoundTheClockAccess,
            bo.AtmOperations,
            bo.IsTerminal,
            1,
            bo.Id,
            bo.AdditionalInformation,
            bo.Street,
            bo.count,
            bo.RegionId
        )
    --не обновлённые в эту итерацию записи будут помечены как не активные
    WHEN NOT MATCHED BY SOURCE and o.[BankId] = @bankId THEN
    update set [NotActive] = 1
    output $action, deleted.[NotActive], inserted.[NotActive], inserted.[IsCashDispenser]
    into @outputResult;

    select (
                select COUNT(*) from @outputResult
                where [action] = 'INSERT' and IsCashDispenser = 1
           )
           as [AtmsInserted],
           (
                select COUNT(*) from @outputResult
                where [action] = 'INSERT' and IsCashDispenser = 0
           )
           as [DepartmentsInserted],
           (
                select COUNT(*) from @outputResult
                where [action] = 'UPDATE' and
                rNotActive = 0 and
                sNotActive = 0 and
                IsCashDispenser = 1
           )
           as [AtmsUpdated],
           (
                select COUNT(*) from @outputResult
                where [action] = 'UPDATE' and
                rNotActive = 0 and
                sNotActive = 0 and
                IsCashDispenser = 0
            )
            as [DepartmentsUpdated],
            (
                select COUNT(*) from @outputResult
                where [action] = 'UPDATE' and
                rNotActive = 1 and
                sNotActive = 0 and
                IsCashDispenser = 1
            )
            as [AtmsArchived],
            (
                select COUNT(*) from @outputResult
                where [action] = 'UPDATE' and
                rNotActive = 1 and
                sNotActive = 0 and
                IsCashDispenser = 0
            )
            as [DepartmentsArchived]