/**
*    Initializes the GPWarehouse data when XLedger is the accounting system
*    AND this is the very first time we are building the GPWarehouse for
*    XLedger.  If there are any AcctTransactions and AcctTransactionsApplyToInfos
*    out there for the OLD accounting system (like GP), mark all of those
*    non-XLedger records as fully applied.
*
*    Called from the BuildGPWarehouse_XLedger stored proc if needed.
*
*/

create procedure [dbo].[BuildGPWarehouse_XLedger_Initialize]
    @inSessionId varbinary(128) = null
as
set nocount on

declare @theInitializedGlobalSystemOptionName varchar(50) = 'XledgerFullyAppliedAcctTransactionsInitialized'
declare @theGsoValue varchar(4) = 'true'
declare @theSession varbinary(128) = dbo.GetHashKey( @theInitializedGlobalSystemOptionName, '098dgfs8@hn097a8df@9dsnfbh284609582734613138757474615150346834b8aer' )

if( @inSessionId = @theSession )
begin
    declare @theMaxATPriKey int
    set @theMaxATPriKey = ( select min( AcctTransactionFid ) - 1 from XLedgerJournalEntryBatchItem )
    if( @theMaxATPriKey is null )
    begin
        set @theMaxATPriKey = ( select max( ATPriKey ) from AcctTransactions )
    end

    declare @theSql nvarchar(max)
    declare @theAlterCount int
    declare @theCounter int = 1

    declare @theTableName sysname
    declare @theTablePriKeyColumnName sysname
    declare @theATPriKeyColumnName sysname

    declare @theTablesToAlter table
    (
        Id int identity( 1, 1 ) not null,
        TableName sysname not null,
        TablePriKeyColumnName sysname not null,
        ATPriKeyColumnName sysname not null
    )
    insert into @theTablesToAlter
    (
        TableName,
        TablePriKeyColumnName,
        ATPriKeyColumnName
    )
    select
        TableName = N'AcctTransactions',
        TablePriKeyColumnName = N'ATPriKey',
        ATPriKeyColumnName = N'ATPriKey'
    union all
    select
        TableName = N'AcctTransactionsApplyToInfo',
        TablePriKeyColumnName = N'AcctTransactionsApplyToInfoID',
        ATPriKeyColumnName = N'AcctTransactionsFID'

    set @theAlterCount = @@rowcount

    while( @theCounter <= @theAlterCount )
    begin
        select
            @theTableName = TableName,
            @theTablePriKeyColumnName = TablePriKeyColumnName,
            @theATPriKeyColumnName = ATPriKeyColumnName
        from @theTablesToAlter
        where Id = @theCounter

        -- #theTargetIds table holds the primary keys of ALL the rows we want to update.
        -- #theBatchIds table holds just the records updated in the current batch. In
        -- each loop, remove 4000 rows from #theTargetIds and insert them into #theBatchIds.
        -- Then, use #theBatchIds to update those 4000 rows in the target table. Loop until
        -- #targetIds is empty.  We only update rows that are NOT linked to an
        -- XLedgerJournalEntryBatchItem row.
        set @theSql = concat( N'set nocount on
create table #theTargetIds (PriKey int)
create table #theBatchIds (PriKey int)
declare @theBatchSize int = 4000

insert into #theTargetIds( PriKey )
select
    PriKey = theTableName.', @theTablePriKeyColumnName, N'
from ', @theTableName, N' as theTableName
left outer join XLedgerJournalEntryBatchItem on theTableName.', @theATPriKeyColumnName , N' = XLedgerJournalEntryBatchItem.AcctTransactionFid
where theTableName.', @theATPriKeyColumnName, N' <= ', @theMaxATPriKey, N' and
    theTableName.FullyAppliedTo = 0 and
    XLedgerJournalEntryBatchItem.XLedgerJournalEntryBatchItemId is null
order by theTableName.', @theTablePriKeyColumnName, N'

while( exists( select top 1 1 from #theTargetIds ) )
begin
    delete top (@theBatchSize)
    from #theTargetIds
    output deleted.PriKey into #theBatchIds

    update theTableName set
        FullyAppliedTo = 1
    from ', @theTableName, N' as theTableName
    inner join #theBatchIds theBatchIds ON theTableName.', @theTablePriKeyColumnName, N' = theBatchIds.PriKey
    left outer join XLedgerJournalEntryBatchItem on theTableName.', @theATPriKeyColumnName , N' = XLedgerJournalEntryBatchItem.AcctTransactionFid
    where XLedgerJournalEntryBatchItem.XLedgerJournalEntryBatchItemId is null

    delete from #theBatchIds
end

drop table #theBatchIds
drop table #theTargetIds
' )
        exec sp_executesql @theSql

        set @theCounter = @theCounter + 1
    end

    -- Mark that this has been done on this database.
    insert into GlobalSystemOption
    (
        [Name],
        [Value]
    )
    select
        [Name] = @theInitializedGlobalSystemOptionName,
        [Value] = @theGsoValue
    except
    select
        [Name],
        [Value] = @theGsoValue
    from GlobalSystemOption
end
GO
GRANT EXECUTE ON  [dbo].[BuildGPWarehouse_XLedger_Initialize] TO [MssExec]
GO