CDR Tickets

I'm looking at implementing this while the clinical trials search redesign project is on hold. The approach I have in mind is to replace the sweep through the spreadsheets which were generated in the past with a check of a new table which records when a glossary term was added to a spreadsheet sent to Vanessa. That table would have columns for:

1. the glossary term name document's CDR ID

2. the name of the spreadsheet it was included in

3. the date the spreadsheet was created

I don't see any holes in this approach, as far as the production system goes, though I can imagine some edge cases where testing could be confusing on the lower tiers. Imagine, for example, that you create a new glossary term name document on a lower tier for testing, and by chance the CDR ID assigned to that new document just happens to match the ID assigned earlier on the production system for a glossary term name document which was included on a spreadsheet which actually got sent to Vanessa. In that case, the tester could be puzzled by the fact that the new glossary term name document created on the lower tier wasn't included on the spreadsheet generated on that tier for testing. As long as we're aware of that (very unlikely) possibility, we should be OK.

Steps for implementation:

1. Write the SQL for creating the new table

2. Run the script on DEV

3. Write the script to populate the table from the spreadsheets already sent to Vanessa

4. Run that script on DEV

5. Write the CGI script to generate a fresh spreadsheet and add the corresponding rows to the new table

6. Have CIAT test the new script

7. Install and populate the new table on QA

8. Install the CGI script on QA

9. Have CIAT test the new script

10. Deploy data and script to PROD (as part of a future release)

See any flaws in this approach?

Adding William to this issue. William, could you please go over this and/or discuss with Amy and see if the above looks reasonable? Thanks.

Sure. Will do.

Your approach looks good to us. We didn't find any problems with it.

Made some good progress on this task:

✔ Write the SQL for creating the new table
✔ Run the script on DEV
✔ Write the script to populate the table
✔ Run that script on DEV
✔ Write the CGI script to generate a fresh spreadsheet

So now we're ready for the next steps:

☐ Have CIAT test the new script on DEV
☐ Install and populate the new table on QA
☐ Install the CGI script on QA
☐ Have CIAT test the new script
☐ Deploy data and script to PROD (as part of a future release)

The tool has been plugged into the CIAT admin menu. In order to test again after running it the first time, you'll need to add more glossary term name documents with pronunciations.

/*
 * Record of having asked for pronunciation audio clips for the names
 * found in a particular GlossaryTermName document.
 *
 *      cdr_id   primary key for the GlossaryTermName document
 * spreadsheet   name of the spreadsheet in which the request was included
 *    requeted   the date the request was generated
 */
CREATE TABLE glossary_term_audio_request
     (cdr_id INTEGER      NOT NULL PRIMARY KEY REFERENCES all_docs,
 spreadsheet VARCHAR(256) NOT NULL,
   requested DATE         NOT NULL)

You must be in a group with REVIEW TERM AUDIO permission to generate the spreadsheet (right now that includes users in the Developers and Glossary Maintainers groups, at least on DEV).

I get a python script error when I click on the link. It is probably related to OCECDR-3823.

A problem occurred in a Python script.

D:\cdr\Log\tmpkxszsy.html contains the description of this error.

Please try again. I had omitted the module which is needed for checking the permission mentioned in my previous comment.

I am getting the same error message even after logging out of XMetal and logging back in.

A problem occurred in a Python script.

D:\cdr\Log\tmpeuiuy6.html contains the description of this error

Not quite the same error, but in the same general area. Please try again. No need to log out of XMetaL.

It worked. Thanks!

I am no longer able to generate new spreadsheets. I get "You are not authorized to generate this spreadsheet" error message.

You need to be a member of a group which has the AUDIO IMPORT permission. Doesn't look like any of the groups have that permission.

NCIWS-D141-V-M:D:\Inetpub\wwwroot\cgi-bin\cdr>cdrsql
SQL> SELECT g.name
   >   FROM grp g
   >   JOIN grp_action ga
   >     ON ga.grp = g.id
   >   JOIN action a
   >     ON ga.action = a.id
   >  WHERE a.name = 'AUDIO IMPORT';
name
-------------------------------------------
SQL>

Thanks! I fixed the error and I am now able to run the report.

The report is running but it is no longer retrieving new data. I've added a few new terms with the pronunciation keys but they are not being picked up by the report.

The software only picks up terms that have been published. You may need to wait until that has happened (or add pronunciation keys to documents which didn't have them, and don't have pronunciation Media links, but have been published).

Verified on DEV.

This is ready for testing on QA.

Verified on QA

I am getting a Python script error on Stage:

A problem occurred in a Python script.

D:\cdr\Log\tmpzn_7u6.html contains the description of this error.

The deployment to stage didn't include the steps identified in the November 5 comment.

I will put in a ticket for the database team to add and populate the new table. Can you tell me if we can hold off on generating any new spreadsheets for Vanessa until this tool is in production? If so, I will ask them to add and populate the table on prod now as well.

Yes, we can hold off on generating spreadsheets for Vanessa until this is in Production.

https://tracker.nci.nih.gov/browse/DBATEAM-1590 submitted. I'll let you know when the table is ready.

The table has been created and populated on STAGE. You should be able to test now.

The table has also been created and populated on PROD, so we'll be all set once tonight's deployment rolls out.

Verified on STAGE. Thank you!

Verified on PROD.