Open joaoluis-pdm opened 3 years ago
WARNING: There are some features (such as the patient-ssapp searching for a site by travel distance, or answering to a pre-screener for a specific single trial) that work better (easily) when there is only one site per-trial. So we might re-consider if this refactoring of having multiple sites per trial is worthwhile (or better to have a backoffice feature to copy trials from one site to another).
Proceeding with implementation, as it is a feature highly requested on most business meetings.
After previous commit, on patient-ssapp, all maps display each clinical site as a marker.
Opinion for fellow developers: On patient-ssapp, the list of results for a MatchRequest (and also "Browse Trials") only display one site for each clinical trials. (Each entry in the list is a ClinicalTrial, not a ClinicalSite).
Do you think than, when the patient supplies the location, the clinical site displayed should be replaced with the nearest one ?
@pccosta-pdm can you please review the commit above
https://github.com/PharmaLedger-IMI/ctr-workspace/commit/1d520758e6745f7ea6850eb2418a142fcfba2e31
This commit changes the "CONTACT CLINICAL SITE" button so that:
1 - it asks for the patient to select a clinical site from the list of available clinical sites (for this trial) (Currently it does not displays the travel distance, as the location is not available here).
The existing behaviour of allowing only one contact for trial is kept.
This solution does not scale well to:
Opinions ?
PS: It is my own opinion that the existing "CONTACT CLINICAL SITE" button should an in-page anchor link to point to the list of sites. Under the address of each site, there should be a "CONTACT THIS CLINICAL SITE" button (which eliminated the need for the user to select clinical site again, but does not solve the problem of editing the phone and email data).
@joaoluis-pdm to add other options to the discussion:
Thank you @pccosta-pdm . I implemented suggestion of option 2. (Option 1 will still require significant work). My plan now is: 1 - to make all "CONTACT CLINICAL SITE" buttons work like this. 2 - squash the commits on this branch to a single one 3 - merge to master and release v0.10.0 (including @lehialessandro 's recent fixes) 4 - (eventually) fix the primary site on clinicalTrial.clinicalSite to be replaced by the one nearest to the patient,
Problem with queries:
SELECT "clinicaltrial"."id" AS "clinicaltrial_id", "clinicaltrial"."name" AS "clinicaltrial_name", "clinicaltrial"."description" AS "clinicaltrial_description", "clinicaltrial"."keyssi" AS "clinicaltrial_keyssi", "clinicaltrial"."dsudata" AS "clinicaltrial_dsudata", "clinicaltrial"."nctnumber" AS "clinicaltrial_nctnumber", "clinicaltrial"."purpose" AS "clinicaltrial_purpose", "clinicaltrial"."phase" AS "clinicaltrial_phase", "clinicaltrial"."timecommitment" AS "clinicaltrial_timecommitment", "clinicaltrial"."physicalcommitment" AS "clinicaltrial_physicalcommitment", "clinicaltrial"."travelstipends" AS "clinicaltrial_travelstipends", "clinicaltrial"."eligibilitycriteria" AS "clinicaltrial_eligibilitycriteria", "clinicaltrial"."status" AS "clinicaltrial_status", "clinicaltrial"."sponsor" AS "clinicaltrial_sponsor", "clinicaltrial"."clinicalsite" AS "clinicaltrial_clinicalsite", "clinicaltrialstatus"."code" AS "clinicaltrialstatus_code", "clinicaltrialstatus"."description" AS "clinicaltrialstatus_description", "clinicaltrialmedicalcondition"."id" AS "clinicaltrialmedicalcondition_id", "clinicaltrialmedicalcondition"."ordering" AS "clinicaltrialmedicalcondition_ordering", "clinicaltrialmedicalcondition"."clinicaltrial" AS "clinicaltrialmedicalcondition_clinicaltrial", "clinicaltrialmedicalcondition"."medicalcondition" AS "clinicaltrialmedicalcondition_medicalcondition", "medicalcondition"."code" AS "medicalcondition_code", "medicalcondition"."name" AS "medicalcondition_name", "clinicalsite"."id" AS "clinicalsite_id", "clinicalsite"."name" AS "clinicalsite_name", "clinicalsite"."address" AS "clinicalsite_address", "clinicalsitearray"."id" AS "clinicalsitearray_id", "clinicalsitearray"."name" AS "clinicalsitearray_name", "clinicalsitearray"."address" AS "clinicalsitearray_address", "csaddress"."id" AS "csaddress_id", "csaddress"."street" AS "csaddress_street", "csaddress"."country" AS "csaddress_country", "csaddress"."location" AS "csaddress_location", "cscountry"."code" AS "cscountry_code", "cscountry"."name" AS "cscountry_name", "cslocation"."id" AS "cslocation_id", "cslocation"."description" AS "cslocation_description", "cslocation"."latitude" AS "cslocation_latitude", "cslocation"."longitude" AS "cslocation_longitude", "cslocation"."center" AS "cslocation_center", "sponsor"."id" AS "sponsor_id", "sponsor"."name" AS "sponsor_name", "sponsor"."logo" AS "sponsor_logo", "address"."id" AS "address_id", "address"."street" AS "address_street", "address"."country" AS "address_country", "address"."location" AS "address_location", "country"."code" AS "country_code", "country"."name" AS "country_name", "location"."id" AS "location_id", "location"."description" AS "location_description", "location"."latitude" AS "location_latitude", "location"."longitude" AS "location_longitude", "location"."center" AS "location_center", point("cslocation"."latitude", "cslocation"."longitude") <@> point(38.7450065, -9.1352708) AS "cstravdistmiles" FROM "clinicaltrial" "clinicaltrial" INNER JOIN "clinicaltrialstatus" "clinicaltrialstatus" ON "clinicaltrialstatus"."code"="clinicaltrial"."status" INNER JOIN "clinicaltrialmedicalcondition" "clinicaltrialmedicalcondition" ON "clinicaltrialmedicalcondition"."clinicaltrial"="clinicaltrial"."id" INNER JOIN "medicalcondition" "medicalcondition" ON "medicalcondition"."code"="clinicaltrialmedicalcondition"."medicalcondition" INNER JOIN "clinicalsite" "clinicalsite" ON "clinicalsite"."id"="clinicaltrial"."clinicalsite" INNER JOIN "clinicaltrialclinicalsite" "clinicaltrial_clinicalsitearray" ON "clinicaltrial_clinicalsitearray"."clinicaltrial"="clinicaltrial"."id" INNER JOIN "clinicalsite" "clinicalsitearray" ON "clinicalsitearray"."id"="clinicaltrial_clinicalsitearray"."clinicalsite" INNER JOIN "address" "csaddress" ON "csaddress"."id"="clinicalsitearray"."address" INNER JOIN "country" "cscountry" ON "cscountry"."code"="csaddress"."country" INNER JOIN "location" "cslocation" ON "cslocation"."id"="csaddress"."location" INNER JOIN "sponsor" "sponsor" ON "sponsor"."id"="clinicaltrial"."sponsor" INNER JOIN "address" "address" ON "address"."id"="clinicalsite"."address" INNER JOIN "country" "country" ON "country"."code"="address"."country" INNER JOIN "location" "location" ON "location"."id"="address"."location" WHERE "clinicaltrialstatus"."code" IN ('REC') AND "medicalcondition"."code" IN ('101000') ORDER BY cstravdistmiles ASC, "clinicalsite"."id" DESC
query: SELECT COUNT(DISTINCT("clinicaltrial"."id")) AS "cnt" FROM "clinicaltrial" "clinicaltrial" INNER JOIN "clinicaltrialstatus" "clinicaltrialstatus" ON "clinicaltrialstatus"."code"="clinicaltrial"."status" INNER JOIN "clinicaltrialmedicalcondition" "clinicaltrialmedicalcondition" ON "clinicaltrialmedicalcondition"."clinicaltrial"="clinicaltrial"."id" INNER JOIN "medicalcondition" "medicalcondition" ON "medicalcondition"."code"="clinicaltrialmedicalcondition"."medicalcondition" INNER JOIN "clinicalsite" "clinicalsite" ON "clinicalsite"."id"="clinicaltrial"."clinicalsite" INNER JOIN "clinicaltrialclinicalsite" "clinicaltrial_clinicalsitearray" ON "clinicaltrial_clinicalsitearray"."clinicaltrial"="clinicaltrial"."id" INNER JOIN "clinicalsite" "clinicalsitearray" ON "clinicalsitearray"."id"="clinicaltrial_clinicalsitearray"."clinicalsite" INNER JOIN "address" "csaddress" ON "csaddress"."id"="clinicalsitearray"."address" INNER JOIN "country" "cscountry" ON "cscountry"."code"="csaddress"."country" INNER JOIN "location" "cslocation" ON "cslocation"."id"="csaddress"."location" INNER JOIN "sponsor" "sponsor" ON "sponsor"."id"="clinicaltrial"."sponsor" INNER JOIN "address" "address" ON "address"."id"="clinicalsite"."address" INNER JOIN "country" "country" ON "country"."code"="address"."country" INNER JOIN "location" "location" ON "location"."id"="address"."location" WHERE "clinicaltrialstatus"."code" IN ('REC') AND "medicalcondition"."code" IN ('101000')
9
query: SELECT DISTINCT "distinctAlias"."clinicaltrial_id" as "ids_clinicaltrial_id", "distinctAlias".cstravdistmiles, "distinctAlias"."clinicalsite_id" FROM (SELECT "clinicaltrial"."id" AS "clinicaltrial_id", "clinicaltrial"."name" AS "clinicaltrial_name", "clinicaltrial"."description" AS "clinicaltrial_description", "clinicaltrial"."keyssi" AS "clinicaltrial_keyssi", "clinicaltrial"."dsudata" AS "clinicaltrial_dsudata", "clinicaltrial"."nctnumber" AS "clinicaltrial_nctnumber", "clinicaltrial"."purpose" AS "clinicaltrial_purpose", "clinicaltrial"."phase" AS "clinicaltrial_phase", "clinicaltrial"."timecommitment" AS "clinicaltrial_timecommitment", "clinicaltrial"."physicalcommitment" AS "clinicaltrial_physicalcommitment", "clinicaltrial"."travelstipends" AS "clinicaltrial_travelstipends", "clinicaltrial"."eligibilitycriteria" AS "clinicaltrial_eligibilitycriteria", "clinicaltrial"."status" AS "clinicaltrial_status", "clinicaltrial"."sponsor" AS "clinicaltrial_sponsor", "clinicaltrial"."clinicalsite" AS "clinicaltrial_clinicalsite", "clinicaltrialstatus"."code" AS "clinicaltrialstatus_code", "clinicaltrialstatus"."description" AS "clinicaltrialstatus_description", "clinicaltrialmedicalcondition"."id" AS "clinicaltrialmedicalcondition_id", "clinicaltrialmedicalcondition"."ordering" AS "clinicaltrialmedicalcondition_ordering", "clinicaltrialmedicalcondition"."clinicaltrial" AS "clinicaltrialmedicalcondition_clinicaltrial", "clinicaltrialmedicalcondition"."medicalcondition" AS "clinicaltrialmedicalcondition_medicalcondition", "medicalcondition"."code" AS "medicalcondition_code", "medicalcondition"."name" AS "medicalcondition_name", "clinicalsite"."id" AS "clinicalsite_id", "clinicalsite"."name" AS "clinicalsite_name", "clinicalsite"."address" AS "clinicalsite_address", "clinicalsitearray"."id" AS "clinicalsitearray_id", "clinicalsitearray"."name" AS "clinicalsitearray_name", "clinicalsitearray"."address" AS "clinicalsitearray_address", "csaddress"."id" AS "csaddress_id", "csaddress"."street" AS "csaddress_street", "csaddress"."country" AS "csaddress_country", "csaddress"."location" AS "csaddress_location", "cscountry"."code" AS "cscountry_code", "cscountry"."name" AS "cscountry_name", "cslocation"."id" AS "cslocation_id", "cslocation"."description" AS "cslocation_description", "cslocation"."latitude" AS "cslocation_latitude", "cslocation"."longitude" AS "cslocation_longitude", "cslocation"."center" AS "cslocation_center", "sponsor"."id" AS "sponsor_id", "sponsor"."name" AS "sponsor_name", "sponsor"."logo" AS "sponsor_logo", "address"."id" AS "address_id", "address"."street" AS "address_street", "address"."country" AS "address_country", "address"."location" AS "address_location", "country"."code" AS "country_code", "country"."name" AS "country_name", "location"."id" AS "location_id", "location"."description" AS "location_description", "location"."latitude" AS "location_latitude", "location"."longitude" AS "location_longitude", "location"."center" AS "location_center", point("cslocation"."latitude", "cslocation"."longitude") <@> point(38.7450065, -9.1352708) AS "cstravdistmiles" FROM "clinicaltrial" "clinicaltrial" INNER JOIN "clinicaltrialstatus" "clinicaltrialstatus" ON "clinicaltrialstatus"."code"="clinicaltrial"."status" INNER JOIN "clinicaltrialmedicalcondition" "clinicaltrialmedicalcondition" ON "clinicaltrialmedicalcondition"."clinicaltrial"="clinicaltrial"."id" INNER JOIN "medicalcondition" "medicalcondition" ON "medicalcondition"."code"="clinicaltrialmedicalcondition"."medicalcondition" INNER JOIN "clinicalsite" "clinicalsite" ON "clinicalsite"."id"="clinicaltrial"."clinicalsite" INNER JOIN "clinicaltrialclinicalsite" "clinicaltrial_clinicalsitearray" ON "clinicaltrial_clinicalsitearray"."clinicaltrial"="clinicaltrial"."id" INNER JOIN "clinicalsite" "clinicalsitearray" ON "clinicalsitearray"."id"="clinicaltrial_clinicalsitearray"."clinicalsite" INNER JOIN "address" "csaddress" ON "csaddress"."id"="clinicalsitearray"."address" INNER JOIN "country" "cscountry" ON "cscountry"."code"="csaddress"."country" INNER JOIN "location" "cslocation" ON "cslocation"."id"="csaddress"."location" INNER JOIN "sponsor" "sponsor" ON "sponsor"."id"="clinicaltrial"."sponsor" INNER JOIN "address" "address" ON "address"."id"="clinicalsite"."address" INNER JOIN "country" "country" ON "country"."code"="address"."country" INNER JOIN "location" "location" ON "location"."id"="address"."location" WHERE "clinicaltrialstatus"."code" IN ('REC') AND "medicalcondition"."code" IN ('101000')) "distinctAlias" ORDER BY "distinctAlias".cstravdistmiles ASC, "distinctAlias"."clinicalsite_id" DESC, "clinicaltrial_id" ASC LIMIT 5
ids_clinicaltrial_id uuid cstravdistmiles double precision clinicalsite_id uuid
1 f6792fa1-c2a4-4f93-be06-2558ad4a00b7 1.5895845000719602 951a89d9-261c-44aa-8275-383c1e5efbb8
2 1ca49499-df7e-42d5-a13e-6a05ebee96be 1.5895845000719602 35be0fb7-fb5b-45e3-80f0-705401183848
3 d4228287-2707-46a7-9a7c-5f874d375921 1.5895845000719602 35be0fb7-fb5b-45e3-80f0-705401183848
4 f6792fa1-c2a4-4f93-be06-2558ad4a00b7 391.5629011758247 951a89d9-261c-44aa-8275-383c1e5efbb8
5 05ca265b-6b41-4f7b-b00d-c63c4b9ebcc5 391.5629011758247 485a1939-b5cc-476b-b055-3e481ace315e
Problem: Note that ctr.id='f6792fa1-c2a4-4f93-be06-2558ad4a00b7' is repeated twice. This is actually counting the number of distinct combinations of ctr.id + cs.travelDistanceMiles + cs,id
What we want is the number of distinct ctr.id combinations, sorted by ascending travel distance.
query: SELECT "clinicaltrial"."id" AS "clinicaltrial_id", "clinicaltrial"."name" AS "clinicaltrial_name", "clinicaltrial"."description" AS "clinicaltrial_description", "clinicaltrial"."keyssi" AS "clinicaltrial_keyssi", "clinicaltrial"."dsudata" AS "clinicaltrial_dsudata", "clinicaltrial"."nctnumber" AS "clinicaltrial_nctnumber", "clinicaltrial"."purpose" AS "clinicaltrial_purpose", "clinicaltrial"."phase" AS "clinicaltrial_phase", "clinicaltrial"."timecommitment" AS "clinicaltrial_timecommitment", "clinicaltrial"."physicalcommitment" AS "clinicaltrial_physicalcommitment", "clinicaltrial"."travelstipends" AS "clinicaltrial_travelstipends", "clinicaltrial"."eligibilitycriteria" AS "clinicaltrial_eligibilitycriteria", "clinicaltrial"."status" AS "clinicaltrial_status", "clinicaltrial"."sponsor" AS "clinicaltrial_sponsor", "clinicaltrial"."clinicalsite" AS "clinicaltrial_clinicalsite", "clinicaltrialstatus"."code" AS "clinicaltrialstatus_code", "clinicaltrialstatus"."description" AS "clinicaltrialstatus_description", "clinicaltrialmedicalcondition"."id" AS "clinicaltrialmedicalcondition_id", "clinicaltrialmedicalcondition"."ordering" AS "clinicaltrialmedicalcondition_ordering", "clinicaltrialmedicalcondition"."clinicaltrial" AS "clinicaltrialmedicalcondition_clinicaltrial", "clinicaltrialmedicalcondition"."medicalcondition" AS "clinicaltrialmedicalcondition_medicalcondition", "medicalcondition"."code" AS "medicalcondition_code", "medicalcondition"."name" AS "medicalcondition_name", "clinicalsite"."id" AS "clinicalsite_id", "clinicalsite"."name" AS "clinicalsite_name", "clinicalsite"."address" AS "clinicalsite_address", "clinicalsitearray"."id" AS "clinicalsitearray_id", "clinicalsitearray"."name" AS "clinicalsitearray_name", "clinicalsitearray"."address" AS "clinicalsitearray_address", "csaddress"."id" AS "csaddress_id", "csaddress"."street" AS "csaddress_street", "csaddress"."country" AS "csaddress_country", "csaddress"."location" AS "csaddress_location", "cscountry"."code" AS "cscountry_code", "cscountry"."name" AS "cscountry_name", "cslocation"."id" AS "cslocation_id", "cslocation"."description" AS "cslocation_description", "cslocation"."latitude" AS "cslocation_latitude", "cslocation"."longitude" AS "cslocation_longitude", "cslocation"."center" AS "cslocation_center", "sponsor"."id" AS "sponsor_id", "sponsor"."name" AS "sponsor_name", "sponsor"."logo" AS "sponsor_logo", "address"."id" AS "address_id", "address"."street" AS "address_street", "address"."country" AS "address_country", "address"."location" AS "address_location", "country"."code" AS "country_code", "country"."name" AS "country_name", "location"."id" AS "location_id", "location"."description" AS "location_description", "location"."latitude" AS "location_latitude", "location"."longitude" AS "location_longitude", "location"."center" AS "location_center", point("cslocation"."latitude", "cslocation"."longitude") <@> point(38.7450065, -9.1352708) AS "cstravdistmiles" FROM "clinicaltrial" "clinicaltrial" INNER JOIN "clinicaltrialstatus" "clinicaltrialstatus" ON "clinicaltrialstatus"."code"="clinicaltrial"."status" INNER JOIN "clinicaltrialmedicalcondition" "clinicaltrialmedicalcondition" ON "clinicaltrialmedicalcondition"."clinicaltrial"="clinicaltrial"."id" INNER JOIN "medicalcondition" "medicalcondition" ON "medicalcondition"."code"="clinicaltrialmedicalcondition"."medicalcondition" INNER JOIN "clinicalsite" "clinicalsite" ON "clinicalsite"."id"="clinicaltrial"."clinicalsite" INNER JOIN "clinicaltrialclinicalsite" "clinicaltrial_clinicalsitearray" ON "clinicaltrial_clinicalsitearray"."clinicaltrial"="clinicaltrial"."id" INNER JOIN "clinicalsite" "clinicalsitearray" ON "clinicalsitearray"."id"="clinicaltrial_clinicalsitearray"."clinicalsite" INNER JOIN "address" "csaddress" ON "csaddress"."id"="clinicalsitearray"."address" INNER JOIN "country" "cscountry" ON "cscountry"."code"="csaddress"."country" INNER JOIN "location" "cslocation" ON "cslocation"."id"="csaddress"."location" INNER JOIN "sponsor" "sponsor" ON "sponsor"."id"="clinicaltrial"."sponsor" INNER JOIN "address" "address" ON "address"."id"="clinicalsite"."address" INNER JOIN "country" "country" ON "country"."code"="address"."country" INNER JOIN "location" "location" ON "location"."id"="address"."location" WHERE ( "clinicaltrialstatus"."code" IN ('REC') AND "medicalcondition"."code" IN ('101000') ) AND ( "clinicaltrial"."id" IN ($1, $2, $3, $4, $5) ) ORDER BY cstravdistmiles ASC, "clinicalsite"."id" DESC -- PARAMETERS: ["f6792fa1-c2a4-4f93-be06-2558ad4a00b7","1ca49499-df7e-42d5-a13e-6a05ebee96be","d4228287-2707-46a7-9a7c-5f874d375921","f6792fa1-c2a4-4f93-be06-2558ad4a00b7","05ca265b-6b41-4f7b-b00d-c63c4b9ebcc5"]
SELECT clinicaltrial.id,
MIN(point(location.latitude, location.longitude) <@> point(38.7450065, -9.1352708)) AS travDistMiles
FROM clinicaltrial,
clinicaltrialclinicalsite,
clinicalsite,
address,
location,
sponsor
WHERE clinicaltrialclinicalsite.clinicaltrial=clinicaltrial.id
AND clinicalsite.id=clinicaltrialclinicalsite.clinicalsite
AND address.id=clinicalsite.address
AND location.id=address.location
AND sponsor.id=clinicaltrial.sponsor
GROUP BY clinicaltrial.id
ORDER BY travDistMiles ASC, clinicaltrial.id DESC;
2021-12-09 - PL CTR Testing Issues- "FIND a TRIAL" does not always shows the sites (within a trial) sorted by travel distance (even when the patient supplied his location on the "Trial Preferences" screen).
bf21475 should fix the lack of sorting on the clinical sites.
(from 2021-12-09 - PL CTR Testing Issues )
When contacting multiple sites, the user can enter and leave the contact page. On re-entering the page, the user return to the last scroll position and not see the top level "contact" button (which is greyed out).
To solve that problem, let's experimenting to scroll up on the refresh event.
This is a long standing error (already modeled initially on the STS and figma clickeable proptotype).
implies changes to:
SQL - data model, implement the many-to-many association between ClinicalTrial and ClinicalSite, possibly named ClinicalTrialSite. The status attributes moves from ClinicalTrial.status to ClinicalTrialSite.status - as each site has its own recruiting status. Example data will have to be changed to include examples of trials with more than one site.
ctr-backoffice-backend/src/ctrial/clinical[trial|site].entity.ts - model the SQL. The ClinicalTrial.clinicalSite property will by replaced by a ClinicalTrial.clinicalTrialSites which is an array of ClinicalTrialSite. This affects the REST schema of these classes.
GET /borest/ctrial/clinicaltrial - instead of listing ClinicalTrials, it will INNER JOIN ClinicalTrials, ClinicalTrialSite, ClinicalSite. The search criterial will apply to this (which means that one trial may appear several times, if the search matches many sites - one for each site). Output schema impacted also from the changes to clinicaltrial.entity.ts
GET /borest/ctrial/clinicaltrial/{id} - Output schema impacted also from the changes to clinicaltrial.entity.ts
/borest/ctrial/clinicaltrial/ and related services impacted also from the schema changes to clinicalsitte.entity.ts
ctr-backoffice-frontend - ?