Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Email to Student - 2nd Disbursement has passed, still pending #3594

Closed
14 tasks done
michesmith opened this issue Jul 30, 2024 · 2 comments
Closed
14 tasks done

Email to Student - 2nd Disbursement has passed, still pending #3594

michesmith opened this issue Jul 30, 2024 · 2 comments
Assignees
@CarlyCotton @lewischen-aot
Labels
Institution Institution Features Student Student Features
Milestone
Full-Time "Asset"

Comments

@michesmith
Copy link
Collaborator

michesmith commented Jul 30, 2024 •
edited by lewischen-aot
Loading

User Story
As a student, I want to be notified if my 2nd disbursement date has passed and my institution has not yet approved the second COE/disbursement. It is not obvious at a glance that there is anything blocking me from receiving my second disbursement of funding (outside of the tracker card) as my application says 'Complete'. This is also extra important for students going to institutions that don't see a large volume of BC students and may not have processes in place to action the COEs in a timely manner.

Acceptance Criteria

  • This email should only be sent once.
  • The day after the earliest disbursement date of a second disbursement (Earliest date 2nd disb: Oct 1, 2024 / Email: Oct 2, 2024) generate an email to the student if COE is still pending.
  • GC Notify Template: 55fcf228-b899-49a7-ab80-9b854c0bd884

SUBJ: Your funding is waiting on your institution - StudentAid BC

Hey ((givenNames)) ((lastName)),

Your second disbursement of funds is waiting on action from your institution. Please contact a financial aid officer at your institution to prevent any delays.

Login now

Technical

  • Add the new notification generation process in the existing student-application-notifications scheduler.
  • Refactor the current scheduler processor to allow multiple notifications to be handled.
    • Create a "service" to isolate each notification.
    • The processor should trigger all the notifications.
  • Conditions to send the notification
    • Disbursement should be "pending".
    • COE should be "pending".
    • Only application current assessment, not overwritten.
    • A notification was never sent before for the assessment.
  • E2E tests
  • PRs
    • DB migration and model changes.
    • Refactor/new services for individual notification processes.
    • E2E
@michesmith michesmith added Institution Institution Features Student Student Features Business Items under Business Consideration labels Jul 30, 2024
@CarlyCotton CarlyCotton changed the title GC Notify Email When Disbursment date passed (1st and 2nd and COE not approved ) GC Notify Email When Disbursment date passed (2nd and COE not approved ) Aug 1, 2024
@CarlyCotton CarlyCotton self-assigned this Aug 13, 2024
@CarlyCotton CarlyCotton changed the title GC Notify Email When Disbursment date passed (2nd and COE not approved ) [NEEDS CONTENT] Email to Student - 2nd Disbursement has passed, still pending Aug 26, 2024
@CarlyCotton CarlyCotton changed the title [NEEDS CONTENT] Email to Student - 2nd Disbursement has passed, still pending Email to Student - 2nd Disbursement has passed, still pending Sep 5, 2024
@CarlyCotton CarlyCotton added Dev & Architecture Development and Architecture and removed Business Items under Business Consideration labels Sep 12, 2024
@andrewsignori-aot
Copy link
Collaborator

@CarlyCotton we planned to execute the check to send the notification once based on the assessment which means that a reassessment may potentially trigger a new notification for the second disbursement. Does it sound ok?

@andrewsignori-aot andrewsignori-aot removed the Dev & Architecture Development and Architecture label Nov 14, 2024
@CarlyCotton
Copy link
Collaborator

@andrewsignori-aot That's good, it's based on the disbursement dates which would change on a reassessment.

@lewischen-aot lewischen-aot self-assigned this Jan 23, 2025
github-merge-queue bot pushed a commit that referenced this issue Jan 28, 2025
@lewischen-aot
#3594 - Email to Student - 2nd Disbursement has passed, still pending…
2285eea 
… (Part 1) (#4299)

As the first part of the story PR, this PR has the following change:
- Added DB migration for inserting the record "Student application
notification for overdue second disbursement still pending." to the
table `notificationMessages`

### Rollback evidence
![Screenshot 2025-01-28
093728](https://github.com/user-attachments/assets/a4ecf1ca-21d3-4826-a7a3-3f604443f5d6)

Screenshot of the added record to the `notificationMessages` table

![image](https://github.com/user-attachments/assets/892de986-c768-4b25-9b57-eaf83fba927a)
lewischen-aot added a commit that referenced this issue Jan 29, 2025
@lewischen-aot
Merge branch 'main' into feature/#3594-email-to-student-2nd-disbursem…
dcb05c4 
…ent-has-passed-part-2
lewischen-aot added a commit that referenced this issue Jan 31, 2025
@lewischen-aot
Merge branch 'main' into feature/#3594-email-to-student-2nd-disbursem…
8307f9e 
…ent-has-passed-part-2
github-merge-queue bot pushed a commit that referenced this issue Jan 31, 2025
@lewischen-aot
#3594 - Email to Student - 2nd Disbursement has passed, still pending…
1beec0d 
… (Part 2) (#4303)

As the second part of the story, this PR has the following changes 
- Added the new notification generation process in the existing
student-application-notifications scheduler.
- Refactored the current scheduler processor to allow multiple
notifications to be handled.
   - Created a "service" to isolate each notification.
   - The processor should trigger all the notifications.
- Created the query to get the second disbursements where the
disbursement dates have passed and the institution has not yet approved
the second COE/disbursement. The logic is as follows:
- First, retrieves disbursements with disbursement dates that have
passed today, notifications have not been sent for completed
applications, which have two disbursements with the second disbursement
being either "Completed" or "Required" for its COE status. Use
`COUNT(assessment.id)` to differentiate 2-disbursements applications
from 1-disbursement applications.
- Then, find the disbursements with "Required" COE status from the above
subquery (the disbursement Ids) that are pending.
   - Conditions to send the notification
      - Disbursement should be "pending".
      - COE should be <del>"pending"</del> "required".
      -  Only completed application current assessment, not overwritten.
- Send the notification to the student using the GC Notify Template in
the story and mark the assessment Id in the record's meta data to ensure
the notification is only sent once.

Screenshot of the notification email
![Screenshot 2025-01-28
165147](https://github.com/user-attachments/assets/6ef87712-081d-4582-b9e2-803ca4821b21)

SQL version of the query as a reference
```sql
with disbursement_set as (
select
	max(ds.id) as "id"
from sims.disbursement_schedules ds 
inner join sims.student_assessments sa on sa.id = ds.student_assessment_id 
inner join sims.applications a on a.id = sa.application_id 
and a."application_status" = 'Completed'
and a.is_archived = false
and ds."coe_status" in ['Completed', 'Required']
and ds.disbursement_date < :today
group by sa.id
having count(sa.id) = 2
)
select
	ds.id as "disbursement_id",
	sa.id as "assessment_id",
	ds.disbursement_date,
	ds."disbursement_schedule_status",
        u.id as "user_id",
	u.first_name as "given_names",
	u.last_name as "last_name"
from sims.disbursement_schedules ds 
inner join sims.student_assessments sa on sa.id = ds.student_assessment_id 
inner join sims.applications a on a.id = sa.application_id 
inner join sims.students s on s.id = a.student_id
inner join sims.users u on u.id = s.user_id
where ds.id in (select "id" from disbursement_set)
and ds."coe_status" = 'Required'
and ds."disbursement_schedule_status" = 'Pending';
```
github-merge-queue bot pushed a commit that referenced this issue Feb 1, 2025
@lewischen-aot
#3594 - Email to Student - 2nd Disbursement has passed, still pending…
ef8da31 
… (Part 3) (#4310)

As the final part of the story, this PR has the following changes:
- Added E2E tests for the second disbursement reminder notification.
- Should generate a second disbursement reminder notification for a
student when the application is Completed and the second disbursement
with Required COE status is still pending when its disbursement date has
passed.
- Should not generate a second disbursement reminder notification for a
student when a notification is already sent for the second disbursement
still pending.
- Should not generate a second disbursement reminder notification for a
student when an application has two disbursements with both COEs
completed but the second disbursement is still pending.


![image](https://github.com/user-attachments/assets/19f8e5e6-6655-4957-8415-6539024ccee9)
This was referenced Feb 25, 2025
Open
Open
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@CarlyCotton CarlyCotton

@lewischen-aot lewischen-aot

Labels
Institution Institution Features Student Student Features
Projects
None yet
Milestone
Full-Time "Asset"
Development

No branches or pull requests
6 participants
@ninosamson @andrewsignori-aot @CarlyCotton @michesmith @lewischen-aot @AnnaPBashkatova