Support LSF FAP 2022_2 meetings and outcomes

[TickleThePanda]

Things we need to do:

• [x] #314
• [x] Creating the booklets
• [x] Extracting proposal data into a spreadsheets for FAP data collection with a query or manually by the UO
• [x] Get data into SharePoint to allow for allocation of time

[EdwardHaynes]

Based on the sp_proposal_list_cache table the query to import to SharePoint will need to include:

• RB_NO
• INSTRUMENT_ALLOCATED
• UN
• ROUND
• CREATED
• ACCESS_ROUTE
• TITLE
• EU_FUNDING_ACCEPTED
• NEWTON_FUNDING_ACCEPTED
• FACILITY_NAME

[EdwardHaynes]

The following query seems to gather all the info we need to put the experiments in SharePoint, and to migrate to SharePoint:

SELECT p.proposal_id AS "Reference Number", 
       REPLACE(SUBSTRING(jsonb_extract_path_text(a.answer, 'value'), 3, 7), '"]', '') AS "Instrument", 
       p.proposer_id AS "Submitter ID",
       SUBSTRING(c.call_short_code, 5) AS "Round", p.created_at AS "Created Date", p.questionary_id,
       (SELECT REPLACE(SUBSTRING(jsonb_extract_path_text(a1.answer, 'value'), 3, 30), '"]', '')
               FROM answers a1
               WHERE a1.questionary_id = p.questionary_id
                     AND a1.question_id = 'selection_from_options_1634224600429') AS "Access Route", 
       p.title AS "Title",
       'LSF' AS "Facility Name"
       FROM proposals p
            JOIN call c ON c.call_id = p.call_id
            JOIN questionaries qy ON qy.questionary_id = p.questionary_id
            JOIN templates t ON t.template_id = qy.template_id
            JOIN answers a ON a.questionary_id = qy.questionary_id
      WHERE c.call_short_code = 'LSF 2022_2'
            AND a.question_id = 'selection_from_options_1634224102965';

I had a look into the two funding types but it seems that they are always null (Newton Funding) or marked as 0 (EU Funding) in sp_proposal_list_cache so I haven't included them.

EDIT

The following query should gather user information for the SP list:

SELECT user_number, display_name, account_email, org_name, dept_name, dept_country 
       FROM reporting_person 
       WHERE user_number in (...);

[simonfernandes]

It looks good to me and I can't see any problems with it when I ran it.

From looking at the SharePoint list, I think we may also need the proposal abstract/basis from this query. And will we also need the PI details from a separate Oracle query?

For the funding, it looks like we asked about Laserlab funding in the form and might need to extract it and add it to an additional list: http://www.facilities.rl.ac.uk/clf/programme/Lists/LaserlabEuLink/AllItems.aspx

[EdwardHaynes]

The final working query for the SharePoint data:

SELECT p.proposal_id AS "Reference Number", 
       REPLACE(SUBSTRING(jsonb_extract_path_text(a.answer, 'value'), 3, 7), '"]', '') AS "Instrument", 
       p.proposer_id AS "Submitter ID",
       SUBSTRING(c.call_short_code, 5) AS "Round", 
       CAST(p.created_at AS DATE) AS "Created Date", 
       p.questionary_id,
       (SELECT REPLACE(SUBSTRING(jsonb_extract_path_text(a1.answer, 'value'), 3, 30), '"]', '')
               FROM answers a1
               WHERE a1.questionary_id = p.questionary_id
                     AND a1.question_id = 'selection_from_options_1634224600429') AS "Access Route", 
       (SELECT SUBSTRING(jsonb_extract_path_text(a1.answer, 'value'), 28, 1)
               FROM answers a1
               WHERE a1.questionary_id = p.questionary_id
                     AND a1.question_id = 'number_input_1634224446028') AS "Requested Time (Weeks)",
       p.title AS "Title",
       p.abstract,
       'LSF' AS "Facility Name",
       (SELECT jsonb_extract_path_text(a1.answer, 'value')
               FROM answers a1
               WHERE a1.questionary_id = p.questionary_id
                     AND a1.question_id = 'text_input_1634226904574') AS "Technical Requirements",
       (SELECT REPLACE(SUBSTRING(jsonb_extract_path_text(a1.answer, 'value'), 3, 30), '"]', '')
               FROM answers a1
               WHERE a1.questionary_id = p.questionary_id
                     AND a1.question_id = 'selection_from_options_1634225453902') AS "Laser Lab Funding"
       FROM proposals p
            JOIN call c ON c.call_id = p.call_id
            JOIN questionaries qy ON qy.questionary_id = p.questionary_id
            JOIN templates t ON t.template_id = qy.template_id
            JOIN answers a ON a.questionary_id = qy.questionary_id
      WHERE c.call_short_code = 'LSF 2022_2'
            AND a.question_id = 'selection_from_options_1634224102965'
      ORDER BY p.proposer_id;

[EdwardHaynes]

All proposals are present in the LSF Proposals List in SharePoint and are picked up by sp_proposal_list_cache when given and allocated time and instrument value.

Leaving the issue open until the user office input the FAP results over the coming fortnight.

[TickleThePanda]

Closing as we've done all of the work we expect to on this.