Delimiter //
/**
 * Author:  armen
 * Created: 25 oct. 2018
 * creation et population de la table TMP_ENTRIES_has_ACCOUNTS
 * contenant les écitures des compte liées (ENTRIES_PERMANENT_ATTACHEMENT = 0)
 * correspondant au filte sur les comptes
 */
Drop Procedure if Exists BUSINESS_LINKED_ACCOUNTS_CREATES_SELECTED_ENT_HAS_ACC_TBL //
Create Procedure BUSINESS_LINKED_ACCOUNTS_CREATES_SELECTED_ENT_HAS_ACC_TBL(
            In idaccounts Int unsigned
        )
Begin
--     Set @blacsehat_accoutFilter = coalesce(idaccounts, '%');
    if idaccounts Is null Then
        Set @blacsehat_accMinVal = 0;
        Set @blacsehat_accMaxVal = 4000000;
    Else
        Set @blacsehat_accMinVal = idaccounts;
        Set @blacsehat_accMaxVal = idaccounts;
    End if;
    -- liste des ENTRIES_has_ACCOUNTS des comptes rattaché  (ENTRIES_PERMANENT_ATTACHEMENT = 0)
    -- correspondants au critere de purges
    Drop Temporary Table If EXISTS TMP_ENTRIES_has_ACCOUNTS ;

    -- Le "alter table" entraine un comit implicite, même si la table est temporary
    -- on doit doc créer la table directelant avec les index requis, 
    -- sans faire un alter
    Create Temporary Table TMP_ENTRIES_has_ACCOUNTS (
                ID_ENTRIES_has_ACCOUNTS Int Unsigned Not Null
                , ACCOUNTS_ID_ACCOUNTS Int Unsigned Not Null
                , ENTRIES_ID_ENTRIES Int Unsigned Not Null
--                 , ENTRIES_ACC_EXT_REF varchar(45)
                , ENTRIES_PERMANENT_ATTACHEMENT Smallint Not Null

                , Index IX_TMP_ENTRIES_has_ACCOUNTS_1(ID_ENTRIES_has_ACCOUNTS)
                , Index IX_TMP_ENTRIES_has_ACCOUNTS_2(ACCOUNTS_ID_ACCOUNTS)
                , Index IX_TMP_ENTRIES_has_ACCOUNTS_3(ENTRIES_ID_ENTRIES)
            )
        ;

    Insert Into TMP_ENTRIES_has_ACCOUNTS (
            ID_ENTRIES_has_ACCOUNTS, ACCOUNTS_ID_ACCOUNTS
            , ENTRIES_ID_ENTRIES, ENTRIES_PERMANENT_ATTACHEMENT
        )
    Select
        ID_ENTRIES_has_ACCOUNTS, ACCOUNTS_ID_ACCOUNTS
        , ENTRIES_ID_ENTRIES, ENTRIES_PERMANENT_ATTACHEMENT
    From ENTRIES_has_ACCOUNTS tar
    Where
        tar.ACCOUNTS_ID_ACCOUNTS Between @blacsehat_accMinVal And @blacsehat_accMaxVal
        And tar.ENTRIES_PERMANENT_ATTACHEMENT = 0
    ;
--     Create Temporary Table TMP_ENTRIES_has_ACCOUNTS As
--     -- explain
--     Select tar.*
--     From ENTRIES_has_ACCOUNTS tar
--     Where
--         tar.ENTRIES_PERMANENT_ATTACHEMENT = 0
--         and tar.ACCOUNTS_ID_ACCOUNTS Between @blacsehat_accMinVal And @blacsehat_accMaxVal
-- --         and tar.ACCOUNTS_ID_ACCOUNTS like @blacsehat_accoutFilter 
--     ;
-- 
--     Alter Table TMP_ENTRIES_has_ACCOUNTS
--             add Index IX_TMP_ENTRIES_has_ACCOUNTS_1(ID_ENTRIES_has_ACCOUNTS)
--             , add Index IX_TMP_ENTRIES_has_ACCOUNTS_2(ACCOUNTS_ID_ACCOUNTS)
--             , add Index IX_TMP_ENTRIES_has_ACCOUNTS_3(ENTRIES_ID_ENTRIES)
--         ;

end //
Delimiter ;