Delimiter //
/**
 * Updates reclaims count on entries tables
 * !! les paramètres ne sont pas de le même ordre que dans la ps BUSINESS_UPDATE_ENTRIES_NB_RECLAIMS !!
 */ 
Drop Procedure if Exists BUSINESS_UPDATE_ENTRIES_RECLAIMS//
 
Create Procedure BUSINESS_UPDATE_ENTRIES_RECLAIMS(in refdate Date, In entries varchar(200), In accounts varchar(200))
Begin

	Set @buer_refDate = Coalesce(refDate, AMO_BAL_REFDATE());

-- 	set @buer_entries = Case When Coalesce(entries, '') like '%,%' Then 
-- 							-- liste d'écritures, on vide le critère
-- 							-- TODO améliorer/supprimer ?
-- 							NULL
-- 						Else
-- 							-- une écriture
-- 							entries
-- 						End ;

    Set @buer_entries_chk =  trim(Coalesce(entries, '')) ;
    if @buer_entries_chk REGEXP '^[0-9]+$' Then
        Set @buer_entries_filter= Concat(' = ', @buer_entries_chk);
        Set @buer_entries = @buer_entries_chk;

    Elseif @buer_entries_chk REGEXP '^(([0-9])+[,;]?)+$' then
        Set @buer_entries_filter= Concat(' In (', @buer_entries_chk, ')');
        Set @buer_entries = null;

    Elseif @buer_entries_chk ='' then
        Set @buer_entries_filter= ' Between 0 And 4000000000 ';
        Set @buer_entries = null;

    Else 
        Set @buer_entries_filter= Concat(' ( Like ',buer_entries_chk, ')');
        Set @buer_entries = null;

    end if;

-- 	set @buer_accounts = Case When Coalesce(accounts, '') like '%,%' Then 
-- 							-- liste de comptes, on vide le critère
-- 							-- TODO améliorer/supprimer ?
-- 							NULL
-- 						Else
-- 							-- un compte
-- 							accounts
-- 						End ;
    Set @buer_accounts_chk =  trim(Coalesce(accounts, '')) ;
    if @buer_accounts_chk REGEXP '^[0-9]+$' Then
        Set @buer_accounts_filter= Concat(' = ', @buer_accounts_chk);
        Set @buer_accounts = @buer_accounts_chk;

    Elseif @buer_accounts_chk REGEXP '^(([0-9])+[,;]?)+$' then
        Set @buer_accounts_filter= Concat(' In (', @buer_accounts_chk, ')');
        Set @buer_accounts = null;

    Elseif @buer_accounts_chk ='' then
        Set @buer_accounts_filter= ' Between 0 And 10000000 ';
        Set @buer_accounts = null;

    Else 
        Set @buer_accounts_filter= Concat(' ( Like ',buer_accounts_chk, ')');
        Set @buer_accounts = null;

    end if;


	call BUSINESS_UPDATE_ENTRIES_NB_RECLAIMS(@buer_refDate, @buer_accounts, @buer_entries);

    set @baseQuery = Concat('UPDATE ENTRIES'
						, ' LEFT Join V_ENTRIES_NB_RECLAIMS_VIEW tmp'
						, '    On ENTRIES.ID_ENTRIES = tmp.ENTRIES_ID_ENTRIES'
				)
			;

    set @setClause = ' Set ENTRIES_RECLAIMS = Coalesce(RECLAIMS_NB_OPENED, 0)';

    set @buer_whereBuilld = ' Where ';
    set @buer_whereClause = ' ';

--     if entries is not null Then
    if @buer_entries_chk <> '' Then
--         set @buer_whereClause = concat(@buer_whereClause, ' ', @buer_whereBuilld, ' ENTRIES.ID_ENTRIES in (', entries , ')');
        set @buer_whereClause = concat(@buer_whereClause, ' ', @buer_whereBuilld, ' ( ENTRIES.ID_ENTRIES ', @buer_entries_filter, ')');
        set @buer_whereBuilld = ' And ';
    End if;

--     if accounts is not null Then
    if @buer_accounts_chk <> '' Then
        set @buer_whereClause = concat(@buer_whereClause, ' '
                                , @buer_whereBuilld, ' ('
								, '   ID_ENTRIES in ('
								, '	    Select eha0.ENTRIES_ID_ENTRIES'
								, '	    from ENTRIES_has_ACCOUNTS eha0'
-- 								, '	    where eha0.ACCOUNTS_ID_ACCOUNTS in (', accounts , ')'
								, '	    where eha0.ACCOUNTS_ID_ACCOUNTS ', @buer_accounts_filter
								, '		  And ENTRIES.ID_ENTRIES = eha0.ENTRIES_ID_ENTRIES '
								, '   )'
								, ' )'
							)
						;
        set @buer_whereBuilld = ' And ';
    End if;

    set @buer_query = concat(@baseQuery, ' ', @setClause, ' ', @buer_whereClause );

    PREPARE bsupentrrclm_targetstmt FROM @buer_query; 
    execute bsupentrrclm_targetstmt 
            ;
    Deallocate prepare bsupentrrclm_targetstmt; 

--     select @buer_query query;


End //
Delimiter ;