Delimiter //
/**
	procédure qui détermine les liste  des tous les profiles du membre par compte
    creation et remplissage de la table TMP_ACC_FULL_ACRED_DATA
	Doit être lancé après BUSINESS_COMPUTE_MEMBERS_CREDENTIALS_DELECATED_MEMBERS qui remplit la table TMP_MBRE_FULL_ACRED_DATA.
    la table TMP_MBRE_FULL_ACRED_DATA  ne concerne que le membre en cours de traitements
    aussi le paramètre du membre en cours de traitement n'est pas nécéssaire
*/
Drop Procedure if Exists BUSINESS_COMPUTE_MEMBERS_CREDENTIALS_ALL_PROFILES//
Create Procedure BUSINESS_COMPUTE_MEMBERS_CREDENTIALS_ALL_PROFILES(
	In enttRefTpeAccAcrd Int Unsigned
)
Begin
    bcmcap_main: Begin
        bcmcap_init: Begin

            if enttRefTpeAccAcrd Is Null Then
                set @bcmcap_enttRefTpeAccAcrd = (select ID_ENTITIES_REF_TYPES  from ENTITIES_REF_TYPES where ENTITIES_REF_TYPES_CODE = 'ACCRED_ACCOUNTS');
            Else
                set @bcmcap_enttRefTpeAccAcrd = enttRefTpeAccAcrd;
            End if;

            Drop Temporary Table if exists TMP_ACC_FULL_ACRED_DATA;
            Create Temporary Table TMP_ACC_FULL_ACRED_DATA (
                    ACCOUNTS_ID_ACCOUNTS Int Unsigned Not Null Primary key
                    , ENTITIES_ID_ENTITIES Int Unsigned Not Null
                    , ACCREDT1 Int Unsigned Not Null Default 0
                    , ACCREDT2 Int Unsigned Not Null Default 0
                    , ACCREDT3 Int Unsigned Not Null Default 0
                    , ACCREDT4 Int Unsigned Not Null Default 0
                    , ACCREDT5 Int Unsigned Not Null Default 0
                    , index (ENTITIES_ID_ENTITIES)
                    , index (ACCREDT1)
                    , index (ACCREDT2)
                    , index (ACCREDT3)
                    , index (ACCREDT4)
                    , index (ACCREDT5)
                )
            ;
	    End;
        bcmcap_filltbl: Begin
            Insert Into TMP_ACC_FULL_ACRED_DATA (
                  ACCOUNTS_ID_ACCOUNTS
                , ENTITIES_ID_ENTITIES
                , ACCREDT1
                , ACCREDT2
                , ACCREDT3
                , ACCREDT4
                , ACCREDT5
            )
-- explain
            Select
                acc.ID_ACCOUNTS, acc.ENTITIES_ID_ENTITIES
                 , Coalesce(
                    (
                        Select
                            acrd1.ENT_REF_DATAS_ID_ENT_REF_DATAS
                        From
                            ACC_REF_DATAS acrd1
                                Inner Join ENTITIES_REF_DATAS erdt1
                                           On acrd1.ENT_REF_DATAS_ID_ENT_REF_DATAS = erdt1.ID_ENTITIES_REF_DATAS
                                Inner Join ENTITIES_REF er1
                                           On erdt1.ENTITIES_REF_ID_ENTITIES_REF = er1.ID_ENTITIES_REF
                        Where
                                er1.ENT_REF_TYPE_ID_ENT_REF_TYPES = @bcmcap_enttRefTpeAccAcrd
                          And er1.ENTITIES_REF_SEQUENCE = 1
                          And acc.ID_ACCOUNTS = acrd1.ACCOUNTS_ID_ACCOUNTS
                        Limit 1
                    ), 0
                ) ACCRED1
                 , Coalesce(
                    (
                        Select
                            acrd2.ENT_REF_DATAS_ID_ENT_REF_DATAS
                        From
                            ACC_REF_DATAS acrd2
                                Inner Join ENTITIES_REF_DATAS erdt2
                                           On acrd2.ENT_REF_DATAS_ID_ENT_REF_DATAS = erdt2.ID_ENTITIES_REF_DATAS
                                Inner Join ENTITIES_REF er2
                                           On erdt2.ENTITIES_REF_ID_ENTITIES_REF = er2.ID_ENTITIES_REF
                        Where
                                er2.ENT_REF_TYPE_ID_ENT_REF_TYPES = @bcmcap_enttRefTpeAccAcrd
                          And er2.ENTITIES_REF_SEQUENCE = 2
                          And acc.ID_ACCOUNTS = acrd2.ACCOUNTS_ID_ACCOUNTS
                        Limit 1
                    ), 0
                ) ACCRED2
                 , Coalesce(
                    (
                        Select
                            acrd3.ENT_REF_DATAS_ID_ENT_REF_DATAS
                        From
                            ACC_REF_DATAS acrd3
                                Inner Join ENTITIES_REF_DATAS erdt3
                                           On acrd3.ENT_REF_DATAS_ID_ENT_REF_DATAS = erdt3.ID_ENTITIES_REF_DATAS
                                Inner Join ENTITIES_REF er3
                                           On erdt3.ENTITIES_REF_ID_ENTITIES_REF = er3.ID_ENTITIES_REF
                        Where
                                er3.ENT_REF_TYPE_ID_ENT_REF_TYPES = @bcmcap_enttRefTpeAccAcrd
                          And er3.ENTITIES_REF_SEQUENCE = 3
                          And acc.ID_ACCOUNTS = acrd3.ACCOUNTS_ID_ACCOUNTS
                        Limit 1
                    ), 0
                ) ACCRED3
                 , Coalesce(
                    (
                        Select
                            acrd4.ENT_REF_DATAS_ID_ENT_REF_DATAS
                        From
                            ACC_REF_DATAS acrd4
                                Inner Join ENTITIES_REF_DATAS erdt4
                                           On acrd4.ENT_REF_DATAS_ID_ENT_REF_DATAS = erdt4.ID_ENTITIES_REF_DATAS
                                Inner Join ENTITIES_REF er4
                                           On erdt4.ENTITIES_REF_ID_ENTITIES_REF = er4.ID_ENTITIES_REF
                        Where
                                er4.ENT_REF_TYPE_ID_ENT_REF_TYPES = @bcmcap_enttRefTpeAccAcrd
                          And er4.ENTITIES_REF_SEQUENCE = 4
                          And acc.ID_ACCOUNTS = acrd4.ACCOUNTS_ID_ACCOUNTS
                        Limit 1
                    ), 0
                ) ACCRED4
                 , Coalesce(
                    (
                        Select
                            acrd5.ENT_REF_DATAS_ID_ENT_REF_DATAS
                        From
                            ACC_REF_DATAS acrd5
                                Inner Join ENTITIES_REF_DATAS erdt5
                                           On acrd5.ENT_REF_DATAS_ID_ENT_REF_DATAS = erdt5.ID_ENTITIES_REF_DATAS
                                Inner Join ENTITIES_REF er5
                                           On erdt5.ENTITIES_REF_ID_ENTITIES_REF = er5.ID_ENTITIES_REF
                        Where
                                er5.ENT_REF_TYPE_ID_ENT_REF_TYPES = @bcmcap_enttRefTpeAccAcrd
                          And er5.ENTITIES_REF_SEQUENCE = 5
                          And acc.ID_ACCOUNTS = acrd5.ACCOUNTS_ID_ACCOUNTS
                        Limit 1
                    ), 0
                ) ACCRED5
            From ACCOUNTS acc

            Where
#                 Exists ( -- en mysql 5.6 exist - performant que le in
                acc.ENTITIES_ID_ENTITIES In (
                    -- vue avec Olivier, les équipes sont désactivé
                        Select
                            tmdm.ENTITIES_ID_ENTITIES
                        From TMP_MBR_DELECATED_MBR tmdm
                        Where acc.ENTITIES_ID_ENTITIES = tmdm.ENTITIES_ID_ENTITIES
#                         Limit 1

                    )
            ;

	    End;

    End;


#
# 		Drop Temporary Table If Exists TMP_MBR_FULL_ACC;
# 		Drop Temporary Table If Exists TMP_MBR_FULL_ACC2;
# 		Drop Temporary Table If Exists TMP_MBR_FULL_ACC3;
# 	memberAccCredentialAllProfilesMain: Begin
#
# 		Create Temporary Table TMP_MBR_FULL_ACC AS
# 		Select
# 			ACC.ENTITIES_ID_ENTITIES, ACC.ID_ACCOUNTS ACCOUNTS_ID_ACCOUNTS
# 			, EHM.ID_ENTITIES_has_MEMBERS, EHM.ID_ENTITIES_has_TEAMS
# 		From
# 			ACCOUNTS ACC
# 			Inner Join TMP_MBR_DELECATED_MBR EHM
# 				On ACC.ENTITIES_ID_ENTITIES = EHM.ENTITIES_ID_ENTITIES
# 		;
# 		Create Index IX_TMP_MBR_FULL_ACC_1 On TMP_MBR_FULL_ACC(ACCOUNTS_ID_ACCOUNTS);
# 		Create Index IX_TMP_MBR_FULL_ACC_2 On TMP_MBR_FULL_ACC(ID_ENTITIES_has_MEMBERS);
# 		Create Index IX_TMP_MBR_FULL_ACC_3 On TMP_MBR_FULL_ACC(ID_ENTITIES_has_TEAMS);
# 		Create Index IX_TMP_MBR_FULL_ACC_4 On TMP_MBR_FULL_ACC(ENTITIES_ID_ENTITIES);
# 	End;
#
# 	memberAccCredentialAllProfilesDel: Begin
# 		-- supression des profiles non authorisé
# 		Delete MFC
# 		from
# 			TMP_MBR_FULL_ACC MFC
# 			Inner 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
# 		;
# 		Delete MFC
# 		from
# 			TMP_MBR_FULL_ACC MFC
# 			Inner Join TMP_DENIED_ENTITIES_has_MEMBERS TDBM
# 				On MFC.ACCOUNTS_ID_ACCOUNTS = TDBM.ACCOUNTS_ID_ACCOUNTS
# 				And MFC.ID_ENTITIES_has_TEAMS = TDBM.ID_ENTITIES_has_TEAMS
# 		;
# 	End;


End //
Delimiter ;

