Delimiter $$
/**
 * Crée les members n'éxistant par déjà dans FI_ANA_MEMBERS
 * @see BUSINESS_UPDATE_FIANA
 * @see BUSINESS_FI_ANA_INSERT_NEW_ACCOUNTS
 * @see BUSINESS_UPDATE_FIANA_MEMBERS
 * @see BUSINESS_BACKUP_FIANA_MEMBERS
 * @see BUSINESS_BACKUP_FIANA_MEMBERS_FROM_HISTO
 */ 
Drop Procedure if Exists BUSINESS_FI_ANA_INSERT_NEW_MEMBERS $$
Create Procedure BUSINESS_FI_ANA_INSERT_NEW_MEMBERS(In idmembers Int Unsigned)
Begin

--     Declare member_filter Varchar(10) Default ifnull(idmembers, '%');
    if idmembers Is null Then
        Set @bfainm_mbrMinVal = 0;
        Set @bfainm_mbrMaxVal = 4e5;
    Else
        Set @bfainm_mbrMinVal = idaccounts;
        Set @bfainm_mbrMaxVal = idaccounts;
    End if;

    fi_ana_insert_new_members: Begin

		Drop Temporary Table If Exists TMP_FIANA_NEW_MEMBERS;
		Create Temporary Table TMP_FIANA_NEW_MEMBERS As 
            Select 
                M.ID_MEMBERS
                , A.ADMIN_PARAMS_ID_ADMIN_PARAMS
            From
                MEMBERS M
                Inner Join ADMIN_PARAMS_has_MEMBERS A
                    On A.MEMBERS_ID_MEMBERS = M.ID_MEMBERS
                Left Join FI_ANA_MEMBERS FI
                    On M.ID_MEMBERS = FI.MEMBERS_ID_MEMBERS
            Where 
                FI.MEMBERS_ID_MEMBERS Is Null
                And M.MEMBERS_TYPE = 0
                And M.ID_MEMBERS Between @bfainm_mbrMinVal  And @bfainm_mbrMaxVal
--                 And M.ID_MEMBERS Like member_filter
		;

		Insert Into 
			FI_ANA_MEMBERS (
            MEMBERS_ID_MEMBERS
            , ADMIN_PARAMS_ID_ADMIN_PARAMS
                , FI_ANA_OUTSTANDING
                , FI_ANA_OVERDUE
                , FI_ANA_NOT_OVERDUE
                , FI_ANA_DUE
                , FI_ANA_FROZEN_AMOUNT
                , FI_ANA_BLOCKED_ALL
                , FI_ANA_BLOCKED_NOTOVERDUE
                , FI_ANA_AGE_PORTFOLIO_PERIOD1
                , FI_ANA_AGE_PORTFOLIO_PERIOD2
                , FI_ANA_AGE_PORTFOLIO_PERIOD3
                , FI_ANA_AGE_PORTFOLIO_PERIOD4
                , FI_ANA_AGE_PORTFOLIO_PERIOD5
                , FI_ANA_AGE_PORTFOLIO_PERIOD6
                , FI_ANA_AGE_PORTFOLIO_PERIOD7
                , FI_ANA_AGE_PORTFOLIO_PERIOD8
                , FI_ANA_AGE_ACCOUNT_PERIOD1
                , FI_ANA_AGE_ACCOUNT_PERIOD2
                , FI_ANA_AGE_ACCOUNT_PERIOD3
                , FI_ANA_AGE_ACCOUNT_PERIOD4
                , FI_ANA_CURRENT_ORDER_AMOUNT
                , FI_ANA_BLOCKED_ORDER_AMOUNT
                , FI_ANA_DELIVERY_TOBEINVOICED
                , FI_ANA_BLOCKED_DELIVERY_TOBEINVOICED
                , FI_ANA_BANK_DRAFT
                , FI_ANA_SALES
                , FI_ANA_PAYMENT
                , FI_ANA_UNBOUND_OUTSTANDING
                , FI_ANA_UNBOUND_OVERDUE
                , FI_ANA_UNBOUND_NOT_OVERDUE
                , FI_ANA_UNBOUND_DUE
                , FI_ANA_UNBOUND_FROZEN_AMOUNT
                , FI_ANA_UNBOUND_BLOCKED_ALL
                , FI_ANA_UNBOUND_BLOCKED_NOTOVERDUE
                , FI_ANA_UNBOUND_CURRENT_ORDER_AMOUNT
                , FI_ANA_UNBOUND_BLOCKED_ORDER_AMOUNT
                , FI_ANA_UNBOUND_DELIVERY_TOBEINVOICED
                , FI_ANA_UNBOUND_BLOCKED_DELIVERY_TOBEINVOICED
                , FI_ANA_UNBOUND_BANK_DRAFT
                , FI_ANA_UNBOUND_SALES
                , FI_ANA_UNBOUND_PAYMENT
                , FI_ANA_AGE_CUSTMMDE_PERIOD1
                , FI_ANA_AGE_CUSTMMDE_PERIOD2
                , FI_ANA_AGE_CUSTMMDE_PERIOD3
                , FI_ANA_AGE_CUSTMMDE_PERIOD4
                , FI_ANA_AGE_CUSTMMDE_DEBIT
                , FI_ANA_AGE_CUSTMMDE_CREDIT
			) 
		Select 
			ID_MEMBERS
            , ADMIN_PARAMS_ID_ADMIN_PARAMS
                , 0 FI_ANA_OUTSTANDING
                , 0 FI_ANA_OVERDUE
                , 0 FI_ANA_NOT_OVERDUE
                , 0 FI_ANA_DUE
                , 0 FI_ANA_FROZEN_AMOUNT
                , 0 FI_ANA_BLOCKED_ALL
                , 0 FI_ANA_BLOCKED_NOTOVERDUE
                , 0 FI_ANA_AGE_PORTFOLIO_PERIOD1
                , 0 FI_ANA_AGE_PORTFOLIO_PERIOD2
                , 0 FI_ANA_AGE_PORTFOLIO_PERIOD3
                , 0 FI_ANA_AGE_PORTFOLIO_PERIOD4
                , 0 FI_ANA_AGE_PORTFOLIO_PERIOD5
                , 0 FI_ANA_AGE_PORTFOLIO_PERIOD6
                , 0 FI_ANA_AGE_PORTFOLIO_PERIOD7
                , 0 FI_ANA_AGE_PORTFOLIO_PERIOD8
                , 0 FI_ANA_AGE_ACCOUNT_PERIOD1
                , 0 FI_ANA_AGE_ACCOUNT_PERIOD2
                , 0 FI_ANA_AGE_ACCOUNT_PERIOD3
                , 0 FI_ANA_AGE_ACCOUNT_PERIOD4
                , 0 FI_ANA_CURRENT_ORDER_AMOUNT
                , 0 FI_ANA_BLOCKED_ORDER_AMOUNT
                , 0 FI_ANA_DELIVERY_TOBEINVOICED
                , 0 FI_ANA_BLOCKED_DELIVERY_TOBEINVOICED
                , 0 FI_ANA_BANK_DRAFT
                , 0 FI_ANA_SALES
                , 0 FI_ANA_PAYMENT
                , 0 FI_ANA_UNBOUND_OUTSTANDING
                , 0 FI_ANA_UNBOUND_OVERDUE
                , 0 FI_ANA_UNBOUND_NOT_OVERDUE
                , 0 FI_ANA_UNBOUND_DUE
                , 0 FI_ANA_UNBOUND_FROZEN_AMOUNT
                , 0 FI_ANA_UNBOUND_BLOCKED_ALL
                , 0 FI_ANA_UNBOUND_BLOCKED_NOTOVERDUE
                , 0 FI_ANA_UNBOUND_CURRENT_ORDER_AMOUNT
                , 0 FI_ANA_UNBOUND_BLOCKED_ORDER_AMOUNT
                , 0 FI_ANA_UNBOUND_DELIVERY_TOBEINVOICED
                , 0 FI_ANA_UNBOUND_BLOCKED_DELIVERY_TOBEINVOICED
                , 0 FI_ANA_UNBOUND_BANK_DRAFT
                , 0 FI_ANA_UNBOUND_SALES
                , 0 FI_ANA_UNBOUND_PAYMENT
                , 0 FI_ANA_AGE_CUSTMMDE_PERIOD1
                , 0 FI_ANA_AGE_CUSTMMDE_PERIOD2
                , 0 FI_ANA_AGE_CUSTMMDE_PERIOD3
                , 0 FI_ANA_AGE_CUSTMMDE_PERIOD4
                , 0 FI_ANA_AGE_CUSTMMDE_DEBIT
                , 0 FI_ANA_AGE_CUSTMMDE_CREDIT
		From 
            TMP_FIANA_NEW_MEMBERS
        ;

		Drop Temporary Table If Exists TMP_FIANA_NEW_MEMBERS;

    End;

    fi_ana_insert_del_old_members: Begin
        if idmembers is null Then
            -- supression de FI_ANA_MEMBERS des donnée ralitatif aux membres supprimé ou non connectés
            Delete fam
            From FI_ANA_MEMBERS fam
            Where
                Not Exists (
                    Select
                        mbr.ID_MEMBERS
                    From MEMBERS mbr
                        Inner Join CONTACTS_INDIVIDUAL ci
                            On mbr.CONTACT_INDIV_ID_CONTACT_INDIV = ci.ID_CONTACTS_INDIVIDUAL
                        Inner Join CONTACTS ctc
                            On ci.CONTACTS_ID_CONTACTS = ctc.ID_CONTACTS
                    Where
                        fam.MEMBERS_ID_MEMBERS = mbr.ID_MEMBERS
                        And (mbr.MEMBERS_TYPE = 0)
                        And (ctc.CONTACTS_TRASHED = 0)

                    Limit 1
                )
            ;
            -- supression de FI_ANA_MEMBERS_HISTO des donnée ralitatif aux membres supprimés 
            -- les membres non connecté sont préservé car il peuvents redevenir connectés
            Delete famh
            From FI_ANA_MEMBERS_HISTO famh
            Where
                Not Exists (
                    Select
                        mbr.ID_MEMBERS
                    From MEMBERS mbr
                        Inner Join CONTACTS_INDIVIDUAL ci
                            On mbr.CONTACT_INDIV_ID_CONTACT_INDIV = ci.ID_CONTACTS_INDIVIDUAL
                        Inner Join CONTACTS ctc
                            On ci.CONTACTS_ID_CONTACTS = ctc.ID_CONTACTS
                    Where
                        famh.MEMBERS_ID_MEMBERS = mbr.ID_MEMBERS
                        And (ctc.CONTACTS_TRASHED = 0)
                    Limit 1
                )
            ;
    
        End if;
    End ;


End $$
Delimiter ;