Delimiter //
/**
 * Author:  armen
 * Created: 23 mars 2018
 * recherche et efface les écritures qui ne doivent plus apparaitre dans
 * les comptes de consolidation
 */
Drop Procedure if Exists BUSINESS_LINKED_ACCOUNTS_CLEAR_UNLINKED_ENTRIES //
Create Procedure BUSINESS_LINKED_ACCOUNTS_CLEAR_UNLINKED_ENTRIES(
            In idaccounts Int unsigned
            , In maxIeration Int 
        )
Begin
    blacue_main: begin
        blacue_init: begin
            -- on utilise Greatest pour eviter les négatifs + un least pour avoir une valeur max
            Set @blacue_maxIeration = Least(Greatest(Coalesce(maxIeration, 5), 3), 15); 
            -- 3 <= @blacue_maxIeration <= 15

            -- creation de la table TMP_ENTRIES_has_ACCOUNTS avec les écriture rattachées (ENTRIES_PERMANENT_ATTACHEMENT = 0)
            -- la creation n'est plus util pour le moment
            -- compte tenu de la refacorisation de la PS
--             call BUSINESS_LINKED_ACCOUNTS_CREATES_SELECTED_ENT_HAS_ACC_TBL( idaccounts);

            call CASH_CHECK_TABLE_EXISTENCE('TMP_ACC_HAS_ACC', @blacue_fulltreetblexists); 
            if @blacue_fulltreetblexists = 0 Then
                call BUSINESS_LINKED_ACCOUNTS_CREATE_FULL_TREE(idaccounts, @blacue_maxIeration);
        --         call BUSINESS_LINKED_ACCOUNTS_CREATE_FULL_TREE(null, @blacue_maxIeration:=5);
            End if;

            Set @blacue_acc_min = Coalesce(idaccounts, 0);
            Set @blacue_acc_max = Coalesce(idaccounts, 1000000);
--             Set @blacue_acc_min = Coalesce(@blacue_acc_min, 0);
--             Set @blacue_acc_max = Coalesce(@blacue_acc_max, 1000000);

        End ;

        blacue_sel_ent: begin
            -- ici la table indexées des comptes liées est créé
            -- where sur plus d'une colonne ACCOUNTS_ID_ACCOUNTS fait chuter les perf
            -- le explain montre que mysql n'utilise plus l'index
            -- on fait donc une boucle avec un seul critere à la foix
            -- creation de la table temporaire des ENTRIES_has_ACCOUNTS selectionnées à conserver

        --     call BUSINESS_LINKED_ACCOUNTS_FIND_ENTRIES_TO_KEEP(idaccounts, @blacue_maxIeration);
        --     call BUSINESS_LINKED_ACCOUNTS_FIND_ENTRIES_TO_KEEP(null, @blacue_maxIeration:=5);

            -- ici la table TMP_ENTRIES_has_ACCOUNTS_TO_KEEP est populé
        --     select * from TMP_ENTRIES_has_ACCOUNTS_TO_KEEP limit 100;
        --     select * from TMP_ENTRIES_has_ACCOUNTS_TO_KEEP Where ACCOUNTS_ID_ACCOUNTS In (10709);
            -- liste des donnée à supprimer
            Drop Temporary Table If Exists TMP_ENTRIES_has_ACCOUNTS_TO_DEL;
            Create Temporary Table TMP_ENTRIES_has_ACCOUNTS_TO_DEL (
                        ID_ENTRIES_has_ACCOUNTS Int Unsigned Not Null
                        , Index IX_TMP_ENTRIES_has_ACCOUNTS_TO_DEL_0(ID_ENTRIES_has_ACCOUNTS)
                    )
                ;
            Insert Into TMP_ENTRIES_has_ACCOUNTS_TO_DEL(
                    ID_ENTRIES_has_ACCOUNTS
                    -- , ENTRIES_ID_ENTRIES, ACCOUNTS_ID_ACCOUNTS
                )
            -- explain
            Select
                eha1.ID_ENTRIES_has_ACCOUNTS
            -- çi-dessous : projection debug
        --     acc1.ID_ACCOUNTS ID_ACC1
        --     , Cast(acc1.ACCOUNTS_REF as Char(20) ) ACC_REF1
        --     , Cast(eha1.ENTRIES_ACC_EXT_REF as Char(20) ) ACC_EXT_REF
        -- --     , eha1.ENTRIES_PERMANENT_ATTACHEMENT PERM_ATT1
        --     , ent.ID_ENTRIES ID_ENT, ent.ENTRIES_DATE ENTDT, ent.ENTRIES_DUE_DATE DUE_DATE
        --     , Cast(ent.ENTRIES_REF as Char(20) ) ENT_REF
        --     , Cast(ent.ENTRIES_ENT_BALANCE AS  Decimal(10,2) ) ENT_BAL
        -- --     , ent.DF_MATCH_STAT_ID_DF_MATCH_STAT MATCH_STAT
        --     , accm.ID_ACCOUNTS ID_ACCm, accm.ACCOUNTS_REF ACC_REFM
        --     , (
        --         Select 
        --              aha.ACCOUNTS_has_ACCOUNTS_PREFIX 
        --         From ACCOUNTS_has_ACCOUNTS  aha
        --         Where 1 = 1 
        --             
        --             And aha.ACCOUNTS_ID_ACCOUNTS_LINK = accm.ID_ACCOUNTS
        -- --             And aha.ACCOUNTS_has_ACCOUNTS_PREFIX like Concat('%.', accm.ID_ACCOUNTS, '.%')
        --             And aha.ACCOUNTS_has_ACCOUNTS_PREFIX like Concat('%.', acc1.ID_ACCOUNTS, '.%')
        --             
        --         Limit 1
        -- 
        --     ) ACC_PREFIX
            From
                ENTRIES_has_ACCOUNTS eha1
                Inner Join ENTRIES ent
                    On ent.ID_ENTRIES = eha1.ENTRIES_ID_ENTRIES
                    And eha1.ENTRIES_PERMANENT_ATTACHEMENT = 0
                Inner Join ENTRIES_has_ACCOUNTS eham
                    On ent.ID_ENTRIES = eham.ENTRIES_ID_ENTRIES
                    And eham.ENTRIES_PERMANENT_ATTACHEMENT = 1
                -- jointures pour afficher les info de debug
            --     Inner Join ACCOUNTS acc1
            --         On eha1.ACCOUNTS_ID_ACCOUNTS = acc1.ID_ACCOUNTS
            --     Inner Join ACCOUNTS accm
            --         On eham.ACCOUNTS_ID_ACCOUNTS = accm.ID_ACCOUNTS

            Where 1 = 1 
                And eha1.ACCOUNTS_ID_ACCOUNTS Between @blacue_acc_min And @blacue_acc_max
        --         And accm.ID_ACCOUNTS Not in (
                And eham.ACCOUNTS_ID_ACCOUNTS Not in (
                    Select 
                        aha.ACCOUNTS_ID_ACCOUNTS_LINK
                    From ACCOUNTS_has_ACCOUNTS  aha
                    Where 1 = 1 
        --                 And aha.ACCOUNTS_ID_ACCOUNTS_LINK = accm.ID_ACCOUNTS
                        And aha.ACCOUNTS_ID_ACCOUNTS_LINK = eham.ACCOUNTS_ID_ACCOUNTS
            --             And aha.ACCOUNTS_has_ACCOUNTS_PREFIX like Concat('%.', accm.ID_ACCOUNTS, '.%') -- implicite
        --                 And aha.ACCOUNTS_has_ACCOUNTS_PREFIX like Concat('%.', acc1.ID_ACCOUNTS, '.%')
                        And aha.ACCOUNTS_has_ACCOUNTS_PREFIX like Concat('%.', eha1.ACCOUNTS_ID_ACCOUNTS, '.%')
                )

                -- clause pour test avec uniquement les lettrés
            --     And ent.DF_MATCH_STAT_ID_DF_MATCH_STAT in (0
            --         , 1, 2
            --     )
            ;

        end ;

        blacue_del_ent: begin


            -- requete base sur TMP_ENTRIES_has_ACCOUNTS_TO_KEEP
        --     Select 
        --         -- Distinct
        --         tar.ID_ENTRIES_has_ACCOUNTS
        -- --  Select tar.ID_ENTRIES_has_ACCOUNTS, tar.ENTRIES_ID_ENTRIES, tar.ACCOUNTS_ID_ACCOUNTS
        --     From TMP_ENTRIES_has_ACCOUNTS tar
        --         Left Join TMP_ENTRIES_has_ACCOUNTS_TO_KEEP src
        --             On tar.ID_ENTRIES_has_ACCOUNTS = src.ID_ENTRIES_has_ACCOUNTS
        --     Where 
        --         src.ID_ENTRIES_has_ACCOUNTS Is Null
        --     ;

            -- ici la liste des donnéé à supprimer à été déterminé dans  TMP_ENTRIES_has_ACCOUNTS_TO_DEL

            Delete tar
            -- select * 
            From ENTRIES_has_ACCOUNTS tar
                Inner Join TMP_ENTRIES_has_ACCOUNTS_TO_DEL src
                    On tar.ID_ENTRIES_has_ACCOUNTS = src.ID_ENTRIES_has_ACCOUNTS
            Where
                tar.ENTRIES_PERMANENT_ATTACHEMENT = 0
            ;
        end ;

        blacue_clear_tmp_tbl: Begin
            -- purge
            drop temporary table If Exists TMP_ENTRIES_has_ACCOUNTS;
            drop temporary table If Exists TMP_ENTRIES_has_ACCOUNTS_TO_KEEP;
        End;
            --  on ne supprime pas cette table car peut être utili dans d'autre PS
        --     drop temporary table If Exists TMP_ACC_HAS_ACC;
            -- on ne supprime pas cette table pour laisser la possibilité de la consulter
        --     drop temporary table If Exists TMP_ENTRIES_has_ACCOUNTS_TO_DEL;

        -- nouvelle procedure
        -- à finaliser / tester -> optim?

        -- 
        -- 
        --             Drop Temporary Table If exists TMP_EAH_TO_DEL ;
        --             Create Temporary Table If Not exists TMP_EAH_TO_DEL (
        --                 ENTRIES_ID_ENTRIES Int Unsigned Not Null
        --                 , ACCOUNTS_ID_ACCOUNTS Int Unsigned Not Null
        --                 , Index (ENTRIES_ID_ENTRIES)
        --                 , Index (ACCOUNTS_ID_ACCOUNTS)
        --             );
        -- 
        --             Start Transaction;
        --             Insert Into TMP_EAH_TO_DEL (
        --                 ENTRIES_ID_ENTRIES, ACCOUNTS_ID_ACCOUNTS
        --             )
        --             Select
        --                 eha1.ENTRIES_ID_ENTRIES, eha1.ACCOUNTS_ID_ACCOUNTS
        --             From
        --                 ENTRIES_has_ACCOUNTS eha1
        --                 Inner Join ENTRIES_has_ACCOUNTS eha2
        --                     On eha1.ENTRIES_ID_ENTRIES = eha2.ENTRIES_ID_ENTRIES
        --             Where 1 = 1
        --             --     And eha1.ACCOUNTS_ID_ACCOUNTS in (6755)
        --                 And eha1.ENTRIES_PERMANENT_ATTACHEMENT = 0
        --                 And eha2.ENTRIES_PERMANENT_ATTACHEMENT = 1
        --             --     And Exists (
        --             --     And eha1.ACCOUNTS_ID_ACCOUNTS In (
        --                 And eha1.ACCOUNTS_ID_ACCOUNTS Not In (
        --                     Select 
        --                         acc.ID_ACCOUNTS
        --                     From
        --                         ACCOUNTS acc
        --                         Left join ACCOUNTS_has_ACCOUNTS aha1
        --                             On acc.ID_ACCOUNTS = aha1.ACCOUNTS_ID_ACCOUNTS
        --                         Left Join ACCOUNTS_has_ACCOUNTS aha2
        --                             On aha1.ACCOUNTS_ID_ACCOUNTS_LINK = aha2.ACCOUNTS_ID_ACCOUNTS
        --                         Left Join ACCOUNTS_has_ACCOUNTS aha3
        --                             On aha2.ACCOUNTS_ID_ACCOUNTS_LINK = aha3.ACCOUNTS_ID_ACCOUNTS
        --                         Left Join ACCOUNTS_has_ACCOUNTS aha4
        --                             On aha3.ACCOUNTS_ID_ACCOUNTS_LINK = aha4.ACCOUNTS_ID_ACCOUNTS
        --                     --     Left Join ACCOUNTS_has_ACCOUNTS aha5
        --                     --         On aha4.ACCOUNTS_ID_ACCOUNTS_LINK = aha5.ACCOUNTS_ID_ACCOUNTS
        --                     Where
        --                         eha1.ACCOUNTS_ID_ACCOUNTS= acc.ID_ACCOUNTS
        --             --             And eha2.ACCOUNTS_ID_ACCOUNTS Not In (
        --                         And eha2.ACCOUNTS_ID_ACCOUNTS In (
        --                                 aha1.ACCOUNTS_ID_ACCOUNTS_LINK
        --                                 , aha2.ACCOUNTS_ID_ACCOUNTS_LINK
        --                                 , aha3.ACCOUNTS_ID_ACCOUNTS_LINK 
        --                                 , aha4.ACCOUNTS_ID_ACCOUNTS_LINK 
        --                             )
        --             -- --         Limit 1
        --                 )
        --             -- Limit 100
        --             ;
        -- 
        -- 
        -- 
        -- 
        -- 
        --             select * 
        --             -- Delete eha
        --             From TMP_EAH_TO_DEL src
        --                 Inner join ENTRIES_has_ACCOUNTS eha
        --                     On src.ENTRIES_ID_ENTRIES = eha.ENTRIES_ID_ENTRIES
        --                     And src.ACCOUNTS_ID_ACCOUNTS = eha.ACCOUNTS_ID_ACCOUNTS
        --             Where
        --                 eha.ENTRIES_PERMANENT_ATTACHEMENT = 0
        --             Limit 10000
        --             ;





        
    end ;
end //
Delimiter ;