CDR Tickets

The idea is that no protocol records would be created in the CDR for the trials that are retrieved and only new trials would be displayed for the user to review (no need to check for updates). After that, the results can be discarded. While we do not want to create or keep the protocol records, being able to get some statistics on the retrieved trials would be beneficial for reporting purposes.

It seems risky to discard the information for a trial as soon as it has been reported once.(I can imagine, for example, CBIIT doing an app-scan which brings up the report page, causing the trials displayed on the report to be dropped from future displays of the report, which would result in CIAT never seeing those trials).

I propose instead a report which would show the ID, title, and link for trials first received by NLM in a specific date range, defaulting to something like the most recent 30 days.

This would be implemented using a new database table, along these lines:

  CREATE TABLE ctgov_trials
       (nct_id VARCHAR(11) NOT NULL PRIMARY KEY,
   trial_title NVARCHAR(1024) NOT NULL
first_received DATETIME)

I'll use the brief title if there is one, falling back on the official title if there's no brief title (unless you'd prefer that I reverse the default).

Does this approach sound reasonable? If so, I would estimate the level of effort to implement this report at a day or less.

This sounds good to me. We'd also like to be able to retrieve the report in Excel for workflow purposes, if that is possible.

I have this implemented on DEV.

http://cdr.dev.cancer.gov/cgi-bin/cdr/RecentCTGovProtocols.py?Session=guest

Thanks, Bob! The report looks good on DEV. Could you please add the following columns/data?

1. Phase
2. Sponsorship (or sponsor/collaborator)
3. Other IDs

Starting over. Moved ticket back to "In Progress" to work with new requirements.

Replaced original table for this ticket with three new ones:

  CREATE TABLE ctgov_trial
       (nct_id VARCHAR(11) NOT NULL PRIMARY KEY,
   trial_title NVARCHAR(1024) NOT NULL,
   trial_phase NVARCHAR(20) NULL,
first_received DATETIME NOT NULL)
CREATE TABLE ctgov_trial_sponsor
     (nct_id VARCHAR(11) NOT NULL REFERENCES ctgov_trial,
    position INTEGER NOT NULL,
     sponsor VARCHAR(1024) NOT NULL,
 PRIMARY KEY (nct_id, position))
CREATE TABLE ctgov_trial_other_id
     (nct_id VARCHAR(11) NOT NULL REFERENCES ctgov_trial,
    position INTEGER NOT NULL,
    other_id VARCHAR(1024) NOT NULL,
 PRIMARY KEY (nct_id, position))
GRANT SELECT on ctgov_trial to CdrGuest
GRANT SELECT on ctgov_trial_sponsor to CdrGuest
GRANT SELECT on ctgov_trial_other_id to CdrGuest

The original report will not work while I am rewriting it. I'll let you know when it's ready for testing again.

I have finished the re-implementation for this ticket. Please test (on DEV):

https://cdr.dev.cancer.gov/cgi-bin/cdr/RecentCTGovProtocols.py?Session=guest

Verified on DEV. Looks great. Thank you!!!

Reminder: We'll want to update tables.sql with the new table definitions, if it hasn't already been done.

It has, just not in Subversion yet (remember?).

In Subversion for this ticket:

• R13198 /branches/Curie/Database/CreateLogins.sql

• R13198 /branches/Curie/Database/tables.sql

• R13199 /branches/Curie/Inetpub/wwwroot/cgi-bin/cdr/RecentCTGovProtocols.py

• R13201 /branches/Curie/Bin/GetRecentCTGovProtocols.cmd

• R13200 /branches/Curie/Utilities/GetRecentCTGovProtocols.py

Will require CBIIT to install a scheduled job to populate the new tables periodically.

The ticket to create the new tables on STAGE and PROD is DBATEAM-1922.

The new tables are on PROD and STAGE. The ticket for creating the scheduled job on QA and DEV is WEBTEAM-6588 (separate WEBTEAM tickets will be needed for STAGE and PROD).

The scheduled job has been installed on DEV and QA. It will kick off at 4am tomorrow morning, so you'll be able to test the report on Monday (or tomorrow, if you're really eager :-) ).

Running the report in Web Page format produces a python script error:

A problem occurred in a Python script.
D:\cdr\Log\tmpkkokbp.html contains the description of this error

This is on QA.

Also, running the report in Worksheet mode appears to display trials that were received on only the selected Start Date. That is, if you choose,

Start Date: 2015-06-02
End Date: 2015-06-22

all the trials that are displayed would have a Received date of 2015-06-02. If you choose another Start Date, all the trials displayed would have that new Start Date. It does not appear to be retrieving the trials within the date range specified.

So, this worked correctly on DEV, but it's broken on QA?

That is correct. I didn't see any of these problems on DEV.

I'm rebuilding and repopulating the tables on QA. Will let you know when they're ready.

Please test again.

It worked. Thanks!

Verified on QA.

The report was showing your start date instead of the received date for all of the trials. It was showing you the correct trials, but the wrong date in the second column. The bug was present on both DEV and QA. Fixed on both tiers.

Verified on PROD. Thank you!