| Issue Number | 4524 |
|---|---|
| Summary | Report listing all URLs |
| Created | 2018-09-19 13:51:22 |
| Issue Type | Inquiry |
| Submitted By | Osei-Poku, William (NIH/NCI) [C] |
| Assigned To | Englisch, Volker (NIH/NCI) [C] |
| Status | Closed |
| Resolved | 2019-07-03 16:35:13 |
| Resolution | Fixed |
| Path | /home/bkline/backups/jira/ocecdr/issue.233317 |
We have an ad hoc query (below) that we run to identify all URLs in a particular doc type that we search for specific terms to identify the URLs that need to be updated or removed from documents. We would like to have this query in a form of a report on the report menu. Also, currently, the report does not include the DrugReferenceLink. We would like to include it as part of this report as well.
SELECT dt.name AS "Doc Type", i.doc_id AS "CDR-ID",
t.value AS "Title", i.value AS "URL",
d.value AS "Display Text", s.value AS "Source Title"
FROM query_term i
JOIN query_term t
ON i.doc_id = t.doc_id
AND t.path = '/Summary/SummaryTitle'
JOIN document x
ON x.id = i.doc_id
JOIN doc_type dt
ON dt.id = x.doc_type
LEFT OUTER JOIN query_term s
ON s.doc_id = i.doc_id
AND s.path like '%/ExternalRef/@SourceTitle'
AND s.node_loc = i.node_loc
JOIN query_term d
ON d.doc_id = i.doc_id
AND d.path like '%/ExternalRef'
AND d.node_loc = i.node_loc
WHERE i.path like '%/ExternalRef/@cdr:xref'
ORDER BY t.path
UNION
SELECT dt.name AS "Doc Type", i.doc_id AS "CDR-ID",
t.title AS "Title", i.value AS "URL",
d.value AS "Display Text", s.value AS "Source Title"
FROM query_term i
JOIN document t
ON i.doc_id = t.id
JOIN doc_type dt
ON dt.id = t.doc_type
AND dt.name NOT IN ('Summary')
LEFT OUTER JOIN query_term s
ON s.doc_id = i.doc_id
AND s.path like '%/ExternalRef/@SourceTitle'
AND s.node_loc = i.node_loc
JOIN query_term d
ON d.doc_id = i.doc_id
AND d.path like '%/ExternalRef'
AND d.node_loc = i.node_loc
WHERE i.path like '%/ExternalRef/@cdr:xref'
ORDER BY 1
And this is the query for the DrugReferenceLink (which should be incorporated into the query above so that we have one report for all the links).
SELECT a.doc_id, a.value, b.value
FROM query_term a
JOIN query_term b
ON a.doc_id = b.doc_id
WHERE a.path LIKE
'/DrugInformationSummary/DrugReference/DrugReferenceLink/@cdr:xref'
AND b.path LIKE '/DrugInformationSummary/Title'
AND a.value is NOT NULL
What format should this report be in? Excel or HTML
What sort order would you like for the second report? (It doesn't
specifically include a sort order.)
What text would you like to use for the menu option/report title?
Where would you like this menu option to appear? Summaries or Drug
Information or both
What format should this report be in? Excel or HTML
The default should be HTML with the option to generate Excel.
What sort order would you like for the second report? (It doesn't specifically include a sort order.)
Order by CDR ID.
What text would you like to use for the menu option/report title?
Search URL list report
Where would you like this menu option to appear? Summaries or Drug Information or both
It should be placed on the General report menu. If possible, we want one report with the option to choose between Summaries and DIS.
If possible, we want one report with the option to choose between Summaries and DIS.
I thought you wanted to have both document types on one report?
Are you looking for the report to
displays either the Summaries or the DIS or
displays both but also allows you to display either the Summaries or the DIS by themselves?
Yes, if we can have both document types on one report, sorted by document type, that would be great. If not, having separate reports as described in my previous comments should also be fine.
*displays both but also allows you to display either the Summaries or the DIS by themselves?
Actually, this option would be better.
I have a better idea now of what the original ad-hoc report is doing. The description of the task as well as one of your comments made me believe this report was only for Summary and DIS documents but I see now that the report select all document types. Because of this I'd like to ask my questions again or at least confirm what you're looking for:
We're creating a report allowing you to get the report by a single document type and
We're including an option to include all document types on one big report (about 5,000 record)
We're creating a report allowing you to get the report by a single document type and
Yes.
We're including an option to include all document types on one big report (about 5,000 record)
No. We currently run the report by document type. There is really no need to run the report for all document types especially if there will be 5000 records to display. It is more likely to run the report by document type.
The new report has been added to the General Reports menu under the option URL List Report.
The following programs have been updated [cdr4524-url-report ad59ef34]:
GeneralReports.py
UrlListReport.py
This is ready for review on DEV.
Verified on DEV. Thanks!
The changes have been merged into our release branch Joule:
GeneralReports.py
https://github.com/NCIOCPL/cdr-admin/commit/ad59ef3
UrlListReport.py
https://github.com/NCIOCPL/cdr-admin/commit/ad59ef3
Verified on QA. Thanks!
Verified on DEV. Thanks!
Elapsed: 0:00:00.002172