rrodrigueznt · July 22, 2019 08:43
diff --git a/IGFAE.AR2018.R b/IGFAE.AR2018.R
 #
 options(stringsAsFactors=F)
 #
 #
 # returns string w/o leading whitespace
 trim.leading <- function (x)  sub("^\\s+", "", x)
 #
 # returns string w/o trailing whitespace		
 trim.trailing <- function (x) sub("\\s+$", "", x)
 #
 # returns string w/o leading or trailing whitespace
 trim <- function (x) gsub("^\\s+|\\s+$", "", x)
 #
 #
 library(RMariaDB)
 library(lubridate)
 library(data.table)
 #
 options(timeout = 1200)
 #
 ##SSH login without password
 ##http://www.linuxproblem.org/art_9.html
 #
 ##https://unix.stackexchange.com/questions/205830/authentication-refused-bad-ownership-or-modes-for-file-var-git-ssh-authorized
 ##https://unix.stackexchange.com/questions/257590/ssh-key-permissions-chmod-settings
 ##chmod u=rwx,g=,o= .ssh
 ##chmod u=rw,g=,o= .ssh/authorized_keys
 ##ssh -p 22 -v -L 3308:127.0.0.1:3306 hostUsername@IGFAEDBhost 
 ##Use -v for verbose mode
 #
 drv = dbDriver("MariaDB")
 con <- dbConnect(RMariaDB::MariaDB(), group = "igfae-mainRepository")
 con <- dbConnect(RMariaDB::MariaDB(), group = "igfae-mainRepositoryTunnel")
 #
 IGFAEAR2018phd <- dbGetQuery(con,statement="select Title,DefenseDate,concat(PA.Name,' ',PA.Surname) as Author,group_concat(concat(PT.Name,' ',PT.Surname) separator ', ') AS Tutors, ThesisType as Type from IGFAEDB.Theses natural join IGFAEDB.Personnel as PA natural join IGFAEDB.Tutors join IGFAEDB.Personnel AS PT on IGFAEDB.Tutors.PersonnelID1=PT.PersonnelID natural join IGFAEDB.ThesisTypes where YEAR(DefenseDate)=2018 Group by ThesesID")
 #
 IGFAEAR2018projGet2018 <- dbGetQuery(con,statement="select ProjectTitle As Title,ProjectReference as Reference,group_concat(concat(Name,' ', Surname) separator ', ') as PIs,TotalAmount,InitialDate,FinalDate,AgencyName as Agency from Projects natural join ProjectHasPersonnel natural join Personnel natural join GroupHasMembers natural join Groups natural join FinancingAgencies where Substring(USCProjectReference,1,4)='2018' and ParticipationTypeID=1 and Institute=0 group by USCProjectReference;")
 #
 IGFAEAR2018projAct2018 <- dbGetQuery(con,statement="select ProjectTitle As Title,ProjectReference as Reference,group_concat(concat(Name,' ', Surname) separator ', ') as PIs,TotalAmount,InitialDate,FinalDate,AgencyName as Agency from Projects natural join ProjectHasPersonnel natural join Personnel natural join GroupHasMembers natural join Groups natural join FinancingAgencies where Year(InitialDate)<=2018 and Year(FinalDate)>=2018 and ParticipationTypeID=1 and Institute=0 group by USCProjectReference;")
 #
 IGFAEAR2018people <- dbGetQuery(con,statement="select Name,Surname,IF(Gender=0,'Male','Female') As Gender,If(Origin=0,'Spanish','Non Spanish') as Origin,BeginDate,If(EndDate is not null, EndDate,'-') as EndDate,CategoryName from (select * FROM (Select 0 as HistoryID,PersonnelID,CategoryID,Name,Surname,StartingDate as BeginDate,EndDate,Gender,Origin from IGFAEDB.Personnel) as P union (select HistoryID,H1.PersonnelID,H1.CategoryID,Name,Surname,BeginDate,H1.EndDate,Gender,Origin from IGFAEDB.History as H1 left join IGFAEDB.Personnel as P1 on P1.PersonnelID=H1.PersonnelID) order by Surname) as t natural join IGFAEDB.Category natural join IGFAEDB.IGFAECategories where BeginDate<'2018-01-01' and (EndDate>'2016-12-31' or EndDate is null) order by CategoryName")
 #
 ## IGFAEAR2018sciPr <- dbGetQuery(con,statement="select ArticleTitle as Title,FullName,ISOAbbreviation,Volume,StartingPage,Year(`Date`),DOI,if(length(NumAuthors)<11,group_concat(distinct concat(AllAuthors,' et al.')),group_Concat(distinct If(PreferredNameForm is not null, PreferredNameForm,concat(Surname,', ',substring(Name,1,1))) separator '; ')) as Authors, group_concat(distinct GroupName separator ',') as `Group` ,group_concat(distinct Acronym separator ', ') as `Research Line`,NumAuthors from IGFAEDB.Articles natural join IGFAEDB.Journals natural join IGFAEDB.ArticleHasAuthors natural join IGFAEDB.Personnel Natural join IGFAEDB.GroupHasMembers natural join IGFAEDB.Groups where YEAR(`Date`)=2018 and ArticleTypeID in (2,4) and Institute=2 group by ArticleID")
 #
 ## IGFAEAR2018sciPr01 <- dbGetQuery(con,statement="select ArticleTitle as Title,FullName,ISOAbbreviation,Volume,StartingPage,Year(`Date`),DOI,if(NumAuthors>11,concat(EASurname,',',EAName,' et al.'), AllAuthors) as AllAuthors,group_Concat(distinct If(PreferredNameForm is not null, PreferredNameForm,concat(Surname,',',substring(Name,1,1))) separator '; ') as IGFAEAuthors, group_concat(distinct GroupName separator ', ') as `Group` ,group_concat(distinct Acronym separator ', ') as `Research Line`,NumAuthors from Articles natural join Journals natural join ArticleHasExternalAuthors natural join ExternalAuthors natural join ArticleHasAuthors natural join Personnel Natural join GroupHasMembers natural join Groups where YEAR(`Date`)=2018 and ArticleTypeID in (2,4) and Institute=2 and ( (NumAuthors>11 and EAOrder=1) or NumAuthors<11) group by ArticleID")
 #
 ## IGFAEAR2018sciPr02 <- dbGetQuery(con,statement="select ArticleTitle as Title,FullName,ISOAbbreviation,Volume,StartingPage,Year(`Date`),DOI,AllAuthors as AllAuthors,group_Concat(distinct If(PreferredNameForm is not null, PreferredNameForm,concat(Surname,', ',substring(Name,1,1))) separator '; ') as IGFAEAuthors, group_concat(distinct GroupName separator ', ') as `Group` ,group_concat(distinct Acronym separator ', ') as `Research Line`,NumAuthors from Articles natural join Journals natural join ArticleHasAuthors natural join Personnel Natural join GroupHasMembers natural join Groups where YEAR(`Date`)=2018 and ArticleTypeID in (2,4) and Institute=2 and ArticleID in ( 190,250,277,341) group by ArticleID")
 #
 ## IGFAEAR2018sciPr03 <- dbGetQuery(con,statement="select ArticleTitle as Title,FullName,ISOAbbreviation,Volume,StartingPage,Year(`Date`),DOI,if(NumAuthors>11,concat(EASurname,', ',EAName,' et al.'), AllAuthors) as AllAuthors,group_Concat(distinct If(PreferredNameForm is not null, PreferredNameForm,concat(Surname,',',substring(Name,1,1))) separator '; ') as IGFAEAuthors, group_concat(distinct GroupName separator ', ') as `Group` ,group_concat(distinct Acronym separator ', ') as `Research Line`,NumAuthors from Articles natural join Journals natural join ArticleHasExternalAuthors natural join ExternalAuthors natural join ArticleHasAuthors natural join Personnel Natural join GroupHasMembers natural join Groups where YEAR(`Date`)=2018 and ArticleTypeID in (2,4) and Institute=2 and EAOrder=3 and ArticleID=209  group by ArticleID")
 #
 ## IGFAEAR2018sciPr04 <- dbGetQuery(con,statement="select ArticleTitle as Title,FullName,ISOAbbreviation,Volume,StartingPage,Year(`Date`),DOI,if(NumAuthors>11,concat(EASurname,',',EAName,' et al.'), AllAuthors) as AllAuthors,group_Concat(distinct If(PreferredNameForm is not null, PreferredNameForm,concat(Surname,',',substring(Name,1,1))) separator '; ') as IGFAEAuthors, group_concat(distinct GroupName separator ', ') as `Group` ,group_concat(distinct Acronym separator ', ') as `Research Line`,NumAuthors from Articles natural join Journals natural join ArticleHasExternalAuthors natural join ExternalAuthors natural join ArticleHasAuthors natural join Personnel Natural join GroupHasMembers natural join Groups where YEAR(`Date`)=2018 and ArticleTypeID in (2,4) and Institute=2 and EAOrder=2 and ArticleID in (209, 212, 259, 294)  group by ArticleID")
 #
 ## IGFAEAR2018sciPrAll <- rbindlist(list(IGFAEAR2018sciPr01,IGFAEAR2018sciPr02,IGFAEAR2018sciPr03,IGFAEAR2018sciPr04))
 #
 ## a <- rbind(IGFAEAR2018sciPr01,IGFAEAR2018sciPr02)
 ## b <- rbind(a,IGFAEAR2018sciPr03)
 ## IGFAEAR2018sciPrAllbyAddition <- rbind(b,IGFAEAR2018sciPr04)
 #
 IGFAEAR2018sciPr <- dbGetQuery(con,statement="select ArticleTitle as Title,FullName,ISOAbbreviation,Volume,StartingPage,Year(`Date`),DOI,if(NumAuthors>11,concat(EASurname,',',EAName,' et al.'), AllAuthors) as AllAuthors,group_Concat(distinct If(PreferredNameForm is not null, PreferredNameForm,concat(Surname,',',substring(Name,1,1))) separator '; ') as IGFAEAuthors, group_concat(distinct GroupName separator ', ') as `Group` ,group_concat(distinct Acronym separator ', ') as `Research Line`,NumAuthors from Articles natural join Journals natural join ArticleHasAuthors natural join Personnel Natural join GroupHasMembers natural join Groups left join ArticleHasExternalAuthors using (ArticleID) left join ExternalAuthors using (ExternalAuthorID) where YEAR(`Date`)=2018 and ArticleTypeID in (2,4) and Institute=2 and ( (NumAuthors>11 and EAOrder=(select min(EAOrder) from ArticleHasExternalAuthors where ArticleHasExternalAuthors.ArticleID=Articles.ArticleID) ) or NumAuthors<11 or EAOrder is null) group by Articles.ArticleID")
 #
 dbDisconnect(con)
 dbUnloadDriver(drv)
 #
	#
	options(stringsAsFactors=F)
	#
	#
	# returns string w/o leading whitespace
	trim.leading <- function (x) sub("^\\s+", "", x)
	#
	# returns string w/o trailing whitespace
	trim.trailing <- function (x) sub("\\s+$", "", x)
	#
	# returns string w/o leading or trailing whitespace
	trim <- function (x) gsub("^\\s+\|\\s+$", "", x)
	#
	#
	library(RMariaDB)
	library(lubridate)
	library(data.table)
	#
	options(timeout = 1200)
	#
	##SSH login without password
	##http://www.linuxproblem.org/art_9.html
	#
	##https://unix.stackexchange.com/questions/205830/authentication-refused-bad-ownership-or-modes-for-file-var-git-ssh-authorized
	##https://unix.stackexchange.com/questions/257590/ssh-key-permissions-chmod-settings
	##chmod u=rwx,g=,o= .ssh
	##chmod u=rw,g=,o= .ssh/authorized_keys
	##ssh -p 22 -v -L 3308:127.0.0.1:3306 hostUsername@IGFAEDBhost
	##Use -v for verbose mode
	#
	drv = dbDriver("MariaDB")
	con <- dbConnect(RMariaDB::MariaDB(), group = "igfae-mainRepository")
	con <- dbConnect(RMariaDB::MariaDB(), group = "igfae-mainRepositoryTunnel")
	#
	IGFAEAR2018phd <- dbGetQuery(con,statement="select Title,DefenseDate,concat(PA.Name,' ',PA.Surname) as Author,group_concat(concat(PT.Name,' ',PT.Surname) separator ', ') AS Tutors, ThesisType as Type from IGFAEDB.Theses natural join IGFAEDB.Personnel as PA natural join IGFAEDB.Tutors join IGFAEDB.Personnel AS PT on IGFAEDB.Tutors.PersonnelID1=PT.PersonnelID natural join IGFAEDB.ThesisTypes where YEAR(DefenseDate)=2018 Group by ThesesID")
	#
	IGFAEAR2018projGet2018 <- dbGetQuery(con,statement="select ProjectTitle As Title,ProjectReference as Reference,group_concat(concat(Name,' ', Surname) separator ', ') as PIs,TotalAmount,InitialDate,FinalDate,AgencyName as Agency from Projects natural join ProjectHasPersonnel natural join Personnel natural join GroupHasMembers natural join Groups natural join FinancingAgencies where Substring(USCProjectReference,1,4)='2018' and ParticipationTypeID=1 and Institute=0 group by USCProjectReference;")
	#
	IGFAEAR2018projAct2018 <- dbGetQuery(con,statement="select ProjectTitle As Title,ProjectReference as Reference,group_concat(concat(Name,' ', Surname) separator ', ') as PIs,TotalAmount,InitialDate,FinalDate,AgencyName as Agency from Projects natural join ProjectHasPersonnel natural join Personnel natural join GroupHasMembers natural join Groups natural join FinancingAgencies where Year(InitialDate)<=2018 and Year(FinalDate)>=2018 and ParticipationTypeID=1 and Institute=0 group by USCProjectReference;")
	#
	IGFAEAR2018people <- dbGetQuery(con,statement="select Name,Surname,IF(Gender=0,'Male','Female') As Gender,If(Origin=0,'Spanish','Non Spanish') as Origin,BeginDate,If(EndDate is not null, EndDate,'-') as EndDate,CategoryName from (select * FROM (Select 0 as HistoryID,PersonnelID,CategoryID,Name,Surname,StartingDate as BeginDate,EndDate,Gender,Origin from IGFAEDB.Personnel) as P union (select HistoryID,H1.PersonnelID,H1.CategoryID,Name,Surname,BeginDate,H1.EndDate,Gender,Origin from IGFAEDB.History as H1 left join IGFAEDB.Personnel as P1 on P1.PersonnelID=H1.PersonnelID) order by Surname) as t natural join IGFAEDB.Category natural join IGFAEDB.IGFAECategories where BeginDate<'2018-01-01' and (EndDate>'2016-12-31' or EndDate is null) order by CategoryName")
	#
	## IGFAEAR2018sciPr <- dbGetQuery(con,statement="select ArticleTitle as Title,FullName,ISOAbbreviation,Volume,StartingPage,Year(`Date`),DOI,if(length(NumAuthors)<11,group_concat(distinct concat(AllAuthors,' et al.')),group_Concat(distinct If(PreferredNameForm is not null, PreferredNameForm,concat(Surname,', ',substring(Name,1,1))) separator '; ')) as Authors, group_concat(distinct GroupName separator ',') as `Group` ,group_concat(distinct Acronym separator ', ') as `Research Line`,NumAuthors from IGFAEDB.Articles natural join IGFAEDB.Journals natural join IGFAEDB.ArticleHasAuthors natural join IGFAEDB.Personnel Natural join IGFAEDB.GroupHasMembers natural join IGFAEDB.Groups where YEAR(`Date`)=2018 and ArticleTypeID in (2,4) and Institute=2 group by ArticleID")
	#
	## IGFAEAR2018sciPr01 <- dbGetQuery(con,statement="select ArticleTitle as Title,FullName,ISOAbbreviation,Volume,StartingPage,Year(`Date`),DOI,if(NumAuthors>11,concat(EASurname,',',EAName,' et al.'), AllAuthors) as AllAuthors,group_Concat(distinct If(PreferredNameForm is not null, PreferredNameForm,concat(Surname,',',substring(Name,1,1))) separator '; ') as IGFAEAuthors, group_concat(distinct GroupName separator ', ') as `Group` ,group_concat(distinct Acronym separator ', ') as `Research Line`,NumAuthors from Articles natural join Journals natural join ArticleHasExternalAuthors natural join ExternalAuthors natural join ArticleHasAuthors natural join Personnel Natural join GroupHasMembers natural join Groups where YEAR(`Date`)=2018 and ArticleTypeID in (2,4) and Institute=2 and ( (NumAuthors>11 and EAOrder=1) or NumAuthors<11) group by ArticleID")
	#
	## IGFAEAR2018sciPr02 <- dbGetQuery(con,statement="select ArticleTitle as Title,FullName,ISOAbbreviation,Volume,StartingPage,Year(`Date`),DOI,AllAuthors as AllAuthors,group_Concat(distinct If(PreferredNameForm is not null, PreferredNameForm,concat(Surname,', ',substring(Name,1,1))) separator '; ') as IGFAEAuthors, group_concat(distinct GroupName separator ', ') as `Group` ,group_concat(distinct Acronym separator ', ') as `Research Line`,NumAuthors from Articles natural join Journals natural join ArticleHasAuthors natural join Personnel Natural join GroupHasMembers natural join Groups where YEAR(`Date`)=2018 and ArticleTypeID in (2,4) and Institute=2 and ArticleID in ( 190,250,277,341) group by ArticleID")
	#
	## IGFAEAR2018sciPr03 <- dbGetQuery(con,statement="select ArticleTitle as Title,FullName,ISOAbbreviation,Volume,StartingPage,Year(`Date`),DOI,if(NumAuthors>11,concat(EASurname,', ',EAName,' et al.'), AllAuthors) as AllAuthors,group_Concat(distinct If(PreferredNameForm is not null, PreferredNameForm,concat(Surname,',',substring(Name,1,1))) separator '; ') as IGFAEAuthors, group_concat(distinct GroupName separator ', ') as `Group` ,group_concat(distinct Acronym separator ', ') as `Research Line`,NumAuthors from Articles natural join Journals natural join ArticleHasExternalAuthors natural join ExternalAuthors natural join ArticleHasAuthors natural join Personnel Natural join GroupHasMembers natural join Groups where YEAR(`Date`)=2018 and ArticleTypeID in (2,4) and Institute=2 and EAOrder=3 and ArticleID=209 group by ArticleID")
	#
	## IGFAEAR2018sciPr04 <- dbGetQuery(con,statement="select ArticleTitle as Title,FullName,ISOAbbreviation,Volume,StartingPage,Year(`Date`),DOI,if(NumAuthors>11,concat(EASurname,',',EAName,' et al.'), AllAuthors) as AllAuthors,group_Concat(distinct If(PreferredNameForm is not null, PreferredNameForm,concat(Surname,',',substring(Name,1,1))) separator '; ') as IGFAEAuthors, group_concat(distinct GroupName separator ', ') as `Group` ,group_concat(distinct Acronym separator ', ') as `Research Line`,NumAuthors from Articles natural join Journals natural join ArticleHasExternalAuthors natural join ExternalAuthors natural join ArticleHasAuthors natural join Personnel Natural join GroupHasMembers natural join Groups where YEAR(`Date`)=2018 and ArticleTypeID in (2,4) and Institute=2 and EAOrder=2 and ArticleID in (209, 212, 259, 294) group by ArticleID")
	#
	## IGFAEAR2018sciPrAll <- rbindlist(list(IGFAEAR2018sciPr01,IGFAEAR2018sciPr02,IGFAEAR2018sciPr03,IGFAEAR2018sciPr04))
	#
	## a <- rbind(IGFAEAR2018sciPr01,IGFAEAR2018sciPr02)
	## b <- rbind(a,IGFAEAR2018sciPr03)
	## IGFAEAR2018sciPrAllbyAddition <- rbind(b,IGFAEAR2018sciPr04)
	#
	IGFAEAR2018sciPr <- dbGetQuery(con,statement="select ArticleTitle as Title,FullName,ISOAbbreviation,Volume,StartingPage,Year(`Date`),DOI,if(NumAuthors>11,concat(EASurname,',',EAName,' et al.'), AllAuthors) as AllAuthors,group_Concat(distinct If(PreferredNameForm is not null, PreferredNameForm,concat(Surname,',',substring(Name,1,1))) separator '; ') as IGFAEAuthors, group_concat(distinct GroupName separator ', ') as `Group` ,group_concat(distinct Acronym separator ', ') as `Research Line`,NumAuthors from Articles natural join Journals natural join ArticleHasAuthors natural join Personnel Natural join GroupHasMembers natural join Groups left join ArticleHasExternalAuthors using (ArticleID) left join ExternalAuthors using (ExternalAuthorID) where YEAR(`Date`)=2018 and ArticleTypeID in (2,4) and Institute=2 and ( (NumAuthors>11 and EAOrder=(select min(EAOrder) from ArticleHasExternalAuthors where ArticleHasExternalAuthors.ArticleID=Articles.ArticleID) ) or NumAuthors<11 or EAOrder is null) group by Articles.ArticleID")
	#
	dbDisconnect(con)
	dbUnloadDriver(drv)
	#