search

UserOfficeProject / issue-tracker

Shared place for features and bugs from all collaborators.
0 stars 0 forks source link

As a user officer or instrument scientist, I need easy access to answers to all questions for each scientific technique #727

Closed simonfernandes closed 10 months ago

simonfernandes commented 1 year ago

User officers and instrument scientists need to be able to retrieve answers to all questions for each scientific technique themselves via the new proposal system.

At the moment, they can only view the list of proposals that give a specific answer to a question, and export those proposals as pdfs, which do not contain this info. This means that they rely on us to provide the data.

Possible solutions:

Which role does this affect.

User officer and instrument scientist

Why it needs to change.

User officers and instrument scientists need quick and easy access to this data.

Other helpful details like screenshots or email content from.

From: Gozzard, Emma (STFC,RAL,ISIS) emma.gozzard@stfc.ac.uk Sent: 25 October 2022 13:57

To: Facilities Business Systems & Support FacilitiesBusinessSystem@stfc.ac.uk; Singh, Sukhvinder (STFC,RAL,ISIS) Sukhvinder.Singh@stfc.ac.uk; Rushwood, Panda (STFC,RAL,ISIS) Thomas.Rushwood@stfc.ac.uk Cc: King, Philip (STFC,RAL,ISIS) philip.king@stfc.ac.uk Subject: ISIS Direct technical Q reporting

Hi all,

I have done some testing to see how easily the answers can be retrieved for the specific technique questions via the new proposal system. Unless I’m doing something very wrong, then I cannot export this information at all. I can only view the list of proposals that give a specific parameter to a question, and export those proposals as pdfs.

Am I doing something really wrong?! Hopefully!

If not, then we will need to find a way of extracting the answers to all questions for each technique, preferably as an excel doc, to give to relevant staff. We will need this very soon.

simonfernandes commented 1 year ago

Here are the Trino queries I've written to send technique question data to Emma, most recently for ISIS Direct 23/2. They should still be accurate if no questions have changed since.

As mentioned above, an easy option could be to add these to the Reporting Tool (probably under a separate drop-down!)

Small Angle Scattering

SELECT p.proposal_id AS "Reference number",
       c.call_short_code AS "Call",
       json_extract_scalar(a_inst.answer, '$.value[0]') AS "Instrument requested",
       json_extract_scalar(a_days_req.answer, '$.value.value') AS "Days requested",
       json_extract_scalar(a_technique.answer, '$.value[0]') AS "Technique",
       json_extract_scalar(a_lengthscale.answer, '$.value') AS "Lengthscale",
       json_extract_scalar(a_sesans.answer, '$.value[0]') AS "SESANS?",
       json_extract_scalar(a_measured.answer, '$.value[0]') AS "Already measured?",
       json_extract_scalar(a_isis_measure.answer, '$.value[0]') AS "Measure at ISIS?",
       json_extract_scalar(a_polarised.answer, '$.value[0]') AS "Polarised SANS?",
       pi.display_name AS "PI",
       p.title AS "Proposal title"
  FROM duo.public.proposals p

       JOIN duo.public.call c
       ON c.call_id = p.call_id

       JOIN duo.public.questionaries qy
       ON qy.questionary_id = p.questionary_id

       JOIN duo.public.questions q_days_req
       ON q_days_req.natural_key = 'days_requested'

       LEFT JOIN duo.public.answers a_days_req
       ON a_days_req.question_id = q_days_req.question_id
          AND a_days_req.questionary_id = qy.questionary_id

       JOIN duo.public.questions q_technique
       ON q_technique.natural_key = 'scientific_technique'

       JOIN duo.public.answers a_technique
       ON a_technique.question_id = q_technique.question_id
          AND a_technique.questionary_id = qy.questionary_id
              AND json_extract_scalar(a_technique.answer, '$.value[0]') = 'Small angle scattering '

       JOIN duo.public.questions q_inst
       ON q_inst.natural_key = 'isis_instrument'

       LEFT JOIN duo.public.answers a_inst
       ON a_inst.question_id = q_inst.question_id
          AND a_inst.questionary_id = qy.questionary_id

       LEFT JOIN duo.public.questions q_lengthscale
       ON q_lengthscale.natural_key = 'samepl_lengthscale_text_input'

       LEFT JOIN duo.public.answers a_lengthscale
       ON a_lengthscale.question_id = q_lengthscale.question_id
          AND a_lengthscale.questionary_id = qy.questionary_id

       LEFT JOIN duo.public.questions q_sesans
       ON q_sesans.natural_key = 'sesans_options'

       LEFT JOIN duo.public.answers a_sesans
       ON a_sesans.question_id = q_sesans.question_id
          AND a_sesans.questionary_id = qy.questionary_id

       LEFT JOIN duo.public.questions q_measured
       ON q_measured.natural_key = 'sans_measurement_yn'

       LEFT JOIN duo.public.answers a_measured
       ON a_measured.question_id = q_measured.question_id
          AND a_measured.questionary_id = qy.questionary_id

       LEFT JOIN duo.public.questions q_isis_measure
       ON q_isis_measure.natural_key = 'sans_measurement_required'

       LEFT JOIN duo.public.answers a_isis_measure
       ON a_isis_measure.question_id = q_isis_measure.question_id
          AND a_isis_measure.questionary_id = qy.questionary_id

       LEFT JOIN duo.public.questions q_polarised
       ON q_polarised.natural_key = 'polarised_sans_yn'

       LEFT JOIN duo.public.answers a_polarised
       ON a_polarised.question_id = q_polarised.question_id
          AND a_polarised.questionary_id = qy.questionary_id

       JOIN duo.public.proposal_table_view ptv
       ON ptv.proposal_pk = p.proposal_pk
          AND LOWER(proposal_status_name) LIKE '%submitted%'

       LEFT JOIN isisuserdb.isisuserdb.reporting_person pi
       ON pi.user_number = CAST(p.proposer_id AS VARCHAR)

WHERE c.call_short_code = 'ISIS Direct 2023_2'
ORDER BY p.proposal_id ASC

Reflectometry

SELECT p.proposal_id AS "Reference number",
       c.call_short_code AS "Call",
       json_extract_scalar(a_inst.answer, '$.value[0]') AS "Instrument requested",
       json_extract_scalar(a_days_req.answer, '$.value.value') AS "Days requested",
       json_extract_scalar(a_technique.answer, '$.value[0]') AS "Technique",
       json_extract(a_type.answer, '$.value') AS "Type",
       json_extract_scalar(a_area.answer, '$.value') AS "Surface area",
       json_extract_scalar(a_sub_thick.answer, '$.value') AS "Substrate thickness",
       json_extract_scalar(a_sub_mat.answer, '$.value') AS "Substrate material",
       json_extract(a_interfaces.answer, '$.value') AS "Interfaces",
       json_extract_scalar(a_sim_meas.answer, '$.value') AS "Simultaneous measurements",
       json_extract_scalar(a_kinetic.answer, '$.value') AS "Kinetic or off-specular",
       pi.display_name AS "PI",
       p.title AS "Proposal title"
  FROM duo.public.proposals p

       JOIN duo.public.call c
       ON c.call_id = p.call_id

       JOIN duo.public.questionaries qy
       ON qy.questionary_id = p.questionary_id

       JOIN duo.public.questions q_days_req
       ON q_days_req.natural_key = 'days_requested'

       LEFT JOIN duo.public.answers a_days_req
       ON a_days_req.question_id = q_days_req.question_id
          AND a_days_req.questionary_id = qy.questionary_id

       JOIN duo.public.questions q_technique
       ON q_technique.natural_key = 'scientific_technique'

       JOIN duo.public.answers a_technique
       ON a_technique.question_id = q_technique.question_id
          AND a_technique.questionary_id = qy.questionary_id
              AND json_extract_scalar(a_technique.answer, '$.value[0]') = 'Reflectometry '

       JOIN duo.public.questions q_inst
       ON q_inst.natural_key = 'isis_instrument'

       LEFT JOIN duo.public.answers a_inst
       ON a_inst.question_id = q_inst.question_id
          AND a_inst.questionary_id = qy.questionary_id

       LEFT JOIN duo.public.questions q_type
       ON q_type.natural_key = 'reflectometry_exp_type_mc'

       LEFT JOIN duo.public.answers a_type
       ON a_type.question_id = q_type.question_id
          AND a_type.questionary_id = qy.questionary_id

       LEFT JOIN duo.public.questions q_area
       ON q_area.natural_key = 'reflectometry_sample_surface_area_text'

       LEFT JOIN duo.public.answers a_area
       ON a_area.question_id = q_area.question_id
          AND a_area.questionary_id = qy.questionary_id

       LEFT JOIN duo.public.questions q_sub_thick
       ON q_sub_thick.natural_key = 'reflectometry_substrate_thickness_area_text'

       LEFT JOIN duo.public.answers a_sub_thick
       ON a_sub_thick.question_id = q_sub_thick.question_id
          AND a_sub_thick.questionary_id = qy.questionary_id

       LEFT JOIN duo.public.questions q_sub_mat
       ON q_sub_mat.natural_key = 'reflectometry_substrate_material_area_text'

       LEFT JOIN duo.public.answers a_sub_mat
       ON a_sub_mat.question_id = q_sub_mat.question_id
          AND a_sub_mat.questionary_id = qy.questionary_id

       LEFT JOIN duo.public.questions q_interfaces
       ON q_interfaces.natural_key = 'reflectometry_interface_mc'

       LEFT JOIN duo.public.answers a_interfaces
       ON a_interfaces.question_id = q_interfaces.question_id
          AND a_interfaces.questionary_id = qy.questionary_id

       LEFT JOIN duo.public.questions q_sim_meas
       ON q_sim_meas.natural_key = 'reflectometry_simultaneous_measurements_text'

       LEFT JOIN duo.public.answers a_sim_meas
       ON a_sim_meas.question_id = q_sim_meas.question_id
          AND a_sim_meas.questionary_id = qy.questionary_id

       LEFT JOIN duo.public.questions q_kinetic
       ON q_kinetic.natural_key = 'reflectometry_measurement_text'

       LEFT JOIN duo.public.answers a_kinetic
       ON a_kinetic.question_id = q_kinetic.question_id
          AND a_kinetic.questionary_id = qy.questionary_id

       JOIN duo.public.proposal_table_view ptv
       ON ptv.proposal_pk = p.proposal_pk
          AND LOWER(proposal_status_name) LIKE '%submitted%'

       LEFT JOIN isisuserdb.isisuserdb.reporting_person pi
       ON pi.user_number = CAST(p.proposer_id AS VARCHAR)

WHERE c.call_short_code = 'ISIS Direct 2023_2'
ORDER BY p.proposal_id ASC

Diffraction

SELECT p.proposal_id AS "Reference number",
       c.call_short_code AS "Call",
       json_extract_scalar(a_inst.answer, '$.value[0]') AS "Instrument requested",
       json_extract_scalar(a_days_req.answer, '$.value.value') AS "Days requested",
       json_extract_scalar(a_technique.answer, '$.value[0]') AS "Technique",
       json_extract_scalar(a_a.answer, '$.value.value') AS "a(Å)",
       json_extract_scalar(a_b.answer, '$.value.value') AS "b(Å)",
       json_extract_scalar(a_c.answer, '$.value.value') AS "c(Å)",
       json_extract_scalar(a_alpha.answer, '$.value.value') AS "alpha(o)",
       json_extract_scalar(a_beta.answer, '$.value.value') AS "beta(o)",
       json_extract_scalar(a_gamma.answer, '$.value.value') AS "gamma(o)",
       json_extract_scalar(a_volume.answer, '$.value.value') AS "volume(Å3)",
       pi.display_name AS "PI",
       p.title AS "Proposal title"
  FROM duo.public.proposals p

       JOIN duo.public.call c
       ON c.call_id = p.call_id

       JOIN duo.public.questionaries qy
       ON qy.questionary_id = p.questionary_id

       JOIN duo.public.questions q_days_req
       ON q_days_req.natural_key = 'days_requested'

       LEFT JOIN duo.public.answers a_days_req
       ON a_days_req.question_id = q_days_req.question_id
          AND a_days_req.questionary_id = qy.questionary_id

       JOIN duo.public.questions q_technique
       ON q_technique.natural_key = 'scientific_technique'

       JOIN duo.public.answers a_technique
       ON a_technique.question_id = q_technique.question_id
          AND a_technique.questionary_id = qy.questionary_id
              AND json_extract_scalar(a_technique.answer, '$.value[0]') = 'Powder / single crystal diffraction'

       JOIN duo.public.questions q_inst
       ON q_inst.natural_key = 'isis_instrument'

       LEFT JOIN duo.public.answers a_inst
       ON a_inst.question_id = q_inst.question_id
          AND a_inst.questionary_id = qy.questionary_id

       LEFT JOIN duo.public.questions q_a
       ON q_a.natural_key = 'Cell_Parameter_A'

       LEFT JOIN duo.public.answers a_a
       ON a_a.question_id = q_a.question_id
          AND a_a.questionary_id = qy.questionary_id

       LEFT JOIN duo.public.questions q_b
       ON q_b.natural_key = 'Cell_Parameter_B'

       LEFT JOIN duo.public.answers a_b
       ON a_b.question_id = q_b.question_id
          AND a_b.questionary_id = qy.questionary_id

       LEFT JOIN duo.public.questions q_c
       ON q_c.natural_key = 'Cell_Parameter_C'

       LEFT JOIN duo.public.answers a_c
       ON a_c.question_id = q_c.question_id
          AND a_c.questionary_id = qy.questionary_id

       LEFT JOIN duo.public.questions q_alpha
       ON q_alpha.natural_key = 'Cell_Parameter_Alpha'

       LEFT JOIN duo.public.answers a_alpha
       ON a_alpha.question_id = q_alpha.question_id
          AND a_alpha.questionary_id = qy.questionary_id

       LEFT JOIN duo.public.questions q_beta
       ON q_beta.natural_key = 'Cell_Parameter_Beta'

       LEFT JOIN duo.public.answers a_beta
       ON a_beta.question_id = q_beta.question_id
          AND a_beta.questionary_id = qy.questionary_id

       LEFT JOIN duo.public.questions q_gamma
       ON q_gamma.natural_key = 'Cell_Parameter_Gamma_'

       LEFT JOIN duo.public.answers a_gamma
       ON a_gamma.question_id = q_gamma.question_id
          AND a_gamma.questionary_id = qy.questionary_id

       LEFT JOIN duo.public.questions q_volume
       ON q_volume.natural_key = 'Cell_Parameter_Volume'

       LEFT JOIN duo.public.answers a_volume
       ON a_volume.question_id = q_volume.question_id
          AND a_volume.questionary_id = qy.questionary_id

       JOIN duo.public.proposal_table_view ptv
       ON ptv.proposal_pk = p.proposal_pk
          AND LOWER(proposal_status_name) LIKE '%submitted%'

       LEFT JOIN isisuserdb.isisuserdb.reporting_person pi
       ON pi.user_number = CAST(p.proposer_id AS VARCHAR)

WHERE c.call_short_code = 'ISIS Direct 2023_2'
ORDER BY p.proposal_id ASC

Engineering

SELECT p.proposal_id AS "Reference number",
       c.call_short_code AS "Call",
       json_extract_scalar(a_inst.answer, '$.value[0]') AS "Instrument requested",
       json_extract_scalar(a_days_req.answer, '$.value.value') AS "Days requested",
       json_extract_scalar(a_technique.answer, '$.value[0]') AS "Technique",
       json_extract(a_coll_imat.answer, '$.value') AS "IMAT collimator size",
       json_extract(a_coll_enginx.answer, '$.value') AS "Engin-X collimator size",
       pi.display_name AS "PI",
       p.title AS "Proposal title"
  FROM duo.public.proposals p

       JOIN duo.public.call c
       ON c.call_id = p.call_id

       JOIN duo.public.questionaries qy
       ON qy.questionary_id = p.questionary_id

       JOIN duo.public.questions q_days_req
       ON q_days_req.natural_key = 'days_requested'

       LEFT JOIN duo.public.answers a_days_req
       ON a_days_req.question_id = q_days_req.question_id
          AND a_days_req.questionary_id = qy.questionary_id

       JOIN duo.public.questions q_technique
       ON q_technique.natural_key = 'scientific_technique'

       JOIN duo.public.answers a_technique
       ON a_technique.question_id = q_technique.question_id
          AND a_technique.questionary_id = qy.questionary_id
              AND json_extract_scalar(a_technique.answer, '$.value[0]') = 'Engineering '

       JOIN duo.public.questions q_inst
       ON q_inst.natural_key = 'isis_instrument'

       LEFT JOIN duo.public.answers a_inst
       ON a_inst.question_id = q_inst.question_id
          AND a_inst.questionary_id = qy.questionary_id

       LEFT JOIN duo.public.questions q_coll_imat
       ON q_coll_imat.natural_key = 'engineering_imat_cillimator_size_mc'

       LEFT JOIN duo.public.answers a_coll_imat
       ON a_coll_imat.question_id = q_coll_imat.question_id
          AND a_coll_imat.questionary_id = qy.questionary_id

       LEFT JOIN duo.public.questions q_coll_enginx
       ON q_coll_enginx.natural_key = 'engineering_enginx_cillimator_size_mc'

       LEFT JOIN duo.public.answers a_coll_enginx
       ON a_coll_enginx.question_id = q_coll_enginx.question_id
          AND a_coll_enginx.questionary_id = qy.questionary_id

       JOIN duo.public.proposal_table_view ptv
       ON ptv.proposal_pk = p.proposal_pk
          AND LOWER(proposal_status_name) LIKE '%submitted%'

       LEFT JOIN isisuserdb.isisuserdb.reporting_person pi
       ON pi.user_number = CAST(p.proposer_id AS VARCHAR)

WHERE c.call_short_code = 'ISIS Direct 2023_2'
ORDER BY p.proposal_id ASC

Liquid (etc.) diffraction

SELECT p.proposal_id AS "Reference number",
       c.call_short_code AS "Call",
       json_extract_scalar(a_inst.answer, '$.value[0]') AS "Instrument requested",
       json_extract_scalar(a_days_req.answer, '$.value.value') AS "Days requested",
       json_extract_scalar(a_technique.answer, '$.value[0]') AS "Technique",
       json_extract_scalar(a_geom.answer, '$.value[0]') AS "Cell geometry",
       json_extract_scalar(a_qrange.answer, '$.value') AS "Q-range",
       json_extract_scalar(a_qres.answer, '$.value') AS "Q-res",
       json_extract_scalar(a_points.answer, '$.value') AS "No. samples & state points",
       pi.display_name AS "PI",
       p.title AS "Proposal title"
  FROM duo.public.proposals p

       JOIN duo.public.call c
       ON c.call_id = p.call_id

       JOIN duo.public.questionaries qy
       ON qy.questionary_id = p.questionary_id

       JOIN duo.public.questions q_days_req
       ON q_days_req.natural_key = 'days_requested'

       LEFT JOIN duo.public.answers a_days_req
       ON a_days_req.question_id = q_days_req.question_id
          AND a_days_req.questionary_id = qy.questionary_id

       JOIN duo.public.questions q_technique
       ON q_technique.natural_key = 'scientific_technique'

       JOIN duo.public.answers a_technique
       ON a_technique.question_id = q_technique.question_id
          AND a_technique.questionary_id = qy.questionary_id
              AND json_extract_scalar(a_technique.answer, '$.value[0]') = 'Liquid / amorphous / complex materials diffraction '

       JOIN duo.public.questions q_inst
       ON q_inst.natural_key = 'isis_instrument'

       LEFT JOIN duo.public.answers a_inst
       ON a_inst.question_id = q_inst.question_id
          AND a_inst.questionary_id = qy.questionary_id

       LEFT JOIN duo.public.questions q_geom
       ON q_geom.natural_key = 'diffraction_cell_geometry'

       LEFT JOIN duo.public.answers a_geom
       ON a_geom.question_id = q_geom.question_id
          AND a_geom.questionary_id = qy.questionary_id

       LEFT JOIN duo.public.questions q_material
       ON q_material.natural_key = 'diffraction_materials_mc'

       LEFT JOIN duo.public.answers a_material
       ON a_material.question_id = q_material.question_id
          AND a_material.questionary_id = qy.questionary_id

       LEFT JOIN duo.public.questions q_qrange
       ON q_qrange.natural_key = 'diffraction_q_range_text'

       LEFT JOIN duo.public.answers a_qrange
       ON a_qrange.question_id = q_qrange.question_id
          AND a_qrange.questionary_id = qy.questionary_id

       LEFT JOIN duo.public.questions q_qres
       ON q_qres.natural_key = 'diffraction_q_resolution_text'

       LEFT JOIN duo.public.answers a_qres
       ON a_qres.question_id = q_qres.question_id
          AND a_qres.questionary_id = qy.questionary_id

       LEFT JOIN duo.public.questions q_points
       ON q_points.natural_key = 'diffraction_samples_text'

       LEFT JOIN duo.public.answers a_points
       ON a_points.question_id = q_points.question_id
          AND a_points.questionary_id = qy.questionary_id

       JOIN duo.public.proposal_table_view ptv
       ON ptv.proposal_pk = p.proposal_pk
          AND LOWER(proposal_status_name) LIKE '%submitted%'

       LEFT JOIN isisuserdb.isisuserdb.reporting_person pi
       ON pi.user_number = CAST(p.proposer_id AS VARCHAR)

WHERE c.call_short_code = 'ISIS Direct 2023_2'
ORDER BY p.proposal_id ASC

Excitations

SELECT p.proposal_id AS "Reference number",
       c.call_short_code AS "Call",
       json_extract_scalar(a_inst.answer, '$.value[0]') AS "Instrument requested",
       json_extract_scalar(a_days_req.answer, '$.value.value') AS "Days requested",
       json_extract_scalar(a_technique.answer, '$.value[0]') AS "Technique",
       json_extract_scalar(a_type.answer, '$.value[0]') AS "Crystal/Powder?",
       json_extract_scalar(a_crysdetails.answer, '$.value') AS "No. cystals & details",
       json_extract_scalar(a_fullsamples.answer, '$.value[0]') AS "Already have full amount samples?",
       json_extract_scalar(a_origin.answer, '$.value') AS "Origin of samples",
       pi.display_name AS "PI",
       p.title AS "Proposal title"
  FROM duo.public.proposals p

       JOIN duo.public.call c
       ON c.call_id = p.call_id

       JOIN duo.public.questionaries qy
       ON qy.questionary_id = p.questionary_id

       JOIN duo.public.questions q_days_req
       ON q_days_req.natural_key = 'days_requested'

       LEFT JOIN duo.public.answers a_days_req
       ON a_days_req.question_id = q_days_req.question_id
          AND a_days_req.questionary_id = qy.questionary_id

       JOIN duo.public.questions q_technique
       ON q_technique.natural_key = 'scientific_technique'

       JOIN duo.public.answers a_technique
       ON a_technique.question_id = q_technique.question_id
          AND a_technique.questionary_id = qy.questionary_id
              AND json_extract_scalar(a_technique.answer, '$.value[0]') = 'Excitations '

       JOIN duo.public.questions q_inst
       ON q_inst.natural_key = 'isis_instrument'

       LEFT JOIN duo.public.answers a_inst
       ON a_inst.question_id = q_inst.question_id
          AND a_inst.questionary_id = qy.questionary_id

       LEFT JOIN duo.public.questions q_type
       ON q_type.natural_key = 'excitations_crystal_powder_mc'

       LEFT JOIN duo.public.answers a_type
       ON a_type.question_id = q_type.question_id
          AND a_type.questionary_id = qy.questionary_id

       LEFT JOIN duo.public.questions q_crysdetails
       ON q_crysdetails.natural_key = 'excitations_crystal_details_text'

       LEFT JOIN duo.public.answers a_crysdetails
       ON a_crysdetails.question_id = q_crysdetails.question_id
          AND a_crysdetails.questionary_id = qy.questionary_id

       LEFT JOIN duo.public.questions q_fullsamples
       ON q_fullsamples.natural_key = 'excitations_sample_quantity_text'

       LEFT JOIN duo.public.answers a_fullsamples
       ON a_fullsamples.question_id = q_fullsamples.question_id
          AND a_fullsamples.questionary_id = qy.questionary_id

       LEFT JOIN duo.public.questions q_origin
       ON q_origin.natural_key = 'excitations_sample_origin_text'

       LEFT JOIN duo.public.answers a_origin
       ON a_origin.question_id = q_origin.question_id
          AND a_origin.questionary_id = qy.questionary_id

       JOIN duo.public.proposal_table_view ptv
       ON ptv.proposal_pk = p.proposal_pk
          AND LOWER(proposal_status_name) LIKE '%submitted%'

       LEFT JOIN isisuserdb.isisuserdb.reporting_person pi
       ON pi.user_number = CAST(p.proposer_id AS VARCHAR)

WHERE c.call_short_code = 'ISIS Direct 2023_2'
ORDER BY p.proposal_id ASC

Vibrational spectoscropy

SELECT p.proposal_id AS "Reference number",
       c.call_short_code AS "Call",
       json_extract_scalar(a_inst.answer, '$.value[0]') AS "Instrument requested",
       json_extract_scalar(a_days_req.answer, '$.value.value') AS "Days requested",
       json_extract_scalar(a_technique.answer, '$.value[0]') AS "Technique",
       json_extract_scalar(a_erange.answer, '$.value[0]') AS "Energy range",
       pi.display_name AS "PI",
       p.title AS "Proposal title"
  FROM duo.public.proposals p

       JOIN duo.public.call c
       ON c.call_id = p.call_id

       JOIN duo.public.questionaries qy
       ON qy.questionary_id = p.questionary_id

       JOIN duo.public.questions q_days_req
       ON q_days_req.natural_key = 'days_requested'

       LEFT JOIN duo.public.answers a_days_req
       ON a_days_req.question_id = q_days_req.question_id
          AND a_days_req.questionary_id = qy.questionary_id

       JOIN duo.public.questions q_technique
       ON q_technique.natural_key = 'scientific_technique'

       JOIN duo.public.answers a_technique
       ON a_technique.question_id = q_technique.question_id
          AND a_technique.questionary_id = qy.questionary_id
              AND json_extract_scalar(a_technique.answer, '$.value[0]') = 'Vibrational spectroscopy'

       JOIN duo.public.questions q_inst
       ON q_inst.natural_key = 'isis_instrument'

       LEFT JOIN duo.public.answers a_inst
       ON a_inst.question_id = q_inst.question_id
          AND a_inst.questionary_id = qy.questionary_id

       LEFT JOIN duo.public.questions q_erange
       ON q_erange.natural_key = 'spectroscopy_energy_range_mc'

       LEFT JOIN duo.public.answers a_erange
       ON a_erange.question_id = q_erange.question_id
          AND a_erange.questionary_id = qy.questionary_id

       JOIN duo.public.proposal_table_view ptv
       ON ptv.proposal_pk = p.proposal_pk
          AND LOWER(proposal_status_name) LIKE '%submitted%'

       LEFT JOIN isisuserdb.isisuserdb.reporting_person pi
       ON pi.user_number = CAST(p.proposer_id AS VARCHAR)

WHERE c.call_short_code = 'ISIS Direct 2023_2'
ORDER BY p.proposal_id ASC

Quasi-elastic scattering

SELECT p.proposal_id AS "Reference number",
       c.call_short_code AS "Call",
       json_extract_scalar(a_inst.answer, '$.value[0]') AS "Instrument requested",
       json_extract_scalar(a_days_req.answer, '$.value.value') AS "Days requested",
       json_extract_scalar(a_technique.answer, '$.value[0]') AS "Technique",
       json_extract_scalar(a_weak.answer, '$.value[0]') AS "Weak scatterer?",
       json_extract_scalar(a_multiple.answer, '$.value[0]') AS "Multiple energy ranges essential?",
       json_extract_scalar(a_polarised.answer, '$.value[0]') AS "Polarised neutrons?",
       json_extract_scalar(a_eres.answer, '$.value') AS "Energy resolution",
       json_extract_scalar(a_erange.answer, '$.value') AS "Energy range",
       json_extract_scalar(a_qrange.answer, '$.value') AS "Q-range",
       json_extract_scalar(a_magnet.answer, '$.value[0]') AS "Magnet required",
       pi.display_name AS "PI",
       p.title AS "Proposal title"
  FROM duo.public.proposals p

       JOIN duo.public.call c
       ON c.call_id = p.call_id

       JOIN duo.public.questionaries qy
       ON qy.questionary_id = p.questionary_id

       JOIN duo.public.questions q_days_req
       ON q_days_req.natural_key = 'days_requested'

       LEFT JOIN duo.public.answers a_days_req
       ON a_days_req.question_id = q_days_req.question_id
          AND a_days_req.questionary_id = qy.questionary_id

       JOIN duo.public.questions q_technique
       ON q_technique.natural_key = 'scientific_technique'

       JOIN duo.public.answers a_technique
       ON a_technique.question_id = q_technique.question_id
          AND a_technique.questionary_id = qy.questionary_id
              AND json_extract_scalar(a_technique.answer, '$.value[0]') = 'Quasi-elastic scattering'

       JOIN duo.public.questions q_inst
       ON q_inst.natural_key = 'isis_instrument'

       LEFT JOIN duo.public.answers a_inst
       ON a_inst.question_id = q_inst.question_id
          AND a_inst.questionary_id = qy.questionary_id

       LEFT JOIN duo.public.questions q_weak
       ON q_weak.natural_key = 'qe_scattering_sample_mc'

       LEFT JOIN duo.public.answers a_weak
       ON a_weak.question_id = q_weak.question_id
          AND a_weak.questionary_id = qy.questionary_id

       LEFT JOIN duo.public.questions q_multiple
       ON q_multiple.natural_key = 'qe_scattering_energy_ranges_mc'

       LEFT JOIN duo.public.answers a_multiple
       ON a_multiple.question_id = q_multiple.question_id
          AND a_multiple.questionary_id = qy.questionary_id

       LEFT JOIN duo.public.questions q_polarised
       ON q_polarised.natural_key = 'qe_scattering_polarised_neutrons_mc'

       LEFT JOIN duo.public.answers a_polarised
       ON a_polarised.question_id = q_polarised.question_id
          AND a_polarised.questionary_id = qy.questionary_id

       LEFT JOIN duo.public.questions q_eres
       ON q_eres.natural_key = 'qe_scattering_energy_resolution_text'

       LEFT JOIN duo.public.answers a_eres
       ON a_eres.question_id = q_eres.question_id
          AND a_eres.questionary_id = qy.questionary_id

       LEFT JOIN duo.public.questions q_erange
       ON q_erange.natural_key = 'qe_scattering_energy_range_text'

       LEFT JOIN duo.public.answers a_erange
       ON a_erange.question_id = q_erange.question_id
          AND a_erange.questionary_id = qy.questionary_id

       LEFT JOIN duo.public.questions q_qrange
       ON q_qrange.natural_key = 'qe_scattering_q_range_text'

       LEFT JOIN duo.public.answers a_qrange
       ON a_qrange.question_id = q_qrange.question_id
          AND a_qrange.questionary_id = qy.questionary_id

       LEFT JOIN duo.public.questions q_magnet
       ON q_magnet.natural_key = 'qe_scattering_magnet_mc'

       LEFT JOIN duo.public.answers a_magnet
       ON a_magnet.question_id = q_magnet.question_id
          AND a_magnet.questionary_id = qy.questionary_id

       JOIN duo.public.proposal_table_view ptv
       ON ptv.proposal_pk = p.proposal_pk
          AND LOWER(proposal_status_name) LIKE '%submitted%'

       LEFT JOIN isisuserdb.isisuserdb.reporting_person pi
       ON pi.user_number = CAST(p.proposer_id AS VARCHAR)

WHERE c.call_short_code = 'ISIS Direct 2023_2'
ORDER BY p.proposal_id ASC

Imaging

SELECT p.proposal_id AS "Reference number",
       c.call_short_code AS "Call",
       json_extract_scalar(a_inst.answer, '$.value[0]') AS "Instrument requested",
       json_extract_scalar(a_days_req.answer, '$.value.value') AS "Days requested",
       json_extract_scalar(a_technique.answer, '$.value[0]') AS "Technique",
       json_extract(a_res.answer, '$.value') AS "Resolution required",
       pi.display_name AS "PI",
       p.title AS "Proposal title"
  FROM duo.public.proposals p

       JOIN duo.public.call c
       ON c.call_id = p.call_id

       JOIN duo.public.questionaries qy
       ON qy.questionary_id = p.questionary_id

       JOIN duo.public.questions q_days_req
       ON q_days_req.natural_key = 'days_requested'

       LEFT JOIN duo.public.answers a_days_req
       ON a_days_req.question_id = q_days_req.question_id
          AND a_days_req.questionary_id = qy.questionary_id

       JOIN duo.public.questions q_technique
       ON q_technique.natural_key = 'scientific_technique'

       JOIN duo.public.answers a_technique
       ON a_technique.question_id = q_technique.question_id
          AND a_technique.questionary_id = qy.questionary_id
              AND json_extract_scalar(a_technique.answer, '$.value[0]') = 'Imaging '

       JOIN duo.public.questions q_inst
       ON q_inst.natural_key = 'isis_instrument'

       LEFT JOIN duo.public.answers a_inst
       ON a_inst.question_id = q_inst.question_id
          AND a_inst.questionary_id = qy.questionary_id

       LEFT JOIN duo.public.questions q_res
       ON q_res.natural_key = 'imaging_resolution_text'

       LEFT JOIN duo.public.answers a_res
       ON a_res.question_id = q_res.question_id
          AND a_res.questionary_id = qy.questionary_id

       JOIN duo.public.proposal_table_view ptv
       ON ptv.proposal_pk = p.proposal_pk
          AND LOWER(proposal_status_name) LIKE '%submitted%'

       LEFT JOIN isisuserdb.isisuserdb.reporting_person pi
       ON pi.user_number = CAST(p.proposer_id AS VARCHAR)

WHERE c.call_short_code = 'ISIS Direct 2023_2'
ORDER BY p.proposal_id ASC

Deep inelastic scattering

SELECT p.proposal_id AS "Reference number",
       c.call_short_code AS "Call",
       json_extract_scalar(a_inst.answer, '$.value[0]') AS "Instrument requested",
       json_extract_scalar(a_days_req.answer, '$.value.value') AS "Days requested",
       json_extract_scalar(a_technique.answer, '$.value[0]') AS "Technique",
       json_extract(a_metals.answer, '$.value[0]') AS "Contains metals?",
       json_extract(a_weak.answer, '$.value[0]') AS "Weak scatterer?",
       pi.display_name AS "PI",
       p.title AS "Proposal title"
  FROM duo.public.proposals p

       JOIN duo.public.call c
       ON c.call_id = p.call_id

       JOIN duo.public.questionaries qy
       ON qy.questionary_id = p.questionary_id

       JOIN duo.public.questions q_days_req
       ON q_days_req.natural_key = 'days_requested'

       LEFT JOIN duo.public.answers a_days_req
       ON a_days_req.question_id = q_days_req.question_id
          AND a_days_req.questionary_id = qy.questionary_id

       JOIN duo.public.questions q_technique
       ON q_technique.natural_key = 'scientific_technique'

       JOIN duo.public.answers a_technique
       ON a_technique.question_id = q_technique.question_id
          AND a_technique.questionary_id = qy.questionary_id
              AND json_extract_scalar(a_technique.answer, '$.value[0]') = 'Deep inelastic scattering, transmission, prompt-gamma activation analysis or neutron-resonance capture analysis'

       JOIN duo.public.questions q_inst
       ON q_inst.natural_key = 'isis_instrument'

       LEFT JOIN duo.public.answers a_inst
       ON a_inst.question_id = q_inst.question_id
          AND a_inst.questionary_id = qy.questionary_id

       LEFT JOIN duo.public.questions q_metals
       ON q_metals.natural_key = 'deep_inelastic_metal_yn'

       LEFT JOIN duo.public.answers a_metals
       ON a_metals.question_id = q_metals.question_id
          AND a_metals.questionary_id = qy.questionary_id

       LEFT JOIN duo.public.questions q_weak
       ON q_weak.natural_key = 'deep_inelastic_weak_scat'

       LEFT JOIN duo.public.answers a_weak
       ON a_weak.question_id = q_weak.question_id
          AND a_weak.questionary_id = qy.questionary_id

       JOIN duo.public.proposal_table_view ptv
       ON ptv.proposal_pk = p.proposal_pk
          AND LOWER(proposal_status_name) LIKE '%submitted%'

       LEFT JOIN isisuserdb.isisuserdb.reporting_person pi
       ON pi.user_number = CAST(p.proposer_id AS VARCHAR)

WHERE c.call_short_code = 'ISIS Direct 2023_2'
ORDER BY p.proposal_id ASC

Electronics irradiation

SELECT p.proposal_id AS "Reference number",
       c.call_short_code AS "Call",
       json_extract_scalar(a_inst.answer, '$.value[0]') AS "Instrument requested",
       json_extract_scalar(a_days_req.answer, '$.value.value') AS "Days requested",
       json_extract_scalar(a_technique.answer, '$.value[0]') AS "Technique",
       json_extract_scalar(a_irrad.answer, '$.value[0]') AS "Irradiation type",
       json_extract_scalar(a_neutron.answer, '$.value[0]') AS "Neutron option",
       pi.display_name AS "PI",
       p.title AS "Proposal title"
  FROM duo.public.proposals p

       JOIN duo.public.call c
       ON c.call_id = p.call_id

       JOIN duo.public.questionaries qy
       ON qy.questionary_id = p.questionary_id

       JOIN duo.public.questions q_days_req
       ON q_days_req.natural_key = 'days_requested'

       LEFT JOIN duo.public.answers a_days_req
       ON a_days_req.question_id = q_days_req.question_id
          AND a_days_req.questionary_id = qy.questionary_id

       JOIN duo.public.questions q_technique
       ON q_technique.natural_key = 'scientific_technique'

       JOIN duo.public.answers a_technique
       ON a_technique.question_id = q_technique.question_id
          AND a_technique.questionary_id = qy.questionary_id
              AND json_extract_scalar(a_technique.answer, '$.value[0]') = 'Electronics irradiation '

       JOIN duo.public.questions q_inst
       ON q_inst.natural_key = 'isis_instrument'

       LEFT JOIN duo.public.answers a_inst
       ON a_inst.question_id = q_inst.question_id
          AND a_inst.questionary_id = qy.questionary_id

       LEFT JOIN duo.public.questions q_irrad
       ON q_irrad.natural_key = 'electronics_irradiation_mc'

       LEFT JOIN duo.public.answers a_irrad
       ON a_irrad.question_id = q_irrad.question_id
          AND a_irrad.questionary_id = qy.questionary_id

       LEFT JOIN duo.public.questions q_neutron
       ON q_neutron.natural_key = 'electronics_irradiation_neutrons_mc'

       LEFT JOIN duo.public.answers a_neutron
       ON a_neutron.question_id = q_neutron.question_id
          AND a_neutron.questionary_id = qy.questionary_id

       JOIN duo.public.proposal_table_view ptv
       ON ptv.proposal_pk = p.proposal_pk
          AND LOWER(proposal_status_name) LIKE '%submitted%'

       LEFT JOIN isisuserdb.isisuserdb.reporting_person pi
       ON pi.user_number = CAST(p.proposer_id AS VARCHAR)

WHERE c.call_short_code = 'ISIS Direct 2023_2'
ORDER BY p.proposal_id ASC

Muons

SELECT p.proposal_id AS "Reference number",
       c.call_short_code AS "Call",
       json_extract_scalar(a_inst.answer, '$.value[0]') AS "Instrument requested",
       json_extract_scalar(a_days_req.answer, '$.value.value') AS "Days requested",
       json_extract_scalar(a_technique.answer, '$.value[0]') AS "Technique",
       json_extract(a_capab.answer, '$.value') AS "Capabilities required",
       json_extract_scalar(a_other.answer, '$.value') AS "Other capabilities",
       json_extract_scalar(a_ptype.answer, '$.value[0]') AS "Pulse beam type (RIKEN)",
       pi.display_name AS "PI",
       p.title AS "Proposal title"
  FROM duo.public.proposals p

       JOIN duo.public.call c
       ON c.call_id = p.call_id

       JOIN duo.public.questionaries qy
       ON qy.questionary_id = p.questionary_id

       JOIN duo.public.questions q_days_req
       ON q_days_req.natural_key = 'days_requested'

       LEFT JOIN duo.public.answers a_days_req
       ON a_days_req.question_id = q_days_req.question_id
          AND a_days_req.questionary_id = qy.questionary_id

       JOIN duo.public.questions q_technique
       ON q_technique.natural_key = 'scientific_technique'

       JOIN duo.public.answers a_technique
       ON a_technique.question_id = q_technique.question_id
          AND a_technique.questionary_id = qy.questionary_id
              AND json_extract_scalar(a_technique.answer, '$.value[0]') = 'Muon'

       JOIN duo.public.questions q_inst
       ON q_inst.natural_key = 'isis_instrument'

       LEFT JOIN duo.public.answers a_inst
       ON a_inst.question_id = q_inst.question_id
          AND a_inst.questionary_id = qy.questionary_id

       LEFT JOIN duo.public.questions q_capab
       ON q_capab.natural_key = 'muons_capabilities_mc'

       LEFT JOIN duo.public.answers a_capab
       ON a_capab.question_id = q_capab.question_id
          AND a_capab.questionary_id = qy.questionary_id

       LEFT JOIN duo.public.questions q_other
       ON q_other.natural_key = 'muons_capabilities_other_text'

       LEFT JOIN duo.public.answers a_other
       ON a_other.question_id = q_other.question_id
          AND a_other.questionary_id = qy.questionary_id

       LEFT JOIN duo.public.questions q_ptype
       ON q_ptype.natural_key = 'muons_single_double_beam_mc'

       LEFT JOIN duo.public.answers a_ptype
       ON a_ptype.question_id = q_ptype.question_id
          AND a_ptype.questionary_id = qy.questionary_id

       JOIN duo.public.proposal_table_view ptv
       ON ptv.proposal_pk = p.proposal_pk
          AND LOWER(proposal_status_name) LIKE '%submitted%'

       LEFT JOIN isisuserdb.isisuserdb.reporting_person pi
       ON pi.user_number = CAST(p.proposer_id AS VARCHAR)

WHERE c.call_short_code = 'ISIS Direct 2023_2'
ORDER BY p.proposal_id ASC
bashanlam commented 1 year ago

@Cat-Lucifer please find our suggestion of adding the above queries to the Reporting Tool so as to extract the answers to all questions for each technique. This is the best cost-effective solution we have at the moment indeed. Love to know your thought.

Cat-Lucifer commented 1 year ago

@Cat-Lucifer please find our suggestion of adding the above queries to the Reporting Tool so as to extract the answers to all questions for each technique. This is the best cost-effective solution we have at the moment indeed. Love to know your thought.

@bashanlam @simonfernandes A reporting tool query sounds good to me. Thanks!

bashanlam commented 1 year ago

@Cat-Lucifer The attached are screen capture of the query of answers to questions for scientific technique. The input of the query is the call short code. Love to hear your thoughts. image image

Cat-Lucifer commented 1 year ago

@bashanlam This looks good, thanks! It would be good to add in some guidance and/or examples of how to enter the short code, as most instrument scientists won't know these as well as we do (or know them at all!). Ideally, if it's relatively easy, we would query on year and round.

bashanlam commented 12 months ago

@Cat-Lucifer The query page is updated as below. Please let me know allow user access group and your thoughts. image

Cat-Lucifer commented 12 months ago

@bashanlam This looks perfect - thanks! Everybody who is listed in the following email distribution lists can be permitted to use this query:

isisinstsci@stfc.ac.uk isisinstrumentscientist@stfc.ac.uk

simonfernandes commented 10 months ago

Closed via https://github.com/isisbusapps/reporting-tool/pull/716