Use ReciterDB for producing files for import into VIVO Dashboard

[paulalbert1]

Context

If we start using ReCiter instead of PubAdmin, we can't kill PubAdmin because VIVO Dashboard gets files from PubAdmin. Here are the existing queries.

In order for this to proceed, this issue needs to be addressed.

The below queries run against ReCiterDb should be able to replace the existing queries run against PubAdmin.

Articles

select distinct
scopusDocID as publication,
max(articleTitle) as label,
max(date_format(publicationDateStandardized, '%Y-%m-%d %T')) as date,
max(case
when issn <> '' then concat("http://vivo.med.cornell.edu/journal/",replace(issn,'-',''))
when journalTitleVerbose <> '' then concat("http://vivo.med.cornell.edu/journal/",md5(journalTitleVerbose))
else concat("http://vivo.med.cornell.edu/journal/",md5(journalTitleISOabbreviation))
end) as journal,
max(volume) as volume,
max(issue) as issue,
max(case
when doi is not null and doi != '' then concat("DOI: ",doi)
else ""
end) as doi,
max(pmid) as pmid,
max(pmcid) as pmcid,
scopusDocID as scopusID,
max(pages) AS pageStart,
NULL AS pageEnd,
max(timesCited) as timesCited,
max(publicationTypeCanonical) as pubtype
from personArticle d
where not (articleTitle like ("%: Retraction") or articleTitle like ("%[Retraction]%") or articleTitle like ("Retraction:%") or articleTitle like ("Retraction.%") or articleTitle like ("[Erratum%") or articleTitle like("Erratum%") or articleTitle like ("ERRATUMERRATUM%") or articleTitle like ("Withdrawn%")) 
and articleTitle is not null 
and journalTitleVerbose is not null
and scopusDocID is not null
and scopusDocID != ''
and scopusDocID != '0'
and userAssertion = 'ACCEPTED'
group by scopusDocID

Journals

select distinct
replace(concat("http://vivo.med.cornell.edu/journal/",issn),'-','') as URI,
max(journalTitleVerbose) as label,
issn,
'' as eissn,
'' as isbn13,
'' as isbn10,
'http://purl.org/ontology/bibo/Journal' as type
from personArticle
where not (articleTitle like ("%: Retraction") or articleTitle like ("%[Retraction]%") or articleTitle like ("Retraction:%") or articleTitle like ("Retraction.%") or articleTitle like ("[Erratum%") or articleTitle like("Erratum%") or articleTitle like ("ERRATUMERRATUM%") or articleTitle like ("Withdrawn%")) 
and articleTitle is not null 
and journalTitleVerbose is not null
and scopusDocID is not null
and scopusDocID != ''
and userAssertion = 'ACCEPTED'
and issn <> ''
and scopusDocID != '0'
group by issn

UNION

(select distinct
concat("http://vivo.med.cornell.edu/journal/",md5(journalTitleVerbose)) as URI,
journalTitleVerbose as label,
'' as issn,
'' as eissn,
'' as isbn13,
'' as isbn10,
'http://purl.org/ontology/bibo/Journal' as type
from personArticle
where not (articleTitle like ("%: Retraction") or articleTitle like ("%[Retraction]%") or articleTitle like ("Retraction:%") or articleTitle like ("Retraction.%") or articleTitle like ("[Erratum%") or articleTitle like("Erratum%") or articleTitle like ("ERRATUMERRATUM%") or articleTitle like ("Withdrawn%")) 
and articleTitle is not null 
and journalTitleVerbose is not null
and scopusDocID is not null
and scopusDocID != ''
and scopusDocID != '0'
and userAssertion = 'ACCEPTED'
and issn = ''
and journalTitleVerbose <> ''
group by journalTitleVerbose)

UNION

(select distinct
concat("http://vivo.med.cornell.edu/journal/",md5(journalTitleISOabbreviation)) as URI,
max(journalTitleVerbose) as label,
issn,
'' as eissn,
'' as isbn13,
'' as isbn10,
'http://purl.org/ontology/bibo/Journal' as type
from personArticle
where not (articleTitle like ("%: Retraction") or articleTitle like ("%[Retraction]%") or articleTitle like ("Retraction:%") or articleTitle like ("Retraction.%") or articleTitle like ("[Erratum%") or articleTitle like("Erratum%") or articleTitle like ("ERRATUMERRATUM%") or articleTitle like ("Withdrawn%")) 
and articleTitle is not null 
and journalTitleVerbose is not null
and scopusDocID is not null
and scopusDocID != ''
and scopusDocID != '0'
and userAssertion = 'ACCEPTED'
and issn = ''
and journalTitleISOabbreviation <> ''
group by journalTitleISOabbreviation)

Authors

select distinct
min(authorshipURI) as authorshipURI,
max(label) as label,
max(firstName) as firstName,
max(lastName) as lastName,
max(cwid) as cwid,
max(affiliation) as affiliation,
max(department) as department,
min(RDFtypes) as RDFtypes,
max(popsURI) as popsURI,
max(directoryURI) as directoryURI,
max(vivoURI) as vivoURI
from
((select distinct
rank, p.pmid,
	case
 	when e.cwid is not null then concat('http://vivo.med.cornell.edu/individual/cwid-', cast(e.cwid as char(20)))
 	when e.cwid is null then concat('http://vivo.med.cornell.edu/individual/person', cast(a.id as char(20)))
	end as authorshipURI,
	case
 	when e.cwid is not null then concat(e.surname,', ',e.givenName, if(middleName is not null, concat(' ',middleName),''))
 	when e.cwid is null then replace(concat(authorLastName,', ',authorFirstName),'.','')
	end as label,
	case
 	when e.cwid is not null then concat(e.givenName,if(middleName is not null, concat(' ',middleName),''))
 	when e.cwid is null then replace(givenName,'.','')
	end as firstName,
	case
 	when e.cwid is not null then e.surname
 	when e.cwid is null then replace(surname,'.','')
	end as lastName,
e.cwid as cwid, 
e.primaryOrg as affiliation,
e.primaryAcademicDepartment as department, 
concat(
if(fullTimeFaculty = 'yes','http://weill.cornell.edu/vivo/ontology/wcmc#FullTimeWCMCFaculty|',''),
if(postdoc = 'yes','http://vivoweb.org/ontology/core#Postdoc|',''),
if(studentMD = 'yes','http://weill.cornell.edu/vivo/ontology/wcmc#StudentMDNYC|',''),
if(studentMDPhD = 'yes','http://weill.cornell.edu/vivo/ontology/wcmc#StudentMdPhdTriI|',''),
if(studentPhDTriI = 'yes','http://weill.cornell.edu/vivo/ontology/wcmc#StudentPhdTriI|',''),
if(studentPhDWeill = 'yes','http://weill.cornell.edu/vivo/ontology/wcmc#StudentPhdWeill|',''),
if(partTimeFaculty = 'yes','http://weill.cornell.edu/vivo/ontology/wcmc#PartTimeWCMCFaculty|',''),
if(voluntaryFaculty = 'yes','http://weill.cornell.edu/vivo/ontology/wcmc#VoluntaryFaculty|',''),
if(emeritusFaculty = 'yes','http://vivoweb.org/ontology/core#EmeritusFaculty|',''),
if(adjunctFaculty = 'yes','http://weill.cornell.edu/vivo/ontology/wcmc#AdjunctFaculty|',''),
if(fellow = 'yes','http://weill.cornell.edu/vivo/ontology/wcmc#Fellow|',''),
if(faculty = 'yes','http://vivoweb.org/ontology/core#FacultyMember|',''),
if(nonFaculty = 'yes','http://vivoweb.org/ontology/core#NonAcademic|',''),
	'http://xmlns.com/foaf/0.1/Person'
) as RDFtypes,
e.popsProfile as popsURI,
e.directoryProfile as directoryURI,
e.vivoProfile as vivoURI
from personArticleAuthor a
left join identity e on e.cwid = a.personIdentifier
left join personArticle p on p.pmid = a.pmid and p.personIdentifier = a.personIdentifier
where targetAuthor = '1'
and userAssertion = 'ACCEPTED'
and not (articleTitle like ("%: Retraction") or articleTitle like ("%[Retraction]%") or articleTitle like ("Retraction:%") or articleTitle like ("Retraction.%") or articleTitle like ("[Erratum%") or articleTitle like("Erratum%") or articleTitle like ("ERRATUMERRATUM%") or articleTitle like ("Withdrawn%")) 
and scopusDocID <> '' 
and scopusDocID != '0'
and e.cwid is not null

UNION

select distinct
rank, p.pmid,
concat('http://vivo.med.cornell.edu/individual/person', cast(a.id as char(20))) as authorshipURI,
replace(concat(authorLastName,', ',authorFirstName),'.','') as label,
replace(authorFirstName,'.','') as firstName,
replace(authorLastName,'.','') as lastName,
null as cwid, 
null as affiliation,
null as department, 
'http://xmlns.com/foaf/0.1/Person' as RDFtypes,
null as popsURI,
null as directoryURI,
null as vivoURI
from personArticleAuthor a
join personArticle p on p.pmid = a.pmid and p.personIdentifier = a.personIdentifier
where targetAuthor = '0'
and userAssertion = 'ACCEPTED'
and not (articleTitle like ("%: Retraction") or articleTitle like ("%[Retraction]%") or articleTitle like ("Retraction:%") or articleTitle like ("Retraction.%") or articleTitle like ("[Erratum%") or articleTitle like("Erratum%") or articleTitle like ("ERRATUMERRATUM%") or articleTitle like ("Withdrawn%")) 
and scopusDocID <> ''
and scopusDocID != '0' )) x
group by pmid, rank

Authorships

select authorship, label, rank, publication, min(person) as person, max(vcard) as vcard from
(select distinct
a.id as id,

  concat(
	'http://vivo.med.cornell.edu/individual/pubid',
	cast(scopusDocID as char(30)),'authorship',
	cast(rank as char(20))
	) as authorship, 
NULL as label,
rank,
scopusDocID as publication,
case
when e.cwid is not null then concat('http://vivo.med.cornell.edu/individual/cwid-',cast(e.cwid as char(20)))
when e.cwid is null then concat('http://vivo.med.cornell.edu/individual/person',cast(a.id as char(20)))
end as person,
case
when e.cwid is not null then concat('http://vivo.med.cornell.edu/individual/arg2000028-cwid-',cast(e.cwid as char(20)))
when e.cwid is null then concat('http://vivo.med.cornell.edu/individual/arg2000028-person',cast(a.id as char(20)))
end as vcard
FROM personArticleAuthor a
inner join personArticle p on p.pmid = a.pmid and p.personIdentifier = a.personIdentifier
left join identity e on e.cwid = a.personIdentifier
where 
scopusDocID is not null and scopusDocID != '' and scopusDocID != '0'
and userAssertion = 'ACCEPTED'
and not (articleTitle like ("%: Retraction") or articleTitle like ("%[Retraction]%") or articleTitle like ("Retraction:%") or articleTitle like ("Retraction.%") or articleTitle like ("[Erratum%") or articleTitle like("Erratum%") or articleTitle like ("ERRATUMERRATUM%") or articleTitle like ("Withdrawn%")) 
and targetAuthor = '1'

UNION

select distinct a.id as id, concat(

	'http://vivo.med.cornell.edu/individual/pubid',
	cast(scopusDocID as char(30)),'authorship',
	cast(rank as char(20))
	) as authorship, 
NULL as label,
rank,
scopusDocID as publication,
concat('http://vivo.med.cornell.edu/individual/person',cast(a.id as char(20))) as person,
case
when e.cwid is null then concat('http://vivo.med.cornell.edu/individual/arg2000028-person',cast(a.id as char(20)))
end as vcard
FROM personArticleAuthor a
inner join personArticle p on p.pmid = a.pmid and p.personIdentifier = a.personIdentifier
left join identity e on e.cwid = a.personIdentifier
where 
scopusDocID is not null and scopusDocID != '' and scopusDocID != '0'
and userAssertion = 'ACCEPTED'
and not (articleTitle like ("%: Retraction") or articleTitle like ("%[Retraction]%") or articleTitle like ("Retraction:%") or articleTitle like ("Retraction.%") or articleTitle like ("[Erratum%") or articleTitle like("Erratum%") or articleTitle like ("ERRATUMERRATUM%") or articleTitle like ("Withdrawn%")) 
and targetAuthor = '0'
group by rank, scopusDocID) x
group by authorship