Resend Simple Report's CSV uploads

[victor-chaparro]

Problem statement

ReportStream has not been receiving Simple Report bulk uploader results through the UP. All Covid reports are still being sent to the CP and they can be used to resend them to STLTs that have been migrated to the UP.

What you need to know

This query can be used to find CSV Upload reports that haven't been delivered from 12/2/2204 - 1/23/2024:

select m.* from report_file rf join covid_result_metadata m on
rf.report_id = m.report_id
where rf.sending_org = 'simple_report' 
and (m.ordering_facility_state in ('AL','AK','AZ','AR','CO','CA','DE','FL','GU','HI','ID','IL','IN','IA','KS','LA','ME','MD','MA','MN','MO','MT','NV','NY','ND','OH','OK','OR','PR','RI','SC','SD','TX','VI','WA')
or m.ordering_provider_state in ('AL','AK','AZ','AR','CO','CA','DE','FL','GU','HI','ID','IL','IN','IA','KS','LA','ME','MD','MA','MN','MO','MT','NV','NY','ND','OH','OK','OR','PR','RI','SC','SD','TX','VI','WA')
or m.testing_lab_state in ('AL','AK','AZ','AR','CO','CA','DE','FL','GU','HI','ID','IL','IN','IA','KS','LA','ME','MD','MA','MN','MO','MT','NV','NY','ND','OH','OK','OR','PR','RI','SC','SD','TX','VI','WA')
or m.patient_state in ('AL','AK','AZ','AR','CO','CA','DE','FL','GU','HI','ID','IL','IN','IA','KS','LA','ME','MD','MA','MN','MO','MT','NV','NY','ND','OH','OK','OR','PR','RI','SC','SD','TX','VI','WA')
)
and rf.sending_org_client = 'csvuploader'
and rf.created_at BETWEEN '2024-12-01' AND '2025-01-24'

or Pennsylvania since they were onboarded on 12/13/24:

select m.* from report_file rf join covid_result_metadata m on
rf.report_id = m.report_id
where rf.sending_org = 'simple_report' 
and test_result = 'Positive'
and (m.ordering_facility_state in ('PA')
or m.ordering_provider_state in ('PA')
or m.testing_lab_state in ('PA')
or m.patient_state in ('PA')
)
and rf.sending_org_client = 'csvuploader'
and rf.created_at BETWEEN '2024-12-13' AND '2025-01-24'

For Washington:

select m.* from report_file rf join covid_result_metadata m on
rf.report_id = m.report_id
where rf.sending_org = 'simple_report' 
and test_result = 'Positive'
and (m.ordering_facility_state in ('WA')
or m.ordering_provider_state in ('WA')
or m.testing_lab_state in ('WA')
or m.patient_state in ('WA')
)
and rf.sending_org_client = 'csvuploader'
and rf.created_at BETWEEN '2025-01-13' AND '2025-01-24'

Using the queries above we can use the report ids to download the reports from azure storage explorer inside the receiver/simple-report.csvuploader folder.
After getting the files we can resend them using the reports API and simple report's azure key.
Before resending the files, \ the simple-repot routing filter will need to be removed from STLTs we have migrated so far.
we need to look for a way to minimize the time the receiver will be active in both pipelines to prevent sending duplicate results while we resend these reports

• Ask Simple Report to pause sending us reports during that time.
• Do one STLT at a time and maybe during off hours?

Acceptance criteria

• Before resending, confirm plan with SR and Tech Lead (Victor)
• All CSV uploads sent through the CP between 12/2/2204 - 1/23/2024 have been resent to migrated STLTs