ISIS Xpress users will be able to access legacy proposals

[TickleThePanda]

Split from https://github.com/UserOfficeProject/stfc-user-office-project/issues/27

/overwrite opened 2020-09-04

[TickleThePanda]

We should size this after #82

[simonfernandes]

Notes from backlog pruning with George and Emma:

On legacy, previous Xpress proposals can be viewed, so we'll still need this to be imported.

[ACLay]

@simonfernandes I was doing a trino query to start thinking about missing legacy imports in allocations, and I've found a few problematic ISIS RB's that might impact this.

1490001, 1490003, & 1490004 are all present in both the Xpress database and ccproposal in regular ISIS.

Trino query

```sql select count(*), reference_number, listagg(facility, ', ') within group (order by facility) from ( select cast(reference_number as varchar) as reference_number, 'ISIS' as facility from iops_v4.iops_v4.proposal union select rb, 'ISIS CC' from iops_v4.iops_v4.ccproposal union --select cast(reference_number as varchar), facility_area --from prop_clf_all.prop_clf_all.proposal --union select reference_number, 'ISIS Xpress' from xpress_v4.xpress_v4.proposal ) group by reference_number order by count(*) desc, try_cast(reference_number as decimal) ```

[simonfernandes]

Sprint planning notes:

Send Emma the details of the conflicting proposals and then Emma can make a judgement about how to renumber them

[simonfernandes]

We can renumber the CC proposals. Sounds like we might be able to renumber them to YY39XXX which it seems is how they should have been in the first place.

---

From: Gozzard, Emma (STFC,RAL,ISIS) emma.gozzard@stfc.ac.uk Sent: Wednesday, August 14, 2024 2:59 PM To: Fernandes, Simon (STFC,RAL,ISIS) simon.fernandes@stfc.ac.uk Subject: RE: Overlapping Xpress and CC proposals Hi Simon,

Ah yes, Director’s Discretion (thank God we don’t do that anymore). These were all meant to be listed as YY39XXX, but I seem to remember my predecessor saying he might have gone a bit off-piste with a few. Joy!

Thanks, Emma

From: Fernandes, Simon (STFC,RAL,ISIS) [simon.fernandes@stfc.ac.uk](mailto:simon.fernandes@stfc.ac.uk) Sent: Wednesday, August 14, 2024 2:57 PM To: Gozzard, Emma (STFC,RAL,ISIS) [emma.gozzard@stfc.ac.uk](mailto:emma.gozzard@stfc.ac.uk) Subject: RE: Overlapping Xpress and CC proposals

Hi Emma,

The legacy CC proposal reference number formatting looks quite confusing, but these are all listed as “Directors Discretion” CC proposals which I think is why they likely differ from the regular format.

Thanks, Simon

From: Gozzard, Emma (STFC,RAL,ISIS) [emma.gozzard@stfc.ac.uk](mailto:emma.gozzard@stfc.ac.uk) Sent: Wednesday, August 14, 2024 2:47 PM To: Fernandes, Simon (STFC,RAL,ISIS) [simon.fernandes@stfc.ac.uk](mailto:simon.fernandes@stfc.ac.uk) Subject: RE: Overlapping Xpress and CC proposals

Hi Simon,

Thanks. I still don’t understand how they got those RB numbers though, as they are nothing like the calibration or commission numbering. Still, it was before my time at STFC and it doesn’t matter now.

Thanks, Emma

From: Fernandes, Simon (STFC,RAL,ISIS) [simon.fernandes@stfc.ac.uk](mailto:simon.fernandes@stfc.ac.uk) Sent: Wednesday, August 14, 2024 2:45 PM To: Gozzard, Emma (STFC,RAL,ISIS) [emma.gozzard@stfc.ac.uk](mailto:emma.gozzard@stfc.ac.uk) Subject: RE: Overlapping Xpress and CC proposals

Hi Emma,

These are Calibration and Commissioning proposals. They’re now created directly in the Scheduler.

Thanks, Simon

From: Gozzard, Emma (STFC,RAL,ISIS) [emma.gozzard@stfc.ac.uk](mailto:emma.gozzard@stfc.ac.uk) Sent: Wednesday, August 14, 2024 2:06 PM To: Fernandes, Simon (STFC,RAL,ISIS) [simon.fernandes@stfc.ac.uk](mailto:simon.fernandes@stfc.ac.uk) Subject: RE: Overlapping Xpress and CC proposals

Hi Simon,

Did we find out what CC actually was in the end? I can make a guess that it was a catch-all for a few different “doesn’t really fit into any other route” experiments. I’m happy for the three listed CC proposals to be renumbered – we should keep the Xpress numbering as is.

Thanks, Emma

From: Fernandes, Simon (STFC,RAL,ISIS) [simon.fernandes@stfc.ac.uk](mailto:simon.fernandes@stfc.ac.uk) Sent: Wednesday, August 14, 2024 12:49 PM To: Gozzard, Emma (STFC,RAL,ISIS) [emma.gozzard@stfc.ac.uk](mailto:emma.gozzard@stfc.ac.uk) Subject: Overlapping Xpress and CC proposals

Hi Emma,

Please could you have a look at these overlapping Xpress and CC proposals when you get a chance? They’re from this issue: https://github.com/UserOfficeProject/issue-tracker/issues/87

Many thanks, Simon

[simonfernandes]

We will need to consider that, although we'll have a firm switchover date, there will be a need for scientists to edit some past proposals to account for sample and review delays. Since the old software will be switched off, we'll need to make sure these are editable in the new software.

@srconway suggested that we could possibly carry on importing only the core details (the questionnaires differ per instrument on legacy so importing the whole thing becomes tricky) and ask scientists to just refer to the legacy PDFs (once they are imported), so long as legacy proposals can still be updated in UOP.

After a discussion with @deepaksftc, if we go that route, we will need to link proposals to techniques to ensure the correct viewing permissions. This could be a small adjustment to the legacy import script.

[ACLay]

Importing all legacy data may need to tie into/be part of a renumbering of legacy proposals brought up in https://github.com/isisbusapps/ISISBusApps/issues/964

Pre-2010 Xpress experiments seem to follow a Y9XXXX (essentially the current scheme but with a single digit for year), so they're only 6 digits long and could clash with the pre-submission range in UOP.

[simonfernandes]

Hi @Cat-Lucifer, as kind-of agreed above, I've come up with some new XBs for the above 3 Xpress proposals we discussed. There are also another couple of Xpress proposals that are going to cause issues with the legacy import, as they already exist as historic LSF proposals. Please could you advise on those too?

@ACLay I was planning to renumber these directly in the legacy database before their import, can you forsee any problems with doing it like this? I think I'd need to make a script and get it reviewed to ensure everything is updated. Would you prefer that I leave the three XBs you mentioned above to do in the Allocations issue? They shouldn't affect this legacy import as CC is not yet imported.

Clashing XB	Clashes with	New XB
1490001	CC	1493023
1490003	CC	1493024
1490004	CC	1493025
1191000	LSF	1190101
1191001	LSF	1190102

[Cat-Lucifer]

@simonfernandes No idea what these LSF ones are. Happy for you change the numbers accordingly.

[simonfernandes]

@Cat-Lucifer Cool, 1190xxx goes up to 100 already, so I'm thinking 1190101 and 1190102 would be good replacement XBs. I've edited the above table to reflect this.

[ACLay]

My concern with renumbering is that we miss references in other datasources like icat, the scheduler db, sharepoint... and lose the ability to trace things through if we just make changes in one place. If it's just a couple of old xpress proposals it's hopefully not gonna be a big deal, but I'd be hesitant to make this a regular part of migration without wider consultation.

There looks to be a lot of potential for LSF clashes in legacy data. This allocations db query is showing nearly 400 LSF proposals with short RB numbers, 57 of which are 7 digits and could clash with ISIS ones.

SELECT *
FROM REPORTING_PROPOSAL rp 
WHERE FACILITY = 'LSF' AND NOT LENGTH(REFERENCE_NUMBER) = 8
ORDER BY cast(REFERENCE_NUMBER AS number) desc

[simonfernandes]

Ok, I will just update the two Xpress numbers that will clash with the legacy import then and leave the others in that case.

[simonfernandes]

@Cat-Lucifer When we import these Xpress legacy proposals, we're doing it in a way that will allow scientists to still update proposals in the new system even if they originated from legacy, for those proposals that are still in progress in some way.

I previously mentioned status mapping in the statuses issue. For technique/instrument mapping, I'm planning to take the instrument from legacy and determine the instrument/technique from the technique information we've got on the new system. I have these questions though:

1. Where a legacy instrument is linked to more than one technique in the new system, is it fine to just assign it to the first technique, whatever that may be?
2. How should we handle legacy instruments that don't appear on the new system? If they're not assigned to a technique then nobody will be able to see them. I believe this will be ALF, ALL, CLARA, MUON, QENS, SANS.

[Cat-Lucifer]

@simonfernandes:

1. I think this is fine. We can amend individual cases if anybody complains later.
2. I know all of these except ALL and CLARA, so will need to check and get back to you on this.

[simonfernandes]

@Cat-Lucifer CLARA doesn't exist on prod (must have been looking at dev accidentally!) and there are 0 ALL proposals on prod, so problem solved I think!

[Cat-Lucifer]

@simonfernandes Fab news! Techniques for the others:

ALF: Powder / single crystal diffraction MUON: Muon QENS: Quasi-elastic scattering SANS: Small angle scattering

[simonfernandes]

Closed via https://github.com/UserOfficeProject/user-office-core/pull/853 and https://github.com/isisbusapps/internal-scripts/pull/153