/**
*    Converts ARC Customer Information records over to XLedger.  This looks to match existing
*    records to AccountingCustomer records by customer number.  Any records that do not match
*    by by customer number are deleted.  User can just opt to delete all records if they like
*    as well.  Assigned collectors and alert groups are not affected by this.
*    
*    @param @inCommitTransaction - Optional boolean flag to control whether changes are
*        committed (1 = commit) or rolled back (0 = rollback).
*    @param @inClearOutOldAccountingSystemRecordsOption -The passed in option code tells us
*        what to do:
*        1: Delete all old accounting system records.  Used when the customer numbers don't
*           match up between accounting systems.
*        2: Match what you can using the Customer Number and delete the old records that do
*           not match up.  Once that is done, user can run reports and then proceed from there.
*        Any other value: The SP exits without doing anything.
*    @param @outXLedgerRecordCount - Output parameter for the number of XLedger records that
*        exist before any conversions.
*    @param @outLegacyRecordCount - Output parameter for the number of Legacy records that exist
*        before any conversions.
*    @param @outOldRecordsConverted - Output parameter for the number of records converted from
*        old accounting system to new accounting system.
*    @param @outOldRecordsDeleted - Output parameter for the number of records deleted from old
*        accounting system (have no matching customer number within the new accounting system.
*    @param @outErrorCode - Indicates if the table has records from an accounting system that is not
*        the current accounting system.  When that is the case, then nothing is done and the user needs
*        to make a decision on how to deal with the records from the old accounting system via the
*        @inClearOutOldAccountingSystemRecordsOption.  Also, if this is a second pass then pass in
*        the @outErrorCode from the first pass.
*        Error Code:
*            0: No error.
*            1: Records exist from some legacy Accounting System but XLedger is your current accounting system.
*            2: Records exist from XLedger Accounting System but your current accounting system is not XLedger.
*            3: ARCCustomerInformation table has both XLedger and legacy data in it.  ProServ needs to have
*               engineering assist to clean up this data.  This can't be auto-fixed without human intervention.
*            90 and up: Unexpected pass 2 error.  Try the process again.  Make sure only one person is doing
*               this.  Don't use direct SQL on this table while doing this.
*/
create procedure [dbo].[ARCUpdateCustomerAlertsConvertToXLedger]
    @inCommitTransaction bit = null,
    @inClearOutOldAccountingSystemRecordsOption int = null,
    @outXLedgerRecordCount int output,
    @outLegacyRecordCount int output,
    @outOldRecordsConverted int output,
    @outOldRecordsDeleted int output,
    @outErrorCode int output
as
begin
    set nocount on;

    declare @inOriginalErrorCode int = isnull( @outErrorCode, 0 );
    declare @NO_ERROR int = 0;
    declare @LEGACY_RECORDS_EXIST_WITH_XLEDGER_SYSTEM int = 1;
    declare @XLEDGER_RECORDS_EXIST_WITH_NON_XLEDGER_SYSTEM int = 2;
    declare @BOTH_XLEDGER_AND_LEGACY_RECORDS_EXIST_NO_CAN_DO int = 3;
    declare @INVALID_ClearOutOldAccountingSystemRecordsOption_FOR_CURRENT_STATE int = 90;
    declare @USER_OPTION_DOES_NOT_MATCH_CURRENT_STATE int = 91;
    declare @GLOBAL_SYSTEM_OPTION_NAME varchar(64) = 'ARCCustomerInformationRecordsConvertedToXLedger'

    -- Variables to store the number of rows affected by this SP
    select
        -- null record counts means that action was not performed.
        @outOldRecordsConverted = null,
        @outOldRecordsDeleted = null,
        @outErrorCode = @NO_ERROR

    -- Assume it is safe to do our thing.
    declare @theOkToContinue bit = 1;
    declare @theOkToDeleteLegacyRecords bit = 0
    declare @theMarkConversionAsCompleted bit = 0;
    declare @theRowCount int

    -- See in the db has XLedger data on file.
    set @outXLedgerRecordCount = ( select count(*) from ARCCustomerInformation where AccountingCustomerFid is not null );

    -- See in the db has legacy data on file.
    set @outLegacyRecordCount = ( select count(*) from ARCCustomerInformation where AccountingCustomerFid is null );

    -- See if this is a situation we can handle or not.
    declare @theAccountingSystemType varchar(2) = dbo.GetAccountingSystemType()
    if( @theAccountingSystemType = 'XL' )
    begin
        if( @outLegacyRecordCount > 0 and @outXLedgerRecordCount = 0 )
        begin
            -- This is a problem we can fix.  User needs to make a decision on how to fix first.
            set @outErrorCode = @LEGACY_RECORDS_EXIST_WITH_XLEDGER_SYSTEM
        end
        else if( @outLegacyRecordCount = 0 )
        begin
            -- There are no legacy records, so we are done.  Mark this conversion task as completed.
            set @theOkToContinue = 0
            set @theMarkConversionAsCompleted = 1
        end
    end
    else -- some legacy accounting system.
    begin
        if( @outLegacyRecordCount = 0 and @outXLedgerRecordCount > 0 )
        begin
            -- OK, what's going on here?  GP to XLedger and then back to GP?  Unsupported.
            set @outErrorCode = @XLEDGER_RECORDS_EXIST_WITH_NON_XLEDGER_SYSTEM
        end
    end

    if( @outLegacyRecordCount > 0 and @outXLedgerRecordCount > 0 )
    begin
        -- Not sure how we can get here, but just in case.
        set @outErrorCode = @BOTH_XLEDGER_AND_LEGACY_RECORDS_EXIST_NO_CAN_DO
    end

    begin transaction;

    if( @theOkToContinue = 1 )
    begin
        if( @inCommitTransaction = 0 )
        begin
            -- If we get here this is a first pass.  User may be able to direct us on what to do
            -- or ProServ/engineering help will be needed.  Since we will rollback, we can get
            -- the various record counts we could do.
            if( isnull( @inClearOutOldAccountingSystemRecordsOption, 1 ) != 0 )
            begin
                set @theOkToContinue = 0
            end
        end
        else -- if( @inCommitTransaction = 1)
        begin
            -- On second pass, @inClearOutOldAccountingSystemRecordsOption can't be 0 if we have an error
            if( ( @outErrorCode != @NO_ERROR and @inOriginalErrorCode != @outErrorCode ) or
                ( @outErrorCode != @NO_ERROR and isnull( @inClearOutOldAccountingSystemRecordsOption, 0 ) = 0 ) )
            begin
                set @theOkToContinue = 0
                set @outErrorCode = @INVALID_ClearOutOldAccountingSystemRecordsOption_FOR_CURRENT_STATE
            end
        end
    end

    if( @theOkToContinue = 1 )
    begin
        -- This is a second pass.  See if the current error matches what the user asked us to do.
        if( @outErrorCode != @NO_ERROR )
        begin
            if( @outErrorCode = @LEGACY_RECORDS_EXIST_WITH_XLEDGER_SYSTEM )
            begin
                if( @inClearOutOldAccountingSystemRecordsOption = 1 )
                begin
                    -- Option 1 tells us to just delete all of the legacy records
                    set @theOkToDeleteLegacyRecords = 1
                end
                if( @inClearOutOldAccountingSystemRecordsOption = 2 or @inCommitTransaction = 0 )
                begin
                    -- Option 2 tells us to match what we can by customer number and
                    -- then just delete all of the remaining unmatched legacy records.
                    update ARCCustomerInformation set
                        AccountingCustomerFid = CustomerInformation.CustomerId
                    from ARCCustomerInformation
                    inner join CustomerInformation_Synonym as CustomerInformation on
                        CustomerInformation.CustomerNumber = ARCCustomerInformation.CustomerNumber
                    where ARCCustomerInformation.AccountingCustomerFid is null

                    -- Set the count of old accounting system rows that were converted.
                    set @outOldRecordsConverted = @@ROWCOUNT;

                    -- Then, delete any remaining unmatched legacy records
                    set @theOkToDeleteLegacyRecords = 1
                end
            end

            if( @theOkToDeleteLegacyRecords = 1 )
            begin
                delete
                from ARCCustomerInformation
                where AccountingCustomerFid is null

                -- Set the count of old accounting system rows that were deleted.
                set @outOldRecordsDeleted = @@ROWCOUNT;

                set @theOkToContinue = 0
                if( 0 = ( select count(*) from ARCCustomerInformation where AccountingCustomerFid is null ) )
                begin
                    -- How can we not get here??
                    select
                        @theOkToContinue = 1,
                        @theMarkConversionAsCompleted = @inCommitTransaction,
                        @outErrorCode = case
                            when @inCommitTransaction = 1 then @NO_ERROR
                            else @outErrorCode
                        end
                end
            end
        end
        else if( @inClearOutOldAccountingSystemRecordsOption != 0 or @inOriginalErrorCode != @NO_ERROR )
        begin
            -- We were given instructions to solve an error (i.e. @inClearOutOldAccountingSystemRecordsOption is not 0).
            -- However, we no longer see that error (because @outErrorCode = @NO_ERROR but @inOriginalErrorCode is some error.
            -- Stop!!.  Let user restart.  Someone else is simultaneously manipulating this table.
            select
                @theOkToContinue = 0,
                @outErrorCode = @USER_OPTION_DOES_NOT_MATCH_CURRENT_STATE
        end
    end

    if( @theMarkConversionAsCompleted = 1 )
    begin
        insert into GlobalSystemOption
        (
            [Name],
            [Value]
        )
        select
            [Name] = @GLOBAL_SYSTEM_OPTION_NAME,
            [Value] = 'true'
        where not exists (select top 1 1 from GlobalSystemOption where [Name] = @GLOBAL_SYSTEM_OPTION_NAME )
        set @theRowCount = @@ROWCOUNT

        if( @theRowCount = 0 )
        begin
            update GlobalSystemOption set
                [Value] = 'true'
            where [Name] = @GLOBAL_SYSTEM_OPTION_NAME
        end
        set @inCommitTransaction = 1
    end

    -- Conditional commit or rollback based on the @inCommitTransaction parameter
    if( @inCommitTransaction = 1 and @outErrorCode = @NO_ERROR )
    begin
        commit transaction;  -- Commit if @inCommitTransaction is true (1)
    end
    else
    begin
        rollback transaction;  -- Rollback if @inCommitTransaction is false (0) or we have an error.
    end
end;
GO
GRANT EXECUTE ON  [dbo].[ARCUpdateCustomerAlertsConvertToXLedger] TO [MssExec]
GO