Delimiter //
/**
 * Author:  armen
 * Created: 6 nov. 2019
 * crée et rempl la table TMP_ENTITIES_ACCNTS_ATTCHD_MBRS_PRFLES
 */
Drop Procedure if Exists BUSINESS_ATTACHED_MEMBERS_PREP_REF_DATAS//
Create Procedure BUSINESS_ATTACHED_MEMBERS_PREP_REF_DATAS(
    in accAcredRefTpe Int unsigned
)
Begin
    bamprd_main: Begin
        if accAcredRefTpe is null Then
            Set @bamprd_accAcredRefTpe = (
                        Select
                            ID_ENTITIES_REF_TYPES
                        From ENTITIES_REF_TYPES
                        Where
                            ENTITIES_REF_TYPES_CODE = 'ACCRED_ACCOUNTS'
                        Limit 1
                    )
                ;
        Else 
            Set @bamprd_accAcredRefTpe = accAcredRefTpe;
        End if;
        
        Drop Temporary Table If Exists TMP_ENTITIES_ACCNTS_ATTCHD_MBRS_PRFLES;
        Create Temporary Table TMP_ENTITIES_ACCNTS_ATTCHD_MBRS_PRFLES (
                ID_ENTITIES_ACCNTS_ATTCHD_MBRS_PRFLES Int Unsigned Not Null 
                , ENTITIES_ID_ENTITIES Int Unsigned Not Null
                , ENTITIES_ACCNTS_ATTCHD_MBRS_PRFLES_SEQUENCE Smallint 
                , ENTITIES_REF_DATAS_ID_ENTITIES_REF_DATAS_1 Int Unsigned Null
                , ENTITIES_REF_DATAS_ID_ENTITIES_REF_DATAS_2 Int Unsigned Null
                , ENTITIES_REF_DATAS_ID_ENTITIES_REF_DATAS_3 Int Unsigned Null
                , ENTITIES_REF_DATAS_ID_ENTITIES_REF_DATAS_4 Int Unsigned Null
                , ENTITIES_REF_DATAS_ID_ENTITIES_REF_DATAS_5 Int Unsigned Null
                , DF_ACCESS_PROFILES_ID_DF_ACCESS_PROFILES Int Unsigned Null
                , index(ENTITIES_ACCNTS_ATTCHD_MBRS_PRFLES_SEQUENCE) 
                , index(ID_ENTITIES_ACCNTS_ATTCHD_MBRS_PRFLES) 
                , index(ENTITIES_ID_ENTITIES) 
            );

        Drop Temporary Table If Exists TMP_ENTITIES_ACCNTS_ATTCHD_MBRS_PRFLES2;
        Create Temporary Table TMP_ENTITIES_ACCNTS_ATTCHD_MBRS_PRFLES2 (
                ID_ENTITIES_ACCNTS_ATTCHD_MBRS_PRFLES Int Unsigned Not Null 
                , ENTITIES_ID_ENTITIES Int Unsigned Not Null
                , ENTITIES_ACCNTS_ATTCHD_MBRS_PRFLES_SEQUENCE Smallint 
                , ENTITIES_REF_DATAS_ID_ENTITIES_REF_DATAS_1 Int Unsigned Null
                , ENTITIES_REF_DATAS_ID_ENTITIES_REF_DATAS_2 Int Unsigned Null
                , ENTITIES_REF_DATAS_ID_ENTITIES_REF_DATAS_3 Int Unsigned Null
                , ENTITIES_REF_DATAS_ID_ENTITIES_REF_DATAS_4 Int Unsigned Null
                , ENTITIES_REF_DATAS_ID_ENTITIES_REF_DATAS_5 Int Unsigned Null
                , DF_ACCESS_PROFILES_ID_DF_ACCESS_PROFILES Int Unsigned Null
                , index(ENTITIES_ACCNTS_ATTCHD_MBRS_PRFLES_SEQUENCE) 
                , index(ID_ENTITIES_ACCNTS_ATTCHD_MBRS_PRFLES) 
                , index(ENTITIES_ID_ENTITIES) 
            );

        Insert Into TMP_ENTITIES_ACCNTS_ATTCHD_MBRS_PRFLES (
            ID_ENTITIES_ACCNTS_ATTCHD_MBRS_PRFLES, ENTITIES_ID_ENTITIES
            , ENTITIES_ACCNTS_ATTCHD_MBRS_PRFLES_SEQUENCE
            , ENTITIES_REF_DATAS_ID_ENTITIES_REF_DATAS_1, ENTITIES_REF_DATAS_ID_ENTITIES_REF_DATAS_2
            , ENTITIES_REF_DATAS_ID_ENTITIES_REF_DATAS_3, ENTITIES_REF_DATAS_ID_ENTITIES_REF_DATAS_4
            , ENTITIES_REF_DATAS_ID_ENTITIES_REF_DATAS_5
            , DF_ACCESS_PROFILES_ID_DF_ACCESS_PROFILES
        )
        Select 
            eaamp.ID_ENTITIES_ACCNTS_ATTCHD_MBRS_PRFLES, eaamp.ENTITIES_ID_ENTITIES
            , eaamp.ENTITIES_ACCNTS_ATTCHD_MBRS_PRFLES_SEQUENCE
            , Case when Trim(Coalesce(erd1.ENTITIES_REF_DATAS_CODE, '')) ='' Then Null Else erd1.ID_ENTITIES_REF_DATAS End
            , Case when Trim(Coalesce(erd2.ENTITIES_REF_DATAS_CODE, '')) ='' Then Null Else erd2.ID_ENTITIES_REF_DATAS End
            , Case when Trim(Coalesce(erd3.ENTITIES_REF_DATAS_CODE, '')) ='' Then Null Else erd3.ID_ENTITIES_REF_DATAS End
            , Case when Trim(Coalesce(erd4.ENTITIES_REF_DATAS_CODE, '')) ='' Then Null Else erd4.ID_ENTITIES_REF_DATAS End
            , Case when Trim(Coalesce(erd5.ENTITIES_REF_DATAS_CODE, '')) ='' Then Null Else erd5.ID_ENTITIES_REF_DATAS End
--                 , Coalesce(eaamp.DF_ACCESS_PROFILES_ID_DF_ACCESS_PROFILES, 0)
            , eaamp.DF_ACCESS_PROFILES_ID_DF_ACCESS_PROFILES
        From ENTITIES_ACCNTS_ATTCHD_MBRS_PRFLES eaamp
            Left Join (
                ENT_ACC_ATCH_MBRS_PRFLS_has_ENT_RF_DTS eaampherd1
                Inner Join ENTITIES_REF_DATAS erd1
                    On eaampherd1.ENTITIES_REF_DATAS_ID_ENTITIES_REF_DATAS = erd1.ID_ENTITIES_REF_DATAS
                Inner Join ENTITIES_REF er1
                    On erd1.ENTITIES_REF_ID_ENTITIES_REF = er1.ID_ENTITIES_REF
                    And er1.ENTITIES_REF_SEQUENCE = 1
                    And er1.ENT_REF_TYPE_ID_ENT_REF_TYPES = @bamprd_accAcredRefTpe

            ) On eaamp.ID_ENTITIES_ACCNTS_ATTCHD_MBRS_PRFLES = eaampherd1.ENT_ACC_ATCH_MBRS_PRFLS_ID_ENT_ACC_ATCH_MBRS_PRFLS
            Left Join (
                ENT_ACC_ATCH_MBRS_PRFLS_has_ENT_RF_DTS eaampherd2
                Inner Join ENTITIES_REF_DATAS erd2
                    On eaampherd2.ENTITIES_REF_DATAS_ID_ENTITIES_REF_DATAS = erd2.ID_ENTITIES_REF_DATAS
                Inner Join ENTITIES_REF er2
                    On erd2.ENTITIES_REF_ID_ENTITIES_REF = er2.ID_ENTITIES_REF
                    And er2.ENTITIES_REF_SEQUENCE = 2
                    And er2.ENT_REF_TYPE_ID_ENT_REF_TYPES = @bamprd_accAcredRefTpe

            ) On eaamp.ID_ENTITIES_ACCNTS_ATTCHD_MBRS_PRFLES = eaampherd2.ENT_ACC_ATCH_MBRS_PRFLS_ID_ENT_ACC_ATCH_MBRS_PRFLS
            Left Join (
                ENT_ACC_ATCH_MBRS_PRFLS_has_ENT_RF_DTS eaampherd3
                Inner Join ENTITIES_REF_DATAS erd3
                    On eaampherd3.ENTITIES_REF_DATAS_ID_ENTITIES_REF_DATAS = erd3.ID_ENTITIES_REF_DATAS
                Inner Join ENTITIES_REF er3
                    On erd3.ENTITIES_REF_ID_ENTITIES_REF = er3.ID_ENTITIES_REF
                    And er3.ENTITIES_REF_SEQUENCE = 3
                    And er3.ENT_REF_TYPE_ID_ENT_REF_TYPES = @bamprd_accAcredRefTpe

            ) On eaamp.ID_ENTITIES_ACCNTS_ATTCHD_MBRS_PRFLES = eaampherd3.ENT_ACC_ATCH_MBRS_PRFLS_ID_ENT_ACC_ATCH_MBRS_PRFLS
            Left Join (
                ENT_ACC_ATCH_MBRS_PRFLS_has_ENT_RF_DTS eaampherd4
                Inner Join ENTITIES_REF_DATAS erd4
                    On eaampherd4.ENTITIES_REF_DATAS_ID_ENTITIES_REF_DATAS = erd4.ID_ENTITIES_REF_DATAS
                Inner Join ENTITIES_REF er4
                    On erd4.ENTITIES_REF_ID_ENTITIES_REF = er4.ID_ENTITIES_REF
                    And er4.ENTITIES_REF_SEQUENCE = 4
                    And er4.ENT_REF_TYPE_ID_ENT_REF_TYPES = @bamprd_accAcredRefTpe

            ) On eaamp.ID_ENTITIES_ACCNTS_ATTCHD_MBRS_PRFLES = eaampherd4.ENT_ACC_ATCH_MBRS_PRFLS_ID_ENT_ACC_ATCH_MBRS_PRFLS
            Left Join (
                ENT_ACC_ATCH_MBRS_PRFLS_has_ENT_RF_DTS eaampherd5
                Inner Join ENTITIES_REF_DATAS erd5
                    On eaampherd5.ENTITIES_REF_DATAS_ID_ENTITIES_REF_DATAS = erd5.ID_ENTITIES_REF_DATAS
                Inner Join ENTITIES_REF er5
                    On erd5.ENTITIES_REF_ID_ENTITIES_REF = er5.ID_ENTITIES_REF
                    And er5.ENTITIES_REF_SEQUENCE = 5
                    And er5.ENT_REF_TYPE_ID_ENT_REF_TYPES = @bamprd_accAcredRefTpe

            ) On eaamp.ID_ENTITIES_ACCNTS_ATTCHD_MBRS_PRFLES = eaampherd5.ENT_ACC_ATCH_MBRS_PRFLS_ID_ENT_ACC_ATCH_MBRS_PRFLS
            Order By eaamp.ENTITIES_ACCNTS_ATTCHD_MBRS_PRFLES_SEQUENCE
        ;


        -- duplication de la table en raison des limitations mysql pour autojointure sur une table temporaire
        Insert Into TMP_ENTITIES_ACCNTS_ATTCHD_MBRS_PRFLES2 (
            ID_ENTITIES_ACCNTS_ATTCHD_MBRS_PRFLES, ENTITIES_ID_ENTITIES
            , ENTITIES_ACCNTS_ATTCHD_MBRS_PRFLES_SEQUENCE
            , ENTITIES_REF_DATAS_ID_ENTITIES_REF_DATAS_1, ENTITIES_REF_DATAS_ID_ENTITIES_REF_DATAS_2
            , ENTITIES_REF_DATAS_ID_ENTITIES_REF_DATAS_3, ENTITIES_REF_DATAS_ID_ENTITIES_REF_DATAS_4
            , ENTITIES_REF_DATAS_ID_ENTITIES_REF_DATAS_5
            , DF_ACCESS_PROFILES_ID_DF_ACCESS_PROFILES
        )
        Select
            ID_ENTITIES_ACCNTS_ATTCHD_MBRS_PRFLES, ENTITIES_ID_ENTITIES
            , ENTITIES_ACCNTS_ATTCHD_MBRS_PRFLES_SEQUENCE
            , ENTITIES_REF_DATAS_ID_ENTITIES_REF_DATAS_1, ENTITIES_REF_DATAS_ID_ENTITIES_REF_DATAS_2
            , ENTITIES_REF_DATAS_ID_ENTITIES_REF_DATAS_3, ENTITIES_REF_DATAS_ID_ENTITIES_REF_DATAS_4
            , ENTITIES_REF_DATAS_ID_ENTITIES_REF_DATAS_5
            , DF_ACCESS_PROFILES_ID_DF_ACCESS_PROFILES
        From TMP_ENTITIES_ACCNTS_ATTCHD_MBRS_PRFLES
        ;


    End;
End //
Delimiter ;


