package eu.dnetlib.openaire; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.text.ParseException; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.Arrays; import java.util.Date; import java.util.HashMap; import java.util.HashSet; import java.util.List; import java.util.Map; import java.util.Set; import java.util.UUID; import javax.sql.DataSource; import org.apache.log4j.Logger; import org.springframework.jdbc.datasource.DataSourceUtils; import org.springframework.transaction.annotation.Propagation; import org.springframework.transaction.annotation.Transactional; import com.googlecode.ehcache.annotations.Cacheable; import com.googlecode.ehcache.annotations.TriggersRemove; import com.googlecode.ehcache.annotations.When; import eu.dnetlib.openaire.thrift.BrowseField; import eu.dnetlib.openaire.thrift.ClaimedDocument; import eu.dnetlib.openaire.thrift.EvaluationStatistics; import eu.dnetlib.openaire.thrift.OrganizationInfo; import eu.dnetlib.openaire.thrift.OrganizationRepository; import eu.dnetlib.openaire.thrift.ProjectInfo; import eu.dnetlib.openaire.thrift.ProjectSearchCriteria; import eu.dnetlib.openaire.thrift.ProjectStatistics; import eu.dnetlib.openaire.thrift.PublicationStatistics; import eu.dnetlib.openaire.thrift.RepositoryInfo; import eu.dnetlib.openaire.thrift.ShortProjectInfo; import eu.dnetlib.openaire.thrift.ShortRepositoryInfo; import eu.dnetlib.openaire.thrift.StatLine; import eu.dnetlib.openaire.thrift.Statistics; import eu.dnetlib.openaire.thrift.Stats; import eu.dnetlib.openaire.thrift.TypedString; import eu.dnetlib.openaire.thrift.UserClaims; import eu.dnetlib.openaire.thrift.UserInfo; @Transactional(readOnly=true, propagation=Propagation.REQUIRED) public class Querynator { private static Logger logger = Logger.getLogger(Querynator.class); private DataSource dataSource = null; private Map statements = new HashMap(); private SimpleDateFormat dateFormat = new SimpleDateFormat("dd/MM/yyyy"); public Querynator() { statements.put("quickSearchRepositories", "SELECT datasourceId, english_name, longitude, latitude FROM datasources WHERE UPPER(english_name) like UPPER(?) or UPPER(official_name) like UPPER(?) order by english_name, official_name LIMIT ?"); statements.put("quickSearchProjects", "SELECT DISTINCT projectId, title, acronym, grant_agreement_number FROM projects WHERE UPPER(title) like UPPER(?) OR UPPER(acronym) like UPPER(?) or UPPER(grant_agreement_number) like UPPER(?) order by acronym, title LIMIT ?"); statements.put("searchOrganizations", "SELECT DISTINCT organizationid, organizations.legal_name, organizations.country_of_origin, longitude, latitude, organizations.web_site_url from datasources, organizations, datasources_organizations where organizations.organizationid = datasources_organizations.organization and datasources.datasourceid = datasources_organizations.datasource and UPPER(organizations.legal_name) like UPPER(?) order by organizations.legal_name LIMIT ?"); statements.put("getOrganization", "SELECT organizationid, legal_name, country_of_origin, web_site_url from organizations where organizationid = ?"); statements.put("getProjectInfo", "SELECT projectid, title, projects.acronym, ec_project_website, website, call_identifier, grant_agreement_number, start_date, end_date, sc39, subdivisions.name as subdivisionName, specificprogrammes.name as programmeName from projects, subdivisions, specificprogrammes where specificprogrammes.specificprogrammeid = subdivisions.hasspecificprogramme and subdivisions.subdivisionid = projects.fundedby and projectid = ?"); statements.put("getOrganizationRepositories", "SELECT datasources.official_name, datasources.logo_url, datasources.web_site_url, od_description, od_numberofitems, od_numberofitemsdate, od_subjects, od_languages, od_contenttypes, openairecompliant, datasources.optional1 from datasources, datasources_organizations where datasources.datasourceid = datasources_organizations.datasource and datasources_organizations.organization = ?"); statements.put("statsTotal", "select count(*) from results"); statements.put("statsAverage", "select avg(cnt1) from (select count(resultId) as cnt1, project from results, results_projects where results.resultid = results_projects.result group by results_projects.project) as cnt1"); statements.put("statsSc39Total", "select count(resultid) from results, results_projects, projects where results.resultid = results_projects.result and results_projects.project = projects.projectid and sc39=true"); statements.put("statsSc39Average", "select avg(cnt1) from (select count(resultid) as cnt1 from results, results_projects, projects where results.resultid = results_projects.result and results_projects.project = projects.projectid and sc39=true group by results_projects.project) as cnt1"); statements.put("statsNoResults", "select count(*) from projects where not exists (select * from results_projects where results_projects.project = projects.projectid )"); statements.put("statsSc39NoResults", "select count(*) from projects where not exists (select * from results_projects where results_projects.project = projects.projectid ) and sc39=true"); statements.put("browseProjectsStats", "select count(results.resultid), projects.acronym, subdivisions.acronym, specificprogrammes.acronym from results, results_projects, projects, subdivisions, specificprogrammes where specificprogrammes.specificprogrammeid=subdivisions.hasspecificprogramme and projects.fundedby=subdivisions.subdivisionid and results.resultid = results_projects.result and projects.projectid = results_projects.project group by specificprogrammes.acronym, subdivisions.acronym, projects.acronym "); statements.put("pubStats", "select total, after, open, avg_proj, avg_year, avg_open_proj, avg_proj_year, avg_open_proj_year from (select count(*) from results) as total, (select count(*) from results where publication_date > '2008-08-01') as after, (select count(*) from results where access_mode='OPEN' and publication_date > '2008-08-01') as open, (select avg(cnt) from (select count(*) as cnt from results_projects group by project) as ss) as avg_proj, (select avg(cnt) from (select count(*) as cnt from results_projects, results where results.resultid = results_projects.result and publication_date is not null group by date_part('year', publication_date) )as ss) as avg_year, (select avg(cnt) from (select count(*) as cnt from results_projects, results where results.resultid = results_projects.result and access_mode='OPEN' group by project) as ss) as avg_open_proj, (select avg(cnt) from (select count(*) as cnt from results_projects, results where results.resultid = results_projects.result and publication_date is not null group by project, date_part('year', publication_date)) as ss) as avg_proj_year, (select avg(cnt) from (select count(*) as cnt from results_projects, results where results.resultid = results_projects.result and access_mode='OPEN' and publication_date is not null group by project, date_part('year', publication_date)) as ss) as avg_open_proj_year"); statements.put("pubStatsProgrammes", "select count(results.resultid) as total, specificprogrammes.acronym, specificprogrammes.name, access_mode, projects.projectid from results, results_projects, projects, subdivisions, specificprogrammes where specificprogrammes.specificprogrammeid=subdivisions.hasspecificprogramme and projects.fundedby=subdivisions.subdivisionid and results.resultid = results_projects.result and projects.projectid = results_projects.project group by specificprogrammes.acronym, specificprogrammes.name, access_mode, projects.projectid order by specificprogrammes.acronym, access_mode"); statements.put("pubStatsDivisions", "select count(results.resultid) as total, subdivisions.acronym, subdivisions.name, access_mode, projects.projectid from results, results_projects, projects, subdivisions, specificprogrammes where specificprogrammes.specificprogrammeid=subdivisions.hasspecificprogramme and projects.fundedby=subdivisions.subdivisionid and results.resultid = results_projects.result and projects.projectid = results_projects.project group by subdivisions.acronym, subdivisions.name, access_mode, projects.projectid order by subdivisions.acronym, access_mode"); statements.put("pubStatsCountries", "select count(*), country_of_origin, countries.name, access_mode, results_projects.project from results, authorships, organizations, countries, results_projects where results.resultid = authorships.result and authorships.affiliation = organizations.organizationid and country_of_origin = countryid and results.resultid = results_projects.result group by country_of_origin, countries.name, access_mode, results_projects.project order by country_of_origin, countries.name, access_mode"); statements.put("pubStatsOrganizations", "select count(results.resultid) as total, COALESCE(NULLIF(organizations.legal_short_name, ''),organizations.legal_name), organizations.legal_name, access_mode, results_projects.project from results, authorships, organizations, results_projects where results_projects.result=results.resultid and results.resultid = authorships.result and authorships.affiliation = organizations.organizationid group by organizations.legal_name, organizations.legal_short_name, access_mode, results_projects.project order by organizations.legal_name, organizations.legal_short_name, access_mode"); statements.put("evalStatsAccess", "select count(*), access_mode, name from results, accessmodes where accessmodeid=access_mode group by access_mode, name"); statements.put("evalStatsSc39NoOpenAccessResults", "select count(*) from projects where not exists (select * from results_projects, results where results_projects.project = projects.projectid and results_projects.result = results.resultid and access_mode='OPEN') and sc39=true"); statements.put("projSc39Projects", "select count(*) from projects where sc39=true"); statements.put("projSc39NoOpenResults", "select count(*) from projects where not exists (select * from results, results_projects where projects.projectid = results_projects.project and results_projects.result = results.resultid) and projects.sc39=true"); statements.put("searchProjectsByProgramme", "SELECT distinct(projectid), title, projects.acronym, ec_project_website, website, call_identifier, grant_agreement_number, start_date, end_date, sc39, subdivisions.name as subdivisionName, specificprogrammes.name as programmeName from projects, subdivisions, specificprogrammes where specificprogrammes.specificprogrammeid = subdivisions.hasspecificprogramme and subdivisions.subdivisionid = projects.fundedby and specificprogrammes.acronym = ? and exists (select * from results_projects where project=projectid) "); statements.put("searchProjectsBySubdivision", "SELECT distinct(projectid), title, projects.acronym, ec_project_website, website, call_identifier, grant_agreement_number, start_date, end_date, sc39, subdivisions.name as subdivisionName, specificprogrammes.name as programmeName from projects, subdivisions, specificprogrammes where specificprogrammes.specificprogrammeid = subdivisions.hasspecificprogramme and subdivisions.subdivisionid = projects.fundedby and subdivisions.acronym = ? and exists (select * from results_projects where project=projectid) "); statements.put("searchProjectsByCountry", "SELECT distinct(projectid), title, projects.acronym, ec_project_website, website, call_identifier, grant_agreement_number, start_date, end_date, sc39, subdivisions.name as subdivisionName, specificprogrammes.name as programmeName from projects, subdivisions, specificprogrammes, results_projects, authorships, organizations where results_projects.project = projects.projectid and authorships.result = results_projects.result and authorships.affiliation = organizations.organizationid and organizations.country_of_origin = ? and specificprogrammes.specificprogrammeid = subdivisions.hasspecificprogramme and subdivisions.subdivisionid = projects.fundedby"); statements.put("searchProjectsByOrganization", "SELECT distinct(projectid), title, projects.acronym, ec_project_website, website, call_identifier, grant_agreement_number, start_date, end_date, sc39, subdivisions.name as subdivisionName, specificprogrammes.name as programmeName from projects, subdivisions, specificprogrammes, results_projects, authorships, organizations where results_projects.project = projects.projectid and authorships.result = results_projects.result and authorships.affiliation = organizations.organizationid and organizations.legal_name = ? and specificprogrammes.specificprogrammeid = subdivisions.hasspecificprogramme and subdivisions.subdivisionid = projects.fundedby"); statements.put("searchProjectsBySc39", "SELECT distinct(projectid), title, projects.acronym, ec_project_website, website, call_identifier, grant_agreement_number, start_date, end_date, sc39, subdivisions.name as subdivisionName, specificprogrammes.name as programmeName from projects, subdivisions, specificprogrammes where specificprogrammes.specificprogrammeid = subdivisions.hasspecificprogramme and subdivisions.subdivisionid = projects.fundedby and sc39 = ?"); statements.put("searchProjectsByNoarticlesOpenAccess", "SELECT distinct(projectid), title, projects.acronym, ec_project_website, website, call_identifier, grant_agreement_number, start_date, end_date, sc39, subdivisions.name as subdivisionName, specificprogrammes.name as programmeName from projects, subdivisions, specificprogrammes where specificprogrammes.specificprogrammeid = subdivisions.hasspecificprogramme and subdivisions.subdivisionid = projects.fundedby and sc39=true and not exists (select * from results, results_projects where results.resultid = results_projects.result and results_projects.project = projects.projectid and access_mode = 'OPEN')"); statements.put("searchProjectsByNoarticles", "SELECT distinct(projectid), title, projects.acronym, ec_project_website, website, call_identifier, grant_agreement_number, start_date, end_date, sc39, subdivisions.name as subdivisionName, specificprogrammes.name as programmeName from projects, subdivisions, specificprogrammes where specificprogrammes.specificprogrammeid = subdivisions.hasspecificprogramme and subdivisions.subdivisionid = projects.fundedby and sc39=true and not exists (select * from results, results_projects where results.resultid = results_projects.result and results_projects.project = projects.projectid)"); statements.put("openaireRepos", "SELECT datasources.official_name, datasources.logo_url, datasources.web_site_url, od_description, od_numberofitems, od_numberofitemsdate, od_subjects, od_languages, od_contenttypes, openairecompliant, datasources.optional1 from datasources where openairecompliant = true"); statements.put("getLanguageCode", "select languageid from languages where name = ?"); statements.put("selectUserId", "select email from users where email=?"); statements.put("getRepositoryId", "select datasourceid from datasources where official_name=? or english_name=?"); statements.put("selectPersonId", "select personid from persons where surname=? or name=?"); statements.put("claimedDocuments", "select results.resultid, results.title, results.description, results.embargo_end_date, results.keywords, results.publication_date, results.publisher, results.accessmode as accessmode, results.language as language, results.repositoryName, creations.date_of_creation from (select results.resultid, results.title, results.description, results.embargo_end_date, results.keywords, results.publication_date, results.publisher, results.accessmode as accessmode, results.language as language, instances.official_name as repositoryName from (select results.resultid, results.title, results.description, results.embargo_end_date, results.keywords, results.publication_date, results.publisher, results.accessmode as accessmode, languages.name as language from (select results.resultid, results.title, results.description, results.embargo_end_date, results.keywords, results.publication_date, results.publisher, accessmodes.name as accessmode, results.language from results left outer join accessmodes on results.access_mode=accessmodes.accessmodeid) as results left outer join languages on results.language=languages.languageid) as results left outer join (select datasources.official_name, instances.resource from instances left join datasources on instances.datasource=datasources.datasourceid) as instances on results.resultid = instances.resource) as results join (select creations.date_of_creation, creations.creation from creations where creations.user_email = ?) as creations on results.resultid=creations.creation"); statements.put("documentAuthors", "select name, surname from persons join authorships on persons.personid=authorships.author where result=?"); statements.put("documentProjects", "select projectid, acronym, title, grant_agreement_number from projects join results_projects on projects.projectid=results_projects.project where results_projects.result=?"); statements.put("allClaimedDocuments", "select results.resultid, results.title, results.description, results.embargo_end_date, results.keywords, results.publication_date, results.publisher, results.accessmode as accessmode, results.language as language, results.repositoryName, creations.date_of_creation, creations.firstname, creations.secondname, creations.email from (select results.resultid, results.title, results.description, results.embargo_end_date, results.keywords, results.publication_date, results.publisher, results.accessmode as accessmode, results.language as language, instances.official_name as repositoryName from ( select results.resultid, results.title, results.description, results.embargo_end_date, results.keywords, results.publication_date, results.publisher, results.accessmode as accessmode, languages.name as language from (select results.resultid, results.title, results.description, results.embargo_end_date, results.keywords, results.publication_date, results.publisher, accessmodes.name as accessmode, results.language from results left outer join accessmodes on results.access_mode=accessmodes.accessmodeid ) as results left outer join languages on results.language=languages.languageid) as results left outer join ( select datasources.official_name, instances.resource from instances left join datasources on instances.datasource=datasources.datasourceid) as instances on results.resultid = instances.resource) as results join ( select creations.date_of_creation, creations.creation, users.firstname as firstname, users.secondname as secondname, users.email as email from creations, users where creations.date_of_creation >= ? and creations.date_of_creation <= ? and creations.user_email=users.email) as creations on results.resultid=creations.creation"); } @Cacheable(cacheName="qyerynator") public Statistics getDocumentStatistics() throws SQLException { String total = "0"; String average = "0"; String sc39Total = "0"; String sc39Average = "0"; String noResults = "0"; String sc39NoResults = "0"; Connection conn = this.getConnection(); ResultSet rs = getPreparedStatement(conn, "statsTotal").executeQuery(); if (rs.next()) total = rs.getInt(1) + ""; rs.close(); rs = getPreparedStatement(conn, "statsAverage").executeQuery(); if (rs.next()) average = rs.getInt(1) + ""; rs.close(); rs = getPreparedStatement(conn, "statsSc39Total").executeQuery(); if (rs.next()) sc39Total = rs.getInt(1) + ""; rs.close(); rs = getPreparedStatement(conn, "statsSc39Average").executeQuery(); if (rs.next()) sc39Average = rs.getInt(1) + ""; rs.close(); rs = getPreparedStatement(conn, "statsNoResults").executeQuery(); if (rs.next()) noResults = rs.getInt(1) + ""; rs.close(); rs = getPreparedStatement(conn, "statsSc39NoResults").executeQuery(); if (rs.next()) sc39NoResults = rs.getInt(1) + ""; rs.close(); return new Statistics(total, average, sc39Total, sc39Average, noResults, sc39NoResults); } @Cacheable(cacheName="qyerynator") public List quickSearchProjects(String namePart, int limit) throws SQLException { logger.debug("Searching for projects whose title or acronym or grant number contains " + namePart); ArrayList list = new ArrayList(); String term = "%" + ((namePart != null)?namePart:"") + "%"; Connection conn = this.getConnection(); PreparedStatement stmt = getPreparedStatement(conn, "quickSearchProjects"); stmt.setString(1, term); stmt.setString(2, term); stmt.setString(3, term); stmt.setInt(4, limit); ResultSet rs = stmt.executeQuery(); while (rs.next()) { String id = rs.getString(1); String name = rs.getString(2); String acronym = rs.getString(3); String grant = rs.getString(4); list.add(new ShortProjectInfo(id, name, acronym, grant)); } rs.close(); stmt.close(); return list; } @Cacheable(cacheName="qyerynator") public List quickSearchRepositories(String namePart, int limit) throws SQLException { List list = new ArrayList(); String term = "%" + ((namePart != null)?namePart:"") + "%"; Connection conn = this.getConnection(); PreparedStatement stmt = this.getPreparedStatement(conn, "quickSearchRepositories"); stmt.setString(1, term); stmt.setString(2, term); stmt.setInt(3, limit); ResultSet rs = stmt.executeQuery(); while (rs.next()) { String id = rs.getString(1); String name = rs.getString(2); float lon = rs.getFloat(3); float lat = rs.getFloat(4); list.add(new ShortRepositoryInfo(id, name, lon, lat)); } rs.close(); stmt.close(); return list; } @Cacheable(cacheName="qyerynator") public List searchOrganizations(String namePart, int limit) throws SQLException { logger.debug("Searching for organizations whose name contains " + namePart + ", limit: " + limit); List list = new ArrayList(); String term = "%" + ((namePart != null)?namePart:"") + "%"; Connection conn = this.getConnection(); PreparedStatement stmt = getPreparedStatement(conn, "searchOrganizations"); stmt.setString(1, term); stmt.setInt(2, limit); ResultSet rs = stmt.executeQuery(); while (rs.next()) { String id = rs.getString(1); String name = rs.getString(2); String country = rs.getString(3); float lon = rs.getFloat(4); float lat = rs.getFloat(5); String siteUrl = rs.getString(6); list.add(new OrganizationInfo(id, name, country, siteUrl, lon, lat)); } rs.close(); stmt.close(); logger.debug("found a total of " + list.size() + " repos"); return list; } @Cacheable(cacheName="qyerynator") public OrganizationRepository getOrganizationRepositories(String orgId) throws SQLException { logger.debug("Searching for repositories of organization: " + orgId); List repos = new ArrayList(); OrganizationInfo org = new OrganizationInfo(); Connection conn = this.getConnection(); PreparedStatement stmt = getPreparedStatement(conn, "getOrganizationRepositories"); stmt.setString(1, orgId); ResultSet rs = stmt.executeQuery(); while (rs.next()) { String repoName = rs.getString(1); String logoUrl = rs.getString(2); String siteUrl = rs.getString(3); String count = rs.getString(5); if (count == null || count.trim().equals("")) count = "0"; repos.add(new RepositoryInfo(). setName(repoName). setImageUrl(logoUrl). setUrl(siteUrl). setDescription(rs.getString(4)). setDocumentCount(count). setDocumentCountDate(rs.getString(6)). setSubjects(rs.getString(7)). setLanguages(rs.getString(8)). setContent(rs.getString(9)). setOpenaireCompatible(rs.getBoolean(10)). setOAI_PMHUrl(rs.getString(11))); } rs.close(); stmt.close(); stmt = getPreparedStatement(conn, "getOrganization"); stmt.setString(1, orgId); rs = stmt.executeQuery(); if (rs.next()) { org.setId(rs.getString(1)); org.setName(rs.getString(2)); org.setCountry(rs.getString(3)); org.setSiteUrl(rs.getString(4)); } rs.close(); stmt.close(); return new OrganizationRepository(org, repos); } @Cacheable(cacheName="qyerynator") public ProjectInfo getProjectInfo(String projectId) throws SQLException { ProjectInfo pInfo = null; Connection conn = this.getConnection(); PreparedStatement stmt = getPreparedStatement(conn, "getProjectInfo"); stmt.setString(1, projectId); ResultSet rs = stmt.executeQuery(); if (rs.next()) { String id = rs.getString(1); String name = rs.getString(2); String acronym = rs.getString(3); String cordisInfo = rs.getString(4); String projectUrl = rs.getString(5); String callId = rs.getString(6); String grantNumber = rs.getString(7); Date startDate = rs.getDate(8); Date endDate = rs.getDate(9); boolean sc39 = rs.getBoolean(10); String subdivName = rs.getString(11); String programmeName = rs.getString(12); if (name == null || name.trim().equals("")) name = rs.getString(3); pInfo = new ProjectInfo(id, name, cordisInfo, acronym, projectUrl, callId, grantNumber, (startDate != null)?dateFormat.format(startDate):"-", (endDate != null)?dateFormat.format(endDate):"-", sc39, subdivName, programmeName); } else { pInfo = new ProjectInfo(); } rs.close(); stmt.close(); return pInfo; } @Cacheable(cacheName="qyerynator") public List getProjectBrowseData() throws SQLException { List list = new ArrayList(); Connection conn = this.getConnection(); PreparedStatement stmt = getPreparedStatement(conn, "browseProjectsStats"); ResultSet rs = stmt.executeQuery(); String pName = null; String dName = null; while (rs.next()) { if (!rs.getString(4).equals(pName)) { pName = rs.getString(4); dName = rs.getString(3); list.add(new BrowseField(pName, rs.getInt(1) , 0)); list.add(new BrowseField(dName, rs.getInt(1) , 1)); list.add(new BrowseField(rs.getString(2), rs.getInt(1), 2)); continue; } if (!rs.getString(3).equals(dName)) { dName = rs.getString(3); list.add(new BrowseField(dName, rs.getInt(1) , 1)); list.add(new BrowseField(rs.getString(2), rs.getInt(1), 2)); continue; } list.add(new BrowseField(rs.getString(2), rs.getInt(1), 2)); } rs.close(); stmt.close(); int psum = 0; int dsum = 0; for (int i = list.size() - 1; i >= 0 ; i--) { BrowseField bf = list.get(i); if (bf.depth == 2 ) dsum += bf.value; else if (bf.depth == 1 ) { bf.value = dsum; psum += dsum; dsum = 0; } else { bf.value = psum; psum = 0; } } return list; } @Cacheable(cacheName="qyerynator") public PublicationStatistics getPublicationStatistics() throws SQLException { logger.debug("Getting count stats"); PublicationStatistics stats = new PublicationStatistics(); Connection conn = this.getConnection(); PreparedStatement stmt = getPreparedStatement(conn, "pubStats"); ResultSet rs = stmt.executeQuery(); if (rs.next()) { stats.setAveragePerProject((int) Float.parseFloat(rs.getString("avg_proj").replace("(","").replace(")", "")) + ""); stats.setTotalAfter2008((int) Float.parseFloat(rs.getString("after").replace("(","").replace(")", "")) + ""); stats.setTotalOpenAccessAfter2008((int) Float.parseFloat(rs.getString("open").replace("(","").replace(")", "")) + ""); stats.setTotalPubs((int) Float.parseFloat(rs.getString("total").replace("(","").replace(")", "")) + ""); stats.setAveragePerYear((int) Float.parseFloat("0" + rs.getString("avg_year").replace("(","").replace(")", "")) + ""); stats.setAverageOpenPerProject((int) Float.parseFloat("0" + rs.getString("avg_open_proj").replace("(","").replace(")", "")) + ""); stats.setAveragePerProjectPerYear((int) Float.parseFloat("0" + rs.getString("avg_proj_year").replace("(","").replace(")", "")) + ""); stats.setAverageOpenPerProjectPerYear((int) Float.parseFloat("0" + rs.getString("avg_open_proj_year").replace("(","").replace(")", "")) + ""); } rs.close(); stmt.close(); logger.debug("getting programme stats"); stats.setProgrammeStats(this.readStats("pubStatsProgrammes")); logger.debug("Getting sub division stats"); stats.setSubdivisionStats(this.readStats("pubStatsDivisions")); logger.debug("Getting country stats"); stats.setCountryStats(readStats("pubStatsCountries")); logger.debug("Getting organization stats"); stats.setOrganizationStats(this.readStats("pubStatsOrganizations")); logger.debug("done"); return stats; } @Cacheable(cacheName="qyerynator") public EvaluationStatistics getEvaluationStatistics() throws SQLException { EvaluationStatistics stats = new EvaluationStatistics(); stats.setPubStats(this.getPublicationStatistics()); Connection conn = this.getConnection(); PreparedStatement stmt = getPreparedStatement(conn, "evalStatsAccess"); ResultSet rs = stmt.executeQuery(); int count = 0; List list = new ArrayList(); while (rs.next()) { if (rs.getString(2).equals("OPEN")) stats.setOpenAccessDocs(rs.getInt(1) + ""); else { count += rs.getInt(1); list.add(new StatLine(new TypedString(rs.getString(2), rs.getString(3)), Arrays.asList(new TypedString[] {new TypedString("value", rs.getString(1) + "")}))); } } rs.close(); stmt.close(); stats.setEmbargoDocs(new Stats(list)); stats.setTotalEmbargoDocs(count + ""); stmt = getPreparedStatement(conn, "statsSc39NoResults"); rs = stmt.executeQuery(); if (rs.next()) { stats.setSc39NoArticles(rs.getInt(1) + ""); } rs.close(); stmt.close(); stmt = getPreparedStatement(conn, "projSc39NoOpenResults"); rs = stmt.executeQuery(); if (rs.next()) { stats.setSc39NoOpenArticles(rs.getInt(1) + ""); } rs.close(); stmt.close(); return stats; } @Cacheable(cacheName="qyerynator") public ProjectStatistics getProjectStatistics() throws SQLException { ProjectStatistics stats = new ProjectStatistics(); stats.setPubStats(this.getPublicationStatistics()); Connection conn = getConnection(); PreparedStatement stmt = getPreparedStatement(conn, "statsSc39NoResults"); ResultSet rs = stmt.executeQuery(); if (rs.next()) stats.setSc39NoArticles(rs.getInt(1) + ""); rs.close(); stmt.close(); stmt = getPreparedStatement(conn, "projSc39NoOpenResults"); rs = stmt.executeQuery(); if (rs.next()) stats.setSc39NoOpenArticles(rs.getInt(1) + ""); rs.close(); stmt.close(); stmt = getPreparedStatement(conn, "projSc39Projects"); rs = stmt.executeQuery(); if (rs.next()) stats.setSc39Projects(rs.getInt(1) + ""); rs.close(); stmt.close(); return stats; } @Cacheable(cacheName="qyerynator") public List searchProjects(ProjectSearchCriteria crit, String value) throws SQLException { List res = new ArrayList(); PreparedStatement stmt = null; Connection conn = getConnection(); logger.debug("searching for projects by: " + crit + ", value: " + value); switch (crit) { case country: stmt = getPreparedStatement(conn, "searchProjectsByCountry"); break; case organization: stmt = getPreparedStatement(conn, "searchProjectsByOrganization"); break; case programme: stmt = getPreparedStatement(conn, "searchProjectsByProgramme"); break; case subdivision: stmt = getPreparedStatement(conn, "searchProjectsBySubdivision"); break; case sc39: stmt = getPreparedStatement(conn, "searchProjectsBySc39"); break; case noArticlesEmbargo: if (value != null && value.trim().toLowerCase().equals("open")) stmt = getPreparedStatement(conn, "searchProjectsByNoarticlesOpenAccess"); else stmt = getPreparedStatement(conn, "searchProjectsByNoarticles"); } if (crit.equals(ProjectSearchCriteria.sc39)) { boolean sc39 = (value != null)?(value.trim().toLowerCase().equals("true") || value.trim().toLowerCase().equals("yes") || value.trim().toLowerCase().equals("on")):false; stmt.setBoolean(1, sc39); } else if (!crit.equals(ProjectSearchCriteria.noArticlesEmbargo)){ stmt.setString(1, value); } ResultSet rs = stmt.executeQuery(); while (rs.next()) { String id = rs.getString(1); String name = rs.getString(2); String acronym = rs.getString(3); String cordisInfo = rs.getString(4); String projectUrl = rs.getString(5); String callId = rs.getString(6); String grantNumber = rs.getString(7); Date startDate = rs.getDate(8); Date endDate = rs.getDate(9); boolean sc39 = rs.getBoolean(10); String subdivName = rs.getString(11); String programmeName = rs.getString(12); if (name == null || name.trim().equals("")) name = rs.getString(3); res.add(new ProjectInfo( id, name, cordisInfo, acronym, projectUrl, callId, grantNumber, (startDate != null?dateFormat.format(startDate):"-"), (endDate != null?dateFormat.format(endDate):"-"), sc39, subdivName, programmeName)); } rs.close(); stmt.close(); logger.debug("done"); return res; } @Cacheable(cacheName="qyerynator") public List getOpenaireRepositories() throws SQLException { List res = new ArrayList(); Connection conn = getConnection(); PreparedStatement stmt = getPreparedStatement(conn, "openaireRepos"); ResultSet rs = stmt.executeQuery(); while (rs.next()) { String repoName = rs.getString(1); String logoUrl = rs.getString(2); String siteUrl = rs.getString(3); String count = rs.getString(5); if (count == null || count.trim().equals("")) count = "0"; res.add(new RepositoryInfo(). setName(repoName). setImageUrl(logoUrl). setUrl(siteUrl). setDescription(rs.getString(4)). setDocumentCount(count). setDocumentCountDate(rs.getString(6)). setSubjects(rs.getString(7)). setLanguages(rs.getString(8)). setContent(rs.getString(9)). setOpenaireCompatible(rs.getBoolean(10)). setOAI_PMHUrl(rs.getString(11))); } rs.close(); stmt.close(); return res; } @Cacheable(cacheName="qyerynator.claim") public List getClaimedDocuments(String userEmail) throws SQLException { List res = new ArrayList(); Connection conn = getConnection(); PreparedStatement stmt = getPreparedStatement(conn, "claimedDocuments"); stmt.setString(1, userEmail); ResultSet rs = stmt.executeQuery(); while (rs.next()) { String docId = rs.getString(1); List authors = this.getDocumentAuthors(docId); List projects = this.getDocumentProjects(docId); ClaimedDocument doc = new ClaimedDocument(); /* 1 results.resultid, 2 results.title, 3 results.description, 4 results.embargo_end_date, 5 results.keywords, 6 results.publication_date, 7 results.publisher, 8 results.accessmode as accessmode, 9 results.language as language, 10 results.repositoryName, 11 creations.date_of_creation */ doc.setAccessMode(rs.getString(8)); doc.setAuthors(authors); doc.setClaimDate((rs.getDate(11)!=null)?rs.getDate(11).toString():""); doc.setDescription(rs.getString(3)); doc.setDocumentId(rs.getString(1)); doc.setEmbargoEndDate((rs.getDate(4)!=null)?rs.getDate(4).toString():""); doc.setLanguage(rs.getString(9)); doc.setProjects(projects); doc.setPublicationDate((rs.getDate(6)!=null)?rs.getDate(6).toString():""); doc.setPublisher(rs.getString(7)); doc.setRepositoryName(rs.getString(10)); doc.setSubjects(rs.getString(5)); doc.setTitle(rs.getString(2)); res.add(doc); } rs.close(); stmt.close(); return res; } @Transactional(readOnly=false, rollbackFor=SQLException.class) @TriggersRemove(cacheName="qyerynator.claim", removeAll=true, when=When.AFTER_METHOD_INVOCATION) public void claimDocuments(UserInfo user, List documents) throws SQLException { // first, add the user String userId; Connection conn = getConnection(); conn.createStatement().execute("SET CONSTRAINTS ALL DEFERRED"); userId = this.selectOrInsertUserId(conn, user); //process docs for (ClaimedDocument doc:documents) { List authorIds = new ArrayList(); // authors for (String author:doc.getAuthors()) authorIds.add(this.selectOrInsertPersonId(conn, author)); // language String langId = this.getLanguageCode(conn, doc.getLanguage()); //repository String repoId = this.getRepositoryId(conn, doc.getRepositoryName()); // publication date Date d = this.parseDate(doc.getPublicationDate()); java.sql.Date publicationDate = (d != null)?new java.sql.Date(d.getTime()):null; // embargo date java.sql.Date embargoDate = null; if (doc.getAccessMode().equals("EMBARGO")) { d = this.parseDate(doc.getEmbargoEndDate()); embargoDate = (d != null)?new java.sql.Date(d.getTime()):null; } // collect document data Map valueMap = new HashMap(); String resultId = UUID.randomUUID().toString(); valueMap.put("resultid", "'" + resultId + "'"); valueMap.put("access_mode", "'" + doc.getAccessMode() + "'"); valueMap.put("title", "'" + doc.getTitle().replace("'", "''") + "'"); if (doc.getDescription() != null && !doc.getDescription().equals("")) valueMap.put("description", "'" + doc.getDescription().trim().replace("'", "''") +"'"); if (embargoDate != null) valueMap.put("embargo_end_date", "'" + embargoDate.toString() + "'"); if (doc.getSubjects() != null && !doc.getSubjects().trim().equals("")) valueMap.put("keywords", "'" + doc.getSubjects().trim().replace("'", "''") + "'"); if (publicationDate != null) valueMap.put("publication_date", "'" + publicationDate.toString() + "'"); if (doc.getPublisher() != null && !doc.getPublisher().trim().equals("")) valueMap.put("publisher", "'" + doc.getPublisher().replace("'", "''") + "'"); if (langId != null) valueMap.put("language", "'" + langId + "'"); // build the query String namesPart = ""; String valuesPart = ""; int counter = 0; for (String key:valueMap.keySet()) { String value = valueMap.get(key); if (counter++ > 0) { namesPart += ", "; valuesPart += ", "; } namesPart += key; valuesPart += value; } String query = "insert into results (" + namesPart + ") values (" + valuesPart + ")"; logger.debug("result insert stmt: " + query); Statement stmt = conn.createStatement(); stmt.executeUpdate(query); stmt.close(); // add authorships (author - result) for (String authorId:authorIds) { query = "insert into authorships (authorshipid, author, result) values ('" + UUID.randomUUID().toString() + "', '" + authorId + "', '" + resultId + "')"; stmt = conn.createStatement(); logger.debug("authorship query: " + query); stmt.executeUpdate(query); stmt.close(); } // add instance (repository-result) if (repoId != null) { query = "insert into instances (instanceid, datasource, resource) values ('" + UUID.randomUUID().toString() + "', '" + repoId + "', '" + resultId + "')"; stmt = conn.createStatement(); logger.debug("instence query: " + query); stmt.executeUpdate(query); stmt.close(); } // add results_projects for (ShortProjectInfo pInfo:doc.getProjects()) { String projectId = pInfo.getProjectId(); query = "insert into results_projects (result, project) values ('" + resultId + "', '" + projectId + "')"; stmt = conn.createStatement(); logger.debug("Result-project: " + query); stmt.executeUpdate(query); stmt.close(); } // add creation (user-result) String now = new java.sql.Date(System.currentTimeMillis()).toString(); query = "insert into creations (date_of_creation, last_update_date, user_email, creation) values " + "('" + now + "', '" + now + "', '" + userId + "', '" + resultId + "')"; stmt = conn.createStatement(); logger.debug("creation query: " + query); stmt.executeUpdate(query); stmt.close(); } } @Cacheable(cacheName="qyerynator.claim") public List getAllClaimedDocuments(String from, String to) throws SQLException { logger.debug("Returning claimed documents from " + from + " to " + to); List res = new ArrayList(); Map claimMap = new HashMap(); Connection conn = getConnection(); PreparedStatement stmt = getPreparedStatement(conn, "allClaimedDocuments"); stmt.setDate(1, new java.sql.Date(this.parseDate(from).getTime())); stmt.setDate(2, new java.sql.Date(this.parseDate(to).getTime())); ResultSet rs = stmt.executeQuery(); while (rs.next()) { String email = rs.getString(14); UserClaims claims = claimMap.get(email); ClaimedDocument doc = new ClaimedDocument(); String docId = rs.getString(1); List authors = this.getDocumentAuthors(docId); List projects = this.getDocumentProjects(docId); if (claims == null) { claims = new UserClaims(); claims.setUserInfo(new UserInfo(email, rs.getString(12), rs.getString(13))); claims.setClaims(new ArrayList()); claimMap.put(email, claims); } doc.setAccessMode(rs.getString(8)); doc.setAuthors(authors); doc.setClaimDate((rs.getDate(11)!=null)?rs.getDate(11).toString():""); doc.setDescription(rs.getString(3)); doc.setDocumentId(rs.getString(1)); doc.setEmbargoEndDate((rs.getDate(4)!=null)?rs.getDate(4).toString():""); doc.setLanguage(rs.getString(9)); doc.setProjects(projects); doc.setPublicationDate((rs.getDate(6)!=null)?rs.getDate(6).toString():""); doc.setPublisher(rs.getString(7)); doc.setRepositoryName(rs.getString(10)); doc.setSubjects(rs.getString(5)); doc.setTitle(rs.getString(2)); claims.getClaims().add(doc); } res.addAll(claimMap.values()); return res; } @TriggersRemove(cacheName={"qyerynator.claim", "qyerynator"}, removeAll=true, when=When.AFTER_METHOD_INVOCATION) public void resetCache() { logger.debug("Reseting cache"); } public void setDataSource(DataSource dataSource) { this.dataSource = dataSource; } private List getDocumentProjects(String docId) throws SQLException { List res = new ArrayList(); Connection conn = getConnection(); PreparedStatement stmt = getPreparedStatement(conn, "documentProjects"); stmt.setString(1, docId); ResultSet rs = stmt.executeQuery(); while (rs.next()) { res.add(new ShortProjectInfo(rs.getString(1), rs.getString(3), rs.getString(2), rs.getString(4))); } rs.close(); stmt.close(); return res; } private List getDocumentAuthors(String docId) throws SQLException { List res = new ArrayList(); Connection conn = getConnection(); PreparedStatement stmt = getPreparedStatement(conn, "documentAuthors"); stmt.setString(1, docId); ResultSet rs = stmt.executeQuery(); while (rs.next()) { String name = rs.getString(1); String surname = rs.getString(2); if (name == null || name.trim().equals("")) res.add(surname); else res.add(surname + ", " + name); } rs.close(); stmt.close(); return res; } private Stats readStats(String statementKey) throws SQLException { Connection conn = this.getConnection(); PreparedStatement stmt = getPreparedStatement(conn, statementKey); ResultSet rs = stmt.executeQuery(); Stats cstats = new Stats(new ArrayList()); String cName = null; String cDesc = null; int total = 0; int totalOpen = 0; Set projIds = new HashSet(); while (rs.next()) { if (cName == null) { cName = rs.getString(2); cDesc = rs.getString(3); } if (!rs.getString(2).equals(cName)) { // wrap up previous programme values TypedString name = new TypedString(cName, cDesc); List values = new ArrayList(); values.add(new TypedString("Total", total + "")); values.add(new TypedString("Open Access", totalOpen + "")); values.add(new TypedString("Open Access (%)", Math.round(totalOpen*100/total) + "")); values.add(new TypedString("Projects", projIds.size() + "")); values.add(new TypedString("Non Open Access", total - totalOpen + "")); cstats.getLines().add(new StatLine(name, values)); // reset counters total = 0; totalOpen = 0; cName = rs.getString(2); cDesc = rs.getString(3); projIds.clear(); } projIds.add(rs.getString(5)); total += rs.getInt(1); boolean openAccess = (rs.getString(4) != null && rs.getString(4).equals("OPEN")); totalOpen += openAccess?rs.getInt(1):0; } rs.close(); stmt.close(); if (cstats.getLines().size() > 0) { if (!cName.equals(cstats.getLines().get(cstats.getLines().size() - 1).getName().getLabel())) { TypedString name = new TypedString(cName, cDesc); List values = new ArrayList(); values.add(new TypedString("Total", total + "")); values.add(new TypedString("Open Access", totalOpen + "")); values.add(new TypedString("Open Access (%)", Math.round(totalOpen*100/total) + "")); values.add(new TypedString("Projects", projIds.size() + "")); values.add(new TypedString("Non Open Access", total - totalOpen + "")); cstats.getLines().add(new StatLine(name, values)); } } return cstats; } private Connection getConnection() { return DataSourceUtils.getConnection(dataSource); } private String getLanguageCode(Connection conn, String name) throws SQLException { String lang = null; if (name != null && !name.trim().equals("")) { PreparedStatement stmt = getPreparedStatement(conn, "getLanguageCode"); stmt.setString(1, name); ResultSet rs = stmt.executeQuery(); if (rs.next()) lang = rs.getString(1); rs.close(); stmt.close(); } return lang; } private String getRepositoryId(Connection conn, String name) throws SQLException { if (name == null || name.trim().equals("")) return null; String id = null; PreparedStatement stmt = getPreparedStatement(conn, "getRepositoryId"); stmt.setString(1, name); stmt.setString(2, name); ResultSet rs = stmt.executeQuery(); if (rs.next()) id = rs.getString(1); rs.close(); stmt.close(); return id; } private String selectOrInsertPersonId(Connection conn, String name) throws SQLException { String id = null; PreparedStatement pstmt = getPreparedStatement(conn, "selectPersonId"); pstmt.setString(1, name); pstmt.setString(2, name); ResultSet rs = pstmt.executeQuery(); if (rs.next()) { id = rs.getString(1); } else { Statement stmt = conn.createStatement(); String personId = UUID.randomUUID().toString(); String query = "insert into persons (personid, surname) values ('" + personId + "', '" + name.replace("'", "''") + "')"; logger.debug("Executing: " + query); stmt.executeUpdate(query); stmt.close(); id = personId; } rs.close(); pstmt.close(); return id; } private String selectOrInsertUserId(Connection conn, UserInfo user) throws SQLException { PreparedStatement pstmt = getPreparedStatement(conn, "selectUserId"); pstmt.setString(1, user.getEmail()); ResultSet rs = pstmt.executeQuery(); if (!rs.next()) { Statement stmt = conn.createStatement(); String query = "insert into users (email, secondname) values ('" + user.getEmail().replace("'", "''") + "', '" + user.getLastName().replace("'", "''") + "')"; logger.debug("Executing: " + query); stmt.executeUpdate(query); stmt.close(); } rs.close(); pstmt.close(); return user.getEmail(); } private Date parseDate(String date) { String[] formats = new String[]{"yyyy-MM-dd", "dd-MM-yyyy", "dd/MM/yyyy", "yyyy"}; Date res = null; if (date == null) return null; for (String format:formats) { SimpleDateFormat sf = new SimpleDateFormat(format); try { res = sf.parse(date); } catch (ParseException e) { } if (res != null) return res; } try { res = this.parseIsoDate(date); } catch (ParseException e) { } return res; } private Date parseIsoDate(String date) throws ParseException { SimpleDateFormat df = new SimpleDateFormat( "yyyy-MM-dd'T'HH:mm:ssz" ); //this is zero time so we need to add that TZ indicator for if ( date.endsWith( "Z" ) ) { date = date.substring( 0, date.length() - 1) + "GMT-00:00"; } else { int inset = 6; String s0 = date.substring( 0, date.length() - inset ); String s1 = date.substring( date.length() - inset, date.length() ); date = s0 + "GMT" + s1; } return df.parse(date); } private PreparedStatement getPreparedStatement(Connection conn, String key) throws SQLException { String query = statements.get(key); return conn.prepareStatement(query); } }