1
|
package eu.dnetlib.statsapi.repositories;
|
2
|
|
3
|
import com.fasterxml.jackson.databind.ObjectMapper;
|
4
|
|
5
|
import eu.dnetlib.statsapi.domain.Community;
|
6
|
import eu.dnetlib.statsapi.domain.Result;
|
7
|
import eu.dnetlib.statsapi.domain.StatsByAccessMode;
|
8
|
|
9
|
import org.apache.commons.dbutils.DbUtils;
|
10
|
import org.apache.log4j.Logger;
|
11
|
import org.springframework.data.redis.core.HashOperations;
|
12
|
import org.springframework.data.redis.core.RedisTemplate;
|
13
|
import org.springframework.stereotype.Repository;
|
14
|
|
15
|
import java.sql.Connection;
|
16
|
import java.sql.PreparedStatement;
|
17
|
import java.sql.ResultSet;
|
18
|
import java.util.Set;
|
19
|
|
20
|
import javax.sql.DataSource;
|
21
|
|
22
|
@Repository
|
23
|
public class CommunityRepository {
|
24
|
|
25
|
private DataSource statsDB;
|
26
|
|
27
|
private RedisTemplate<String, String> redisTemplate;
|
28
|
|
29
|
private HashOperations<String, String, String> jedis;
|
30
|
|
31
|
private final Logger log = Logger.getLogger(this.getClass());
|
32
|
|
33
|
public CommunityRepository(DataSource statsDB, RedisTemplate<String, String> redisTemplate) {
|
34
|
this.statsDB = statsDB;
|
35
|
this.redisTemplate = redisTemplate;
|
36
|
this.jedis = this.redisTemplate.opsForHash();
|
37
|
}
|
38
|
|
39
|
public Result refreshCommunities() {
|
40
|
RedisTemplate<String, String> jd = redisTemplate;
|
41
|
Set<String> keys = jd.keys("community:*");
|
42
|
for (String key : keys) {
|
43
|
jd.delete(key);
|
44
|
getCommunity(key.substring(key.lastIndexOf(":") + 1));
|
45
|
}
|
46
|
return new Result("OK", "200", null);
|
47
|
}
|
48
|
|
49
|
public Result getCommunity(String community_id) {
|
50
|
Connection connection = null;
|
51
|
PreparedStatement st = null;
|
52
|
ResultSet rs = null;
|
53
|
|
54
|
String redisKey = "community:" + community_id;
|
55
|
String redisResponse = jedis.get(redisKey, "result");
|
56
|
try {
|
57
|
if (redisResponse != null) {
|
58
|
return new Result("OK", "200", new ObjectMapper().readValue(redisResponse, Community.class));
|
59
|
} else {
|
60
|
connection = statsDB.getConnection();
|
61
|
st = connection.prepareStatement("SELECT r.type, COUNT(DISTINCT r.id) AS total, COUNT(DISTINCT CASE WHEN r.bestlicense='Open Access' THEN r.id END) AS open_access, COUNT(DISTINCT CASE WHEN r.bestlicense='Embargo' THEN r.id END) AS embargo, COUNT(DISTINCT CASE WHEN r.bestlicense='Restricted' THEN r.id END) AS restricted, COUNT(DISTINCT CASE WHEN r.bestlicense='Closed Access' THEN r.id END) AS closed_access FROM result_concepts rc, result r WHERE rc.id=r.id AND (rc.concept=? OR rc.concept LIKE ? || '::%') GROUP BY r.type;");
|
62
|
st.setString(1, community_id);
|
63
|
st.setString(2, community_id);
|
64
|
|
65
|
StatsByAccessMode publications = new StatsByAccessMode();
|
66
|
StatsByAccessMode software = new StatsByAccessMode();
|
67
|
StatsByAccessMode datasets = new StatsByAccessMode();
|
68
|
|
69
|
rs = st.executeQuery();
|
70
|
while (rs.next()) {
|
71
|
switch (rs.getString(1)) {
|
72
|
case "publication":
|
73
|
publications = new StatsByAccessMode(rs.getInt(2), rs.getInt(3), rs.getInt(4), rs.getInt(5), rs.getInt(6));
|
74
|
break;
|
75
|
case "software":
|
76
|
software = new StatsByAccessMode(rs.getInt(2), rs.getInt(3), rs.getInt(4), rs.getInt(5), rs.getInt(6));
|
77
|
break;
|
78
|
case "dataset":
|
79
|
datasets = new StatsByAccessMode(rs.getInt(2), rs.getInt(3), rs.getInt(4), rs.getInt(5), rs.getInt(6));
|
80
|
break;
|
81
|
}
|
82
|
}
|
83
|
rs.close();
|
84
|
st.close();
|
85
|
|
86
|
st = connection.prepareStatement("SELECT r.type, COUNT(DISTINCT pr.id) FROM result r, result_concepts rc, project_results pr WHERE r.id=rc.id AND r.id=pr.result AND (rc.concept=? OR rc.concept LIKE ? || '::%') GROUP BY r.type;");
|
87
|
st.setString(1, community_id);
|
88
|
st.setString(2, community_id);
|
89
|
|
90
|
int projects = 0;
|
91
|
rs = st.executeQuery();
|
92
|
while (rs.next()) {
|
93
|
projects += rs.getInt(2);
|
94
|
switch (rs.getString(1)) {
|
95
|
case "publication":
|
96
|
publications.setProjects(rs.getInt(2));
|
97
|
break;
|
98
|
case "software":
|
99
|
software.setProjects(rs.getInt(2));
|
100
|
break;
|
101
|
case "dataset":
|
102
|
datasets.setProjects(rs.getInt(2));
|
103
|
break;
|
104
|
}
|
105
|
}
|
106
|
rs.close();
|
107
|
st.close();
|
108
|
|
109
|
st = connection.prepareStatement("SELECT COUNT(DISTINCT c.name) FROM result_concepts rc, concept c, category cat WHERE c.id=rc.concept AND cat.id=c.category AND cat.id=? || '::virtual';");
|
110
|
st.setString(1, community_id);
|
111
|
|
112
|
int virtual_organizations = 0;
|
113
|
rs = st.executeQuery();
|
114
|
while (rs.next()) {
|
115
|
virtual_organizations = rs.getInt(1);
|
116
|
}
|
117
|
|
118
|
Community community = new Community(publications, datasets, software, projects, virtual_organizations);
|
119
|
jedis.put(redisKey, "persistent", "false");
|
120
|
jedis.put(redisKey, "result", new ObjectMapper().writeValueAsString(community));
|
121
|
|
122
|
return new Result("OK", "200", community);
|
123
|
}
|
124
|
} catch (Exception e) {
|
125
|
log.error(e.getMessage());
|
126
|
} finally {
|
127
|
DbUtils.closeQuietly(rs);
|
128
|
DbUtils.closeQuietly(st);
|
129
|
DbUtils.closeQuietly(connection);
|
130
|
}
|
131
|
return new Result("Not Found", "404", null);
|
132
|
}
|
133
|
}
|