1
|
package eu.dnetlib.goldoa.service.stats;
|
2
|
|
3
|
import eu.dnetlib.goldoa.domain.Request;
|
4
|
import eu.dnetlib.goldoa.domain.stats.Browse;
|
5
|
import eu.dnetlib.goldoa.domain.stats.DateSeries;
|
6
|
import eu.dnetlib.goldoa.domain.stats.MapData;
|
7
|
import eu.dnetlib.goldoa.domain.stats.Numbers;
|
8
|
import eu.dnetlib.goldoa.domain.stats.Quadruple;
|
9
|
import eu.dnetlib.goldoa.domain.stats.Series;
|
10
|
import eu.dnetlib.goldoa.domain.stats.Triple;
|
11
|
import eu.dnetlib.goldoa.domain.stats.Tuple;
|
12
|
import org.springframework.beans.factory.annotation.Autowired;
|
13
|
import org.springframework.jdbc.core.JdbcTemplate;
|
14
|
import org.springframework.jdbc.core.RowMapper;
|
15
|
import org.springframework.transaction.annotation.Transactional;
|
16
|
|
17
|
import javax.sql.DataSource;
|
18
|
import java.sql.ResultSet;
|
19
|
import java.sql.SQLException;
|
20
|
import java.text.SimpleDateFormat;
|
21
|
import java.util.ArrayList;
|
22
|
import java.util.Date;
|
23
|
import java.util.HashMap;
|
24
|
import java.util.List;
|
25
|
import java.util.Map;
|
26
|
|
27
|
/**
|
28
|
* Created by antleb on 11/19/15.
|
29
|
*/
|
30
|
@Transactional(readOnly = true)
|
31
|
public class StatsManagerImpl implements StatsManager {
|
32
|
|
33
|
public static final String NUMBERS_REQUESTS = "select count(*) from request r where r.status in (" + Request.RequestStatus.APPROVED.getCode() + ", " + Request.RequestStatus.ACCOUNTING_PROCESSING.getCode() + ", " + Request.RequestStatus.ACCOUNTING_PAID.getCode() + ", " + Request.RequestStatus.CONDITIONALLY_APPROVED.getCode() + ", " + Request.RequestStatus.ACCOUNTING_ON_HOLD.getCode() + ")" ;
|
34
|
public static final String NUMBERS_PROJECTS = "select count(distinct project) from request r where r.status in (" + Request.RequestStatus.APPROVED.getCode() + ", " + Request.RequestStatus.ACCOUNTING_PROCESSING.getCode() + ", " + Request.RequestStatus.ACCOUNTING_PAID.getCode() + ", " + Request.RequestStatus.CONDITIONALLY_APPROVED.getCode() + ", " + Request.RequestStatus.ACCOUNTING_ON_HOLD.getCode() + ")";
|
35
|
public static final String NUMBERS_TOTAL_AMOUNT = "select sum(apc_paid) from request r";
|
36
|
public static final String NUMBERS_AVERAGE_AMOUNT = "select avg(apc_paid)::INTEGER from request r";
|
37
|
|
38
|
public static final String SERIES_COUNTRY = "select c.name as category, count(r.id) as count, sum(apc_paid) as apc, avg(apc_paid) as avgapc from request r join organisation o on r.organization=o.id join country c on c.id=o.country %WHERE% group by c.name";
|
39
|
public static final String SERIES_STATUS = "select r.status as category, count(r.id) as count, sum(apc_paid) as apc, avg(apc_paid) as avgapc from request r %WHERE% group by r.status";
|
40
|
public static final String SERIES_TYPE = "select p.type as category, count(r.id) as count, sum(apc_paid) as apc, avg(apc_paid) as avgapc from request r join publication p on p.id=r.publication %WHERE% group by p.type";
|
41
|
public static final String SERIES_ORGANIZATION = "select case when o.shortname is not null then o.shortname else o.name end as category, count(r.id) as count, sum(apc_paid) as apc, avg(apc_paid) as avgapc from request r join organisation o on r.organization=o.id %WHERE% group by o.shortname, o.name";
|
42
|
public static final String SERIES_PUBLISHER = "select p.name as category, count(r.id) as count, sum(apc_paid) as apc, avg(apc_paid) as avgapc from (select r.id, r.status, r.apc_paid, j.publisher, 'j' from request r join journal j on r.journal=j.id where j.publisher is not null union select r.id, r.status, r.apc_paid, p.id, 'p' from request r join publisher p on r.publisher=p.id) as r join publisher p on r.publisher=p.id %WHERE% group by p.name";
|
43
|
|
44
|
public static final String BROWSE_SCIENTIFIC_AREA = "select distinct p.scientificarea as id, p.scientificarea as category, count(r.id) as count from request r join project p on r.project=p.id group by p.scientificarea order by category";
|
45
|
public static final String BROWSE_COUNTRY = "select c.id as id, c.name as category, count(r.id) as count from request r join organisation o on r.organization=o.id join country c on c.id=o.country group by c.name, c.id order by category";
|
46
|
public static final String BROWSE_ORGANIZATION = "select o.id as id, case when o.shortname is not null then o.shortname else o.name end as category, count(r.id) as count from request r join organisation o on o.id=r.organization group by o.shortname, o.name, o.id order by category";
|
47
|
public static final String BROWSE_PUBLISHER = "select p.id as id, p.name as category, count(r.id) as count from (select r.id, r.status, j.publisher, 'j' from request r join journal j on r.journal=j.id where j.publisher is not null union select r.id, r.status, p.id, 'p' from request r join publisher p on r.publisher=p.id) as r join publisher p on r.publisher=p.id group by p.name, p.id order by category";
|
48
|
public static final String BROWSE_STATUS = "select r.status as id, r.status as category, count(r.id) as count from request r group by status";
|
49
|
|
50
|
@Autowired
|
51
|
private DataSource dataSource;
|
52
|
|
53
|
@Override
|
54
|
public Numbers getNumbers(Numbers.Category[] categories) {
|
55
|
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
|
56
|
Map<Numbers.Category, Integer> map = new HashMap<Numbers.Category, Integer>();
|
57
|
|
58
|
for (Numbers.Category category:categories) {
|
59
|
switch (category) {
|
60
|
case REQUESTS:
|
61
|
map.put(Numbers.Category.REQUESTS, jdbcTemplate.queryForObject(NUMBERS_REQUESTS, Integer.class));
|
62
|
break;
|
63
|
case PROJECTS:
|
64
|
map.put(Numbers.Category.PROJECTS, jdbcTemplate.queryForObject(NUMBERS_PROJECTS, Integer.class));
|
65
|
break;
|
66
|
case TOTAL_AMOUNT:
|
67
|
map.put(Numbers.Category.TOTAL_AMOUNT, jdbcTemplate.queryForObject(NUMBERS_TOTAL_AMOUNT, Integer.class));
|
68
|
break;
|
69
|
case AVERAGE_AMOUNT:
|
70
|
map.put(Numbers.Category.AVERAGE_AMOUNT, jdbcTemplate.queryForObject(NUMBERS_AVERAGE_AMOUNT, Integer.class));
|
71
|
break;
|
72
|
}
|
73
|
}
|
74
|
|
75
|
return new Numbers(map);
|
76
|
}
|
77
|
|
78
|
@Override
|
79
|
public Series getSeries(Series.Category[] categories, Request.RequestStatus[] statuses) {
|
80
|
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
|
81
|
Map<Series.Category, List<Quadruple<String, Integer, Float, Float>>> map = new HashMap<Series.Category, List<Quadruple<String, Integer, Float, Float>>>();
|
82
|
|
83
|
for (final Series.Category category:categories) {
|
84
|
String query = null;
|
85
|
|
86
|
switch (category) {
|
87
|
case COUNTRY:
|
88
|
query = SERIES_COUNTRY;
|
89
|
break;
|
90
|
case ORGANIZATION:
|
91
|
query = SERIES_ORGANIZATION;
|
92
|
break;
|
93
|
case PUBLISHER:
|
94
|
query = SERIES_PUBLISHER;
|
95
|
break;
|
96
|
case STATUS:
|
97
|
query = SERIES_STATUS;
|
98
|
break;
|
99
|
case PUBLICATION_TYPE:
|
100
|
query = SERIES_TYPE;
|
101
|
}
|
102
|
|
103
|
if (statuses != null) {
|
104
|
StringBuilder ssb = new StringBuilder();
|
105
|
|
106
|
for (Request.RequestStatus status:statuses) {
|
107
|
String clause;
|
108
|
int code = status.getCode();
|
109
|
|
110
|
switch (code) {
|
111
|
case 0:
|
112
|
clause = " r.status=0";
|
113
|
break;
|
114
|
default:
|
115
|
clause = " (r.status = " + code + ")";
|
116
|
break;
|
117
|
}
|
118
|
|
119
|
ssb.append(ssb.length()==0?clause:" or " + clause);
|
120
|
}
|
121
|
|
122
|
query = query.replace("%WHERE%", " where " + ssb.toString());
|
123
|
} else {
|
124
|
query = query.replace("%WHERE%", "");
|
125
|
}
|
126
|
|
127
|
map.put(category, jdbcTemplate.query(query, new RowMapper<Quadruple<String, Integer, Float, Float>>() {
|
128
|
@Override
|
129
|
public Quadruple<String, Integer, Float, Float> mapRow(ResultSet rs, int rowNum) throws SQLException {
|
130
|
if (category != Series.Category.STATUS)
|
131
|
return new Quadruple<String, Integer, Float, Float>(rs.getString("category"), rs.getInt("count"), rs.getFloat("apc"), rs.getFloat("avgapc"));
|
132
|
else
|
133
|
return new Quadruple<String, Integer, Float, Float>(Request.RequestStatus.forStatus(rs.getInt("category")).getValue(), rs.getInt("count"), rs.getFloat("apc"), rs.getFloat("avgapc"));
|
134
|
}
|
135
|
}));
|
136
|
}
|
137
|
|
138
|
return new Series(map);
|
139
|
}
|
140
|
|
141
|
@Override
|
142
|
public DateSeries getDateSeries(DateSeries.Over over, boolean cumulative, Date from, Date to, String[] publishers, String[] organizations, String[] countries, String[] scientificAreas) {
|
143
|
|
144
|
StringBuilder sb = new StringBuilder();
|
145
|
String dateColumn = null;
|
146
|
String windowKeyword ;
|
147
|
List<String> clauses = new ArrayList<String>();
|
148
|
|
149
|
switch (over) {
|
150
|
case DAY:
|
151
|
dateColumn = "date_trunc('day', submissiondate)";
|
152
|
break;
|
153
|
case MONTH:
|
154
|
dateColumn = "date_trunc('month', submissiondate)";
|
155
|
break;
|
156
|
case YEAR:
|
157
|
dateColumn = "date_trunc('year', submissiondate)";
|
158
|
break;
|
159
|
}
|
160
|
|
161
|
if (cumulative)
|
162
|
windowKeyword = "order";
|
163
|
else
|
164
|
windowKeyword = "partition";
|
165
|
|
166
|
if (from != null) {
|
167
|
clauses.add(" r.submissiondate >= '" + new SimpleDateFormat("yyyy-MM-dd").format(from) + "' ");
|
168
|
}
|
169
|
if (to != null) {
|
170
|
clauses.add(" r.submissiondate <= '" + new SimpleDateFormat("yyyy-MM-dd").format(to) + "' ");
|
171
|
}
|
172
|
|
173
|
if (publishers != null && publishers.length > 0) {
|
174
|
String clause = "pub.id in (";
|
175
|
|
176
|
for (int i = 0; i < publishers.length; i++) {
|
177
|
if (i > 0)
|
178
|
clause += ", ";
|
179
|
|
180
|
clause += "'" + publishers[i] + "'";
|
181
|
}
|
182
|
|
183
|
clause += ")";
|
184
|
|
185
|
clauses.add(clause);
|
186
|
}
|
187
|
|
188
|
if (organizations != null && organizations.length > 0) {
|
189
|
String clause = "o.id in (";
|
190
|
|
191
|
for (int i = 0; i < organizations.length; i++) {
|
192
|
if (i > 0)
|
193
|
clause += ", ";
|
194
|
|
195
|
clause += "'" + organizations[i] + "'";
|
196
|
}
|
197
|
|
198
|
clause += ")";
|
199
|
|
200
|
clauses.add(clause);
|
201
|
}
|
202
|
|
203
|
if (countries != null && countries.length > 0) {
|
204
|
String clause = "c.id in (";
|
205
|
|
206
|
for (int i = 0; i < countries.length; i++) {
|
207
|
if (i > 0)
|
208
|
clause += ", ";
|
209
|
|
210
|
clause += "'" + countries[i] + "'";
|
211
|
}
|
212
|
|
213
|
clause += ")";
|
214
|
|
215
|
clauses.add(clause);
|
216
|
}
|
217
|
|
218
|
if (scientificAreas != null && scientificAreas.length > 0) {
|
219
|
String clause = "p.scientificarea in (";
|
220
|
|
221
|
for (int i = 0; i < scientificAreas.length; i++) {
|
222
|
if (i > 0)
|
223
|
clause += ", ";
|
224
|
|
225
|
clause += "'" + scientificAreas[i] + "'";
|
226
|
}
|
227
|
|
228
|
clause += ")";
|
229
|
|
230
|
clauses.add(clause);
|
231
|
}
|
232
|
|
233
|
sb.append("select distinct ").append(dateColumn).append(" as date, count(r.id) over (").append(windowKeyword).append(" by ").append(dateColumn).append(") as count, sum(apc_paid) over (").append(windowKeyword).append(" by ").append(dateColumn).append(") as apc ");
|
234
|
sb.append("from request r left join project p on r.project=p.id left join organisation o on r.organization=o.id left join country c on c.id=o.country left join (select r.id as rid, p.id, p.name from request r join journal j on r.journal=j.id join publisher p on p.id=j.publisher union select r.id, p.id, p.name from request r join publisher p on r.publisher=p.id) as pub on pub.rid=r.id");
|
235
|
|
236
|
if (clauses.size() > 0)
|
237
|
sb.append(" where ");
|
238
|
|
239
|
for (int i = 0; i < clauses.size(); i++) {
|
240
|
if (i > 0)
|
241
|
sb.append(" and ");
|
242
|
|
243
|
sb.append(" (").append(clauses.get(i)).append(") ");
|
244
|
}
|
245
|
|
246
|
sb.append(" order by ").append(dateColumn);
|
247
|
|
248
|
// System.out.println(sb.toString());return null;
|
249
|
|
250
|
return new DateSeries(new JdbcTemplate(dataSource).query(sb.toString(), new RowMapper<Triple<Date, Integer, Float>>() {
|
251
|
@Override
|
252
|
public Triple<Date, Integer, Float> mapRow(ResultSet rs, int rowNum) throws SQLException {
|
253
|
return new Triple<Date, Integer, Float>(rs.getDate("date"), rs.getInt("count"), rs.getFloat("apc"));
|
254
|
}
|
255
|
}));
|
256
|
}
|
257
|
|
258
|
public static void main(String[] args) {
|
259
|
System.out.println(new StatsManagerImpl().getDateSeries(DateSeries.Over.DAY, true, null,null,null,null,null,null));
|
260
|
}
|
261
|
|
262
|
@Override
|
263
|
public Browse browse(Browse.Category[] categories) {
|
264
|
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
|
265
|
Map<Browse.Category, List<Triple<String, String, Integer>>> map = new HashMap<Browse.Category, List<Triple<String, String, Integer>>>();
|
266
|
|
267
|
for (final Browse.Category category:categories) {
|
268
|
String query = null;
|
269
|
|
270
|
switch (category) {
|
271
|
case COUNTRY:
|
272
|
query = BROWSE_COUNTRY;
|
273
|
break;
|
274
|
case ORGANIZATION:
|
275
|
query = BROWSE_ORGANIZATION;
|
276
|
break;
|
277
|
case PUBLISHER:
|
278
|
query = BROWSE_PUBLISHER;
|
279
|
break;
|
280
|
case SCIENTIFIC_AREA:
|
281
|
query = BROWSE_SCIENTIFIC_AREA;
|
282
|
break;
|
283
|
case STATUS:
|
284
|
query = BROWSE_STATUS;
|
285
|
break;
|
286
|
}
|
287
|
|
288
|
map.put(category, jdbcTemplate.query(query, new RowMapper<Triple<String, String, Integer>>() {
|
289
|
@Override
|
290
|
public Triple<String, String, Integer> mapRow(ResultSet rs, int rowNum) throws SQLException {
|
291
|
if (category != Browse.Category.STATUS)
|
292
|
return new Triple<String, String, Integer>(rs.getString("id"), rs.getString("category"), rs.getInt("count"));
|
293
|
else
|
294
|
return new Triple<String, String, Integer>(Request.RequestStatus.forStatus(rs.getInt("id")).name(), Request.RequestStatus.forStatus(rs.getInt("category")).getValue(), rs.getInt("count"));
|
295
|
}
|
296
|
}));
|
297
|
}
|
298
|
|
299
|
return new Browse(map);
|
300
|
}
|
301
|
|
302
|
@Override
|
303
|
public MapData getMapData() {
|
304
|
return null;
|
305
|
}
|
306
|
}
|