Critical errors in Eval status and no errors shown in report

[esaber76]

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

Logged in as saber_ds (Submitter) and imported 2023 Q3 emissions data from historical for ORIS 880023, CP1, K1-K4.

Evaluated file and Eval Status on the screen indicates critical errors but evaluation report shows nothing.

ORIS 1571, 3 and 1012, CS023, MS2, 2, 3 2023 Q3 emissions data are other examples.

Could be the same issue as https://github.com/US-EPA-CAMD/easey-ui/issues/5987.

4/23 update: Example for ORIS 880023 on tst.

CAMDECMPSWKS.CHECK_SESSION.SESSION_COMMENT indicates the following error:

Check Session Failed: BulkLoad[camdecmpswks.check_log]: 22P04: missing data for column "chk_log_comment"

Based on this error, CHECK_LOG is not being populated for the CHK_SESSION_ID resulting in an evaluation report not showing any errors.

Note: These rows are removed from camdecmpswks.check_session if a user reverts to official so may need to recreate issue to see problem in database table.

select  fac.Oris_Code,
        fac.Facility_Name,
        (
            select  string_agg( coalesce( unt.Unitid, stp.Stack_Name ), ', ' order by case when loc.Unit_Id is null then 0 else 1 end, coalesce( unt.Unitid, stp.Stack_Name ) )
              from  camdecmpswks.MONITOR_PLAN_LOCATION mpl
                    join camdecmpswks.MONITOR_LOCATION loc on loc.Mon_Loc_Id = mpl.Mon_Loc_Id
                    left join CAMD.UNIT unt on unt.Unit_Id = loc.Unit_Id
                    left join camdecmpswks.STACK_PIPE stp on stp.Stack_Pipe_Id = loc.Stack_Pipe_Id
             where  mpl.Mon_Plan_Id = pln.Mon_Plan_Id
        ) as Locations,
        prd.Period_Abbreviation as Quarter,
        lst.Mon_Plan_Id,
        lst.Rpt_Period_Id,
        fac.Fac_Id
  from  ( 
            select  chs.Mon_Plan_Id,
                    chs.Rpt_Period_Id
              from  camdecmpswks.CHECK_SESSION chs
             where  chs.session_comment = 'Check Session Failed: BulkLoad[camdecmpswks.check_log]: 22P04: missing data for column "chk_log_comment"'
        ) lst
        join camdecmpsmd.REPORTING_PERIOD prd
          on prd.Rpt_Period_Id = lst.Rpt_Period_Id
        join camdecmpswks.MONITOR_PLAN pln
          on pln.Mon_Plan_Id = lst.Mon_Plan_Id
        join camd.PLANT fac 
          on fac.Fac_Id = pln.Fac_Id
 order 
    by  fac.Oris_Code;

[djw4erg]

Background:

Components, Systems and Formulas have both Ids and Identifiers in the database. The Ids are primary keys while the Identifiers along with the Location Id (MON_LOC_ID) form unique keys. The emissions import logic for Components attempts to use the Component Identifier as if it is a unique identifier. Since the import logic only checks identifiers for locations in the target MP, a problem only occurs if components at separate locations in the same MP have the same Component Identifier.

Because Monitor Systems and Monitor Formulas use identifiers in the same way and use the same logic to determine database ids, both should have the same problem as Components.

Note that the "Ids" in the JSON for Component, System and Formulas are actually identifiers.

Below are the emission, QAT, QCE and TEE tables that contain Component, Monitor System, or Monitor Formula FK. All will need to be checked for the problem either in this ticket or another ticket.

Tables Containing Component, System and Formula Ids:

Emission Tables:

DAILY_TEST_SUMMARY: COMPONENT_ID, MON_SYS_ID DERIVED_HRLY_VALUE: MON_FORM_ID, MON_SYS_ID HRLY_FUEL_FLOW: MON_SYS_ID HRLY_GAS_FLOW_METER: COMPONENT_ID HRLY_PARAM_FUEL_FLOW: MON_FORM_ID, MON_SYS_ID LONG_TERM_FUEL_FLOW: MON_SYS_ID MATS_DERIVED_HRLY_VALUE: MON_FORM_ID MATS_MONITOR_HRLY_VALUE: COMPONENT_ID, MON_SYS_ID MONITOR_HRLY_VALUE: COMPONENT_ID, MON_SYS_ID SAMPLING_TRAIN: COMPONENT_ID SORBENT_TRAP: MON_SYS_ID WEEKLY_TEST_SUMMARY: COMPONENT_ID, MON_SYS_ID

Monitoring Plan Tables

ANALYZER_RANGE: COMPONENT_ID COMPONENT: COMPONENT_ID MONITOR_FORMULA: MON_FORM_ID MONITOR_SYSTEM: MON_SYS_ID MONITOR_SYSTEM_COMPONENT: COMPONENT_ID, MON_SYS_ID SYSTEM_FUEL_FLOW: MON_SYS_ID

QA Test Tables:

AE_HI_GAS: MON_SYS_ID AE_HI_OIL: MON_SYS_ID CALIBRATION_STANDARD: COMPONENT_ID TEST_SUMMARY: COMPONENT_ID, MON_SYS_ID

QA Cert Event and Test Exemption & Extension Tables:

QA_CERT_EVENT: COMPONENT_ID, MON_SYS_ID TEST_EXTENSION_EXEMPTION: COMPONENT_ID, MON_SYS_ID

[djw4erg]

Example for changes to handle duplicate identifiers at separate locations within an MP

Below is the example changes to handle the fact that component, formula, and system identifiers can be the same for different locations in the same MP.

Note especially:

1. The Dictionary, IdentifierDictionaries, and LocationIdentifiers type definitions.
2. The use of "identifiers.locations[locationId]" instead of just identifiers in the main for loop.
3. The noted need to change the buildObjectList() versions that use "identifiers" to use "identifiers.locations[monitorLocationId]" instead.

/* Test Class for Test Data */
class ValuePairs {

  constructor( otherId: string, otherLocationId: string, otherIdentifier: string) {
    this.id = otherId;
    this.locationId = otherLocationId
    this.identifier = otherIdentifier;
  }

  id: string;
  locationId: string;
  identifier: string;
}

/* Test Data Setup */
const locationList: string[] = [ "LocationId1", "LocationId2", "LocationId3" ];

const componentValues: ValuePairs[] = [
  { locationId: "LocationId1", identifier:"C11", id:"Location1Component1" },
  { locationId: "LocationId1", identifier:"C02", id:"Location1Component2" },
  { locationId: "LocationId1", identifier:"C13", id:"Location1Component3" },
  { locationId: "LocationId1", identifier:"C14", id:"Location1Component4" },
  { locationId: "LocationId1", identifier:"C15", id:"Location1Component5" },
  { locationId: "LocationId2", identifier:"C21", id:"Location2Component1" },
  { locationId: "LocationId2", identifier:"C02", id:"Location2Component2" },
  { locationId: "LocationId2", identifier:"C23", id:"Location2Component3" },
  { locationId: "LocationId2", identifier:"C24", id:"Location2Component4" },
  { locationId: "LocationId2", identifier:"C25", id:"Location2Component5" },
];

const formulaValues: ValuePairs[] = [
  { locationId: "LocationId1", identifier:"F11", id:"Location1Formula1" },
  { locationId: "LocationId1", identifier:"F12", id:"Location1Formula2" },
  { locationId: "LocationId1", identifier:"F03", id:"Location1Formula3" },
  { locationId: "LocationId1", identifier:"F14", id:"Location1Formula4" },
  { locationId: "LocationId1", identifier:"F15", id:"Location1Formula5" },
  { locationId: "LocationId2", identifier:"F21", id:"Location2Formula1" },
  { locationId: "LocationId2", identifier:"F22", id:"Location2Formula2" },
  { locationId: "LocationId2", identifier:"F03", id:"Location2Formula3" },
  { locationId: "LocationId2", identifier:"F24", id:"Location2Formula4" },
  { locationId: "LocationId2", identifier:"F25", id:"Location2Formula5" },
];

const systemValues: ValuePairs[] = [
  { locationId: "LocationId1", identifier:"S11", id:"Location1System1" },
  { locationId: "LocationId1", identifier:"S12", id:"Location1System2" },
  { locationId: "LocationId1", identifier:"S13", id:"Location1System3" },
  { locationId: "LocationId1", identifier:"S04", id:"Location1System4" },
  { locationId: "LocationId1", identifier:"S15", id:"Location1System5" },
  { locationId: "LocationId2", identifier:"S21", id:"Location2System1" },
  { locationId: "LocationId2", identifier:"S22", id:"Location2System2" },
  { locationId: "LocationId2", identifier:"S23", id:"Location2System3" },
  { locationId: "LocationId2", identifier:"S04", id:"Location2System4" },
  { locationId: "LocationId2", identifier:"S25", id:"Location2System5" },
];

const userId: string = "djw2";

/* Type Definitions */
type Dictionary = { [index: string]: string }

type IdentifierDictionaries = {
  components: Dictionary,
  monitorFormulas: Dictionary,
  monitoringSystems: Dictionary,
}

type LocationIdentifiers = {
  locations: { [key: string]: IdentifierDictionaries },
  userId: string,
}

/* getUnifiedIdentifiers Example: Begin */

/* Initialize New Identifiers */
const identifiers: LocationIdentifiers = {
  locations: { },
  userId,
};

for (const locationId of locationList)
{
  identifiers.locations[locationId] = { components: {}, monitorFormulas: {}, monitoringSystems: {} }

  // Simulate getIdentifiers(): Begin
  const partialIdentifiers: IdentifierDictionaries = { components: {}, monitorFormulas: {}, monitoringSystems: {} }

  for (const componentPair of componentValues){
    if (componentPair.locationId == locationId) {
      partialIdentifiers.components[componentPair.identifier] = componentPair.id;
    }
  }

  for (const formulaPair of formulaValues){
    if (formulaPair.locationId == locationId) {
      partialIdentifiers.monitorFormulas[formulaPair.identifier] = formulaPair.id;
    }
  }

  for (const systemPair of systemValues){
    if (systemPair.locationId == locationId) {
      partialIdentifiers.monitoringSystems[systemPair.identifier] = systemPair.id;
    }
  }
  // Simulate getIdentifiers(): End

  // Probably still need this seciton of getUnifiedIdentifiers() to clear undefined from getIdentifiers data.
  Object.keys(partialIdentifiers.components).forEach(
    key =>
      partialIdentifiers.components[key] === undefined &&
      delete partialIdentifiers.components[key],
  );
  Object.keys(partialIdentifiers.monitorFormulas).forEach(
    key =>
      partialIdentifiers.monitorFormulas[key] === undefined &&
      delete partialIdentifiers.monitorFormulas[key],
  );
  Object.keys(partialIdentifiers.monitoringSystems).forEach(
    key =>
      partialIdentifiers.monitoringSystems[key] === undefined &&
      delete partialIdentifiers.monitoringSystems[key],
  );

  // Changed this section of getUnifiedIdentifiers() to add indexing into locations.
  Object.assign(identifiers.locations[locationId].components, partialIdentifiers.components)
  Object.assign(identifiers.locations[locationId].monitorFormulas, partialIdentifiers.monitorFormulas)
  Object.assign(identifiers.locations[locationId].monitoringSystems, partialIdentifiers.monitoringSystems)
}

/* getUnifiedIdentifiers Example: End */

console.log(identifiers)

/*
Need to change the buildObjectList() that use "identifiers" references to use "identifiers.locations[monitorLocationId]" instead.
*/

[mxtomoto1]

Steps on dev:

1. Check out ORIS 1571, 3 - import historical emissions data from 2023 Q3.
2. Evaluate and check evaluation report.

Error appears still present - attached image shows the evaluation report when Evaluation Status shows Critical Errors.

[esaber76]

@mxtomoto1 - did you download the report or view from email attachment? Viewing the complete reports from the UI is currently broken by #6252.

[mxtomoto1]

The above testing was done by opening up the Emissions Evaluation Report from the UI by clicking the "Critical Errors" button. Downloading the report from the popup or viewing the email attachment do not provide any additional information - critical errors are still missing.

(The left is the email attachment, and the right is from downloading the report.)

[ergjustin]

• The Check Session Failed: BulkLoad[camdecmpswks.check_log]: 22P04: missing data for column "chk_log_comment" issue, caused by a new line issue in the bulk load process, has been fixed in the quartz-scheduler.

• The error details not displaying issue was fixed in the ecmps-ui (related to : #6252 )

• Implemented the necessary view change for the bulk load in camdecmpswks.vw_qa_cert_event

  NULL::text AS max_op_days_prior_qtr,
  NULL::text AS min_op_days_prior_qtr,
  NULL::text AS max_op_hours_prior_qtr,
  NULL::text AS min_op_hours_prior_qtr,
                        To
  NULL::integer AS max_op_days_prior_qtr,
  NULL::integer AS min_op_days_prior_qtr,
  NULL::integer AS max_op_hours_prior_qtr,
  NULL::integer AS min_op_hours_prior_qtr,

[esaber76]

Acceptance Test:

This was still an issue in tst as of 6/24/2024 for the location below. It should be resolved with the next push to tst and the steps below should work.

1. Log in, check out ORIS 1571, 3, and revert to official.
2. Import 2023 Q3 emissions from historical and evaluate.
3. The Eval Status column on the Evaluate screen matches what is shown in the evaluation report.
4. If the Eval Status has no errors, export/reimport both the MP and EM data.
5. Evaluate both.
6. Assuming a 2024 GF qualification record wasn't added, the MP and EM evaluations should have critical errors.
7. The Eval Status columns on the Evaluate screen should match what is shown in the evaluation reports.