1
|
package eu.dnetlib.goldoa.service.stats;
|
2
|
|
3
|
import eu.dnetlib.goldoa.domain.Request;
|
4
|
import eu.dnetlib.goldoa.domain.stats.*;
|
5
|
import eu.dnetlib.goldoa.service.RequestManager;
|
6
|
import org.apache.log4j.Logger;
|
7
|
import org.hibernate.SessionFactory;
|
8
|
import org.springframework.beans.factory.annotation.Autowired;
|
9
|
import org.springframework.stereotype.Service;
|
10
|
import org.springframework.transaction.annotation.Transactional;
|
11
|
|
12
|
import java.text.ParseException;
|
13
|
import java.text.SimpleDateFormat;
|
14
|
import java.util.*;
|
15
|
|
16
|
/*
|
17
|
* Created by antleb on 11/19/15.
|
18
|
*/
|
19
|
|
20
|
|
21
|
@Transactional(readOnly = true)
|
22
|
@Service("statsManager")
|
23
|
public class StatsManagerImpl implements StatsManager {
|
24
|
|
25
|
private static Logger logger = Logger.getLogger(StatsManagerImpl.class);
|
26
|
|
27
|
public static final String SERIES_COUNTRY = "select c.name as category, count(r.id) as count, sum(apcpaid) as apc, avg(apcpaid) as avgapc " +
|
28
|
"from request r join organization o on r.organization=o.id join country c on c.id=o.country %WHERE% group by c.name";
|
29
|
|
30
|
public static final String SERIES_STATUS = "select r.status as category, count(r.id) as count, sum(apcpaid) as apc, avg(apcpaid) as avgapc " +
|
31
|
"from request r %WHERE% group by r.status";
|
32
|
public static final String SERIES_TYPE = "select p.type as category, count(r.id) as count, sum(apcpaid) as apc, avg(apcpaid) as avgapc " +
|
33
|
"from request r join publication p on p.id=r.publication %WHERE% group by p.type";
|
34
|
public static final String SERIES_ORGANIZATION = "select o.name as category, count(r.id) as count, sum(apcpaid) as apc, avg(apcpaid) as avgapc " +
|
35
|
"from request r join organization o on r.organization=o.id %WHERE% group by o.shortname, o.name";
|
36
|
public static final String SERIES_PUBLISHER = "select p.name as category, count(r.id) as count, sum(apcpaid) as apc, avg(apcpaid) as avgapc " +
|
37
|
"from (select r.id, r.status, r.apcpaid, j.publisher, 'j' from request r join journal j on r.journal=j.id " +
|
38
|
"where j.publisher is not null " +
|
39
|
"union select r.id, r.status, r.apcpaid, p.id, 'p' from request r join publisher p on r.publisher=p.id) " +
|
40
|
"as r join publisher p on r.publisher=p.id %WHERE% group by p.name";
|
41
|
|
42
|
|
43
|
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";
|
44
|
public static final String BROWSE_COUNTRY = "select c.id as id, c.name as category, count(r.id) as count from request r join organization o on r.organization=o.id join country c on c.id=o.country group by c.name, c.id order by category";
|
45
|
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 organization o on o.id=r.organization group by o.shortname, o.name, o.id order by category";
|
46
|
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";
|
47
|
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";
|
48
|
|
49
|
@Autowired
|
50
|
private RequestManager requestManager;
|
51
|
|
52
|
@Autowired
|
53
|
private SessionFactory sessionFactory;
|
54
|
|
55
|
@Override
|
56
|
public Numbers getNumbers(Numbers.Category[] categories) {
|
57
|
|
58
|
Map<Numbers.Category, Number> map = new HashMap<>();
|
59
|
|
60
|
for (Numbers.Category category:categories) {
|
61
|
switch (category) {
|
62
|
case REQUESTS:
|
63
|
map.put(Numbers.Category.REQUESTS, requestManager.getActiveNumber());
|
64
|
break;
|
65
|
case PAID_REQUESTS:
|
66
|
map.put(Numbers.Category.PAID_REQUESTS,requestManager.getPaidNumber() );
|
67
|
break;
|
68
|
case PROJECTS:
|
69
|
map.put(Numbers.Category.PROJECTS,requestManager.getNumberOfProjects());
|
70
|
break;
|
71
|
case TOTAL_AMOUNT:
|
72
|
map.put(Numbers.Category.TOTAL_AMOUNT,requestManager.getTotalAmount() );
|
73
|
break;
|
74
|
case AVERAGE_AMOUNT:
|
75
|
map.put(Numbers.Category.AVERAGE_AMOUNT,requestManager.getAverageAmount() );
|
76
|
break;
|
77
|
case AVERAGE_PER_ARTICLE:
|
78
|
map.put(Numbers.Category.AVERAGE_PER_ARTICLE,requestManager.getAverageAmountPerArticle() );
|
79
|
break;
|
80
|
case AVERAGE_PER_MONOGRAPH:
|
81
|
map.put(Numbers.Category.AVERAGE_PER_MONOGRAPH,requestManager.getAverageAmountPerMonograph());
|
82
|
break;
|
83
|
case AVERAGE_PER_BOOK_CHAPTER:
|
84
|
map.put(Numbers.Category.AVERAGE_PER_BOOK_CHAPTER,requestManager.getAverageAmountPerBookChapter() );
|
85
|
break;
|
86
|
case PAID_REQUESTS_WITH_DOI:
|
87
|
map.put(Numbers.Category.PAID_REQUESTS_WITH_DOI,requestManager.getNumberOfPaidRequestsWithDOI());
|
88
|
break;
|
89
|
case APPROVED_REQUESTS_WITH_DOI:
|
90
|
map.put(Numbers.Category.APPROVED_REQUESTS_WITH_DOI,requestManager.getNumberOfApprovedRequestsWithDOI());
|
91
|
break;
|
92
|
case PAID_ARTICLE_REQUESTS:
|
93
|
map.put(Numbers.Category.PAID_ARTICLE_REQUESTS,requestManager.getNumberOfPaidPublicationPerArticle());
|
94
|
break;
|
95
|
case PAID_MONOGRAPH_REQUESTS:
|
96
|
map.put(Numbers.Category.PAID_MONOGRAPH_REQUESTS, requestManager.getNumberOfPaidPublicationPerMonograph());
|
97
|
break;
|
98
|
case PAID_BOOK_CHAPTER_REQUESTS:
|
99
|
map.put(Numbers.Category.PAID_BOOK_CHAPTER_REQUESTS, requestManager.getNumberOfPaidPublicationPerBookChapter());
|
100
|
break;
|
101
|
}
|
102
|
}
|
103
|
|
104
|
return new Numbers(map);
|
105
|
}
|
106
|
|
107
|
@Override
|
108
|
@SuppressWarnings("unchecked")
|
109
|
public Series getSeries(Series.Category[] categories, Request.RequestStatus[] statuses) {
|
110
|
Map<Series.Category, List<Quadruple<String, Integer, Float, Float>>> map = new HashMap<Series.Category, List<Quadruple<String, Integer, Float, Float>>>();
|
111
|
|
112
|
for (final Series.Category category:categories) {
|
113
|
String query = null;
|
114
|
|
115
|
switch (category) {
|
116
|
case COUNTRY:
|
117
|
query = SERIES_COUNTRY;
|
118
|
break;
|
119
|
case ORGANIZATION:
|
120
|
query = SERIES_ORGANIZATION;
|
121
|
break;
|
122
|
case PUBLISHER:
|
123
|
query = SERIES_PUBLISHER;
|
124
|
break;
|
125
|
case STATUS:
|
126
|
query = SERIES_STATUS;
|
127
|
break;
|
128
|
case PUBLICATION_TYPE:
|
129
|
query = SERIES_TYPE;
|
130
|
}
|
131
|
|
132
|
if (statuses != null) {
|
133
|
StringBuilder ssb = new StringBuilder();
|
134
|
|
135
|
for (Request.RequestStatus status:statuses) {
|
136
|
String clause;
|
137
|
int code = status.getCode();
|
138
|
|
139
|
switch (code) {
|
140
|
case 0:
|
141
|
clause = " r.status=0";
|
142
|
break;
|
143
|
default:
|
144
|
clause = " (r.status = " + code + ")";
|
145
|
break;
|
146
|
}
|
147
|
|
148
|
ssb.append(ssb.length()==0?clause:" or " + clause);
|
149
|
}
|
150
|
|
151
|
query = query.replace("%WHERE%", " where " + ssb.toString());
|
152
|
} else {
|
153
|
query = query.replace("%WHERE%", "");
|
154
|
}
|
155
|
|
156
|
|
157
|
List<Object[]> resultSet = sessionFactory.getCurrentSession().createSQLQuery(query).list();
|
158
|
Quadruple<String, Integer, Float, Float> q = null;
|
159
|
List<Quadruple<String, Integer, Float, Float>> l = new ArrayList<>();
|
160
|
for(Object[] rs : resultSet){
|
161
|
if(rs[0] == null) rs[0] = "";
|
162
|
if(rs[2] == null) rs[2] = "0.0";
|
163
|
if(rs[3] == null) rs[3] = "0.0";
|
164
|
|
165
|
|
166
|
if(category != Series.Category.STATUS)
|
167
|
q = new Quadruple<>(rs[0].toString(),
|
168
|
Integer.parseInt(rs[1].toString()), Float.parseFloat(rs[2].toString()), Float.parseFloat(rs[3].toString()));
|
169
|
else
|
170
|
q = new Quadruple<>(Request.RequestStatus.forStatus((Integer) rs[0]).getValue(),
|
171
|
Integer.parseInt(rs[1].toString()), Float.parseFloat(rs[2].toString()), Float.parseFloat(rs[3].toString()));
|
172
|
l.add(q);
|
173
|
}
|
174
|
map.put(category,l);
|
175
|
}
|
176
|
|
177
|
return new Series(map);
|
178
|
}
|
179
|
|
180
|
@Override
|
181
|
@SuppressWarnings("unchecked")
|
182
|
public DateSeries getDateSeries(DateSeries.Over over, boolean cumulative, Date from, Date to, String[] publishers, String[] organizations, String[] countries, String[] scientificAreas) {
|
183
|
|
184
|
StringBuilder sb = new StringBuilder();
|
185
|
String dateColumn = null;
|
186
|
String windowKeyword ;
|
187
|
List<String> clauses = new ArrayList<String>();
|
188
|
|
189
|
switch (over) {
|
190
|
case DAY:
|
191
|
dateColumn = "date_trunc('day', submissiondate)";
|
192
|
break;
|
193
|
case MONTH:
|
194
|
dateColumn = "date_trunc('month', submissiondate)";
|
195
|
break;
|
196
|
case YEAR:
|
197
|
dateColumn = "date_trunc('year', submissiondate)";
|
198
|
break;
|
199
|
}
|
200
|
|
201
|
if (cumulative)
|
202
|
windowKeyword = "order";
|
203
|
else
|
204
|
windowKeyword = "partition";
|
205
|
|
206
|
if (from != null) {
|
207
|
clauses.add(" r.submissiondate >= '" + new SimpleDateFormat("yyyy-MM-dd").format(from) + "' ");
|
208
|
}
|
209
|
if (to != null) {
|
210
|
clauses.add(" r.submissiondate <= '" + new SimpleDateFormat("yyyy-MM-dd").format(to) + "' ");
|
211
|
}
|
212
|
|
213
|
if (publishers != null && publishers.length > 0) {
|
214
|
String clause = "pub.id in (";
|
215
|
|
216
|
for (int i = 0; i < publishers.length; i++) {
|
217
|
if (i > 0)
|
218
|
clause += ", ";
|
219
|
|
220
|
clause += "'" + publishers[i] + "'";
|
221
|
}
|
222
|
|
223
|
clause += ")";
|
224
|
|
225
|
clauses.add(clause);
|
226
|
}
|
227
|
|
228
|
if (organizations != null && organizations.length > 0) {
|
229
|
String clause = "o.id in (";
|
230
|
|
231
|
for (int i = 0; i < organizations.length; i++) {
|
232
|
if (i > 0)
|
233
|
clause += ", ";
|
234
|
|
235
|
clause += "'" + organizations[i] + "'";
|
236
|
}
|
237
|
|
238
|
clause += ")";
|
239
|
|
240
|
clauses.add(clause);
|
241
|
}
|
242
|
|
243
|
if (countries != null && countries.length > 0) {
|
244
|
String clause = "c.id in (";
|
245
|
|
246
|
for (int i = 0; i < countries.length; i++) {
|
247
|
if (i > 0)
|
248
|
clause += ", ";
|
249
|
|
250
|
clause += "'" + countries[i] + "'";
|
251
|
}
|
252
|
|
253
|
clause += ")";
|
254
|
|
255
|
clauses.add(clause);
|
256
|
}
|
257
|
|
258
|
if (scientificAreas != null && scientificAreas.length > 0) {
|
259
|
String clause = "p.scientificarea in (";
|
260
|
|
261
|
for (int i = 0; i < scientificAreas.length; i++) {
|
262
|
if (i > 0)
|
263
|
clause += ", ";
|
264
|
|
265
|
clause += "'" + scientificAreas[i] + "'";
|
266
|
}
|
267
|
|
268
|
clause += ")";
|
269
|
|
270
|
clauses.add(clause);
|
271
|
}
|
272
|
|
273
|
sb.append("select distinct ").append(dateColumn).append(" as date, count(r.id) over (").append(windowKeyword).append(" by ").append(dateColumn).append(") as count, sum(apcpaid) over (").append(windowKeyword).append(" by ").append(dateColumn).append(") as apc ");
|
274
|
sb.append("from request r left join project p on r.project=p.id left join organization 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");
|
275
|
|
276
|
if (clauses.size() > 0)
|
277
|
sb.append(" where ");
|
278
|
|
279
|
for (int i = 0; i < clauses.size(); i++) {
|
280
|
if (i > 0)
|
281
|
sb.append(" and ");
|
282
|
|
283
|
sb.append(" (").append(clauses.get(i)).append(") ");
|
284
|
}
|
285
|
|
286
|
sb.append(" order by ").append(dateColumn);
|
287
|
|
288
|
// System.out.println(sb.toString());return null;
|
289
|
|
290
|
List<Object[]> resultSet = sessionFactory.getCurrentSession().createSQLQuery(sb.toString()).list();
|
291
|
List<Triple<Date, Integer, Float>> l = new ArrayList<>();
|
292
|
|
293
|
SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.s");
|
294
|
for(Object[] rs : resultSet){
|
295
|
Date date = null;
|
296
|
try {
|
297
|
if(rs[0] == null)
|
298
|
date = null;
|
299
|
else
|
300
|
date = formatter.parse(rs[0].toString());
|
301
|
if(rs[2] == null)
|
302
|
rs[2] = "0.0";
|
303
|
Triple<Date, Integer, Float> t = new Triple<Date,Integer, Float>(date,
|
304
|
Integer.parseInt(rs[1].toString()),Float.parseFloat(rs[2].toString()));
|
305
|
l.add(t);
|
306
|
} catch (ParseException e) {
|
307
|
e.printStackTrace();
|
308
|
}
|
309
|
}
|
310
|
return new DateSeries(l);
|
311
|
}
|
312
|
|
313
|
public static void main(String[] args) {
|
314
|
System.out.println(new StatsManagerImpl().getDateSeries(DateSeries.Over.DAY, true, null,null,null,null,null,null));
|
315
|
}
|
316
|
|
317
|
@Override
|
318
|
@SuppressWarnings("unchecked")
|
319
|
public Browse browse(Browse.Category[] categories) {
|
320
|
Map<Browse.Category, List<Triple<String, String, Integer>>> map = new HashMap<Browse.Category, List<Triple<String, String, Integer>>>();
|
321
|
|
322
|
for (final Browse.Category category:categories) {
|
323
|
String query = null;
|
324
|
|
325
|
switch (category) {
|
326
|
case COUNTRY:
|
327
|
query = BROWSE_COUNTRY;
|
328
|
break;
|
329
|
case ORGANIZATION:
|
330
|
query = BROWSE_ORGANIZATION;
|
331
|
break;
|
332
|
case PUBLISHER:
|
333
|
query = BROWSE_PUBLISHER;
|
334
|
break;
|
335
|
case SCIENTIFIC_AREA:
|
336
|
query = BROWSE_SCIENTIFIC_AREA;
|
337
|
break;
|
338
|
case STATUS:
|
339
|
query = BROWSE_STATUS;
|
340
|
break;
|
341
|
}
|
342
|
|
343
|
List<Object[]> resultSet = sessionFactory.getCurrentSession().createSQLQuery(query).list();
|
344
|
Triple<String, String, Integer> t;
|
345
|
List<Triple<String, String, Integer>> l = new ArrayList<>();
|
346
|
for(Object[] rs : resultSet){
|
347
|
if(rs[0] == null ) rs[0] = "";
|
348
|
if(rs[1] == null ) rs[1] = "";
|
349
|
if(rs[2] == null ) rs[2] = 0;
|
350
|
|
351
|
if(category != Browse.Category.STATUS)
|
352
|
t = new Triple<>(rs[0].toString(),rs[1].toString(), Integer.parseInt(rs[2].toString()));
|
353
|
else
|
354
|
t = new Triple<>(Request.RequestStatus.forStatus(Integer.parseInt(rs[0].toString())).getValue(),
|
355
|
Request.RequestStatus.forStatus(Integer.parseInt(rs[0].toString())).getValue(),
|
356
|
Integer.parseInt(rs[2].toString()));
|
357
|
l.add(t);
|
358
|
}
|
359
|
map.put(category,l);
|
360
|
}
|
361
|
|
362
|
return new Browse(map);
|
363
|
}
|
364
|
|
365
|
@Override
|
366
|
public MapData getMapData() {
|
367
|
return null;
|
368
|
}
|
369
|
}
|