Add hierarchy for QA evaluations

[esaber76]

Found during regression testing in ecmps-tst.app.cloud.gov v2.0.90.

Logged in as saber_ds (Submitter) and attempted resubmission of flow RATA, F2LREF and F2LCHK tests at the same time.

These tests have dependencies. When evaluating all 3 at once, the F2LREF test receives critical errors for not evaluating the RATA. After evaluating the RATA and then the F2LREF test, the F2LCHK is set back to needs evaluation. End users would need to know to evaluate the RATA first, F2LREF second and the F2LCHK last in this situation.

Since there is the option to evaluate them all at once, a hierarchy needs to be used during the evaluation process.

This also affects FF2LBAS, FF2LTST and PEI/FFACC/FFACCTT QA tests as another example.

2712, CS004A is an example with 2023 Q3 RATA, F2LREF, and F2LCHK tests (ensure submission windows are open for these tests when testing).

7870, CT1 is an example with 2023 Q2 FF2LBAS, FF2LTST, FFACCTT, and PEI tests (ensure submission windows are open for these tests when testing).

Implementation Details

The ordering should apply to tests in the Workspace that are selected as part of a group for evaluation.

TEST_EVAL_ORDER Value by Test Type:

RATA: 1 F2LREF: 2 F2LCHK: 3 FFACC, FFACCTT, and PEI: 4 FF2LBAS: 5 FF2LTST: 6 All Other Test Types: 7

[djw4erg]

ECMPS 1.0 Research Results:

ECMPS 1.0 Objects:

• ECMPS.Client.Evaluate.PageTestsOptions.GetConfigInfo (ECMPS Client App): Calls ECMPS.Eval.GetTestSumIDs to get information for test selected to evaluate for the current MP, and orders them by TEST_EVAL_ORDER, TEST_TYPE_CD and TEST_NUM.
• ECMPS.Eval.GetTestSumIDs (Database Function): Defines TEST_EVAL_ORDER used as the primary sort.

TEST_EVAL_ORDER Value by Test Type:

• RATA: 1
• F2LREF: 2
• F2LCHK: 3
• FFACC, FFACCTT, and PEI: 4
• FF2LBAS: 5
• FF2LTST: 6
• All Other Test Types: 7

[djw4erg]

ECMPS 1.0 Database ECMPS.Eval.GetTestSumIDs Function Definition:

CREATE FUNCTION [Eval].[GetTestSumIDs] 
(
    @V_MON_PLAN_ID      DB_ID_KEY,
    @V_BEGIN_DATE       DateTime, 
    @V_END_DATE         DateTime,
    @V_COMPLETE_HISTORY int,
    @V_TEST_TYPE_CD     LOOKUP_CODE
)
RETURNS 
@TestSumIDs TABLE 
(
    TEST_SUM_ID     DB_ID_KEY,
    TEST_NUM        varchar(18),
    NEEDS_EVAL_FLG  FLAG,
    TEST_TYPE_CD    LOOKUP_CODE,
    TEST_EVAL_ORDER int
)
AS
BEGIN
    -- Fill the table variable with the rows for your result set
    INSERT INTO @TestSumIDs( TEST_SUM_ID, TEST_NUM,  NEEDS_EVAL_FLG, TEST_TYPE_CD, TEST_EVAL_ORDER )
    SELECT  ts.TEST_SUM_ID, ts.TEST_NUM, COALESCE( ts.NEEDS_EVAL_FLG, 'Y' ) as NEEDS_EVAL_FLG, ts.TEST_TYPE_CD,
            CASE TS.TEST_TYPE_CD
                WHEN 'RATA' THEN 1
                WHEN 'F2LREF'   THEN 2
                WHEN 'F2LCHK'   THEN 3
                WHEN 'FFACC'    THEN 4
                WHEN 'FFACCTT'  THEN 4
                WHEN 'PEI'  THEN 4
                WHEN 'FF2LBAS'  THEN 5
                WHEN 'FF2LTST'  THEN 6
                ELSE  7
            END as TEST_EVAL_ORDER
    FROM TEST_SUMMARY ts
        INNER JOIN MONITOR_PLAN_LOCATION mpl on ts.MON_LOC_ID=mpl.MON_LOC_ID
        LEFT JOIN VW_CLIENT_REPORTING_PERIOD rp on rp.RPT_PERIOD_ID = ts.RPT_PERIOD_ID
        LEFT OUTER JOIN FUEL_FLOWMETER_ACCURACY ffa on ffa.TEST_SUM_ID=ts.TEST_SUM_ID
    WHERE mpl.MON_PLAN_ID = @V_MON_PLAN_ID
      AND (    ( @V_COMPLETE_HISTORY = 1 )
            OR ( ts.RPT_PERIOD_ID IS NOT NULL AND rp.QUARTER_END_DATE BETWEEN @V_BEGIN_DATE AND @V_END_DATE )
            OR ( ts.RPT_PERIOD_ID IS NULL AND COALESCE(ffa.REINSTALL_DATE, ts.END_DATE) BETWEEN @V_BEGIN_DATE AND @V_END_DATE )
          )
      AND (    ( @V_TEST_TYPE_CD IS NULL )
            OR ( @V_TEST_TYPE_CD IS NOT NULL AND ts.TEST_TYPE_CD = @V_TEST_TYPE_CD )
          )

    RETURN 
END