Delimiter //
/**
	procédure qui rempli la table précalculée des profils a utilser par compte pour le membre
*/
Drop Procedure if Exists BUSINESS_COMPUTE_MEMBERS_CREDENTIALS_FILL_PROFILE_TABLE//
Create Procedure BUSINESS_COMPUTE_MEMBERS_CREDENTIALS_FILL_PROFILE_TABLE(
	In memberId Int Unsigned 
	, In user_lng Int Unsigned 
	, In ref_type Int Unsigned 
)
Begin
    bcmcfpt_main: begin
        bcmcfpt_init: begin
            set @bcmcfpt_ref_type  = ref_type;
            set @bcmcfpt_user_lng  = user_lng;
		    Set @bcmcfpt_user_id = memberId;
        end ;
        bcmcfpt_filltbl1: begin
            Drop temporary table if exists TMP_MBR_ACCRED_UNFILTERED;
            Create Temporary Table TMP_MBR_ACCRED_UNFILTERED as
-- explain
            Select
                tacc.ACCOUNTS_ID_ACCOUNTS
                 , (
                    Select tmac.DF_ACCESS_PROFILES_ID_DF_ACCESS_PROFILES
                    From TMP_MBRE_FULL_ACRED_DATA tmac
                    Where tacc.ENTITIES_ID_ENTITIES = tmac.ENTITIES_ID_ENTITIES
                      And (
                          (
                              tmac.ACCREDT1 In (0, tacc.ACCREDT1)
                              And tmac.ACCREDT2 In (0, tacc.ACCREDT2)
                              And tmac.ACCREDT3 In (0, tacc.ACCREDT3)
                              And tmac.ACCREDT4 In (0, tacc.ACCREDT4)
                              And tmac.ACCREDT5 In (0, tacc.ACCREDT5)
                          )
                          Or (
                              tacc.ACCREDT1 In (0, tmac.ACCREDT1)
                              And tacc.ACCREDT2 In (0, tmac.ACCREDT2)
                              And tacc.ACCREDT3 In (0, tmac.ACCREDT3)
                              And tacc.ACCREDT4 In (0, tmac.ACCREDT4)
                              And tacc.ACCREDT5 In (0, tmac.ACCREDT5)
                          )
                        )
                    Order By tmac.ENTITIES_ID_ENTITIES, tmac.DF_ACCESS_PROFILES_MNGT_LVL
                    Limit 1
                ) DF_ACCESS_PROFILES_ID_DF_ACCESS_PROFILES
            From TMP_ACC_FULL_ACRED_DATA tacc
            ;
        end ;
        bcmcfpt_fillmbrtbl: begin

            call BUSINESS_COMPUTE_MEMBERS_CREDENTIALS_CREATE_PROFILE_TABLE(@bcmcfpt_user_id
                                                                        , @bcmcfpt_user_lng
                                                                        , @bcmcfpt_ref_type
                                                                        , @bcmcfpt_tblName
                                                                    )
                    ;

            Set @bcmcfpt_mbrtblquery = Concat(
                    ' Insert Into ', @bcmcfpt_tblName,' ( '
                    , '		ACCOUNTS_ID_ACCOUNTS '
                    , '		, MEMBERS_ID_MEMBERS '
                    , '		, DF_ACCESS_PROFILES_ID_DF_ACCESS_PROFILES '
                    , ' ) '
                    ' Select ACCOUNTS_ID_ACCOUNTS, ? '
                    , ' , DF_ACCESS_PROFILES_ID_DF_ACCESS_PROFILES '
                    , ' From TMP_MBR_ACCRED_UNFILTERED '
                    , ' Where '
                    , '  DF_ACCESS_PROFILES_ID_DF_ACCESS_PROFILES Is Not Null '
                )
            ;

            PREPARE bcmcfpt_insertTblMbrStm FROM @bcmcfpt_mbrtblquery;
            EXECUTE bcmcfpt_insertTblMbrStm
				using
					@bcmcma_user_id
				;
			Deallocate prepare bcmcfpt_insertTblMbrStm;

        end ;
        bcmcfpt_fillalltbl: begin
            -- ajout des données dans la table global
		Set @bcmcfpt_alltblquery = concat(
				' Insert Into TMP_ACCOUNTS_CREDENTIALS_MEMBER__ALL ( '
				, '		ACCOUNTS_ID_ACCOUNTS, MEMBERS_ID_MEMBERS, DF_ACC_PRFLS_ID_DF_ACC_PRFLS '
				, ' ) '
				, ' Select  '
				, '		MBR.ACCOUNTS_ID_ACCOUNTS, MBR.MEMBERS_ID_MEMBERS, DF_ACCESS_PROFILES_ID_DF_ACCESS_PROFILES'
				, '  From ', @bcmcfpt_tblName, ' MBR '
				, '		Left Join TMP_ACCOUNTS_CREDENTIALS_MEMBER__ALL TALL '
				, '			On MBR.ACCOUNTS_ID_ACCOUNTS = TALL.ACCOUNTS_ID_ACCOUNTS '
				, '			And MBR.MEMBERS_ID_MEMBERS = TALL.MEMBERS_ID_MEMBERS '
				, ' Where TALL.ACCOUNTS_ID_ACCOUNTS Is Null'
			);

		    PREPARE bcmcfpt_insertTblAllStm FROM @bcmcfpt_alltblquery;
			execute bcmcfpt_insertTblAllStm
				;
			Deallocate prepare bcmcfpt_insertTblAllStm;
        end ;

    end ;
#
# 	memberAccCredentialFillProfilesInit: Begin
# 		set @bcmcfpt_ref_type  = ref_type;
# 		set @bcmcfpt_user_lng  = user_lng;
# 		Set @bcmcfpt_user_id = memberId;
# 	End;
#
# 	memberAccCredentialFillProfilesMain: Begin
#
# 		-- limitation mysql ??
# 		-- la sous requete executé dans l'ensemble ne donne avec les equipe ne donne pas les résulat escompté
# 		-- la sous requete isolé semble fonctionner
# 		-- on utilise une table temporaire
#
# 		Drop Temporary table if exists TMP_MBR_PROFILE_LEVEL;
# 		Create Temporary table  TMP_MBR_PROFILE_LEVEL As
#
# 		Select
# 			MFC.ACCOUNTS_ID_ACCOUNTS
# 			, @bcmcfpt_user_id
# -- 			, max(DFP1.DF_ACCESS_PROFILES_MNGT_LVL) DF_ACCESS_PROFILES_MNGT_LVL
# 			, min(DFP1.DF_ACCESS_PROFILES_MNGT_LVL) DF_ACCESS_PROFILES_MNGT_LVL
#
# 		From
#             TMP_MBR_FULL_ACC MFC
# 			Left Join ENTITIES_has_MEMBERS EHM1
# 				On MFC.ID_ENTITIES_has_MEMBERS = EHM1.ID_ENTITIES_has_MEMBERS
# 			Left Join ENTITIES_has_TEAMS EHT1
# 				On MFC.ID_ENTITIES_has_TEAMS = EHT1.ID_ENTITIES_has_TEAMS
# 			Inner Join DF_ACCESS_PROFILES DFP1
# 				On DFP1.ID_DF_ACCESS_PROFILES = Coalesce(EHM1.DF_ACCESS_PROFILES_ID_DF_ACCESS_PROFILES, EHT1.DF_ACCESS_PROFILES_ID_DF_ACCESS_PROFILES)
# 		Where  1 = 1
# 		Group By  MFC.ACCOUNTS_ID_ACCOUNTS
# 		;
# 		Create Index IX_TMP_MBR_PROFILE_LEVEL_1 On TMP_MBR_PROFILE_LEVEL (ACCOUNTS_ID_ACCOUNTS);
#
#
#         memberAccCredentialFillClearOld: Begin
# --             -- supression des anciennes accredition du membres au dernier moment
# --             -- de maniere à limiter le temps ou le membre ne peux plus intervenir sur le compte
# --             -- supression des précalcul
# --             -- même si supprimé à l'aide de BUSINESS_COMPUTE_MEMBERS_CREDENTIALS_CLEAR_ALL
# --             -- -> permet d'appeler cette procédure en solo (maj de membre par exemple)
#             call BUSINESS_COMPUTE_MEMBERS_CREDENTIALS_CREATE_PROFILE_TABLE(@bcmcfpt_user_id
#                                                                         , @bcmcfpt_user_lng
#                                                                         , @bcmcfpt_ref_type
#                                                                         , @bcmcfpt_tblName
#                                                                     )
#                     ;
#         End;
#
#             -- détermination du profiles à utiliser pour le membre par compte
# 			-- on choisie dans la liste des profils
# 			--  ceux qui ne sont pas exclus
# 			--  ayant le niveau le plus fort (valeur entier la plus petites)
# 			-- arbitrairement on prend le profile avec l'id le plus grand (dernier profiel créé)
#             -- insertion dans la table dédié des accrediton du membre
# 			Set @query = concat(
# 				' Insert Into ', @bcmcfpt_tblName,' ( '
# 				, '		ACCOUNTS_ID_ACCOUNTS '
# 				, '		, MEMBERS_ID_MEMBERS '
# 				, '		, DF_ACCESS_PROFILES_ID_DF_ACCESS_PROFILES '
# 				, ' ) '
# 				, 'Select '
# 				, '	TMFA.ACCOUNTS_ID_ACCOUNTS '
# 				, '	, ? ' -- @bcmcfpt_user_id
# 				, '	, max(Coalesce(DFA1.ID_DF_ACCESS_PROFILES, DFA2.ID_DF_ACCESS_PROFILES)) '
# 				, 'From TMP_MBR_FULL_ACC TMFA '
# 				, '	Left Join ( '
# 				, '		ENTITIES_has_MEMBERS EHA '
# 				, '			Inner join DF_ACCESS_PROFILES DFA1 '
# 				, '				On EHA.DF_ACCESS_PROFILES_ID_DF_ACCESS_PROFILES = DFA1.ID_DF_ACCESS_PROFILES '
# 				, '	)  On TMFA.ID_ENTITIES_has_MEMBERS = EHA.ID_ENTITIES_has_MEMBERS '
# 				, '	Left Join ( '
# 				, '		ENTITIES_has_TEAMS EHT '
# 				, '			Inner join DF_ACCESS_PROFILES DFA2 '
# 				, '				On EHT.DF_ACCESS_PROFILES_ID_DF_ACCESS_PROFILES = DFA2.ID_DF_ACCESS_PROFILES '
# 				, '	)  On TMFA.ID_ENTITIES_has_TEAMS = EHT.ID_ENTITIES_has_TEAMS '
# 				, '	Inner Join TMP_MBR_PROFILE_LEVEL TMPL '
# 				, '		On TMFA.ACCOUNTS_ID_ACCOUNTS = TMPL.ACCOUNTS_ID_ACCOUNTS '
# 				, '		And TMPL.DF_ACCESS_PROFILES_MNGT_LVL = Coalesce(DFA1.DF_ACCESS_PROFILES_MNGT_LVL, DFA2.DF_ACCESS_PROFILES_MNGT_LVL) '
# 				, 'Group by TMFA.ACCOUNTS_ID_ACCOUNTS '
#
# -- 				, 'Select	 '
# -- 				, '		MFC.ACCOUNTS_ID_ACCOUNTS  '
# -- 				, '	,  ', @bcmcfpt_user_id
# -- 				, '	, (  '
# -- 				, '		Select  '
# -- 				, '			min(DFP2.ID_DF_ACCESS_PROFILES)  '
# -- 				, '		From MEMBERS MBR2 '
# -- 				, '			Inner Join ENTITIES_has_MEMBERS EHM2  '
# -- 				, '				On MBR2.ID_MEMBERS = EHM2.MEMBERS_ID_MEMBERS '
# -- 				, '			Inner Join DF_ACCESS_PROFILES DFP2  '
# -- 				, '				On DFP2.ID_DF_ACCESS_PROFILES  = Coalesce(EHM2.DF_ACCESS_PROFILES_ID_DF_ACCESS_PROFILES, MBR2.DF_ACCSS_PRFL_ID_DF_ACCSS_PRFL ) '
# -- 				, '		Where EHM2.MEMBERS_ID_MEMBERS =  ? ' --  @bcmcfpt_user_id
# -- 				, '			And max(DFP1.DF_ACCESS_PROFILES_MNGT_LVL)  = DFP2.DF_ACCESS_PROFILES_MNGT_LVL  '
# -- 				, '			And max(EHM1.ENTITIES_ID_ENTITIES)  = EHM2.ENTITIES_ID_ENTITIES  '
# -- 				, '	) DF_ACCESS_PROFILES_ID_DF_ACCESS_PROFILES  '
# -- 				, 'From  '
# -- 				, '	MEMBERS MBR1 '
# -- 				, '	Inner Join ENTITIES_has_MEMBERS EHM1  '
# -- 				, '		On MBR1.ID_MEMBERS = EHM1.MEMBERS_ID_MEMBERS '
# -- 				, '	Inner Join TMP_MBR_FULL_ACC MFC  '
# -- 				, '		On MFC.ID_ENTITIES_has_MEMBERS = EHM1.ID_ENTITIES_has_MEMBERS  '
# -- 				, '	Inner Join DF_ACCESS_PROFILES DFP1  '
# -- 				, '		On DFP1.ID_DF_ACCESS_PROFILES = Coalesce(EHM1.DF_ACCESS_PROFILES_ID_DF_ACCESS_PROFILES, MBR1.DF_ACCSS_PRFL_ID_DF_ACCSS_PRFL ) '
# -- 				, '	Left Join TMP_DENIED_ENTITIES_has_MEMBERS TDBM  '
# -- 				, '		On MFC.ACCOUNTS_ID_ACCOUNTS = TDBM.ACCOUNTS_ID_ACCOUNTS  '
# -- 				, '		And MFC.ID_ENTITIES_has_MEMBERS = TDBM.ID_ENTITIES_has_MEMBERS  '
# -- 				, ' Where  '
# -- 				, '		EHM1.MEMBERS_ID_MEMBERS = ? ' --   @bcmcfpt_user_id
# -- 				, '		And TDBM.ACCOUNTS_ID_ACCOUNTS Is Null  '
# -- 				, ' Group By  MFC.ACCOUNTS_ID_ACCOUNTS  '
# 			);
#
#
# 			PREPARE insertTblStm FROM @query;
# 			execute insertTblStm
# 				using
# 					@bcmcfpt_user_id
# 				-- , @bcmcfpt_user_id
# 				;
# 			Deallocate prepare insertTblStm;
#
# 	End;
#
# 	memberAccCredentialFillGlobalProfiles: Begin
# 		-- ajout des données dans la table global
# 		Set @query = concat(
# 				' Insert Into TMP_ACCOUNTS_CREDENTIALS_MEMBER__ALL ( '
# -- 				, '		ACCOUNTS_ID_ACCOUNTS, MEMBERS_ID_MEMBERS '
# 				, '		ACCOUNTS_ID_ACCOUNTS, MEMBERS_ID_MEMBERS, DF_ACC_PRFLS_ID_DF_ACC_PRFLS '
# 				, ' ) '
# 				, ' Select  '
# -- 				, '		MBR.ACCOUNTS_ID_ACCOUNTS, MBR.MEMBERS_ID_MEMBERS '
# 				, '		MBR.ACCOUNTS_ID_ACCOUNTS, MBR.MEMBERS_ID_MEMBERS, DF_ACCESS_PROFILES_ID_DF_ACCESS_PROFILES'
# 				, '  From ', @bcmcfpt_tblName, ' MBR '
# 				, '		Left Join TMP_ACCOUNTS_CREDENTIALS_MEMBER__ALL TALL '
# 				, '			On MBR.ACCOUNTS_ID_ACCOUNTS = TALL.ACCOUNTS_ID_ACCOUNTS '
# 				, '			And MBR.MEMBERS_ID_MEMBERS = TALL.MEMBERS_ID_MEMBERS '
# 				, ' Where TALL.ACCOUNTS_ID_ACCOUNTS Is Null'
# 			);
#
#
# 		PREPARE insertGlobTblStm FROM @query;
# 			execute insertGlobTblStm
# 				;
# 			Deallocate prepare insertGlobTblStm;
#
# 	End ;

End //
Delimiter ;

