Revision 57121
Added by Dimitris Pierrakos over 4 years ago
UsageStatsRepository.java | ||
---|---|---|
74 | 74 |
ObjectMapper objectMapper = new ObjectMapper(); |
75 | 75 |
return objectMapper.readValue(string, objectMapper.getTypeFactory().constructCollectionType(List.class, ReportItem.class)); |
76 | 76 |
} |
77 |
*/ |
|
78 |
|
|
77 |
*/ |
|
79 | 78 |
public UsageStats executeUsageStats(String query, List<String> values, String type) { |
80 | 79 |
|
81 | 80 |
UsageStats usageStats = new UsageStats(); |
... | ... | |
89 | 88 |
ResultSet rs = null; |
90 | 89 |
try { |
91 | 90 |
connection = usageStatsDB.getConnection(); |
91 |
java.sql.Timestamp timestamp1 = new java.sql.Timestamp(System.currentTimeMillis()); |
|
92 |
//System.out.println("DB start "+timestamp1); |
|
93 |
log.info("Query started..." + timestamp1); |
|
92 | 94 |
st = connection.prepareStatement(query); |
93 | 95 |
int i = 1; |
94 | 96 |
for (String s : values) { |
... | ... | |
138 | 140 |
else if (rs.getString(1).equals("openaire") && rs.getString(2) != null && !rs.getString(2).equals("") && !rs.getString(2).equals("null")) { |
139 | 141 |
openaire = Integer.parseInt(rs.getString(2)); |
140 | 142 |
} |
141 |
*/ |
|
143 |
*/
|
|
142 | 144 |
|
143 | 145 |
} |
144 | 146 |
usageStats.setTotal_views(Integer.toString(total_views)); |
... | ... | |
161 | 163 |
DbUtils.closeQuietly(rs); |
162 | 164 |
DbUtils.closeQuietly(st); |
163 | 165 |
DbUtils.closeQuietly(connection); |
166 |
java.sql.Timestamp timestamp = new java.sql.Timestamp(System.currentTimeMillis()); |
|
167 |
//System.out.println("DB end "+timestamp); |
|
168 |
log.info("Query end..." + timestamp); |
|
169 |
|
|
164 | 170 |
} |
165 | 171 |
return usageStats; |
166 | 172 |
} |
... | ... | |
170 | 176 |
try { |
171 | 177 |
String redis_result = jedis.get("total_stats", "result"); |
172 | 178 |
if (redis_result != null) { |
173 |
totalStats = fromJsonTotalStats(redis_result);
|
|
179 |
totalStats = fromJsonTotalStats(redis_result); |
|
174 | 180 |
} else { |
175 | 181 |
return updateTotalStats(); |
176 | 182 |
} |
... | ... | |
189 | 195 |
|
190 | 196 |
try { |
191 | 197 |
connection = usageStatsDB.getConnection(); |
198 |
java.sql.Timestamp timestamp1 = new java.sql.Timestamp(System.currentTimeMillis()); |
|
199 |
//System.out.println("DB start "+timestamp1); |
|
200 |
log.info("Query started..." + timestamp1); |
|
201 |
|
|
192 | 202 |
//st = connection.prepareStatement("SELECT count(distinct d.repository_id) AS repository, count(distinct d.result_id) AS items, sum(d.count) AS downloads, sum(v.count) AS views from public.downloads_stats d FULL OUTER JOIN public.views_stats v ON d.source=v.source AND d.repository_id=v.repository_id AND d.result_id=v.result_id AND d.date=v.date;"); |
193 | 203 |
st = connection.prepareStatement("SELECT count(distinct repository_id) AS repository, count(distinct result_id) AS items, sum(downloads) AS downloads, sum(views) AS views from usage_stats"); |
194 | 204 |
rs = st.executeQuery(); |
... | ... | |
201 | 211 |
st.close(); |
202 | 212 |
|
203 | 213 |
//st = connection.prepareStatement("select coalesce(d.date,v.date) as month, count(distinct d.repository_id) as repository, count(distinct d.result_id) as items, sum(d.count) as downloads, sum(v.count) as views from public.downloads_stats d FULL OUTER JOIN public.views_stats v ON d.source=v.source AND d.repository_id=v.repository_id AND d.result_id=v.result_id AND d.date=v.date group by month order by month;"); |
204 |
st = connection.prepareStatement("SELECT date, count(distinct repository_id) AS repository, count(distinct result_id) AS items, sum(downloads) AS downloads, sum(views) AS views FROM shadow.usage_stats GROUP BY date ORDER BY date;");
|
|
214 |
st = connection.prepareStatement("SELECT date, count(distinct repository_id) AS repository, count(distinct result_id) AS items, sum(downloads) AS downloads, sum(views) AS views FROM usage_stats GROUP BY date ORDER BY date;"); |
|
205 | 215 |
rs = st.executeQuery(); |
206 | 216 |
while (rs.next()) { |
207 | 217 |
int year = Integer.parseInt(rs.getString(1).substring(0, 4)); |
... | ... | |
226 | 236 |
st.close(); |
227 | 237 |
|
228 | 238 |
//st = connection.prepareStatement("SELECT COALESCE(SUBSTRING(d.date FROM 1 FOR 4), SUBSTRING(v.date FROM 1 FOR 4)) AS year, COUNT(DISTINCT d.repository_id) AS repository, COUNT(DISTINCT d.result_id) AS items, SUM(d.count) AS downloads, SUM(v.count) AS views FROM public.downloads_stats d FULL OUTER JOIN public.views_stats v ON d.source=v.source AND d.repository_id=v.repository_id AND d.result_id=v.result_id AND d.date=v.date GROUP BY year ORDER BY year;"); |
229 |
st = connection.prepareStatement("SELECT SUBSTRING(date FROM 1 FOR 4) AS year, COUNT(DISTINCT repository_id) AS repository, COUNT(DISTINCT result_id) AS items, SUM(downloads) AS downloads, SUM(views) AS views FROM shadow.usage_stats GROUP BY year ORDER BY year;");
|
|
239 |
st = connection.prepareStatement("SELECT SUBSTRING(date FROM 1 FOR 4) AS year, COUNT(DISTINCT repository_id) AS repository, COUNT(DISTINCT result_id) AS items, SUM(downloads) AS downloads, SUM(views) AS views FROM usage_stats GROUP BY year ORDER BY year;"); |
|
230 | 240 |
rs = st.executeQuery(); |
231 | 241 |
List<YearlyStats> yearlyStatsList = new ArrayList<>(); |
232 | 242 |
while (rs.next()) { |
... | ... | |
250 | 260 |
DbUtils.closeQuietly(rs); |
251 | 261 |
DbUtils.closeQuietly(st); |
252 | 262 |
DbUtils.closeQuietly(connection); |
263 |
java.sql.Timestamp timestamp = new java.sql.Timestamp(System.currentTimeMillis()); |
|
264 |
//System.out.println("DB end "+timestamp); |
|
265 |
log.info("Query end..." + timestamp); |
|
266 |
|
|
253 | 267 |
} |
254 | 268 |
return totalStats; |
255 | 269 |
} |
... | ... | |
259 | 273 |
return objectMapper.readValue(string, TotalStats.class); |
260 | 274 |
} |
261 | 275 |
|
262 |
|
|
263 | 276 |
public String executeRepoId(String repositoryIdentifier, String report) { |
264 | 277 |
PreparedStatement st = null; |
265 | 278 |
Connection connection = null; |
266 | 279 |
ResultSet rs = null; |
267 | 280 |
try { |
268 | 281 |
connection = usageStatsDB.getConnection(); |
282 |
java.sql.Timestamp timestamp1 = new java.sql.Timestamp(System.currentTimeMillis()); |
|
283 |
//System.out.println("DB start "+timestamp1); |
|
284 |
log.info("Query started..." + timestamp1); |
|
285 |
|
|
269 | 286 |
String[] split = repositoryIdentifier.split(":"); |
270 | 287 |
String openaire_id = "-1"; |
271 | 288 |
switch (split[0].toLowerCase()) { |
272 | 289 |
case "openaire": |
273 |
if(!report.equals("jr1")) { |
|
274 |
//st = connection.prepareStatement("select id from public.datasource where id=?"); |
|
275 |
st = connection.prepareStatement("select id from shadow.datasource where id=?"); |
|
290 |
if (!report.equals("jr1")) { |
|
291 |
st = connection.prepareStatement("select id from datasource where id=?"); |
|
276 | 292 |
st.setString(1, repositoryIdentifier.replaceFirst(split[0] + ":", "")); |
277 | 293 |
} else { |
278 |
//st = connection.prepareStatement("select id from public.datasource where id=? AND (type='Journal' OR type='Journal Aggregator/Publisher')"); |
|
279 |
st = connection.prepareStatement("select id from shadow.datasource where id=? AND (type='Journal' OR type='Journal Aggregator/Publisher')"); |
|
294 |
st = connection.prepareStatement("select id from datasource where id=? AND (type='Journal' OR type='Journal Aggregator/Publisher')"); |
|
280 | 295 |
st.setString(1, repositoryIdentifier.replaceFirst(split[0] + ":", "")); |
281 | 296 |
} |
282 | 297 |
|
... | ... | |
287 | 302 |
return openaire_id; |
288 | 303 |
|
289 | 304 |
case "opendoar": |
290 |
if(!report.equals("jr1")) { |
|
291 |
//st = connection.prepareStatement("select id from public.datasource_oids where orid=?"); |
|
292 |
st = connection.prepareStatement("select id from shadow.datasource_oids where orid=?"); |
|
305 |
if (!report.equals("jr1")) { |
|
306 |
st = connection.prepareStatement("select id from datasource_oids where orid=?"); |
|
293 | 307 |
st.setString(1, "opendoar____::" + repositoryIdentifier.replaceFirst(split[0] + ":", "")); |
294 | 308 |
} else { |
295 |
//st = connection.prepareStatement("select distinct d.id from public.datasource d, public.datasource_oids di where di.orid=? and d.id=di.id and (type='Journal' OR type='Journal Aggregator/Publisher')"); |
|
296 |
st = connection.prepareStatement("select distinct d.id from shadow.datasource d, shadow.datasource_oids di where di.orid=? and d.id=di.id and (type='Journal' OR type='Journal Aggregator/Publisher')"); |
|
309 |
st = connection.prepareStatement("select distinct d.id from datasource d, datasource_oids di where di.orid=? and d.id=di.id and (type='Journal' OR type='Journal Aggregator/Publisher')"); |
|
297 | 310 |
st.setString(1, "opendoar____::" + repositoryIdentifier.replaceFirst(split[0] + ":", "")); |
298 | 311 |
} |
299 | 312 |
|
... | ... | |
303 | 316 |
} |
304 | 317 |
return openaire_id; |
305 | 318 |
case "issn": |
306 |
//st = connection.prepareStatement("select distinct d.id from public.datasource d, public.datasource_oids di, public.datasource_results dr where d.id=dr.id and di.orid like ? and d.id=di.id and (type='Journal' OR type='Journal Aggregator/Publisher')"); |
|
307 |
st = connection.prepareStatement("select distinct d.id from shadow.datasource d, shadow.datasource_oids di, shadow.datasource_results dr where d.id=dr.id and di.orid like ? and d.id=di.id and (type='Journal' OR type='Journal Aggregator/Publisher')"); |
|
319 |
st = connection.prepareStatement("select distinct d.id from datasource d, datasource_oids di, datasource_results dr where d.id=dr.id and di.orid like ? and d.id=di.id and (type='Journal' OR type='Journal Aggregator/Publisher')"); |
|
308 | 320 |
st.setString(1, "%" + repositoryIdentifier.replaceFirst(split[0] + ":", "") + "%"); |
309 | 321 |
|
310 | 322 |
rs = st.executeQuery(); |
... | ... | |
321 | 333 |
DbUtils.closeQuietly(rs); |
322 | 334 |
DbUtils.closeQuietly(st); |
323 | 335 |
DbUtils.closeQuietly(connection); |
336 |
java.sql.Timestamp timestamp = new java.sql.Timestamp(System.currentTimeMillis()); |
|
337 |
//System.out.println("DB end "+timestamp); |
|
338 |
log.info("Query end..." + timestamp); |
|
339 |
|
|
324 | 340 |
} |
325 | 341 |
return "-1"; |
326 | 342 |
} |
... | ... | |
347 | 363 |
ResultSet rs = null; |
348 | 364 |
try { |
349 | 365 |
connection = usageStatsDB.getConnection(); |
366 |
java.sql.Timestamp timestamp1 = new java.sql.Timestamp(System.currentTimeMillis()); |
|
367 |
//System.out.println("DB start "+timestamp1); |
|
368 |
log.info("Query started..." + timestamp1); |
|
369 |
|
|
350 | 370 |
//st = connection.prepareStatement("SELECT DISTINCT roid.id FROM public.result_oids roid, public.downloads_stats s WHERE s.result_id=roid.id AND roid.orid=? UNION SELECT DISTINCT roid.id FROM public.result_oids roid, public.views_stats s WHERE s.result_id=roid.id AND roid.orid=?"); |
351 |
st = connection.prepareStatement("SELECT DISTINCT roid.id FROM shadow.result_oids roid, shadow.usage_stats us WHERE us.result_id=roid.id AND roid.orid=?");
|
|
371 |
st = connection.prepareStatement("SELECT DISTINCT roid.id FROM result_oids roid, usage_stats us WHERE us.result_id=roid.id AND roid.orid=?");
|
|
352 | 372 |
st.setString(1, oid); |
353 | 373 |
st.setString(2, oid); |
354 | 374 |
|
... | ... | |
364 | 384 |
DbUtils.closeQuietly(rs); |
365 | 385 |
DbUtils.closeQuietly(st); |
366 | 386 |
DbUtils.closeQuietly(connection); |
387 |
java.sql.Timestamp timestamp = new java.sql.Timestamp(System.currentTimeMillis()); |
|
388 |
//System.out.println("DB end "+timestamp); |
|
389 |
log.info("Query end..." + timestamp); |
|
390 |
|
|
367 | 391 |
} |
368 | 392 |
} |
369 | 393 |
|
... | ... | |
373 | 397 |
ResultSet rs = null; |
374 | 398 |
try { |
375 | 399 |
connection = usageStatsDB.getConnection(); |
400 |
java.sql.Timestamp timestamp1 = new java.sql.Timestamp(System.currentTimeMillis()); |
|
401 |
//System.out.println("DB start "+timestamp1); |
|
402 |
log.info("Query started..." + timestamp1); |
|
403 |
|
|
376 | 404 |
//st = connection.prepareStatement("SELECT DISTINCT poid.id FROM public.result_pids poid, public.downloads_stats s WHERE s.result_id=poid.id AND poid.type='doi' AND poid.pid=? UNION SELECT DISTINCT poid.id FROM public.result_pids poid, public.views_stats s WHERE s.result_id=poid.id AND poid.type='doi' AND poid.pid=?"); |
377 |
st = connection.prepareStatement("SELECT DISTINCT poid.id FROM shadow.result_pids poid, shadow.usage_stats us WHERE us.result_id=poid.id AND poid.type='doi' AND poid.pid=?");
|
|
405 |
st = connection.prepareStatement("SELECT DISTINCT poid.id FROM result_pids poid, usage_stats us WHERE us.result_id=poid.id AND poid.type='doi' AND poid.pid=?");
|
|
378 | 406 |
st.setString(1, doi); |
379 | 407 |
st.setString(2, doi); |
380 | 408 |
|
... | ... | |
389 | 417 |
DbUtils.closeQuietly(rs); |
390 | 418 |
DbUtils.closeQuietly(st); |
391 | 419 |
DbUtils.closeQuietly(connection); |
420 |
java.sql.Timestamp timestamp = new java.sql.Timestamp(System.currentTimeMillis()); |
|
421 |
//System.out.println("DB end "+timestamp); |
|
422 |
log.info("Query end..." + timestamp); |
|
423 |
|
|
392 | 424 |
} |
393 | 425 |
} |
394 | 426 |
|
... | ... | |
409 | 441 |
endCalendar.setTime(endDate); |
410 | 442 |
int diffYear = endCalendar.get(Calendar.YEAR) - startCalendar.get(Calendar.YEAR); |
411 | 443 |
int diffMonth = diffYear * 12 + endCalendar.get(Calendar.MONTH) - startCalendar.get(Calendar.MONTH); |
412 |
*/ |
|
413 |
|
|
444 |
*/ |
|
414 | 445 |
try { |
415 | 446 |
connection = usageStatsDB.getConnection(); |
447 |
java.sql.Timestamp timestamp1 = new java.sql.Timestamp(System.currentTimeMillis()); |
|
448 |
//System.out.println("DB start "+timestamp1); |
|
449 |
log.info("Query started..." + timestamp1); |
|
450 |
|
|
416 | 451 |
if (repositoryIdentifier.equals("")) { |
417 | 452 |
if (itemDataType.equals("")) { |
418 | 453 |
//st = connection.prepareStatement("SELECT res.repository_id, r.title, r.publisher, r.source, rc.type, pids.pid, d.name, res.ddate, oids.orid, res.downloads, res.views FROM (SELECT coalesce(ds.repository_id, vs.repository_id) AS repository_id, coalesce(ds.result_id, vs.result_id) AS result_id, coalesce(ds.date, vs.date) AS ddate, coalesce(ds.sum, 0) AS downloads, coalesce(vs.sum,0) AS views FROM (SELECT s.repository_id, s.result_id, s.date, sum(s.count) FROM public.downloads_stats s WHERE s.date>=? AND s.date<=? AND s.result_id=? GROUP BY s.repository_id, s.result_id, s.date) AS ds FULL OUTER JOIN (SELECT s.repository_id, s.result_id, s.date, sum(s.count) FROM public.views_stats s WHERE s.date>=? AND s.date<=? AND s.result_id=? GROUP BY s.repository_id, s.result_id, s.date) AS vs ON ds.result_id=vs.result_id AND ds.date=vs.date) AS res JOIN public.result r ON res.result_id=r.id JOIN public.datasource d ON d.id=res.repository_id JOIN public.result_classifications rc ON rc.id=r.id LEFT JOIN (SELECT pids.id, string_agg(pids.pid, '#!#') AS pid FROM public.result_pids pids WHERE pids.id=? AND type='doi' GROUP BY pids.id) AS pids ON pids.id=r.id LEFT JOIN (SELECT oids.id, string_agg(oids.orid, '#!#') AS orid FROM public.result_oids oids WHERE oids.id=? GROUP BY oids.id) AS oids ON oids.id=r.id ORDER BY res.repository_id, res.ddate;"); |
419 |
st = connection.prepareStatement("SELECT res.repository_id, r.publisher, rc.type, pids.pid, d.name, res.date, oids.orid, res.downloads, res.views FROM (SELECT us.repository_id, us.result_id, us.date, us.downloads, us.views FROM shadow.usage_stats us WHERE us.date>=? AND us.date<=? AND us.result_id=?) AS res JOIN shadow..result r ON res.result_id=r.id JOIN shadow..datasource d ON d.id=res.repository_id JOIN shadow.result_classifications rc ON rc.id=r.id LEFT JOIN (SELECT pids.id, string_agg(pids.pid, '#!#') AS pid FROM shadow.result_pids pids WHERE pids.id=? AND type='doi' GROUP BY pids.id) AS pids ON pids.id=r.id LEFT JOIN (SELECT oids.id, string_agg(oids.orid, '#!#') AS orid FROM shadow.result_oids oids WHERE oids.id=? GROUP BY oids.id) AS oids ON oids.id=r.id ORDER BY res.repository_id, res.date;");
|
|
454 |
st = connection.prepareStatement("SELECT res.repository_id, r.title, r.publisher, r.source, rc.type, pids.pid, d.name, res.date, oids.orid, res.downloads, res.views FROM (SELECT us.repository_id, us.result_id, us.date, us.downloads, us.views FROM usage_stats us WHERE us.date>=? AND us.date<=? AND us.result_id=?) AS res JOIN result r ON res.result_id=r.id JOIN datasource d ON d.id=res.repository_id JOIN result_classifications rc ON rc.id=r.id LEFT JOIN (SELECT pids.id, string_agg(pids.pid, '#!#') AS pid FROM result_pids pids WHERE pids.id=? AND type='doi' GROUP BY pids.id) AS pids ON pids.id=r.id LEFT JOIN (SELECT oids.id, string_agg(oids.orid, '#!#') AS orid FROM result_oids oids WHERE oids.id=? GROUP BY oids.id) AS oids ON oids.id=r.id ORDER BY res.repository_id, res.date;");
|
|
420 | 455 |
st.setString(1, beginDateStr); |
421 | 456 |
st.setString(2, endDateStr); |
422 | 457 |
st.setString(3, openaire); |
... | ... | |
427 | 462 |
st.setString(5, openaire); |
428 | 463 |
} else { |
429 | 464 |
//st = connection.prepareStatement("SELECT res.repository_id, r.title, r.publisher, r.source, rc.type, pids.pid, d.name, res.ddate, oids.orid, res.downloads, res.views FROM (SELECT coalesce(ds.repository_id, vs.repository_id) AS repository_id, coalesce(ds.result_id, vs.result_id) AS result_id, coalesce(ds.date, vs.date) AS ddate, coalesce(ds.sum, 0) AS downloads, coalesce(vs.sum,0) AS views FROM (SELECT s.repository_id, s.result_id, s.date, sum(s.count) FROM public.downloads_stats s WHERE s.date>=? AND s.date<=? AND s.result_id=? GROUP BY s.repository_id, s.result_id, s.date) AS ds FULL OUTER JOIN (SELECT s.repository_id, s.result_id, s.date, sum(s.count) FROM public.views_stats s WHERE s.date>=? AND s.date<=? AND s.result_id=? GROUP BY s.repository_id, s.result_id, s.date) AS vs ON ds.result_id=vs.result_id AND ds.date=vs.date) AS res JOIN public.result r ON res.result_id=r.id JOIN public.datasource d ON d.id=res.repository_id JOIN public.result_classifications rc ON rc.id=r.id AND rc.type=? LEFT JOIN (SELECT pids.id, string_agg(pids.pid, '#!#') AS pid FROM public.result_pids pids WHERE pids.id=? AND type='doi' GROUP BY pids.id) AS pids ON pids.id=r.id LEFT JOIN (SELECT oids.id, string_agg(oids.orid, '#!#') AS orid FROM public.result_oids oids WHERE oids.id=? GROUP BY oids.id) AS oids ON oids.id=r.id ORDER BY res.repository_id, res.ddate;"); |
430 |
st = connection.prepareStatement("SELECT res.repository_id, r.publisher, rc.type, pids.pid, d.name, res.date, oids.orid, res.downloads, res.views FROM (SELECT us.repository_id, us.result_id, us.date, us.downloads, us.views FROM usage_stats us WHERE us.date>=? AND us.date<=? AND us.result_id=?) AS res JOIN shadow.result r ON res.result_id=r.id JOIN shadow.datasource d ON d.id=res.repository_id JOIN shadow.result_classifications rc ON rc.id=r.id AND rc.type=? LEFT JOIN (SELECT pids.id, string_agg(pids.pid, '#!#') AS pid FROM shadow.result_pids pids WHERE pids.id=? AND type='doi' GROUP BY pids.id) AS pids ON pids.id=r.id LEFT JOIN (SELECT oids.id, string_agg(oids.orid, '#!#') AS orid FROM shadow.result_oids oids WHERE oids.id=? GROUP BY oids.id) AS oids ON oids.id=r.id ORDER BY res.repository_id, res.date;");
|
|
465 |
st = connection.prepareStatement("SELECT res.repository_id, r.title, r.publisher, r.source, rc.type, pids.pid, d.name, res.date, oids.orid, res.downloads, res.views FROM (SELECT us.repository_id, us.result_id, us.date, us.downloads, us.views FROM usage_stats us WHERE us.date>=? AND us.date<=? AND us.result_id=?) AS res JOIN result r ON res.result_id=r.id JOIN datasource d ON d.id=res.repository_id JOIN result_classifications rc ON rc.id=r.id AND rc.type=? LEFT JOIN (SELECT pids.id, string_agg(pids.pid, '#!#') AS pid FROM result_pids pids WHERE pids.id=? AND type='doi' GROUP BY pids.id) AS pids ON pids.id=r.id LEFT JOIN (SELECT oids.id, string_agg(oids.orid, '#!#') AS orid FROM result_oids oids WHERE oids.id=? GROUP BY oids.id) AS oids ON oids.id=r.id ORDER BY res.repository_id, res.date;");
|
|
431 | 466 |
st.setString(1, beginDateStr); |
432 | 467 |
st.setString(2, endDateStr); |
433 | 468 |
st.setString(3, openaire); |
... | ... | |
441 | 476 |
} else { |
442 | 477 |
if (itemDataType.equals("")) { |
443 | 478 |
//st = connection.prepareStatement("SELECT res.repository_id, r.title, r.publisher, r.source, rc.type, pids.pid, d.name, res.ddate, oids.orid, res.downloads, res.views FROM (SELECT coalesce(ds.repository_id, vs.repository_id) AS repository_id, coalesce(ds.result_id, vs.result_id) AS result_id, coalesce(ds.date, vs.date) AS ddate, coalesce(ds.sum, 0) AS downloads, coalesce(vs.sum,0) AS views FROM (SELECT s.repository_id, s.result_id, s.date, sum(s.count) FROM public.downloads_stats s WHERE s.date>=? AND s.date<=? AND s.result_id=? AND s.repository_id=? GROUP BY s.repository_id, s.result_id, s.date) AS ds FULL OUTER JOIN (SELECT s.repository_id, s.result_id, s.date, sum(s.count) FROM public.views_stats s WHERE s.date>=? AND s.date<=? AND s.result_id=? AND s.repository_id=? GROUP BY s.repository_id, s.result_id, s.date) AS vs ON ds.result_id=vs.result_id AND ds.date=vs.date) AS res JOIN public.result r ON res.result_id=r.id JOIN public.datasource d ON d.id=res.repository_id JOIN public.result_classifications rc ON rc.id=r.id LEFT JOIN (SELECT pids.id, string_agg(pids.pid, '#!#') AS pid FROM public.result_pids pids WHERE pids.id=? AND type='doi' GROUP BY pids.id) AS pids ON pids.id=r.id LEFT JOIN (SELECT oids.id, string_agg(oids.orid, '#!#') AS orid FROM public.result_oids oids WHERE oids.id=? GROUP BY oids.id) AS oids ON oids.id=r.id ORDER BY res.repository_id, res.ddate;"); |
444 |
st = connection.prepareStatement("SELECT res.repository_id, r.publisher, rc.type, pids.pid, d.name, res.date, oids.orid, res.downloads, res.views FROM (SELECT us.repository_id, us.result_id, us.date, us.downloads, us.views FROM usage_stats us WHERE us.date>=? AND us.date<=? AND us.result_id=? AND us.repository_id=?) AS res JOIN shadow.result r ON res.result_id=r.id JOIN shadow.datasource d ON d.id=res.repository_id JOIN shadow.result_classifications rc ON rc.id=r.id LEFT JOIN (SELECT pids.id, string_agg(pids.pid, '#!#') AS pid FROM shadow.result_pids pids WHERE pids.id=? AND type='doi' GROUP BY pids.id) AS pids ON pids.id=r.id LEFT JOIN (SELECT oids.id, string_agg(oids.orid, '#!#') AS orid FROM shadow.result_oids oids WHERE oids.id=? GROUP BY oids.id) AS oids ON oids.id=r.id ORDER BY res.repository_id, res.date;");
|
|
479 |
st = connection.prepareStatement("SELECT res.repository_id, r.title, r.publisher, r.source, rc.type, pids.pid, d.name, res.date, oids.orid, res.downloads, res.views FROM (SELECT us.repository_id, us.result_id, us.date, us.downloads, us.views FROM usage_stats us WHERE us.date>=? AND us.date<=? AND us.result_id=? AND us.repository_id=?) AS res JOIN public.result r ON res.result_id=r.id JOIN public.datasource d ON d.id=res.repository_id JOIN public.result_classifications rc ON rc.id=r.id LEFT JOIN (SELECT pids.id, string_agg(pids.pid, '#!#') AS pid FROM public.result_pids pids WHERE pids.id=? AND type='doi' GROUP BY pids.id) AS pids ON pids.id=r.id LEFT JOIN (SELECT oids.id, string_agg(oids.orid, '#!#') AS orid FROM public.result_oids oids WHERE oids.id=? GROUP BY oids.id) AS oids ON oids.id=r.id ORDER BY res.repository_id, res.date;");
|
|
445 | 480 |
st.setString(1, beginDateStr); |
446 | 481 |
st.setString(2, endDateStr); |
447 | 482 |
st.setString(3, openaire); |
... | ... | |
454 | 489 |
st.setString(6, openaire); |
455 | 490 |
} else { |
456 | 491 |
//st = connection.prepareStatement("SELECT res.repository_id, r.title, r.publisher, r.source, rc.type, pids.pid, d.name, res.ddate, oids.orid, res.downloads, res.views FROM (SELECT coalesce(ds.repository_id, vs.repository_id) AS repository_id, coalesce(ds.result_id, vs.result_id) AS result_id, coalesce(ds.date, vs.date) AS ddate, coalesce(ds.sum, 0) AS downloads, coalesce(vs.sum,0) AS views FROM (SELECT s.repository_id, s.result_id, s.date, sum(s.count) FROM public.downloads_stats s WHERE s.date>=? AND s.date<=? AND s.result_id=? AND s.repository_id=? GROUP BY s.repository_id, s.result_id, s.date) AS ds FULL OUTER JOIN (SELECT s.repository_id, s.result_id, s.date, sum(s.count) FROM public.views_stats s WHERE s.date>=? AND s.date<=? AND s.result_id=? AND s.repository_id=? GROUP BY s.repository_id, s.result_id, s.date) AS vs ON ds.result_id=vs.result_id AND ds.date=vs.date) AS res JOIN public.result r ON res.result_id=r.id JOIN public.datasource d ON d.id=res.repository_id JOIN public.result_classifications rc ON rc.id=r.id AND rc.type=? LEFT JOIN (SELECT pids.id, string_agg(pids.pid, '#!#') AS pid FROM public.result_pids pids WHERE pids.id=? AND type='doi' GROUP BY pids.id) AS pids ON pids.id=r.id LEFT JOIN (SELECT oids.id, string_agg(oids.orid, '#!#') AS orid FROM public.result_oids oids WHERE oids.id=? GROUP BY oids.id) AS oids ON oids.id=r.id ORDER BY res.repository_id, res.ddate;"); |
457 |
st = connection.prepareStatement("SELECT res.repository_id, r.publisher, rc.type, pids.pid, d.name, res.date, oids.orid, res.downloads, res.views FROM (SELECT us.repository_id, us.result_id, us.date, us.downloads, us.views FROM usage_stats us WHERE us.date>=? AND us.date<=? AND us.result_id=? AND us.repository_id=?) AS res JOIN shadow.result r ON res.result_id=r.id JOIN shadow.datasource d ON d.id=res.repository_id JOIN shadow.result_classifications rc ON rc.id=r.id AND rc.type=? LEFT JOIN (SELECT pids.id, string_agg(pids.pid, '#!#') AS pid FROM shadow.result_pids pids WHERE pids.id=? AND type='doi' GROUP BY pids.id) AS pids ON pids.id=r.id LEFT JOIN (SELECT oids.id, string_agg(oids.orid, '#!#') AS orid FROM shadow.result_oids oids WHERE oids.id=? GROUP BY oids.id) AS oids ON oids.id=r.id ORDER BY res.repository_id, res.date;");
|
|
492 |
st = connection.prepareStatement("SELECT res.repository_id, r.title, r.publisher, r.source, rc.type, pids.pid, d.name, res.date, oids.orid, res.downloads, res.views FROM (SELECT us.repository_id, us.result_id, us.date, us.downloads, us.views FROM usage_stats us WHERE us.date>=? AND us.date<=? AND us.result_id=? AND us.repository_id=?) AS res JOIN result r ON res.result_id=r.id JOIN datasource d ON d.id=res.repository_id JOIN result_classifications rc ON rc.id=r.id LEFT JOIN (SELECT pids.id, string_agg(pids.pid, '#!#') AS pid FROM result_pids pids WHERE pids.id=? AND type='doi' GROUP BY pids.id) AS pids ON pids.id=r.id LEFT JOIN (SELECT oids.id, string_agg(oids.orid, '#!#') AS orid FROM result_oids oids WHERE oids.id=? GROUP BY oids.id) AS oids ON oids.id=r.id ORDER BY res.repository_id, res.date;");
|
|
458 | 493 |
st.setString(1, beginDateStr); |
459 | 494 |
st.setString(2, endDateStr); |
460 | 495 |
st.setString(3, openaire); |
... | ... | |
564 | 599 |
DbUtils.closeQuietly(rs); |
565 | 600 |
DbUtils.closeQuietly(st); |
566 | 601 |
DbUtils.closeQuietly(connection); |
602 |
java.sql.Timestamp timestamp = new java.sql.Timestamp(System.currentTimeMillis()); |
|
603 |
//System.out.println("DB end "+timestamp); |
|
604 |
log.info("Query end..." + timestamp); |
|
605 |
|
|
567 | 606 |
} |
568 | 607 |
} |
608 |
|
|
569 | 609 |
public void executeRepo(List<ReportItem> reportItems, String repositoryIdentifier, String itemDataType, Date beginDate, Date endDate, String granularity) { |
570 | 610 |
SimpleDateFormat report_dateFormat = new SimpleDateFormat("yyyy-MM-dd"); |
571 | 611 |
SimpleDateFormat postgresFormat = new SimpleDateFormat("yyyy/MM"); |
... | ... | |
578 | 618 |
|
579 | 619 |
try { |
580 | 620 |
connection = usageStatsDB.getConnection(); |
621 |
java.sql.Timestamp timestamp1 = new java.sql.Timestamp(System.currentTimeMillis()); |
|
622 |
//System.out.println("DB start "+timestamp1); |
|
623 |
log.info("Query started..." + timestamp1); |
|
581 | 624 |
|
582 | 625 |
if (repositoryIdentifier.equals("")) { |
583 | 626 |
if (itemDataType.equals("")) { |
584 | 627 |
//st = connection.prepareStatement("SELECT d.id, d.name, d.websiteurl, dois.orid, res.ddate, res.downloads, res.views FROM (SELECT coalesce(ds.source, vs.source), coalesce(ds.repository_id, vs.repository_id) AS repository_id, coalesce(ds.date, vs.date) AS ddate, coalesce(ds.sum, 0) AS downloads, coalesce(vs.sum,0) AS views FROM (SELECT s.source, s.repository_id, s.date, sum(s.count) FROM public.downloads_stats s WHERE s.date>=? AND s.date<=? GROUP BY s.source, s.repository_id, s.date) AS ds FULL OUTER JOIN (SELECT s.source, s.repository_id, s.date, sum(s.count) FROM public.views_stats s WHERE s.date>=? AND s.date<=? GROUP BY s.source, s.repository_id, s.date) AS vs ON ds.source=vs.source AND ds.repository_id=vs.repository_id AND ds.date=vs.date) AS res JOIN public.datasource d ON d.id=res.repository_id JOIN public.datasource_oids dois ON d.id=dois.id WHERE dois.orid LIKE 'opendoar%' ORDER BY d.id, d.name, res.ddate ASC;"); |
585 |
st = connection.prepareStatement("SELECT d.id, d.name, d.websiteurl, dois.orid, res.date, res.downloads, res.views FROM (SELECT us.source, us.repository_id, us.date, sum(us.downloads) AS downloads, sum(us.views) AS views FROM shadow.usage_stats us WHERE us.date>=? AND us.date<=? GROUP BY us.source, us.repository_id, us.date) AS res JOIN shadow.datasource d ON d.id=res.repository_id JOIN shadow.datasource_oids dois ON d.id=dois.id WHERE dois.orid LIKE 'opendoar%' ORDER BY d.id, d.name, res.date ASC;");
|
|
628 |
st = connection.prepareStatement("SELECT d.id, d.name, d.websiteurl, dois.orid, res.date, res.downloads, res.views FROM (SELECT us.source, us.repository_id, us.date, sum(us.downloads) AS downloads, sum(us.views) AS views FROM usage_stats us WHERE us.date>=? AND us.date<=? GROUP BY us.source, us.repository_id, us.date) AS res JOIN datasource d ON d.id=res.repository_id JOIN datasource_oids dois ON d.id=dois.id WHERE dois.orid LIKE 'opendoar%' ORDER BY d.id, d.name, res.date ASC;");
|
|
586 | 629 |
st.setString(1, beginDateStr); |
587 | 630 |
st.setString(2, endDateStr); |
588 | 631 |
//st.setString(3, beginDateStr); |
589 | 632 |
//st.setString(4, endDateStr); |
590 | 633 |
} else { |
591 | 634 |
//st = connection.prepareStatement("SELECT d.id, d.name, d.websiteurl, dois.orid, res.ddate, res.downloads, res.views FROM (SELECT coalesce(ds.source, vs.source), coalesce(ds.repository_id, vs.repository_id) AS repository_id, coalesce(ds.date, vs.date) AS ddate, coalesce(ds.sum, 0) AS downloads, coalesce(vs.sum,0) AS views FROM (SELECT s.source, s.repository_id, s.date, sum(s.count) FROM public.downloads_stats s, result_classifications rc WHERE rc.id=s.result_id AND s.date>=? AND s.date<=? AND rc.type=? GROUP BY s.source, s.repository_id, s.date) AS ds FULL OUTER JOIN (SELECT s.source, s.repository_id, s.date, sum(s.count) FROM public.views_stats s, public.result_classifications rc WHERE rc.id=s.result_id AND s.date>=? AND s.date<=? AND rc.type=? GROUP BY s.source, s.repository_id, s.date) AS vs ON ds.source=vs.source AND ds.repository_id=vs.repository_id AND ds.date=vs.date) AS res JOIN public.datasource d ON d.id=res.repository_id JOIN public.datasource_oids dois ON d.id=dois.id WHERE dois.orid LIKE 'opendoar%' ORDER BY d.id, d.name, res.ddate ASC;"); |
592 |
//st = connection.prepareStatement("SELECT d.id, d.name, d.websiteurl, dois.orid, res.date, res.downloads, res.views FROM (SELECT us.source, us.repository_id, us.date, sum(us.downloads) AS downloads, sum(us.views) AS views FROM public.usage_stats us, result_classifications rc WHERE rc.id=us.result_id AND us.date>=? AND us.date<=? AND rc.type=? GROUP BY us.source, us.repository_id, us.date) AS res JOIN public.datasource d ON d.id=res.repository_id JOIN public.datasource_oids dois ON d.id=dois.id WHERE dois.orid LIKE 'opendoar%' ORDER BY d.id, d.name, res.date ASC;"); |
|
593 |
st = connection.prepareStatement("SELECT d.id, d.name, d.websiteurl, dois.orid, res.date, res.downloads, res.views FROM (SELECT us.source, us.repository_id, us.date, sum(us.downloads) AS downloads, sum(us.views) AS views FROM shadow.usage_stats us, result_classifications rc WHERE rc.id=us.result_id AND us.date>=? AND us.date<=? AND rc.type=? GROUP BY us.source, us.repository_id, us.date) AS res JOIN shadow.datasource d ON d.id=res.repository_id JOIN shadow.datasource_oids dois ON d.id=dois.id WHERE dois.orid LIKE 'opendoar%' ORDER BY d.id, d.name, res.date ASC;"); |
|
635 |
st = connection.prepareStatement("SELECT d.id, d.name, d.websiteurl, dois.orid, res.date, res.downloads, res.views FROM (SELECT us.source, us.repository_id, us.date, sum(us.downloads) AS downloads, sum(us.views) AS views FROM usage_stats us, result_classifications rc WHERE rc.id=us.result_id AND us.date>=? AND us.date<=? AND rc.type=? GROUP BY us.source, us.repository_id, us.date) AS res JOIN datasource d ON d.id=res.repository_id JOIN datasource_oids dois ON d.id=dois.id WHERE dois.orid LIKE 'opendoar%' ORDER BY d.id, d.name, res.date ASC;"); |
|
594 | 636 |
st.setString(1, beginDateStr); |
595 | 637 |
st.setString(2, endDateStr); |
596 | 638 |
st.setString(3, itemDataType); |
... | ... | |
601 | 643 |
} else { |
602 | 644 |
if (itemDataType.equals("")) { |
603 | 645 |
//st = connection.prepareStatement("SELECT d.id, d.name, d.websiteurl, dois.orid, res.ddate, res.downloads, res.views FROM (SELECT coalesce(ds.source, vs.source), coalesce(ds.repository_id, vs.repository_id) AS repository_id, coalesce(ds.date, vs.date) AS ddate, coalesce(ds.sum, 0) AS downloads, coalesce(vs.sum,0) AS views FROM (SELECT s.source, s.repository_id, s.date, sum(s.count) FROM public.downloads_stats s WHERE s.date>=? AND s.date<=? AND s.repository_id=? GROUP BY s.source, s.repository_id, s.date) AS ds FULL OUTER JOIN (SELECT s.source, s.repository_id, s.date, sum(s.count) FROM public.views_stats s WHERE s.date>=? AND s.date<=? AND s.repository_id=? GROUP BY s.source, s.repository_id, s.date) AS vs ON ds.source=vs.source AND ds.repository_id=vs.repository_id AND ds.date=vs.date) AS res JOIN public.datasource d ON d.id=res.repository_id JOIN public.datasource_oids dois ON d.id=dois.id WHERE dois.orid LIKE 'opendoar%' ORDER BY d.id, d.name, res.ddate ASC;"); |
604 |
st = connection.prepareStatement("SELECT d.id, d.name, d.websiteurl, dois.orid, res.date, res.downloads, res.views FROM (SELECT us.source, us.repository_id, us.date, sum(us.downloads) AS downloads, sum(us.views) AS views FROM shadow.usage_stats us WHERE us.date>=? AND us.date<=? AND us.repository_id=? GROUP BY us.source, us.repository_id, us.date) AS res JOIN shadow.datasource d ON d.id=res.repository_id JOIN shadow.datasource_oids dois ON d.id=dois.id WHERE dois.orid LIKE 'opendoar%' ORDER BY d.id, d.name, res.date ASC;");
|
|
646 |
st = connection.prepareStatement("SELECT d.id, d.name, d.websiteurl, dois.orid, res.date, res.downloads, res.views FROM (SELECT us.source, us.repository_id, us.date, sum(us.downloads) AS downloads, sum(us.views) AS views FROM usage_stats us WHERE us.date>=? AND us.date<=? AND us.repository_id=? GROUP BY us.source, us.repository_id, us.date) AS res JOIN datasource d ON d.id=res.repository_id JOIN datasource_oids dois ON d.id=dois.id WHERE dois.orid LIKE 'opendoar%' ORDER BY d.id, d.name, res.date ASC;");
|
|
605 | 647 |
st.setString(1, beginDateStr); |
606 | 648 |
st.setString(2, endDateStr); |
607 | 649 |
st.setString(3, repositoryIdentifier); |
... | ... | |
610 | 652 |
//st.setString(6, repositoryIdentifier); |
611 | 653 |
} else { |
612 | 654 |
//st = connection.prepareStatement("SELECT d.id, d.name, d.websiteurl, dois.orid, res.ddate, res.downloads, res.views FROM (SELECT coalesce(ds.source, vs.source), coalesce(ds.repository_id, vs.repository_id) AS repository_id, coalesce(ds.date, vs.date) AS ddate, coalesce(ds.sum, 0) AS downloads, coalesce(vs.sum,0) AS views FROM (SELECT s.source, s.repository_id, s.date, sum(s.count) FROM public.downloads_stats s, public.result_classifications rc WHERE rc.id=s.result_id AND s.date>=? AND s.date<=? AND rc.type=? AND s.repository_id=? GROUP BY s.source, s.repository_id, s.date) AS ds FULL OUTER JOIN (SELECT s.source, s.repository_id, s.date, sum(s.count) FROM public.views_stats s, public.result_classifications rc WHERE rc.id=s.result_id AND s.date>=? AND s.date<=? AND rc.type=? AND s.repository_id=? GROUP BY s.source, s.repository_id, s.date) AS vs ON ds.source=vs.source AND ds.repository_id=vs.repository_id AND ds.date=vs.date) AS res JOIN public.datasource d ON d.id=res.repository_id JOIN public.datasource_oids dois ON d.id=dois.id WHERE dois.orid LIKE 'opendoar%' ORDER BY d.id, d.name, res.ddate ASC;"); |
613 |
st = connection.prepareStatement("SELECT d.id, d.name, d.websiteurl, dois.orid, res.date, res.downloads, res.views FROM (SELECT us.source, us.repository_id, us.date, sum(us.downloads) AS downloads, sum(us.views) AS views FROM shadow.usage_stats us, shadow.result_classifications rc WHERE rc.id=us.result_id AND us.date>=? AND us.date<=? AND rc.type=? AND us.repository_id=? GROUP BY us.source, us.repository_id, us.date) AS res JOIN shadow.datasource d ON d.id=res.repository_id JOIN shadow.datasource_oids dois ON d.id=dois.id WHERE dois.orid LIKE 'opendoar%' ORDER BY d.id, d.name, res.date ASC;");
|
|
655 |
st = connection.prepareStatement("SELECT d.id, d.name, d.websiteurl, dois.orid, res.date, res.downloads, res.views FROM (SELECT us.source, us.repository_id, us.date, sum(us.downloads) AS downloads, sum(us.views) AS views FROM usage_stats us, result_classifications rc WHERE rc.id=us.result_id AND us.date>=? AND us.date<=? AND rc.type=? AND us.repository_id=? GROUP BY us.source, us.repository_id, us.date) AS res JOIN datasource d ON d.id=res.repository_id JOIN datasource_oids dois ON d.id=dois.id WHERE dois.orid LIKE 'opendoar%' ORDER BY d.id, d.name, res.date ASC;");
|
|
614 | 656 |
st.setString(1, beginDateStr); |
615 | 657 |
st.setString(2, endDateStr); |
616 | 658 |
st.setString(3, itemDataType); |
... | ... | |
632 | 674 |
connection.close(); |
633 | 675 |
return; |
634 | 676 |
} |
635 |
*/ |
|
636 |
|
|
677 |
*/ |
|
637 | 678 |
rs = st.executeQuery(); |
638 | 679 |
String repository = ""; |
639 | 680 |
String lastDate = ""; |
... | ... | |
644 | 685 |
startCalendar.setTime(beginDate); |
645 | 686 |
Calendar endCalendar = Calendar.getInstance(); |
646 | 687 |
endCalendar.setTime(endDate); |
647 |
*/ |
|
648 |
|
|
688 |
*/ |
|
649 | 689 |
int ft_total = 0; |
650 | 690 |
int abstr = 0; |
651 | 691 |
if (granularity.equalsIgnoreCase("totals")) { |
... | ... | |
710 | 750 |
jedis.put(redis_key, "query", st.toString()); |
711 | 751 |
jedis.put(redis_key, "result", toJson(reportItems)); |
712 | 752 |
jedis.put(redis_key, "fetchMode", "3"); |
713 |
*/ |
|
714 |
|
|
753 |
*/ |
|
715 | 754 |
rs.close(); |
716 | 755 |
st.close(); |
717 | 756 |
connection.close(); |
... | ... | |
721 | 760 |
DbUtils.closeQuietly(rs); |
722 | 761 |
DbUtils.closeQuietly(st); |
723 | 762 |
DbUtils.closeQuietly(connection); |
763 |
java.sql.Timestamp timestamp = new java.sql.Timestamp(System.currentTimeMillis()); |
|
764 |
//System.out.println("DB end "+timestamp); |
|
765 |
log.info("Query end..." + timestamp); |
|
766 |
|
|
724 | 767 |
} |
725 | 768 |
} |
769 |
|
|
726 | 770 |
public void executeJournal(List<ReportItem> reportItems, String repositoryIdentifier, String itemDataType, Date beginDate, Date endDate, String granularity) { |
727 | 771 |
SimpleDateFormat report_dateFormat = new SimpleDateFormat("yyyy-MM-dd"); |
728 | 772 |
SimpleDateFormat postgresFormat = new SimpleDateFormat("yyyy/MM"); |
... | ... | |
735 | 779 |
|
736 | 780 |
try { |
737 | 781 |
connection = usageStatsDB.getConnection(); |
782 |
java.sql.Timestamp timestamp1 = new java.sql.Timestamp(System.currentTimeMillis()); |
|
783 |
//System.out.println("DB start "+timestamp1); |
|
784 |
log.info("Query started..." + timestamp1); |
|
738 | 785 |
|
739 | 786 |
if (repositoryIdentifier.equals("")) { |
740 | 787 |
if (itemDataType.equals("")) { |
741 | 788 |
//st = connection.prepareStatement("SELECT d.id, d.name, d.websiteurl, dois.orid, res.ddate, res.downloads, res.views FROM (SELECT coalesce(ds.source, vs.source), coalesce(ds.repository_id, vs.repository_id) AS repository_id, coalesce(ds.date, vs.date) AS ddate, coalesce(ds.sum, 0) AS downloads, coalesce(vs.sum,0) AS views FROM (SELECT s.source, s.repository_id, s.date, sum(s.count) FROM public.downloads_stats s WHERE s.date>=? AND s.date<=? GROUP BY s.source, s.repository_id, s.date) AS ds FULL OUTER JOIN (SELECT s.source, s.repository_id, s.date, sum(s.count) FROM public.views_stats s WHERE s.date>=? AND s.date<=? GROUP BY s.source, s.repository_id, s.date) AS vs ON ds.source=vs.source AND ds.repository_id=vs.repository_id AND ds.date=vs.date) AS res JOIN public.datasource d ON d.id=res.repository_id JOIN public.datasource_oids dois ON d.id=dois.id WHERE (d.type='Journal' OR d.type='Journal Aggregator/Publisher') ORDER BY d.id, d.name, res.ddate ASC;"); |
742 |
st = connection.prepareStatement("SELECT d.id, d.name, d.websiteurl, dois.orid, res.date, res.downloads, res.views FROM (SELECT us.source, us.repository_id, us.date, sum(us.downloads) as downloads, sum(us.views) as views FROM shadow.usage_stats us WHERE us.date>=? AND us.date<=? GROUP BY us.source, us.repository_id, us.date) AS res JOIN shadow.datasource d ON d.id=res.repository_id JOIN shadow.datasource_oids dois ON d.id=dois.id WHERE (d.type='Journal' OR d.type='Journal Aggregator/Publisher') ORDER BY d.id, d.name, res.date ASC;");
|
|
789 |
st = connection.prepareStatement("SELECT d.id, d.name, d.websiteurl, dois.orid, res.date, res.downloads, res.views FROM (SELECT us.source, us.repository_id, us.date, sum(us.downloads) as downloads, sum(us.views) as views FROM usage_stats us WHERE us.date>=? AND us.date<=? GROUP BY us.source, us.repository_id, us.date) AS res JOIN datasource d ON d.id=res.repository_id JOIN datasource_oids dois ON d.id=dois.id WHERE (d.type='Journal' OR d.type='Journal Aggregator/Publisher') ORDER BY d.id, d.name, res.date ASC;");
|
|
743 | 790 |
st.setString(1, beginDateStr); |
744 | 791 |
st.setString(2, endDateStr); |
745 | 792 |
//st.setString(3, beginDateStr); |
746 | 793 |
//st.setString(4, endDateStr); |
747 | 794 |
} else { |
748 | 795 |
//st = connection.prepareStatement("SELECT d.id, d.name, d.websiteurl, dois.orid, res.ddate, res.downloads, res.views FROM (SELECT coalesce(ds.source, vs.source), coalesce(ds.repository_id, vs.repository_id) AS repository_id, coalesce(ds.date, vs.date) AS ddate, coalesce(ds.sum, 0) AS downloads, coalesce(vs.sum,0) AS views FROM (SELECT s.source, s.repository_id, s.date, sum(s.count) FROM public.downloads_stats s, public.result_classifications rc WHERE rc.id=s.result_id AND s.date>=? AND s.date<=? AND rc.type=? GROUP BY s.source, s.repository_id, s.date) AS ds FULL OUTER JOIN (SELECT s.source, s.repository_id, s.date, sum(s.count) FROM public.views_stats s, public.result_classifications rc WHERE rc.id=s.result_id AND s.date>=? AND s.date<=? AND rc.type=? GROUP BY s.source, s.repository_id, s.date) AS vs ON ds.source=vs.source AND ds.repository_id=vs.repository_id AND ds.date=vs.date) AS res JOIN public.datasource d ON d.id=res.repository_id JOIN public.datasource_oids dois ON d.id=dois.id WHERE (d.type='Journal' OR d.type='Journal Aggregator/Publisher') ORDER BY d.id, d.name, res.ddate ASC;"); |
749 |
st = connection.prepareStatement("SELECT d.id, d.name, d.websiteurl, dois.orid, res.date, res.downloads, res.views FROM (SELECT us.source, us.repository_id, us.date, sum(us.downloads) as downloads, sum(us.views) as views FROM shadow.usage_stats us, shadow.result_classifications rc WHERE rc.id=us.result_id AND us.date>=? AND us.date<=? AND rc.type=? GROUP BY us.source, us.repository_id, us.date) AS res JOIN shadow.datasource d ON d.id=res.repository_id JOIN shadow.datasource_oids dois ON d.id=dois.id WHERE (d.type='Journal' OR d.type='Journal Aggregator/Publisher') ORDER BY d.id, d.name, res.date ASC;");
|
|
796 |
st = connection.prepareStatement("SELECT d.id, d.name, d.websiteurl, dois.orid, res.date, res.downloads, res.views FROM (SELECT us.source, us.repository_id, us.date, sum(us.downloads) as downloads, sum(us.views) as views FROM usage_stats us, result_classifications rc WHERE rc.id=us.result_id AND us.date>=? AND us.date<=? AND rc.type=? GROUP BY us.source, us.repository_id, us.date) AS res JOIN datasource d ON d.id=res.repository_id JOIN datasource_oids dois ON d.id=dois.id WHERE (d.type='Journal' OR d.type='Journal Aggregator/Publisher') ORDER BY d.id, d.name, res.date ASC;");
|
|
750 | 797 |
st.setString(1, beginDateStr); |
751 | 798 |
st.setString(2, endDateStr); |
752 | 799 |
st.setString(3, itemDataType); |
... | ... | |
757 | 804 |
} else { |
758 | 805 |
if (itemDataType.equals("")) { |
759 | 806 |
//st = connection.prepareStatement("SELECT d.id, d.name, d.websiteurl, dois.orid, res.ddate, res.downloads, res.views FROM (SELECT coalesce(ds.source, vs.source), coalesce(ds.repository_id, vs.repository_id) AS repository_id, coalesce(ds.date, vs.date) AS ddate, coalesce(ds.sum, 0) AS downloads, coalesce(vs.sum,0) AS views FROM (SELECT s.source, s.repository_id, s.date, sum(s.count) FROM public.downloads_stats s WHERE s.date>=? AND s.date<=? AND s.repository_id=? GROUP BY s.source, s.repository_id, s.date) AS ds FULL OUTER JOIN (SELECT s.source, s.repository_id, s.date, sum(s.count) FROM public.views_stats s WHERE s.date>=? AND s.date<=? AND s.repository_id=? GROUP BY s.source, s.repository_id, s.date) AS vs ON ds.source=vs.source AND ds.repository_id=vs.repository_id AND ds.date=vs.date) AS res JOIN public.datasource d ON d.id=res.repository_id JOIN public.datasource_oids dois ON d.id=dois.id WHERE (d.type='Journal' OR d.type='Journal Aggregator/Publisher') ORDER BY d.id, d.name, res.ddate ASC;"); |
760 |
st = connection.prepareStatement("SELECT d.id, d.name, d.websiteurl, dois.orid, res.date, res.downloads, res.views FROM (SELECT us.source, us.repository_id, us.date, sum(us.downloads) as downloads, sum(us.views) as views FROM shadow.usage_stats us WHERE us.date>=? AND us.date<=? AND us.repository_id=? GROUP BY us.source, us.repository_id, us.date) AS res JOIN shadow.datasource d ON d.id=res.repository_id JOIN shadow.datasource_oids dois ON d.id=dois.id WHERE (d.type='Journal' OR d.type='Journal Aggregator/Publisher') ORDER BY d.id, d.name, res.date ASC;");
|
|
807 |
st = connection.prepareStatement("SELECT d.id, d.name, d.websiteurl, dois.orid, res.date, res.downloads, res.views FROM (SELECT us.source, us.repository_id, us.date, sum(us.downloads) as downloads, sum(us.views) as views FROM usage_stats us WHERE us.date>=? AND us.date<=? AND us.repository_id=? GROUP BY us.source, us.repository_id, us.date) AS res JOIN datasource d ON d.id=res.repository_id JOIN datasource_oids dois ON d.id=dois.id WHERE (d.type='Journal' OR d.type='Journal Aggregator/Publisher') ORDER BY d.id, d.name, res.date ASC;");
|
|
761 | 808 |
st.setString(1, beginDateStr); |
762 | 809 |
st.setString(2, endDateStr); |
763 | 810 |
st.setString(3, repositoryIdentifier); |
... | ... | |
766 | 813 |
//st.setString(6, repositoryIdentifier); |
767 | 814 |
} else { |
768 | 815 |
//st = connection.prepareStatement("SELECT d.id, d.name, d.websiteurl, dois.orid, res.ddate, res.downloads, res.views FROM (SELECT coalesce(ds.source, vs.source), coalesce(ds.repository_id, vs.repository_id) AS repository_id, coalesce(ds.date, vs.date) AS ddate, coalesce(ds.sum, 0) AS downloads, coalesce(vs.sum,0) AS views FROM (SELECT s.source, s.repository_id, s.date, sum(s.count) FROM public.downloads_stats s, public.result_classifications rc WHERE rc.id=s.result_id AND s.date>=? AND s.date<=? AND rc.type=? AND s.repository_id=? GROUP BY s.source, s.repository_id, s.date) AS ds FULL OUTER JOIN (SELECT s.source, s.repository_id, s.date, sum(s.count) FROM public.views_stats s, public.result_classifications rc WHERE rc.id=s.result_id AND s.date>=? AND s.date<=? AND rc.type=? AND s.repository_id=? GROUP BY s.source, s.repository_id, s.date) AS vs ON ds.source=vs.source AND ds.repository_id=vs.repository_id AND ds.date=vs.date) AS res JOIN public.datasource d ON d.id=res.repository_id JOIN public.datasource_oids dois ON d.id=dois.id WHERE (d.type='Journal' OR d.type='Journal Aggregator/Publisher') ORDER BY d.id, d.name, res.ddate ASC;"); |
769 |
st = connection.prepareStatement("SELECT d.id, d.name, d.websiteurl, dois.orid, res.date, res.downloads, res.views FROM (SELECT us.source, us.repository_id, us.date, sum(us.downloads) as downloads, sum(us.views) as views FROM shadow.usage_stats us, shadow.result_classifications rc WHERE rc.id=us.result_id AND us.date>=? AND us.date<=? AND rc.type=? AND us.repository_id=? GROUP BY us.source, us.repository_id, us.date) AS res JOIN shadow.datasource d ON d.id=res.repository_id JOIN shadow.datasource_oids dois ON d.id=dois.id WHERE (d.type='Journal' OR d.type='Journal Aggregator/Publisher') ORDER BY d.id, d.name, res.date ASC;");
|
|
816 |
st = connection.prepareStatement("SELECT d.id, d.name, d.websiteurl, dois.orid, res.date, res.downloads, res.views FROM (SELECT us.source, us.repository_id, us.date, sum(us.downloads) as downloads, sum(us.views) as views FROM usage_stats us, result_classifications rc WHERE rc.id=us.result_id AND us.date>=? AND us.date<=? AND rc.type=? AND us.repository_id=? GROUP BY us.source, us.repository_id, us.date) AS res JOIN datasource d ON d.id=res.repository_id JOIN datasource_oids dois ON d.id=dois.id WHERE (d.type='Journal' OR d.type='Journal Aggregator/Publisher') ORDER BY d.id, d.name, res.date ASC;");
|
|
770 | 817 |
st.setString(1, beginDateStr); |
771 | 818 |
st.setString(2, endDateStr); |
772 | 819 |
st.setString(3, itemDataType); |
... | ... | |
788 | 835 |
connection.close(); |
789 | 836 |
return; |
790 | 837 |
} |
791 |
*/ |
|
792 |
|
|
838 |
*/ |
|
793 | 839 |
rs = st.executeQuery(); |
794 | 840 |
String repository = ""; |
795 | 841 |
String lastDate = ""; |
... | ... | |
800 | 846 |
startCalendar.setTime(beginDate); |
801 | 847 |
Calendar endCalendar = Calendar.getInstance(); |
802 | 848 |
endCalendar.setTime(endDate); |
803 |
*/ |
|
804 |
|
|
849 |
*/ |
|
805 | 850 |
int ft_total = 0; |
806 | 851 |
int abstr = 0; |
807 | 852 |
if (granularity.equalsIgnoreCase("totals")) { |
... | ... | |
815 | 860 |
reportItem = new ReportItem(null, rs.getString(2), "Platform", null); |
816 | 861 |
reportItem.addIdentifier(new ItemIdentifier("OpenAIRE", rs.getString(1))); |
817 | 862 |
reportItem.addIdentifier(new ItemIdentifier("ISSN", rs.getString(4).substring(rs.getString(4).lastIndexOf(":") + 1))); |
818 |
if(rs.getString(3) != null) { |
|
863 |
if (rs.getString(3) != null) {
|
|
819 | 864 |
reportItem.addIdentifier(new ItemIdentifier("URL", rs.getString(3))); |
820 | 865 |
} |
821 | 866 |
ft_total = 0; |
... | ... | |
844 | 889 |
reportItem = new ReportItem(null, rs.getString(2), "Platform", null); |
845 | 890 |
reportItem.addIdentifier(new ItemIdentifier("OpenAIRE", rs.getString(1))); |
846 | 891 |
reportItem.addIdentifier(new ItemIdentifier("ISSN", rs.getString(4).substring(rs.getString(4).lastIndexOf(":") + 1))); |
847 |
if(rs.getString(3) != null) { |
|
892 |
if (rs.getString(3) != null) {
|
|
848 | 893 |
reportItem.addIdentifier(new ItemIdentifier("URL", rs.getString(3))); |
849 | 894 |
} |
850 | 895 |
} |
... | ... | |
870 | 915 |
jedis.put(redis_key, "query", st.toString()); |
871 | 916 |
jedis.put(redis_key, "result", toJson(reportItems)); |
872 | 917 |
jedis.put(redis_key, "fetchMode", "3"); |
873 |
*/ |
|
874 |
|
|
918 |
*/ |
|
875 | 919 |
rs.close(); |
876 | 920 |
st.close(); |
877 | 921 |
connection.close(); |
... | ... | |
881 | 925 |
DbUtils.closeQuietly(rs); |
882 | 926 |
DbUtils.closeQuietly(st); |
883 | 927 |
DbUtils.closeQuietly(connection); |
928 |
java.sql.Timestamp timestamp = new java.sql.Timestamp(System.currentTimeMillis()); |
|
929 |
//System.out.println("DB end "+timestamp); |
|
930 |
log.info("Query end..." + timestamp); |
|
931 |
|
|
884 | 932 |
} |
885 | 933 |
} |
886 | 934 |
|
... | ... | |
889 | 937 |
SimpleDateFormat postgresFormat = new SimpleDateFormat("yyyy/MM"); |
890 | 938 |
String beginDateStr = postgresFormat.format(beginDate); |
891 | 939 |
String endDateStr = postgresFormat.format(endDate); |
892 |
|
|
940 |
|
|
893 | 941 |
Connection connection = null; |
894 | 942 |
PreparedStatement st = null; |
895 | 943 |
ResultSet rs = null; |
... | ... | |
898 | 946 |
connection = usageStatsDB.getConnection(); |
899 | 947 |
java.sql.Timestamp timestamp1 = new java.sql.Timestamp(System.currentTimeMillis()); |
900 | 948 |
//System.out.println("DB start "+timestamp1); |
901 |
log.info("Query started..."+timestamp1);
|
|
949 |
log.info("Query started..." + timestamp1);
|
|
902 | 950 |
if (itemDataType.equals("")) { |
903 | 951 |
//st = connection.prepareStatement("SELECT res.result_id, r.title, r.publisher, r.source, rc.type, pids.pid, d.name, res.ddate, oids.orid, res.downloads, res.views FROM (SELECT coalesce(ds.repository_id, vs.repository_id) AS repository_id, coalesce(ds.result_id, vs.result_id) AS result_id, coalesce(ds.date, vs.date) AS ddate, coalesce(ds.sum, 0) AS downloads, coalesce(vs.sum,0) AS views FROM (SELECT s.repository_id, s.result_id, s.date, sum(s.count) FROM public.downloads_stats s WHERE s.date>=? AND s.date<=? AND s.repository_id=? GROUP BY s.repository_id, s.result_id, s.date) AS ds FULL OUTER JOIN (SELECT s.repository_id, s.result_id, s.date, sum(s.count) FROM public.views_stats s WHERE s.date>=? AND s.date<=? AND s.repository_id=? GROUP BY s.repository_id, s.result_id, s.date) AS vs ON ds.result_id=vs.result_id AND ds.date=vs.date) AS res JOIN public.result r ON res.result_id=r.id JOIN public.datasource d ON d.id=res.repository_id JOIN public.result_classifications rc ON rc.id=r.id LEFT JOIN (SELECT pids.id, string_agg(pids.pid, '#!#') AS pid FROM public.result_pids pids, public.result_datasources rd WHERE rd.id=pids.id AND type='doi' AND rd.datasource=? GROUP BY pids.id) AS pids ON pids.id=r.id LEFT JOIN (SELECT oids.id, string_agg(oids.orid, '#!#') AS orid FROM public.result_oids oids, public.result_datasources rd WHERE rd.id=oids.id AND rd.datasource=? GROUP BY oids.id) AS oids ON oids.id=r.id ORDER BY res.result_id, res.ddate;"); |
904 |
st = connection.prepareStatement("SELECT res.result_id, r.publisher, rc.type, pids.pid, d.name, res.date, oids.orid, res.downloads, res.views FROM (SELECT us.repository_id, us.result_id, us.date, sum(us.downloads) as downloads, sum(us.views) as views FROM shadow.usage_stats us WHERE us.date>=? AND us.date<=? AND us.repository_id=? GROUP BY us.repository_id, us.result_id, us.date) AS res JOIN shadow.result r ON res.result_id=r.id JOIN shadow.datasource d ON d.id=res.repository_id JOIN shadow.result_classifications rc ON rc.id=r.id LEFT JOIN (SELECT pids.id, string_agg(pids.pid, '#!#') AS pid FROM shadow.result_pids pids, shadow.result_datasources rd WHERE rd.id=pids.id AND type='doi' AND rd.datasource=? GROUP BY pids.id) AS pids ON pids.id=r.id LEFT JOIN (SELECT oids.id, string_agg(oids.orid, '#!#') AS orid FROM shadow.result_oids oids, shadow.result_datasources rd WHERE rd.id=oids.id AND rd.datasource=? GROUP BY oids.id) AS oids ON oids.id=r.id ORDER BY res.result_id, res.date;"); |
|
905 |
|
|
952 |
st = connection.prepareStatement("SELECT res.result_id, r.title, r.publisher, r.source, rc.type, pids.pid, d.name, res.date, oids.orid, res.downloads, res.views FROM (SELECT us.repository_id, us.result_id, us.date, sum(us.downloads) as downloads, sum(us.views) as views FROM usage_stats us WHERE us.date>=? AND us.date<=? AND us.repository_id=? GROUP BY us.repository_id, us.result_id, us.date) AS res JOIN result r ON res.result_id=r.id JOIN datasource d ON d.id=res.repository_id JOIN result_classifications rc ON rc.id=r.id LEFT JOIN (SELECT pids.id, string_agg(pids.pid, '#!#') AS pid FROM result_pids pids, result_datasources rd WHERE rd.id=pids.id AND type='doi' AND rd.datasource=? GROUP BY pids.id) AS pids ON pids.id=r.id LEFT JOIN (SELECT oids.id, string_agg(oids.orid, '#!#') AS orid FROM result_oids oids, result_datasources rd WHERE rd.id=oids.id AND rd.datasource=? GROUP BY oids.id) AS oids ON oids.id=r.id ORDER BY res.result_id, res.date;"); |
|
906 | 953 |
st.setString(1, beginDateStr); |
907 | 954 |
st.setString(2, endDateStr); |
908 | 955 |
st.setString(3, repositoryIdentifier); |
... | ... | |
914 | 961 |
} else { |
915 | 962 |
//st = connection.prepareStatement("SELECT res.result_id, r.title, r.publisher, r.source, rc.type, pids.pid, d.name, res.ddate, oids.orid, res.downloads, res.views FROM (SELECT coalesce(ds.repository_id, vs.repository_id) AS repository_id, coalesce(ds.result_id, vs.result_id) AS result_id, coalesce(ds.date, vs.date) AS ddate, coalesce(ds.sum, 0) AS downloads, coalesce(vs.sum,0) AS views FROM (SELECT s.repository_id, s.result_id, s.date, sum(s.count) FROM public.downloads_stats s WHERE s.date>=? AND s.date<=? AND s.repository_id=? GROUP BY s.repository_id, s.result_id, s.date) AS ds FULL OUTER JOIN (SELECT s.repository_id, s.result_id, s.date, sum(s.count) FROM public.views_stats s WHERE s.date>=? AND s.date<=? AND s.repository_id=? GROUP BY s.repository_id, s.result_id, s.date) AS vs ON ds.result_id=vs.result_id AND ds.date=vs.date) AS res JOIN public.result r ON res.result_id=r.id JOIN public.datasource d ON d.id=res.repository_id JOIN public.result_classifications rc ON rc.id=r.id LEFT JOIN (SELECT pids.id, string_agg(pids.pid, '#!#') AS pid FROM public.result_pids pids, result_datasources rd WHERE rd.id=pids.id AND type='doi' AND rd.datasource=? GROUP BY pids.id) AS pids ON pids.id=r.id LEFT JOIN (SELECT oids.id, string_agg(oids.orid, '#!#') AS orid FROM public.result_oids oids, public.result_datasources rd WHERE rd.id=oids.id AND rd.datasource=? GROUP BY oids.id) AS oids ON oids.id=r.id WHERE rc.type=? ORDER BY res.result_id, res.ddate;"); |
916 | 963 |
//st = connection.prepareStatement("SELECT res.result_id, r.title, r.publisher, r.source, rc.type, pids.pid, d.name, res.date, oids.orid, res.downloads, res.views FROM (SELECT us.repository_id, us.result_id, us.date, sum(us.downloads) as downloads, sum(us.views) as views FROM public.usage_stats us WHERE us.date>=? AND us.date<=? AND us.repository_id=? GROUP BY us.repository_id, us.result_id, us.date) AS res JOIN public.result r ON res.result_id=r.id JOIN public.datasource d ON d.id=res.repository_id JOIN public.result_classifications rc ON rc.id=r.id LEFT JOIN (SELECT pids.id, string_agg(pids.pid, '#!#') AS pid FROM public.result_pids pids, result_datasources rd WHERE rd.id=pids.id AND type='doi' AND rd.datasource=? GROUP BY pids.id) AS pids ON pids.id=r.id LEFT JOIN (SELECT oids.id, string_agg(oids.orid, '#!#') AS orid FROM public.result_oids oids, public.result_datasources rd WHERE rd.id=oids.id AND rd.datasource=? GROUP BY oids.id) AS oids ON oids.id=r.id WHERE rc.type=? ORDER BY res.result_id, res.date;"); |
917 |
st = connection.prepareStatement("SELECT res.result_id, r.publisher, rc.type, pids.pid, d.name, res.date, oids.orid, res.downloads, res.views FROM (SELECT us.repository_id, us.result_id, us.date, sum(us.downloads) as downloads, sum(us.views) as views FROM shadow.usage_stats us WHERE us.date>=? AND us.date<=? AND us.repository_id=? GROUP BY us.repository_id, us.result_id, us.date) AS res JOIN shadow.result r ON res.result_id=r.id JOIN shadow.datasource d ON d.id=res.repository_id JOIN shadow.result_classifications rc ON rc.id=r.id LEFT JOIN (SELECT pids.id, string_agg(pids.pid, '#!#') AS pid FROM shadow.result_pids pids, result_datasources rd WHERE rd.id=pids.id AND type='doi' AND rd.datasource=? GROUP BY pids.id) AS pids ON pids.id=r.id LEFT JOIN (SELECT oids.id, string_agg(oids.orid, '#!#') AS orid FROM shadow.result_oids oids, shadow.result_datasources rd WHERE rd.id=oids.id AND rd.datasource=? GROUP BY oids.id) AS oids ON oids.id=r.id WHERE rc.type=? ORDER BY res.result_id, res.date;"); |
|
964 |
st = connection.prepareStatement("SELECT res.result_id, r.title, r.publisher, r.source, rc.type, pids.pid, d.name, res.date, oids.orid, res.downloads, res.views FROM (SELECT us.repository_id, us.result_id, us.date, sum(us.downloads) as downloads, sum(us.views) as views FROM usage_stats us WHERE us.date>=? AND us.date<=? AND us.repository_id=? GROUP BY us.repository_id, us.result_id, us.date) AS res JOIN result r ON res.result_id=r.id JOIN datasource d ON d.id=res.repository_id JOIN result_classifications rc ON rc.id=r.id LEFT JOIN (SELECT pids.id, string_agg(pids.pid, '#!#') AS pid FROM result_pids pids, result_datasources rd WHERE rd.id=pids.id AND type='doi' AND rd.datasource=? GROUP BY pids.id) AS pids ON pids.id=r.id LEFT JOIN (SELECT oids.id, string_agg(oids.orid, '#!#') AS orid FROM result_oids oids, result_datasources rd WHERE rd.id=oids.id AND rd.datasource=? GROUP BY oids.id) AS oids ON oids.id=r.id WHERE rc.type=? ORDER BY res.result_id, res.date;"); |
|
965 |
|
|
918 | 966 |
st.setString(1, beginDateStr); |
919 | 967 |
st.setString(2, endDateStr); |
920 | 968 |
st.setString(3, repositoryIdentifier); |
... | ... | |
936 | 984 |
connection.close(); |
937 | 985 |
return; |
938 | 986 |
} |
939 |
*/ |
|
987 |
*/
|
|
940 | 988 |
//connection.setAutoCommit(false); |
941 | 989 |
//st.setFetchSize(5000); |
942 |
|
|
943 | 990 |
rs = st.executeQuery(); |
944 | 991 |
String result = ""; |
945 | 992 |
String lastDate = ""; |
... | ... | |
955 | 1002 |
reportItems.add(reportItem); |
956 | 1003 |
} |
957 | 1004 |
result = rs.getString(1); |
958 |
//reportItem = new ReportItem(rs.getString(3), rs.getString(7), rs.getString(5), rs.getString(2)); |
|
959 |
reportItem = new ReportItem(rs.getString(2), rs.getString(5), " ", " "); |
|
1005 |
reportItem = new ReportItem(rs.getString(3), rs.getString(7), rs.getString(5), rs.getString(2)); |
|
960 | 1006 |
reportItem.addIdentifier(new ItemIdentifier("OpenAIRE", rs.getString(1))); |
961 |
//reportItem.addIdentifier(new ItemIdentifier("URLs", rs.getString(4))); |
|
962 |
reportItem.addIdentifier(new ItemIdentifier("URLs", " ")); |
|
963 |
//if (rs.getString(9) != null && !rs.getString(9).equals("")) { |
|
964 |
if (rs.getString(7) != null && !rs.getString(7).equals("")) { |
|
965 |
//if (rs.getString(9).contains("#!#")) { |
|
966 |
if (rs.getString(7).contains("#!#")) { |
|
967 |
//reportItem.addIdentifier(new ItemIdentifier("OAI", rs.getString(9).substring(0, rs.getString(9).indexOf("#!#")))); |
|
968 |
reportItem.addIdentifier(new ItemIdentifier("OAI", rs.getString(7).substring(0, rs.getString(7).indexOf("#!#")))); |
|
1007 |
reportItem.addIdentifier(new ItemIdentifier("URLs", rs.getString(4))); |
|
1008 |
if (rs.getString(9) != null && !rs.getString(9).equals("")) { |
|
1009 |
if (rs.getString(9).contains("#!#")) { |
|
1010 |
reportItem.addIdentifier(new ItemIdentifier("OAI", rs.getString(9).substring(0, rs.getString(9).indexOf("#!#")))); |
|
969 | 1011 |
} else { |
970 |
//reportItem.addIdentifier(new ItemIdentifier("OAI", rs.getString(9))); |
|
971 |
reportItem.addIdentifier(new ItemIdentifier("OAI", rs.getString(7))); |
|
1012 |
reportItem.addIdentifier(new ItemIdentifier("OAI", rs.getString(9))); |
|
972 | 1013 |
} |
973 | 1014 |
} |
974 |
//if (rs.getString(6) != null && !rs.getString(6).equals("")) { |
|
975 |
if (rs.getString(4) != null && !rs.getString(6).equals("")) { |
|
976 |
//if (rs.getString(6).contains("#!#")) { |
|
977 |
if (rs.getString(4).contains("#!#")) { |
|
978 |
//reportItem.addIdentifier(new ItemIdentifier("DOI", rs.getString(6).substring(0, rs.getString(6).indexOf("#!#")))); |
|
979 |
reportItem.addIdentifier(new ItemIdentifier("DOI", rs.getString(4).substring(0, rs.getString(4).indexOf("#!#")))); |
|
1015 |
if (rs.getString(6) != null && !rs.getString(6).equals("")) { |
|
1016 |
if (rs.getString(6).contains("#!#")) { |
|
1017 |
reportItem.addIdentifier(new ItemIdentifier("DOI", rs.getString(6).substring(0, rs.getString(6).indexOf("#!#")))); |
|
980 | 1018 |
} else { |
981 |
//reportItem.addIdentifier(new ItemIdentifier("DOI", rs.getString(6))); |
|
982 |
reportItem.addIdentifier(new ItemIdentifier("DOI", rs.getString(4))); |
|
1019 |
reportItem.addIdentifier(new ItemIdentifier("DOI", rs.getString(6))); |
|
983 | 1020 |
} |
984 | 1021 |
} |
985 | 1022 |
ft_total = 0; |
986 | 1023 |
abstr = 0; |
987 | 1024 |
} |
988 |
//ft_total += rs.getInt(10); |
|
989 |
ft_total += rs.getInt(8); |
|
990 |
//abstr += rs.getInt(11); |
|
991 |
abstr += rs.getInt(9); |
|
1025 |
ft_total += rs.getInt(10); |
|
1026 |
abstr += rs.getInt(11); |
|
992 | 1027 |
} |
993 | 1028 |
if (reportItem != null) { |
994 | 1029 |
reportItem.addPerformance(new ItemPerformance(report_dateFormat.format(beginDate), report_dateFormat.format(endDate), Integer.toString(ft_total), Integer.toString(abstr))); |
... | ... | |
1007 | 1042 |
} |
1008 | 1043 |
result = rs.getString(1); |
1009 | 1044 |
lastDate = beginDateStr; |
1010 |
//reportItem = new ReportItem(rs.getString(3), rs.getString(7), rs.getString(5), rs.getString(2)); |
|
1011 |
reportItem = new ReportItem(rs.getString(2), rs.getString(5), rs.getString(3), " "); |
|
1045 |
reportItem = new ReportItem(rs.getString(3), rs.getString(7), rs.getString(5), rs.getString(2)); |
|
1012 | 1046 |
reportItem.addIdentifier(new ItemIdentifier("OpenAIRE", rs.getString(1))); |
1013 | 1047 |
reportItem.addIdentifier(new ItemIdentifier("URLs", rs.getString(4))); |
1014 |
//if (rs.getString(9) != null && !rs.getString(9).equals("")) { |
|
1015 |
if (rs.getString(7) != null && !rs.getString(7).equals("")) { |
|
1016 |
//if (rs.getString(9).contains("#!#")) { |
|
1017 |
if (rs.getString(7).contains("#!#")) { |
|
1018 |
//reportItem.addIdentifier(new ItemIdentifier("OAI", rs.getString(9).substring(0, rs.getString(9).indexOf("#!#")))); |
|
1019 |
reportItem.addIdentifier(new ItemIdentifier("OAI", rs.getString(7).substring(0, rs.getString(7).indexOf("#!#")))); |
|
1048 |
if (rs.getString(9) != null && !rs.getString(9).equals("")) { |
|
1049 |
if (rs.getString(9).contains("#!#")) { |
|
1050 |
reportItem.addIdentifier(new ItemIdentifier("OAI", rs.getString(9).substring(0, rs.getString(9).indexOf("#!#")))); |
|
1020 | 1051 |
} else { |
1021 |
//reportItem.addIdentifier(new ItemIdentifier("OAI", rs.getString(9))); |
|
1022 |
reportItem.addIdentifier(new ItemIdentifier("OAI", rs.getString(7))); |
|
1052 |
reportItem.addIdentifier(new ItemIdentifier("OAI", rs.getString(9))); |
|
1023 | 1053 |
} |
1024 | 1054 |
} |
1025 |
//if (rs.getString(6) != null && !rs.getString(6).equals("")) { |
|
1026 |
if (rs.getString(4) != null && !rs.getString(4).equals("")) { |
|
1027 |
if (rs.getString(4).contains("#!#")) { |
|
1028 |
reportItem.addIdentifier(new ItemIdentifier("DOI", rs.getString(4).substring(0, rs.getString(4).indexOf("#!#")))); |
|
1055 |
if (rs.getString(6) != null && !rs.getString(6).equals("")) { |
|
1056 |
if (rs.getString(6).contains("#!#")) { |
|
1057 |
reportItem.addIdentifier(new ItemIdentifier("DOI", rs.getString(6).substring(0, rs.getString(6).indexOf("#!#")))); |
|
1029 | 1058 |
} else { |
1030 |
reportItem.addIdentifier(new ItemIdentifier("DOI", rs.getString(4)));
|
|
1059 |
reportItem.addIdentifier(new ItemIdentifier("DOI", rs.getString(6)));
|
|
1031 | 1060 |
} |
1032 | 1061 |
} |
1033 | 1062 |
} |
1034 |
fillWithZeros(postgresFormat.parse(lastDate), postgresFormat.parse(rs.getString(6)), reportItem);
|
|
1063 |
fillWithZeros(postgresFormat.parse(lastDate), postgresFormat.parse(rs.getString(8)), reportItem);
|
|
1035 | 1064 |
Calendar endC = Calendar.getInstance(); |
1036 |
endC.setTime(postgresFormat.parse(rs.getString(6)));
|
|
1065 |
endC.setTime(postgresFormat.parse(rs.getString(8)));
|
|
1037 | 1066 |
endC.set(Calendar.DATE, endC.getActualMaximum(Calendar.DATE)); |
1038 | 1067 |
if (reportItem != null) { |
1039 |
reportItem.addPerformance(new ItemPerformance(report_dateFormat.format(postgresFormat.parse(rs.getString(6))), report_dateFormat.format(endC.getTime()), rs.getString(8), rs.getString(9)));
|
|
1068 |
reportItem.addPerformance(new ItemPerformance(report_dateFormat.format(postgresFormat.parse(rs.getString(8))), report_dateFormat.format(endC.getTime()), rs.getString(10), rs.getString(11)));
|
|
1040 | 1069 |
} |
1041 |
endC.setTime(postgresFormat.parse(rs.getString(6)));
|
|
1070 |
endC.setTime(postgresFormat.parse(rs.getString(8)));
|
|
1042 | 1071 |
endC.add(Calendar.MONTH, 1); |
1043 | 1072 |
lastDate = postgresFormat.format(endC.getTime()); |
1044 | 1073 |
} |
... | ... | |
1054 | 1083 |
jedis.put(redis_key, "query", st.toString()); |
1055 | 1084 |
jedis.put(redis_key, "result", toJson(reportItems)); |
1056 | 1085 |
jedis.put(redis_key, "fetchMode", "3"); |
1057 |
*/ |
|
1058 |
|
|
1086 |
*/ |
|
1059 | 1087 |
} catch (Exception e) { |
1060 | 1088 |
log.error("Batch Item Report failed: ", e); |
1061 | 1089 |
} finally { |
... | ... | |
1064 | 1092 |
DbUtils.closeQuietly(connection); |
1065 | 1093 |
java.sql.Timestamp timestamp = new java.sql.Timestamp(System.currentTimeMillis()); |
1066 | 1094 |
//System.out.println("DB end "+timestamp); |
1067 |
log.info("Query end..."+timestamp);
|
|
1095 |
log.info("Query end..." + timestamp);
|
|
1068 | 1096 |
|
1069 | 1097 |
} |
1070 | 1098 |
} |
Also available in: Unified diff
Updates to queries, remove schema prefix