Delimiter //
/**
 * Author:  armen
 * Created: 29 nov. 2019
 */

Drop Procedure If Exists LOADER_ACCOUNTS_NORMALIZE_MAP_PENALTIES_RULES_CODE_PROVIDED//
Create Procedure LOADER_ACCOUNTS_NORMALIZE_MAP_PENALTIES_RULES_CODE_PROVIDED(
    In batchnum Int
    , In rulesTypesCode varchar(40)
    , In fieldsuffix varchar(30)
)
Begin
    Set @lanmprcp_batchnum = batchnum;
    Set @lanmprcp_rulesTypesCode = rulesTypesCode;
    Set @lanmprcp_fieldsuffix = fieldsuffix;
    
    Set @lanmprcp_empty = '';

    Set @lanmprcp_mapQuery = concat(
                'Update '
                , '    LOADER_ACCOUNTS lacc '
                , '    Inner Join LOADER_STATUS lst '
                , '        On lacc.LOADER_STATUS_ID_LOADER_STATUS = lst.ID_LOADER_STATUS '
                , '    Inner Join DF_PENALTIES_RULES dpr '
                , '        On lacc.DF_PENALTIES_RULES_CODE_', @lanmprcp_fieldsuffix, ' = dpr.DF_PENALTIES_RULES_CODE '
                , '    Inner Join DF_PENALTIES_RULES_TYPES dprt '
                , '        On dpr.DF_PENALTIES_RULES_TYPES_ID_DF_PENALTIES_RULES_TYPES = dprt.ID_DF_PENALTIES_RULES_TYPES '
                , ' Set '
                , '    lacc.ID_DF_PENALTIES_RULES_CODE_', @lanmprcp_fieldsuffix, ' = dpr.ID_DF_PENALTIES_RULES '
                , ' Where '
                , '    lst.LOADER_STATUS_BATCH_NUM = ? ' -- 1 = batch num
                , '    And lst.LOADER_STATUS_ERRCOUNT = 0 '
                , '    And dprt.DF_PENALTIES_RULES_TYPES_CODE = ? ' -- 2 =  rules type code
                , '    And Trim(Coalesce(lacc.DF_PENALTIES_RULES_CODE_', @lanmprcp_fieldsuffix, ', ?)) <>  ? ' -- 3 & 4 = empty
            )
        ;
    PREPARE lanmprcp_updtstmt FROM @lanmprcp_mapQuery; 
    execute lanmprcp_updtstmt 
            using  @lanmprcp_batchnum 
                , @lanmprcp_rulesTypesCode
                , @lanmprcp_empty, @lanmprcp_empty
        ;
    Deallocate prepare lanmprcp_updtstmt; 


    Call LOADER_BUILD_ACCOUNTS_CHECK_BASE_QUERY (Concat('DF_PENALTIES_RULES_CODE_', @lanmprcp_fieldsuffix),
                    @lanmprcp_batchnum , 
                    'frais non trouvés ', -- unknown fees
                    null, 1,
                    @lanmprcp_chkQuery
            );


    Set @lanmprcp_chkQuery = Concat(@lanmprcp_chkQuery
                            , ' And ID_DF_PENALTIES_RULES_CODE_', @lanmprcp_fieldsuffix, ' Is Null'
                            , ' And Trim(Coalesce(DF_PENALTIES_RULES_CODE_', @lanmprcp_fieldsuffix, ', ?)) <>  ? '
                        )
                    ;

    Prepare lanmprcp_chkstmt From @lanmprcp_chkQuery;
    Execute lanmprcp_chkstmt
            using @lanmprcp_empty, @lanmprcp_empty
        ;
    Deallocate Prepare lanmprcp_chkstmt;
-- 
-- Update
--     LOADER_ACCOUNTS lacc
--     Inner Join LOADER_STATUS lst
--         On lacc.LOADER_STATUS_ID_LOADER_STATUS = lst.ID_LOADER_STATUS
-- set lst.LOADER_STATUS_WARNCOUNT = 1+ Coalesce(lst.LOADER_STATUS_WARNCOUNT ,0)
--     
-- Where
--     lst.LOADER_STATUS_BATCH_NUM = 0
--     And lst.LOADER_STATUS_ERRCOUNT = 0
--     And Coalesce(lacc.DF_PENALTIES_RULES_CODE_', @lanmprcp_fieldsuffix, ' , '') <> ''
--     And lacc.ID_DF_PENALTIES_RULES_CODE_', @lanmprcp_fieldsuffix, ' Is Null
-- 

End//
Delimiter ;