Delimiter //
/**
* determine une numerotation de lot qui respecte les contraintes du lettrage
*/
Drop Procedure if Exists LOADER_MATCHINGCODE_NORMALIZE_MAP_BATCH_NUM//
Create Procedure LOADER_MATCHINGCODE_NORMALIZE_MAP_BATCH_NUM (
)
Begin
    batchNum__main: Begin

        Set @max_batch = Coalesce(GET_LOADER_INFO('MAX_BATCH', 1), 0);
        Set @batch_size = Coalesce(GET_LOADER_INFO('BATCH_SIZE', 1), 1000);

        Drop temporary Table if Exists TMP_ORDERLOADER_MATCHINGCODES;
        -- classement
        Create temporary Table TMP_ORDERLOADER_MATCHINGCODES (
            ID_TMP_ORDERLOADER_MATCHINGCODES Int Not Null Primary Key Auto_increment
            , LOADER_STATUS_ID_LOADER_STATUS Int Not Null
            , MATCHINGCODES_CODE Varchar(20) Null
            , Index (MATCHINGCODES_CODE)
        ) ;
        Insert Into TMP_ORDERLOADER_MATCHINGCODES(
            LOADER_STATUS_ID_LOADER_STATUS, MATCHINGCODES_CODE
        ) 
        Select LOADER_STATUS_ID_LOADER_STATUS, Coalesce(MATCHINGCODES_CODE, MATCHINGCODES_ACCOUNTS_CODE)
        From LOADER_MATCHINGCODES
        Where Coalesce(MATCHINGCODES_CODE, MATCHINGCODES_ACCOUNTS_CODE) Is Not Null
        Order By 2;

        -- initialisation
        update LOADER_MATCHINGCODES LM
        Set
            LM.MATCHINGCODES_BATCH_NUM = null
        
        ;

        Set @loopCount = 0;
        matchingNewBatchLoop: loop
                Set @minOffet = @loopCount * @batch_size +1;
                Set @loopCount = @loopCount +1;
                if @loopCount > @max_batch Then
                    leave matchingNewBatchLoop;
                End if;
                Set @maxOffset = @loopCount * @batch_size;
                --
                -- on affecte un nouveau numero de lot
                -- pour les lot non encore affecté !!!
                update LOADER_MATCHINGCODES LM
                    Inner Join TMP_ORDERLOADER_MATCHINGCODES TOM
                        On LM.LOADER_STATUS_ID_LOADER_STATUS = TOM.LOADER_STATUS_ID_LOADER_STATUS
                Set
                    LM.MATCHINGCODES_BATCH_NUM = @loopCount
                Where
                    TOM.ID_TMP_ORDERLOADER_MATCHINGCODES Between @minOffet And @maxOffset
                    And LM.MATCHINGCODES_BATCH_NUM Is Null
                ;
                --
                -- puis on recherche le code lettrage de la derniere ligne du lot
                --
                Set @codeLettrage = (Select MATCHINGCODES_CODE 
                                    From TMP_ORDERLOADER_MATCHINGCODES 
                                    Where ID_TMP_ORDERLOADER_MATCHINGCODES = @maxOffset
                                )
                            ;
                -- on affecte le meme numero de lot a toute les ligne ayant le même code lettrage
                update LOADER_MATCHINGCODES LM
                    Inner Join TMP_ORDERLOADER_MATCHINGCODES TOM
                        On LM.LOADER_STATUS_ID_LOADER_STATUS = TOM.LOADER_STATUS_ID_LOADER_STATUS
                Set
                    LM.MATCHINGCODES_BATCH_NUM = @loopCount
                Where
                    TOM.ID_TMP_ORDERLOADER_MATCHINGCODES > @maxOffset
--                     And LM.MATCHINGCODES_CODE = @codeLettrage
--                     And Coalesce(LM.MATCHINGCODES_CODE, LM.MATCHINGCODES_ACCOUNTS_CODE) = @codeLettrage
                    And @codeLettrage In (LM.MATCHINGCODES_CODE, LM.MATCHINGCODES_ACCOUNTS_CODE)
                    And LM.MATCHINGCODES_BATCH_NUM Is Null
                ;
                
        end loop matchingNewBatchLoop;
        
        -- Mise à jour du numéro de lot
        -- voir si nécéssaire
        -- on peut utilisé MATCHINGCODES_BATCH_NUM dans le sous procédures
        Update
            LOADER_STATUS LS
            Inner Join LOADER_MATCHINGCODES LM
                On LS.ID_LOADER_STATUS = LM.LOADER_STATUS_ID_LOADER_STATUS
        Set 
            LS.LOADER_STATUS_BATCH_NUM = LM.MATCHINGCODES_BATCH_NUM
        ;

        -- purges des données
        Drop temporary Table if Exists TMP_ORDERLOADER_MATCHINGCODES;
    End;
    

End //
Delimiter ;