Delimiter $$
/**
 * compute BUSINESS_COMPUTE_ENTRIES_DATA_NB_DOCS
 * calcul le nombre d edocument attaché par mouvements
 * rempli la table ENTRIES_COMPUTED_DATA, champ ENTRIES_COMPUTED_DATA_INT, clé NBDOCS
 */ 
Drop Procedure if Exists BUSINESS_COMPUTE_ENTRIES_DATA_NB_DOCS $$
Create Procedure BUSINESS_COMPUTE_ENTRIES_DATA_NB_DOCS(
    In refdate DateTime
    , In idaccounts Int unsigned
    , In identities Int unsigned
    , In tmpTableNameOfEntr varchar(50)
)
Begin
    entr_computed_data_nbdocs: begin
	
--         if refdate is null Then
--             set refdate = AMO_BAL_REFDATE();
--         End if ;

        set @bcednd_refdate = Coalesce(refdate, AMO_BAL_REFDATE());
--         set @bcednd_refdate = curdate();
        set @bcednd_tmpTableNameOfEntr = Coalesce(tmpTableNameOfEntr, '');
        set @bcednd_idaccounts = Coalesce(idaccounts, 0);
        set @bcednd_identities = Coalesce(identities, 0);

        bcednd_process_full: Begin
            if @bcednd_idaccounts <> 0 and @bcednd_identities <> 0 then
                leave bcednd_process_full;
            End if;
            if @bcednd_tmpTableNameOfEntr <> ''  then 
                leave bcednd_process_full;
            End if;

            -- purge des données
            Delete ENTRIES_COMPUTED_DATA 
            From ENTRIES_COMPUTED_DATA 
            Where ENTRIES_COMPUTED_DATA_KEY = 'NBDOCS'
            ;
            -- mise à jour des données
            Insert Into ENTRIES_COMPUTED_DATA (
                ENTRIES_ID_ENTRIES, ENTRIES_COMPUTED_DATA_KEY, ENTRIES_COMPUTED_DATA_INT
            )
            Select
                ENTRIES.ID_ENTRIES, 'NBDOCS', Count(ATTACHED_DOCUMENTS.ID_ATTACHED_DOCUMENTS)
            From 
                ENTRIES
                LEFT JOIN ENTRIES_has_ATTACHED_DOCUMENTS
                    ON ENTRIES.ID_ENTRIES = ENTRIES_has_ATTACHED_DOCUMENTS.ENTRIES_ID_ENTRIES
                Left Join ATTACHED_DOCUMENTS
                    ON ENTRIES_has_ATTACHED_DOCUMENTS.ATTACHED_DOC_ID_ATTACHED_DOC = ATTACHED_DOCUMENTS.ID_ATTACHED_DOCUMENTS
                    And ATTACHED_DOCUMENTS.ATTACHED_DOCUMENTS_DATE <= @bcednd_refdate
            Where 
                ENTRIES.ENTRIES_DATE <= @bcednd_refdate
            Group By ENTRIES.ID_ENTRIES
            ;

            leave entr_computed_data_nbdocs;

        end ;
        bcednd_process_filtered: Begin
            -- on est dans ce block si 
            -- - soit idaccounts est fournis 
            -- - soit identities est fournis
            -- - soit tmpTableNameOfEntr est fournis
            bcednd_process_preptbl: Begin
                Drop Temporary Table If Exists TMP_ENTRIES_COMPUTED_DATA_ENTRIES;
                Create Temporary Table If Not Exists TMP_ENTRIES_COMPUTED_DATA_ENTRIES (
                            ENTRIES_ID_ENTRIES Int Unsigned Not Null
                            , Index(ENTRIES_ID_ENTRIES)
                        )
                    ;

                set @bcednd_isertQuery = Concat (
                            'Insert Into TMP_ENTRIES_COMPUTED_DATA_ENTRIES(ENTRIES_ID_ENTRIES) '
                            , 'Select eha.ENTRIES_ID_ENTRIES '
                            , 'From ENTRIES_has_ACCOUNTS eha '
                            , '     Inner Join ACCOUNTS acc '
                            , '         On eha.ACCOUNTS_ID_ACCOUNTS = acc.ID_ACCOUNTS '
                            , 'Where eha.ENTRIES_PERMANENT_ATTACHEMENT = 1 ' 
                        )
                    ;
                if @bcednd_idaccounts <> 0 then
                    set @bcednd_isertQuery = Concat (@bcednd_isertQuery,  ' '
#                                 , ' And eha.ACCOUNTS_ID_ACCOUNTS = ', @bcednd_idaccounts
                                , ' And acc.ID_ACCOUNTS = ', @bcednd_idaccounts
                            )
                        ;
                end if;
                
                if @bcednd_identities <> 0 then
                    set @bcednd_isertQuery = Concat (@bcednd_isertQuery,  ' '
                                , ' And acc.ENTITIES_ID_ENTITIES = ', @bcednd_identities
                            )
                        ;
                end if;
                
                
                if @bcednd_tmpTableNameOfEntr <> '' then
                    set @bcednd_isertQuery = Concat (@bcednd_isertQuery,  ' '
                                , ' And Exists( '
                                , '  Select src.ENTRIES_ID_ENTRIES '
                                , '  From `', @bcednd_tmpTableNameOfEntr, '` src '
                                , '  Where eha.ENTRIES_ID_ENTRIES = src.ENTRIES_ID_ENTRIES '
                                , '  And src.ENTRIES_ID_ENTRIES Is Not Null '
                                , '  Limit 1 '
                                , ' ) '
                            )
                        ;
                end if;

                
                PREPARE bcednd_isertQueryStmt FROM @bcednd_isertQuery; 
				execute bcednd_isertQueryStmt 
					;
				Deallocate prepare bcednd_isertQueryStmt; 		

            end;
            
            bcednd_process_del: Begin


                -- purge des données
                Delete ENTRIES_COMPUTED_DATA
                -- explain select *
                From ENTRIES_COMPUTED_DATA
                Where ENTRIES_COMPUTED_DATA_KEY = 'NBDOCS'
                    AND ENTRIES_ID_ENTRIES IN (
                        Select src.ENTRIES_ID_ENTRIES
                        From TMP_ENTRIES_COMPUTED_DATA_ENTRIES src
                    )
                ;
            End;
            bcednd_process_ins: Begin
                -- mise à jour des données
                Insert Into ENTRIES_COMPUTED_DATA (
                    ENTRIES_ID_ENTRIES, ENTRIES_COMPUTED_DATA_KEY, ENTRIES_COMPUTED_DATA_INT
                )
                -- explain
                Select
                    ENTRIES.ID_ENTRIES, 'NBDOCS', Count(ATTACHED_DOCUMENTS.ID_ATTACHED_DOCUMENTS)
                From 
                    ENTRIES
                    LEFT JOIN ENTRIES_has_ATTACHED_DOCUMENTS
                        ON ENTRIES.ID_ENTRIES = ENTRIES_has_ATTACHED_DOCUMENTS.ENTRIES_ID_ENTRIES
                    Left Join ATTACHED_DOCUMENTS
                        ON ENTRIES_has_ATTACHED_DOCUMENTS.ATTACHED_DOC_ID_ATTACHED_DOC = ATTACHED_DOCUMENTS.ID_ATTACHED_DOCUMENTS
                        And ATTACHED_DOCUMENTS.ATTACHED_DOCUMENTS_DATE <= @bcednd_refdate
                Where 
                    ENTRIES.ENTRIES_DATE <= @bcednd_refdate
                  AND ENTRIES.ID_ENTRIES IN (
                    Select src.ENTRIES_ID_ENTRIES
                    From TMP_ENTRIES_COMPUTED_DATA_ENTRIES src
                )
                Group By ENTRIES.ID_ENTRIES
                ;
                
            end ;
        end ;


    end;
    
end $$
Delimiter ;
