GPL-609 As a SSR (Rich C) I would like the positive Lighthouse report to include whether samples have been cherry picked so that we can check if requested samples have already been started.

[TWJW-SANGER]

User story
As a SSR (Rich C) I would like the positive crawler report to include whether samples have been cherry picked (included in a released Batch in Sequencescape), so that we can check if requested samples have already been started.

Who are the primary contacts for this story
Richard C

Acceptance criteria
To be considered successful the solution must allow:

• Report should add a True/False or Date/NULL field for cherry picking (needs refining)

[stevieing]

Plan for implementation:

• use the events warehouse
• two events which are relevant; cherrypick layout set and slf cherrypick (preferred)
• The root sample id equates to the sample description
• sample description is in the samples table in the mlwh to get the sanger sample id
• the sanger sample id in subjects table called friendly name
• subjects table is joined to events via roles

plan is to write a single SQL statement to pull the data from the events / ml warehouse with a method in lighthouse to add this data to the positives report.

[stevieing]

select mlwh_sample.description as description
FROM mlwarehouse.sample as mlwh_sample
JOIN mlwh_events.subjects mlwh_events_subjects ON (mlwh_events_subjects.friendly_name = sanger_sample_id)
JOIN mlwh_events.roles mlwh_events_roles ON (mlwh_events_roles.subject_id = mlwh_events_subjects.id)
JOIN mlwh_events.events mlwh_events_events ON (mlwh_events_roles.event_id = mlwh_events_events.id)
JOIN mlwh_events.event_types mlwh_events_event_types ON (mlwh_events_events.event_type_id = mlwh_events_event_types.id)
WHERE mlwh_sample.description IN ('sample1,sample2')
AND mlwh_events_event_types.key = 'slf_cherrypicking'
GROUP BY mlwh_sample.description

[stevieing]

• mock database connection
• getting cherrypicked samples with tests
• getting all positive samples
• getting location barcodes from labwhere
• Join data from labwhere with positive samples
• Join data from cherrypicking
• join data from samples declarations
• get distinct plate barcodes

[KatyTaylor]

Closing this as it's in the Done column