#3282 - Detect Assessment Offering Dates Change (Part 2)

sh16011993 commented 2 weeks ago

As a part of this PR, the following was completed:

workflow-wrap-up worker was updated to populate the new column previous_date_changed_reported_assessment_id for an application in Completed state. The following were considered when updating the worker:

Scenario 1 Checked if there is a previously reported assessment for the application (ordered by assessment_date desc, limit 1), immediately before the assessment being processed right now.

Scenario 2 If no assessment was ever reported, checked if an e-Cert was generated for this application.

If Scenario 1 or Scenario 2 produced an assessment: Checked if the offering dates changed, If yes, saved the assessment in the previous_date_changed_reported_assessment_id. If no, then left the column as is.

Generated Queries for reference:


-- SELECT 1st

SELECT
    "studentAssessment"."id" AS "studentAssessmentId",
    "offering"."study_start_date" AS "offeringStudyStartDate",
    "offering"."study_end_date" AS "offeringStudyEndDate"
FROM
    "sims"."student_assessments" "studentAssessment"
INNER JOIN "sims"."education_programs_offerings" "offering" ON
    "offering"."id" = "studentAssessment"."offering_id"
INNER JOIN "sims"."applications" "application" ON
    "application"."id" = "studentAssessment"."application_id"
WHERE
    "application"."id" = $1
    AND "studentAssessment"."previous_date_changed_reported_assessment_id" IS NOT NULL
    AND "studentAssessment"."reported_date" IS NOT NULL
ORDER BY
    "studentAssessment"."assessment_date" DESC
LIMIT 1;

-- SELECT 2nd

SELECT
    "studentAssessment"."id" AS "studentAssessmentId",
    "offering"."study_start_date" AS "offeringStudyStartDate",
    "offering"."study_end_date" AS "offeringStudyEndDate"
FROM
    "sims"."student_assessments" "studentAssessment"
INNER JOIN "sims"."education_programs_offerings" "offering" ON
    "offering"."id" = "studentAssessment"."offering_id"
INNER JOIN "sims"."applications" "application" ON
    "application"."id" = "studentAssessment"."application_id"
INNER JOIN "sims"."disbursement_schedules" "disbursementSchedules" ON
    "disbursementSchedules"."student_assessment_id" = "studentAssessment"."id"
WHERE
    "application"."id" = $1
    AND "disbursementSchedules"."disbursement_schedule_status" = $2
ORDER BY
    "studentAssessment"."assessment_date" DESC
LIMIT 1;

-- UPDATE

UPDATE
    "sims"."student_assessments"
SET
    "previous_date_changed_reported_assessment_id" = $1,
    "modifier" = $2,
    "updated_at" = CURRENT_TIMESTAMP
WHERE
    "id" = $3;

Next PR: e2e tests