Delimiter //
/**
 * Author:  armen
 * Created: 23 mars 2018
 */
Drop Procedure if Exists BUSINESS_LINKED_ACCOUNTS_CIRCULARE_REF_CLEAR //
Create Procedure BUSINESS_LINKED_ACCOUNTS_CIRCULARE_REF_CLEAR(
            In idaccounts Int unsigned
            , In maxIeration Int 
            , In circularRefTable Varchar(50)
        )
Begin

    if circularRefTable Is Null Then
        call BUSINESS_LINKED_ACCOUNTS_CHECK_CIRCULARE_REF(
                    idaccounts
                    , maxIeration -- In maxIeration Int 
                    , @blacrc_circularRefTable 
                    , @blacrc_ciculareRefCount 
                )
            ;
    Else
        Set @blacrc_circularRefTable = circularRefTable;
    End If ;

--     Set @blacrc_accoutFilter = coalesce(idaccounts, '%');
    if idaccounts Is null Then
        Set @blacrc_accMinVal = 0;
        Set @blacrc_accMaxVal = 4e7;
    Else
        Set @blacrc_accMinVal = idaccounts;
        Set @blacrc_accMaxVal = idaccounts;
    End if;

    -- supression des écritures lié
    set @blacrc_queryDeleteTable = Concat(
                'Delete tar '
                , 'From ACCOUNTS_has_ACCOUNTS t1 '
                ,   'Inner Join  ', @blacrc_circularRefTable,' src '
                ,       'On t1.ACCOUNTS_ID_ACCOUNTS_LINK = src.ACCOUNTS_ID_ACCOUNTS_LINK '
                ,   'Inner Join ENTRIES_has_ACCOUNTS tar '
                ,       'On tar.ACCOUNTS_ID_ACCOUNTS = t1.ACCOUNTS_ID_ACCOUNTS ' 
                ,       'And tar.ENTRIES_PERMANENT_ATTACHEMENT = 0 '
                , 'where ('
                ,       '(t1.ACCOUNTS_ID_ACCOUNTS Between ? And ? )'
                ,       'Or (t1.ACCOUNTS_ID_ACCOUNTS_LINK Between ? And ? ) '
                ,   ')'
    --                     't1.ACCOUNTS_ID_ACCOUNTS Like ? '
    --                     'Or t1.ACCOUNTS_ID_ACCOUNTS_LINK Like ? '
            )
        ;

    PREPARE deleteStmt FROM @blacrc_queryDeleteTable; 
    execute deleteStmt 
            using @blacrc_accMinVal, @blacrc_accMaxVal, @blacrc_accMinVal, @blacrc_accMaxVal
--             using @blacrc_accoutFilter, @blacrc_accoutFilter
        ;

    Deallocate prepare deleteStmt; 			        

    -- supression des comptes lié
    set @blacrc_queryDeleteTable = Concat(
                'Delete tar '
                , 'From ACCOUNTS_has_ACCOUNTS tar '
                , 'Inner Join  ', @blacrc_circularRefTable,' src '
--                     , 'On tar.ACCOUNTS_ID_ACCOUNTS = src.ACCOUNTS_ID_ACCOUNTS '
--                     , 'And tar.ACCOUNTS_ID_ACCOUNTS_LINK = src.ACCOUNTS_ID_ACCOUNTS_LINK '
                ,    'On tar.ACCOUNTS_ID_ACCOUNTS_LINK = src.ACCOUNTS_ID_ACCOUNTS_LINK '
                , 'where ('
                ,       '(tar.ACCOUNTS_ID_ACCOUNTS  Between ? And ? )'
                ,       'Or (tar.ACCOUNTS_ID_ACCOUNTS_LINK  Between ? And ? ) '
--                     'tar.ACCOUNTS_ID_ACCOUNTS Like ? '
--                     'Or tar.ACCOUNTS_ID_ACCOUNTS_LINK Like ? '
                ,   ')' 
            )
        ;
    PREPARE deleteStmt FROM @blacrc_queryDeleteTable; 
    execute deleteStmt 
            using @blacrc_accMinVal, @blacrc_accMaxVal, @blacrc_accMinVal, @blacrc_accMaxVal
--             using @blacrc_accoutFilter , @blacrc_accoutFilter 
        ;

    Deallocate prepare deleteStmt; 				

end //
Delimiter ;

