1
|
package eu.dnetlib.data.claims.handler;
|
2
|
|
3
|
import com.google.gson.*;
|
4
|
import eu.dnetlib.data.claims.entity.Metrics;
|
5
|
import eu.dnetlib.data.claims.sql.SQLStoreException;
|
6
|
import eu.dnetlib.data.claims.sql.SqlDAO;
|
7
|
import eu.dnetlib.data.claims.utils.QueryGenerator;
|
8
|
import org.apache.logging.log4j.LogManager;
|
9
|
import org.apache.logging.log4j.Logger;
|
10
|
import org.postgresql.util.PGobject;
|
11
|
import org.springframework.scheduling.annotation.EnableScheduling;
|
12
|
import org.springframework.scheduling.annotation.Scheduled;
|
13
|
import org.springframework.stereotype.Service;
|
14
|
|
15
|
import java.sql.ResultSet;
|
16
|
import java.sql.SQLException;
|
17
|
import java.util.ArrayList;
|
18
|
import java.util.Date;
|
19
|
import java.util.List;
|
20
|
|
21
|
@EnableScheduling
|
22
|
@Service
|
23
|
public class MetricsHandler {
|
24
|
SqlDAO sqlDAO = null;
|
25
|
QueryGenerator queryGenerator = null;
|
26
|
|
27
|
private static final Logger logger = LogManager.getLogger(MetricsHandler.class);
|
28
|
|
29
|
public int countEUProjects() throws SQLStoreException, SQLException {
|
30
|
logger.info("Counting EU projects...");
|
31
|
ArrayList<Object> params = new ArrayList<>();
|
32
|
String query = queryGenerator.generateSelectNumOfEUProjectsQuery();
|
33
|
ResultSet rs = sqlDAO.executePreparedQuery(query, params);
|
34
|
int res = fetchCountByResultSet(rs);
|
35
|
|
36
|
return res;
|
37
|
}
|
38
|
|
39
|
public int countCountriesFromClaimedBy() throws SQLStoreException, SQLException {
|
40
|
logger.info("Counting countries for people who claimed...");
|
41
|
ArrayList<Object> params = new ArrayList<>();
|
42
|
String query = queryGenerator.generateSelectNumOfCountriesQuery();
|
43
|
ResultSet rs = sqlDAO.executePreparedQuery(query, params);
|
44
|
int res = fetchCountByResultSet(rs);
|
45
|
|
46
|
return res;
|
47
|
}
|
48
|
|
49
|
public int countUniqueResearchResults() throws SQLStoreException, SQLException {
|
50
|
logger.info("Counting unique research results claimed...");
|
51
|
ArrayList<Object> params = new ArrayList<>();
|
52
|
String query = queryGenerator.generateSelectNumOfUniqueResearchResultsQuery();
|
53
|
ResultSet rs = sqlDAO.executePreparedQuery(query, params);
|
54
|
int res = fetchCountByResultSet(rs);
|
55
|
|
56
|
return res;
|
57
|
}
|
58
|
|
59
|
// every day at midnight
|
60
|
@Scheduled(cron = "0 0 0 * * ?")
|
61
|
// every 5 mins for testing
|
62
|
// @Scheduled(cron = "0 0/5 * * * *")
|
63
|
public void calculateMetrics() throws SQLStoreException, Exception {
|
64
|
logger.info("Calculating metrics...");
|
65
|
String id = null;
|
66
|
String query = queryGenerator.generateSelectAndBuildMetricsQuery();
|
67
|
ResultSet rs1 = sqlDAO.executePreparedQuery(query);
|
68
|
Metrics calculatedMetrics = fetchMetricsByResultSet(rs1, false);
|
69
|
|
70
|
ArrayList<Object> params = new ArrayList<>();
|
71
|
query = queryGenerator.generateUpdateMetricsQuery(calculatedMetrics, params);
|
72
|
sqlDAO.executeUpdateQuery(query, params);
|
73
|
}
|
74
|
|
75
|
public Metrics getMetrics() throws SQLStoreException, SQLException {
|
76
|
logger.info("Fetching metrics...");
|
77
|
String query = queryGenerator.generateSelectMetricsQuery();
|
78
|
ResultSet rs = sqlDAO.executePreparedQuery(query);
|
79
|
Metrics res = fetchMetricsByResultSet(rs, true);
|
80
|
return res;
|
81
|
}
|
82
|
|
83
|
private Metrics fetchMetricsByResultSet(ResultSet rs, boolean fromMetricsTable) throws SQLException {
|
84
|
logger.debug("fetchMetricsByResultSet (from table="+fromMetricsTable+")");
|
85
|
Metrics metrics = null;
|
86
|
boolean hasNext = false;
|
87
|
List<JsonObject> metrics_per_dashboard = new ArrayList<>();
|
88
|
|
89
|
if(fromMetricsTable) {
|
90
|
if(rs.next()) {
|
91
|
metrics = new Metrics();
|
92
|
PGobject pg = (PGobject) rs.getObject("metrics_per_dashboard");
|
93
|
String jsonText = pg.getValue();
|
94
|
|
95
|
Gson gson = new Gson();
|
96
|
JsonArray myList2 = gson.fromJson(jsonText, JsonArray.class);
|
97
|
|
98
|
List<JsonObject> listObj = new ArrayList<>();
|
99
|
|
100
|
for(JsonElement jsonElement : myList2) {
|
101
|
listObj.add(jsonElement.getAsJsonObject());
|
102
|
}
|
103
|
|
104
|
metrics.setMetrics_per_dashboard(listObj);
|
105
|
metrics.setDate(rs.getTimestamp("date"));
|
106
|
}
|
107
|
} else {
|
108
|
while (rs.next()) {
|
109
|
String[] dashboard_elements = ((String) rs.getString("claimed_in_dashboard")).split("_", 2);
|
110
|
|
111
|
JsonObject dashboardMetrics = new JsonObject();
|
112
|
dashboardMetrics.add("environment", new JsonPrimitive(dashboard_elements[0]));
|
113
|
dashboardMetrics.add("dashboard", new JsonPrimitive(dashboard_elements[1]));
|
114
|
dashboardMetrics.add("claims", new JsonPrimitive(rs.getInt("total_claims")));
|
115
|
dashboardMetrics.add("users", new JsonPrimitive(rs.getInt("total_users")));
|
116
|
dashboardMetrics.add("projects", new JsonPrimitive(rs.getInt("projects")));
|
117
|
dashboardMetrics.add("eu_projects", new JsonPrimitive(rs.getInt("eu_projects")));
|
118
|
dashboardMetrics.add("countries", new JsonPrimitive(rs.getInt("countries")));
|
119
|
dashboardMetrics.add("research_results", new JsonPrimitive(rs.getInt("research_results")));
|
120
|
|
121
|
metrics_per_dashboard.add(dashboardMetrics);
|
122
|
|
123
|
hasNext = true;
|
124
|
}
|
125
|
|
126
|
if (hasNext) {
|
127
|
metrics = new Metrics();
|
128
|
metrics.setMetrics_per_dashboard(metrics_per_dashboard);
|
129
|
metrics.setDate(new Date());
|
130
|
metrics.setId("current");
|
131
|
}
|
132
|
}
|
133
|
|
134
|
return metrics;
|
135
|
}
|
136
|
|
137
|
private int fetchCountByResultSet(ResultSet rs) throws SQLException {
|
138
|
int count = 0;
|
139
|
if(rs.next()) {
|
140
|
count = rs.getInt("count");
|
141
|
}
|
142
|
return count;
|
143
|
}
|
144
|
|
145
|
public SqlDAO getSqlDAO() {
|
146
|
return sqlDAO;
|
147
|
}
|
148
|
|
149
|
public void setSqlDAO(SqlDAO sqlDAO) {
|
150
|
this.sqlDAO = sqlDAO;
|
151
|
}
|
152
|
|
153
|
public QueryGenerator getQueryGenerator() {
|
154
|
return queryGenerator;
|
155
|
}
|
156
|
|
157
|
public void setQueryGenerator(QueryGenerator queryGenerator) {
|
158
|
this.queryGenerator = queryGenerator;
|
159
|
}
|
160
|
}
|