Delimiter //
/**
	Procédure qui supprime toutes les tables d'accréditation pré-calculé des membres supprimés
*/
Drop Procedure if Exists BUSINESS_COMPUTE_MEMBERS_CREDENTIALS_CLEAR_DELETED//
 
Create Procedure BUSINESS_COMPUTE_MEMBERS_CREDENTIALS_CLEAR_DELETED()
Begin
    memberCredentialClearAll: begin
		Declare credentialTblName varchar(50);
		Declare credTblLstCursor Cursor For
			Select 
				TBLS.`TABLE_NAME`
			From information_schema.TABLES TBLS
				Left Join MEMBERS
					On TBLS.`TABLE_NAME` like Concat('TMP_%_CREDENTIALS_MEMBER__', ID_MEMBERS)
			where TBLS.TABLE_SCHEMA = schema()
				And TBLS.`TABLE_NAME` like 'TMP_%_CREDENTIALS_MEMBER__%'
				And TBLS.`TABLE_NAME` Not In ('TMP_ACCOUNTS_CREDENTIALS_MEMBER__ALL')
				And ID_MEMBERS Is Null
			;
		Open credTblLstCursor;
		credTableBlk: Begin
			Declare Exit Handler for NOT FOUND 
                Begin
                    Close credTblLstCursor;
                End ;
			credTableDropLoop: loop
				fetch credTblLstCursor Into credentialTblName;
				set @query = Concat('Drop Table if exists ', credentialTblName);
		        PREPARE credTblDropStmt FROM @query; 
				execute credTblDropStmt 
					;
				Deallocate prepare credTblDropStmt; 				
			End loop credTableDropLoop;
		End;

		Set @mbrAlltableExists = Coalesce(
								(
									Select count(*)
									From information_schema.TABLES
									Where
									TABLE_SCHEMA = schema()
									And `TABLE_NAME` = 'TMP_ACCOUNTS_CREDENTIALS_MEMBER__ALL'
								)
								, 0
							);
		if @mbrAlltableExists > 0 Then
			-- purges des membres supprimer dans la table global
			Delete tar
			from TMP_ACCOUNTS_CREDENTIALS_MEMBER__ALL  tar
				Left Join MEMBERS src
					On tar.MEMBERS_ID_MEMBERS = src.ID_MEMBERS
			WHERE src.ID_MEMBERS Is Null;
		End if;
		

# 		Drop Temporary table If exists TMP_DF_ACCESS_PROFILES1;
# 		Drop Temporary table If exists TMP_DF_ACCESS_PROFILES2;

    end ;
end //
Delimiter ;

