Test: Submission failure emails

[annalbrecht]

Based on info in #6205.

We need a way to fail whole submissions and individual submissions in a set.

• If the submission queueing fails, need to verify if the on-screen grid is updated to indicate that (after refreshing the screen).
• After the submission processing job completes, if the submission failed, the submitter should be sent an email to notify them about the failure.
• After the submission processing job completes, if one submission in a set was successful and one failed, the submitter should be sent 2 emails-- one notifying them about the failure, one for the successful submission.

[yonatan-dp]

Scenario 1: Failure During Submission Processing

Objective: Simulate a failure during submission procssing.

---

Steps:

1. Identify a Submission Set on the Dev Database.

• Find a submission_set entry. The submission set does need to have a status_cd of 'QUEUED'.
• Note the submission_set_id (e.g., '7767f6e7-d700-8a34-6561-368d79ea13420').

2. Corrupt a Submission Queue Record:

• Locate the corresponding entries in submission_queue where submission_set_id (for the above example) is '7767f6e7-d700-8a34-6561-368d79ea13420'.
• Choose one of the submission_queue records related to the MP process (Monitoring Plan).
• Update the process_cd in the submission_queue record to an invalid value (such as XYZ).

UPDATE camdecmpsaux.submission_queue
SET process_cd = 'XYZ'
WHERE 
submission_set_id = '7767f6e7-d700-8a34-6561-368d79ea13420'
and process_cd = 'MP'

3. Set the Submission Set Status to 'QUEUED':

UPDATE camdecmpsaux.submission_set
SET status_cd = 'QUEUED'
WHERE submission_set_id = '7767f6e7-d700-8a34-6561-368d79ea13420';

3. Wait for the Quartz job to pick up the submission set.

• The quartz job processes submission sets in the QUEUED status every few minutes.

---

Expected Outcome:

• The system should throw an error during submission process because it cannot find the process code.
• A failure email should be sent to the user and ECMPS support.
• The submission_set status should be updated to 'ERROR'.
• The note field should contain the error details and note_time should indicate when the error occurred.

4. Restore the submission_set record to its original value

UPDATE camdecmpsaux.submission_set SET process_cd = 'MP' WHERE submission_set_id = '7767f6e7-d700-8a34-6561-368d79ea13420';

Scenario 2: Failure for specific qa test records

Objective: Simulate failure for selected qa_test, qce, or tee submission queue records.

---

Steps:

1. Identify a Submission Set:

• Find a submission_set entry. The submission set does need to have a status_cd of 'QUEUED'.
• Note the submission_set_id (e.g., '7767f6e7-d700-8a34-6561-368d79ea13420').

2. Make sure the submission set has a qa_test, qce, or tee submission queue record.

select * from camdecmpsaux.submission_queue sq 
where sq.submission_set_id = '7767f6e7-d700-8a34-6561-368d79ea13420'

3. Corrupt a reporting entry in the camdaux.datatable for the desired report type (qa_test, qce, or tee)

• The dataset_cd for the qa reports are: QAT_FEEDBACK, QCE_FEEDBACK, and TEE_FEEDBACK
• Here are the procedures for TEE, as an example
  1. Save the sql_statement column value first to restore later.

select sql_statement FROM camdaux.datatable WHERE dataset_cd = 'TEE_FEEDBACK';

2. Update the sql_statement value to something incorrect (see example given below).

   UPDATE camdaux.datatable 
   set sql_statement = 'SELECT * FROM {SCHEMA}.xyz_abc_feedback($1)'
   WHERE dataset_cd = 'TEE_FEEDBACK';

3. Set the Submission Set Status to 'QUEUED':

UPDATE camdecmpsaux.submission_set
SET status_cd = 'QUEUED'
WHERE submission_set_id = '7767f6e7-d700-8a34-6561-368d79ea13420';

3. Wait for the Quartz job to pick up the submission set.

• The quartz job processes submission sets in the QUEUED status every few minutes.

Expected Outcome:

• The system should throw an error for each submission queue record for that report type. Other submission queue records should process normally.
• A failure email should be sent to the user and ECMPS support.
• The submission_set status should be updated to 'ERROR'.
• The note field should contain the error details and note_time should indicate when the error occurred.

4. Restore the camdaux.datatable sql_statement value to its original value

UPDATE camdaux.datatable 
set sql_statement = 'SELECT * FROM {SCHEMA}.get_test_extension_exemption_feedback($1)'
WHERE dataset_cd = 'TEE_FEEDBACK';

[mxtomoto1]

Scenario 1 as described above was followed for testing. Tester / submitter received a failure email (unable to ascertain if ECMPS Support received failure email). The submission_set status was updated to 'ERROR', and the note field contained the error details and note_time indicated when the error occurred.