Delimiter $$
/**
 *
 * détermine l'id de la regle des pénalités à appliquer par compte
 *
 **/
# Delimiter $$
Drop Procedure if Exists BUSINESS_COMPUTE_PENALTIES $$
Create Procedure BUSINESS_COMPUTE_PENALTIES(
        In queryAccountsFilter Text
    )
Begin
	cpt_pntl_main : Begin
        cpt_pntl_init : Begin

            set @cptpntl_useBal = 1;

            Set @cptpntl_cialDeptTypeId = (Select ID_DF_DEBTS_TYPES From DF_DEBTS_TYPES where DF_DEBTS_TYPES_CODE = 'COMMERCIAL');
    -- 		
            Drop Temporary Table If Exists TMP_COMPUTES_PENALTIES_ACCOUNTS;
            set @cpt_pntl_queryAccountsFilter = queryAccountsFilter;
#             set @cpt_pntl_queryAccountsFilter = null;
            Set @queryAccountsFilter  = Coalesce(@cpt_pntl_queryAccountsFilter
                                                -- vue avec olivier si dept type non précicé alors clien type commercial
                                                , Concat('Select ID_ACCOUNTS ACCOUNTS_ID_ACCOUNTS From ACCOUNTS Where ' 
    -- 													, 'Coalesce(', @cptpntl_cialDeptTypeId, ', DF_DEBTS_TYPES_ID_DF_DEBTS_TYPES )'
                                                        , 'Coalesce(DF_DEBTS_TYPES_ID_DF_DEBTS_TYPES, ', @cptpntl_cialDeptTypeId, ' )'
                                                        , ' = ' ,  @cptpntl_cialDeptTypeId 
                                                )
                                            )
                                    ;


            Drop temporary table if exists TMP_COMPUTES_PENALTIES_ACCOUNTS;
            Create temporary table TMP_COMPUTES_PENALTIES_ACCOUNTS (
                        ACCOUNTS_ID_ACCOUNTS Int Unsigned Not Null
                        , Index(ACCOUNTS_ID_ACCOUNTS)
                    )
                ;

            if @queryAccountsFilter regexp '^([0-9]+ *,? *)+$' Then
#                 Set @queryAccountsFilter = Concat ('Create Temporary Table TMP_COMPUTES_PENALTIES_ACCOUNTS AS Select cptpntl_acc.ID_ACCOUNTS ACCOUNTS_ID_ACCOUNTS '
#                                                 , 'From ACCOUNTS cptpntl_acc '
#                                                 , 'Where cptpntl_acc.ID_ACCOUNTS In (', @queryAccountsFilter, ') '
#                                             )
#                                         ;
                Set @queryAccountsFilter = Concat ('Insert Into TMP_COMPUTES_PENALTIES_ACCOUNTS(ACCOUNTS_ID_ACCOUNTS) Select cptpntl_acc.ID_ACCOUNTS ACCOUNTS_ID_ACCOUNTS '
                                                , 'From ACCOUNTS cptpntl_acc '
                                                , 'Where cptpntl_acc.ID_ACCOUNTS In (', @queryAccountsFilter, ') '
                                            )
                                        ;
            else
#                 Set @queryAccountsFilter = Concat('Create Temporary Table TMP_COMPUTES_PENALTIES_ACCOUNTS AS '
#                                             , ' Select ACCOUNTS_ID_ACCOUNTS From ('
#                                             , @queryAccountsFilter
#                                             , ' ) TMP_PENALTIES_ACC_FILTER'
#                                         )
#                                     ;
                Set @queryAccountsFilter = Concat('Insert Into TMP_COMPUTES_PENALTIES_ACCOUNTS(ACCOUNTS_ID_ACCOUNTS)  '
                                            , ' Select ACCOUNTS_ID_ACCOUNTS From ('
                                            , @queryAccountsFilter
                                            , ' ) TMP_PENALTIES_ACC_FILTER'
                                        )
                                    ;
            End if;


            PREPARE credTblAccFilterStmt FROM @queryAccountsFilter; 
            execute credTblAccFilterStmt ;
            Deallocate prepare credTblAccFilterStmt; 			

#             Create Index IX_TMP_COMPUTES_PENALTIES_ACCOUNTS_01 On TMP_COMPUTES_PENALTIES_ACCOUNTS(ACCOUNTS_ID_ACCOUNTS);


        End ;

        cpt_pntl_compute : Begin
            call BUSINESS_COMPUTE_PENALTIES_PREP_ENTRIES('FR.441-6');
            call BUSINESS_COMPUTE_PENALTIES_ACCOUNTS_PENALITY_RULES('FR.441-6', @cptpntl_cialDeptTypeId, @cptpntl_useBal);
            call BUSINESS_COMPUTE_PENALTIES_PREP_ENTRIES('FR.LATE_PENAL_CAUSE');
            call BUSINESS_COMPUTE_PENALTIES_ACCOUNTS_PENALITY_RULES('FR.LATE_PENAL_CAUSE', @cptpntl_cialDeptTypeId, @cptpntl_useBal);
        End;
    End;


end $$
Delimiter ;


