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, sum(CASE WHEN r.bestlicence='Open Access' THEN 1 else 0 END) AS open_access, sum(CASE WHEN r.bestlicence='Embargo' THEN 1 else 0 END) AS embargo, sum(CASE WHEN r.bestlicence='Restricted' THEN 1 else 0 END) AS restricted, sum(CASE WHEN r.bestlicence='Closed Access' THEN 1 else 0 END) AS closed_access FROM result_concepts rc, result r WHERE rc.id=r.id AND (rc.concept=? OR rc.concept LIKE concat(? , '::%')) 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
|
StatsByAccessMode others = new StatsByAccessMode();
|
69
|
|
70
|
rs = st.executeQuery();
|
71
|
while (rs.next()) {
|
72
|
switch (rs.getString(1)) {
|
73
|
case "publication":
|
74
|
publications = new StatsByAccessMode(rs.getInt(2), rs.getInt(3), rs.getInt(4), rs.getInt(5), rs.getInt(6));
|
75
|
break;
|
76
|
case "software":
|
77
|
software = new StatsByAccessMode(rs.getInt(2), rs.getInt(3), rs.getInt(4), rs.getInt(5), rs.getInt(6));
|
78
|
break;
|
79
|
case "dataset":
|
80
|
datasets = new StatsByAccessMode(rs.getInt(2), rs.getInt(3), rs.getInt(4), rs.getInt(5), rs.getInt(6));
|
81
|
break;
|
82
|
case "other":
|
83
|
others = new StatsByAccessMode(rs.getInt(2), rs.getInt(3), rs.getInt(4), rs.getInt(5), rs.getInt(6));
|
84
|
break;
|
85
|
}
|
86
|
}
|
87
|
rs.close();
|
88
|
st.close();
|
89
|
|
90
|
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 concat(?, '::%')) GROUP BY r.type;");
|
91
|
st.setString(1, community_id);
|
92
|
st.setString(2, community_id);
|
93
|
|
94
|
int projects = 0;
|
95
|
rs = st.executeQuery();
|
96
|
while (rs.next()) {
|
97
|
projects += rs.getInt(2);
|
98
|
switch (rs.getString(1)) {
|
99
|
case "publication":
|
100
|
publications.setProjects(rs.getInt(2));
|
101
|
break;
|
102
|
case "software":
|
103
|
software.setProjects(rs.getInt(2));
|
104
|
break;
|
105
|
case "dataset":
|
106
|
datasets.setProjects(rs.getInt(2));
|
107
|
break;
|
108
|
case "other":
|
109
|
others.setProjects(rs.getInt(2));
|
110
|
break;
|
111
|
}
|
112
|
}
|
113
|
rs.close();
|
114
|
st.close();
|
115
|
|
116
|
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=concat(?, '::virtual');");
|
117
|
st.setString(1, community_id);
|
118
|
|
119
|
int virtual_organizations = 0;
|
120
|
rs = st.executeQuery();
|
121
|
while (rs.next()) {
|
122
|
virtual_organizations = rs.getInt(1);
|
123
|
}
|
124
|
|
125
|
Community community = new Community(publications, datasets, software, others, projects, virtual_organizations);
|
126
|
jedis.put(redisKey, "persistent", "false");
|
127
|
jedis.put(redisKey, "result", new ObjectMapper().writeValueAsString(community));
|
128
|
|
129
|
return new Result("OK", "200", community);
|
130
|
}
|
131
|
} catch (Exception e) {
|
132
|
log.error(e);
|
133
|
} finally {
|
134
|
DbUtils.closeQuietly(rs);
|
135
|
DbUtils.closeQuietly(st);
|
136
|
DbUtils.closeQuietly(connection);
|
137
|
}
|
138
|
return new Result("Not Found", "404", null);
|
139
|
}
|
140
|
}
|