GPL-630 LH plate arrived, has plate map or destroyed? to manage reefer volumes

[rl15]

User story
GPL-630 | As heron stakeholders (Emma, SSR, Tanya) we would like a report (SQL, SQL view/ Tableau vis) that shows for a RNA LH plate, the date arrived, LIMS submission, has plate map & if destroyed

Who are the primary contacts for this story
Sameena I
Danni W?
Emma G
Tanya B
Rich C/
Deb P

Acceptance criteria
To be considered successful the solution must allow:

• Report available to uses with four columns;
  LH RNA plate barcode eg: AP-rna-00110029,
  Date first scanned into LabWhere (labwhere_create) (timestamp)
  LIMS submission for one or more samples in plate (yes/no)
  Has plate map (yes/no)
  If destroyed (Bin location in LabWhere) (yes/no)
• Report needs to be in format TBD.

Dependencies
This story is blocked by the following dependencies:
Operations create new 'Heron bin' location of type bin in LabWhere

Additional context
Add any other context or screenshots about the feature request here.

[rl15]

Deb P wrote (Friday, 4 September 2020 at 09:12)

Or this just could be an extension to the report that Neil runs
smb://file01-s0/dna_pipelines/Team 251 Tech Admin/SSR/Heron Project/SSR/Heron Study reports

[rl15]

Deb P wrote (Friday, 4 September 2020 at 08:46)
I think it would be handy to have that data all in one place ie the lighthouse report (http://lighthouse-ui.psd.sanger.ac.uk/)– then we can download it to excel.

[rl15]

Work flow discussed with Operations & PAM in zoom meeting on 9th Sept
https://drive.google.com/file/d/1jfMbwrCpEDaynelEthDeucXivAQkppEN/view?usp=sharing

Key decision operations will manage with ad 'hoc report' until we can get to this feature. At time of writing 17 Backlog items prioritised above this. Potential that reporting features released in September may allow ad hoc reports to be painless.

[TWJW-SANGER]

Not sure if has_platemap is obtainable from just MLWH. Before a plate_map is processed, the plate barcode only exists in labware reefer locations when the box is scanned in.
While labwhere events for this are in MLWH they don't have location types (plates, boxes etc) so it doesn't look like can distinguish between a box and a plate without a plate map. (Possibly on name but bit icky)
Might need to query labware database & MLWH for this one.

[KatyTaylor]

@TWJW-SANGER in case this adds some info you didn't know -
The earliest we sensibly know about the plate maps is when the crawler scheduled job runs at 1am each morning. At that point, the samples are created in a) the MongoDB, and b) in the lighthouse_sample table in the MLWH (this is quite new)

So if the sample is in the lighthouse_sample table in MLWH, it has a plate map. But there might be a day or so delay between when it gets uploaded to sftp and when it's in a database.

[TWJW-SANGER]

Tanya is happy to deprecate if Emma agrees