Delimiter //
/**
	procédure qui détermine les liste des profiles du membre non applicable par compte
	-> Creation et remplissable de la table temporraire TMP_DENIED_ENTITIES_has_MEMBERS
    @deprecated
	contre performant si il y a un grand nombre de règles
    n'est plus utilisé
*/
Drop Procedure if Exists BUSINESS_COMPUTE_MEMBERS_CREDENTIALS_DENIED_PROFILES//
Create Procedure BUSINESS_COMPUTE_MEMBERS_CREDENTIALS_DENIED_PROFILES(
	In memberId Int Unsigned 
	, In user_lng Int Unsigned 
	, In ref_type Int Unsigned 
)
Begin
	memberAccCredentialDeniedProfilesInit: Begin
		set @ref_type  = ref_type;
		set @user_lng  = user_lng;
		Set @user_id = memberId;

		Drop Temporary Table If Exists TMP_DENIED_ENTITIES_has_MEMBERS;
		Drop Temporary Table If Exists TMP_DENIED_ENTITIES_has_DELECATEDMEMBERS;
		Drop Temporary Table If Exists TMP_DENIED_ENTITIES_has_DELECATEDMEMBERS0;
	End;

	memberAccCredentialDeniedProfilesDelectedMbr: Begin
		Create Temporary Table TMP_DENIED_ENTITIES_has_DELECATEDMEMBERS  (
			ID_ENTITIES Int Unsigned Not Null
			, ID_ENTITIES_REF_TYPES  Int Unsigned Not Null
			, ID_ENTITIES_REF Int Unsigned Not Null
			, ID_ENTITIES_has_MEMBERS Int Unsigned Null
			, DF_LANGUAGES_ID_DF_LANGUAGES  Int Unsigned Null
			, ID_ENTITIES_has_TEAMS  Int Unsigned Null
			, ENTT_RF_DATAS_ID_ENTT_RF_DATAS Int Unsigned Null
			, Index IX_TMP_DENIED_ENTITIES_has_DELECATEDMEMBERS_1 (ID_ENTITIES)
			, Index IX_TMP_DENIED_ENTITIES_has_DELECATEDMEMBERS_2 (ID_ENTITIES_REF_TYPES)
			, Index IX_TMP_DENIED_ENTITIES_has_DELECATEDMEMBERS_3 (ID_ENTITIES_REF)
			, Index IX_TMP_DENIED_ENTITIES_has_DELECATEDMEMBERS_4 (DF_LANGUAGES_ID_DF_LANGUAGES)
			, Index IX_TMP_DENIED_ENTITIES_has_DELECATEDMEMBERS_5 (ENTT_RF_DATAS_ID_ENTT_RF_DATAS)
		
		);

		Create Temporary Table TMP_DENIED_ENTITIES_has_DELECATEDMEMBERS0  (
			ID_ENTITIES Int Unsigned Not Null
			, ID_ENTITIES_REF_TYPES  Int Unsigned Not Null
			, ID_ENTITIES_REF Int Unsigned Not Null
			, ID_ENTITIES_has_MEMBERS Int Unsigned Null
			, ID_ENTITIES_has_TEAMS  Int Unsigned Null
			, DF_LANGUAGES_ID_DF_LANGUAGES Int Unsigned Null
			, ENTT_RF_DATAS_ID_ENTT_RF_DATAS Int Unsigned Null
		);
		Insert TMP_DENIED_ENTITIES_has_DELECATEDMEMBERS0 (
			ID_ENTITIES
			, ID_ENTITIES_REF_TYPES
			, ID_ENTITIES_REF
			, ID_ENTITIES_has_MEMBERS
			, ID_ENTITIES_has_TEAMS
			, DF_LANGUAGES_ID_DF_LANGUAGES
			, ENTT_RF_DATAS_ID_ENTT_RF_DATAS
		)
		Select 
			EMR.ID_ENTITIES
			, EMR.ID_ENTITIES_REF_TYPES
			, EMR.ID_ENTITIES_REF
			, EMR.ID_ENTITIES_has_MEMBERS
			, null
			, EMR.DF_LANGUAGES_ID_DF_LANGUAGES
			, EMR.ENTT_RF_DATAS_ID_ENTT_RF_DATAS
		From 
			V_ENTITIES_MEMBERS_REF EMR
			Inner Join V_MEMBERS_DELEGATED VMD
-- 				On EMR.ID_MEMBERS = VMD.MEMBERS_DELEGATED
				On EMR.ID_MEMBERS = VMD.ID_MEMBERS
		where
-- 			VMD.ID_MEMBERS = @user_id
			VMD.MEMBERS_DELEGATED = @user_id
			And EMR.DF_LANGUAGES_ID_DF_LANGUAGES = @user_lng
			And EMR.ID_ENTITIES_REF_TYPES = @ref_type
			And EMR.ENTT_RF_DATAS_ID_ENTT_RF_DATAS Is Not Null
		Union All
		Select Distinct 
			ETR.ID_ENTITIES
			, ETR.ID_ENTITIES_REF_TYPES
			, ETR.ID_ENTITIES_REF
			, null
			, THA1.ID_TEAMS_has_MEMBERS
			, ETR.DF_LANGUAGES_ID_DF_LANGUAGES
			, ETR.ENT_REF_DATAS_ID_ENT_REF_DATAS
		From 
			V_ENTITIES_TEAMS_REF ETR
			Inner Join TEAMS_has_MEMBERS THA1
				On ETR.ID_TEAMS = THA1.TEAMS_ID_TEAMS
			Inner Join V_MEMBERS_DELEGATED VMD
				On THA1.MEMBERS_ID_MEMBERS = VMD.ID_MEMBERS
		where 1 = 1
			And VMD.MEMBERS_DELEGATED = @user_id
			And ETR.DF_LANGUAGES_ID_DF_LANGUAGES = @user_lng
			And ETR.ID_ENTITIES_REF_TYPES = @ref_type
			And ETR.ENT_REF_DATAS_ID_ENT_REF_DATAS Is Not Null
		;
-- 		Create Index IX_TMP_DENIED_ENTITIES_has_DELECATEDMEMBERS_1 On TMP_DENIED_ENTITIES_has_DELECATEDMEMBERS(ID_ENTITIES);
-- 		Create Index IX_TMP_DENIED_ENTITIES_has_DELECATEDMEMBERS_2 On TMP_DENIED_ENTITIES_has_DELECATEDMEMBERS(ID_ENTITIES_REF_TYPES);
-- 		Create Index IX_TMP_DENIED_ENTITIES_has_DELECATEDMEMBERS_3 On TMP_DENIED_ENTITIES_has_DELECATEDMEMBERS(ID_ENTITIES_REF);
-- 		Create Index IX_TMP_DENIED_ENTITIES_has_DELECATEDMEMBERS_4 On TMP_DENIED_ENTITIES_has_DELECATEDMEMBERS(DF_LANGUAGES_ID_DF_LANGUAGES);
-- 		Create Index IX_TMP_DENIED_ENTITIES_has_DELECATEDMEMBERS_5 On TMP_DENIED_ENTITIES_has_DELECATEDMEMBERS(ENTT_RF_DATAS_ID_ENTT_RF_DATAS);
		
		Insert TMP_DENIED_ENTITIES_has_DELECATEDMEMBERS (
			ID_ENTITIES
			, ID_ENTITIES_REF_TYPES
			, ID_ENTITIES_REF
			, ID_ENTITIES_has_MEMBERS
			, ID_ENTITIES_has_TEAMS
			, DF_LANGUAGES_ID_DF_LANGUAGES
			, ENTT_RF_DATAS_ID_ENTT_RF_DATAS
		)
		Select Distinct 
			ID_ENTITIES
			, ID_ENTITIES_REF_TYPES
			, ID_ENTITIES_REF
			, ID_ENTITIES_has_MEMBERS
			, ID_ENTITIES_has_TEAMS
			, DF_LANGUAGES_ID_DF_LANGUAGES
			, ENTT_RF_DATAS_ID_ENTT_RF_DATAS
		From TMP_DENIED_ENTITIES_has_DELECATEDMEMBERS0
		;
		Drop Temporary Table if exists TMP_DENIED_ENTITIES_has_DELECATEDMEMBERS0;

	end;

	memberAccCredentialDeniedProfilesMain: Begin
-- 		Create Temporary Table TMP_DENIED_ENTITIES_has_MEMBERS AS
		Create Temporary Table TMP_DENIED_ENTITIES_has_MEMBERS (
			ENTITIES_ID_ENTITIES Int Unsigned Not Null
			, ACCOUNTS_ID_ACCOUNTS Int Unsigned Not Null
			, ID_ENTITIES_has_MEMBERS Int Unsigned Null
			, ID_ENTITIES_has_TEAMS Int Unsigned Null
			, Index IX_TMP_DENIED_ENTITIES_has_MEMBERS_1 (ACCOUNTS_ID_ACCOUNTS)
			, Index IX_TMP_DENIED_ENTITIES_has_MEMBERS_2 (ID_ENTITIES_has_MEMBERS)
			, Index IX_TMP_DENIED_ENTITIES_has_MEMBERS_3 (ID_ENTITIES_has_TEAMS)
			, Index IX_TMP_DENIED_ENTITIES_has_MEMBERS_4 (ENTITIES_ID_ENTITIES)
		)
		;

-- debug
leave memberAccCredentialDeniedProfilesMain;

		Insert Into TMP_DENIED_ENTITIES_has_MEMBERS (
			ENTITIES_ID_ENTITIES, ACCOUNTS_ID_ACCOUNTS
			, ID_ENTITIES_has_MEMBERS, ID_ENTITIES_has_TEAMS
		)
		-- explain
		Select 
-- 			ARD.ENTITIES_ID_ENTITIES, EMR.ID_ENTITIES_has_MEMBERS, ARD.ACCOUNTS_ID_ACCOUNTS
			ARD.ENTITIES_ID_ENTITIES, ARD.ACCOUNTS_ID_ACCOUNTS
			, EMR.ID_ENTITIES_has_MEMBERS, EMR.ID_ENTITIES_has_TEAMS
		    -- select count(*)
		From
			TMP_DENIED_ENTITIES_has_DELECATEDMEMBERS EMR
			Inner Join V_ACC_REF_DATAS ARD
				On EMR.ID_ENTITIES = ARD.ENTITIES_ID_ENTITIES
				And EMR.ID_ENTITIES_REF_TYPES  = ARD.ID_ENTITIES_REF_TYPES-- util ? implicite
				And EMR.ID_ENTITIES_REF = ARD.ID_ENTITIES_REF
				And EMR.DF_LANGUAGES_ID_DF_LANGUAGES = ARD.ID_DF_LANGUAGES
# 				And EMR.ENTT_RF_DATAS_ID_ENTT_RF_DATAS = ARD.ENT_REF_DATAS_ID_ENT_REF_DATAS
				And EMR.ENTT_RF_DATAS_ID_ENTT_RF_DATAS <> ARD.ENT_REF_DATAS_ID_ENT_REF_DATAS
		Where
			ARD.ID_DF_LANGUAGES = @user_lng
			And ARD.ID_ENTITIES_REF_TYPES = @ref_type
			And ARD.ENT_REF_DATAS_ID_ENT_REF_DATAS is Not Null
#         limit 10000000
		;
		
	End;

End //
Delimiter ;

