Revision 57356
Added by Dimitris Pierrakos over 4 years ago
modules/dnet-openaire-usage-stats-api/branches/usage-stats-api-compression/src/main/java/eu/dnetlib/usagestats/repositories/UsageStatsRepository.java | ||
---|---|---|
21 | 21 |
import javax.sql.DataSource; |
22 | 22 |
|
23 | 23 |
import java.security.MessageDigest; |
24 |
import java.sql.Array; |
|
24 | 25 |
import java.sql.Connection; |
25 | 26 |
import java.sql.PreparedStatement; |
26 | 27 |
import java.sql.ResultSet; |
28 |
import java.sql.Types; |
|
27 | 29 |
import java.text.SimpleDateFormat; |
28 | 30 |
import java.util.ArrayList; |
29 | 31 |
import java.util.Calendar; |
... | ... | |
368 | 370 |
log.info("Query started..." + timestamp1); |
369 | 371 |
|
370 | 372 |
//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=?"); |
371 |
st = connection.prepareStatement("SELECT DISTINCT us.original_result_id FROM result_oids roid, usage_stats us WHERE us.result_id=roid.id AND roid.orid=?"); |
|
372 |
st.setString(1, oid); |
|
373 |
//st.setString(2, oid); |
|
373 |
//st = connection.prepareStatement("SELECT DISTINCT us.original_result_id FROM result_oids roid, usage_stats us WHERE us.result_id=roid.id AND roid.orid=?"); |
|
374 |
st = connection.prepareStatement("SELECT DISTINCT original_result_id FROM reports WHERE orid=?;"); |
|
375 |
st.setObject(1, connection.createArrayOf("text", new String[]{oid})); |
|
376 |
//st.setString(1, oid); |
|
374 | 377 |
|
375 | 378 |
rs = st.executeQuery(); |
376 | 379 |
|
... | ... | |
402 | 405 |
log.info("Query started..." + timestamp1); |
403 | 406 |
|
404 | 407 |
//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=?"); |
405 |
st = connection.prepareStatement("SELECT DISTINCT us.original_result_id FROM result_pids poid, usage_stats us WHERE us.result_id=poid.id AND poid.type='doi' AND poid.pid=?"); |
|
408 |
//st = connection.prepareStatement("SELECT DISTINCT us.original_result_id FROM result_pids poid, usage_stats us WHERE us.result_id=poid.id AND poid.type='doi' AND poid.pid=?"); |
|
409 |
st = connection.prepareStatement("SELECT DISTINCT original_result_id FROM reports, result r, result_pids poid where original_result_id=r.original_id and r.id=poid.id and poid.type='doi' AND poid.pid=?;"); |
|
406 | 410 |
st.setString(1, doi); |
407 | 411 |
//st.setString(2, doi); |
408 | 412 |
|
... | ... | |
452 | 456 |
if (itemDataType.equals("")) { |
453 | 457 |
//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;"); |
454 | 458 |
//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;"); |
455 |
st = connection.prepareStatement("SELECT us.repository_id, r.title, r.publisher, r.source, rc.type, string_agg(distinct pids.pid, '#!#') AS pid, d.name, us.date, string_agg(distinct oids.orid, '#!#') AS orid, us.downloads, us.views FROM usage_stats us JOIN result r ON us.result_id=r.id JOIN datasource d ON d.id=us.repository_id JOIN result_classifications rc ON rc.id=r.id JOIN result_pids pids ON pids.id=r.id AND pids.type='doi' JOIN result_oids oids ON oids.id=r.id WHERE us.date>=? AND us.date<=? AND us.original_result_id=? GROUP BY us.repository_id, r.title, r.publisher, r.source, rc.type, d.name, us.date, us.downloads, us.views ORDER BY us.repository_id, us.date;"); |
|
459 |
//st = connection.prepareStatement("SELECT us.repository_id, r.title, r.publisher, r.source, rc.type, string_agg(distinct pids.pid, '#!#') AS pid, d.name, us.date, string_agg(distinct oids.orid, '#!#') AS orid, us.downloads, us.views FROM usage_stats us JOIN result r ON us.result_id=r.id JOIN datasource d ON d.id=us.repository_id JOIN result_classifications rc ON rc.id=r.id JOIN result_pids pids ON pids.id=r.id AND pids.type='doi' JOIN result_oids oids ON oids.id=r.id WHERE us.date>=? AND us.date<=? AND us.original_result_id=? GROUP BY us.repository_id, r.title, r.publisher, r.source, rc.type, d.name, us.date, us.downloads, us.views ORDER BY us.repository_id, us.date;"); |
|
460 |
st = connection.prepareStatement("select repository_id, r.title, r.publisher, r.source , rc.type, re.pid, name, date,orid,downloads,views from reports re, result_pids pids, result r, result_classifications rc where date>=? AND date<=? and original_result_id=r.original_id and r.id=pids.id and rc.id=r.id and original_result_id=? and pids.type='doi';"); |
|
456 | 461 |
st.setString(1, beginDateStr); |
457 | 462 |
st.setString(2, endDateStr); |
458 | 463 |
st.setString(3, openaire); |
... | ... | |
464 | 469 |
} else { |
465 | 470 |
//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;"); |
466 | 471 |
//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;"); |
467 |
st = connection.prepareStatement("SELECT us.repository_id, r.title, r.publisher, r.source, rc.type, string_agg(distinct pids.pid, '#!#') AS pid, d.name, us.date, string_agg(distinct oids.orid, '#!#') AS orid, us.downloads, us.views FROM usage_stats us JOIN result r ON us.result_id=r.id JOIN datasource d ON d.id=us.repository_id JOIN result_classifications rc ON rc.id=r.id JOIN result_pids pids ON pids.id=r.id AND pids.type='doi' JOIN result_oids oids ON oids.id=r.id WHERE us.date>=? AND us.date<=? AND us.original_result_id=? AND rc.type=? GROUP BY us.repository_id, r.title, r.publisher, r.source, rc.type, d.name, us.date, us.downloads, us.views ORDER BY us.repository_id, us.date;"); |
|
472 |
//st = connection.prepareStatement("SELECT us.repository_id, r.title, r.publisher, r.source, rc.type, string_agg(distinct pids.pid, '#!#') AS pid, d.name, us.date, string_agg(distinct oids.orid, '#!#') AS orid, us.downloads, us.views FROM usage_stats us JOIN result r ON us.result_id=r.id JOIN datasource d ON d.id=us.repository_id JOIN result_classifications rc ON rc.id=r.id JOIN result_pids pids ON pids.id=r.id AND pids.type='doi' JOIN result_oids oids ON oids.id=r.id WHERE us.date>=? AND us.date<=? AND us.original_result_id=? AND rc.type=? GROUP BY us.repository_id, r.title, r.publisher, r.source, rc.type, d.name, us.date, us.downloads, us.views ORDER BY us.repository_id, us.date;"); |
|
473 |
st = connection.prepareStatement("select repository_id, r.title, r.publisher, r.source , rc.type, re.pid, name, date,orid,downloads,views from reports re, result_pids pids, result r, result_classifications rc where date>=? AND date<=? and original_result_id=r.original_id and r.id=pids.id and rc.id=r.id and original_result_id=? and rc.type=? and pids.type='doi';"); |
|
468 | 474 |
st.setString(1, beginDateStr); |
469 | 475 |
st.setString(2, endDateStr); |
470 | 476 |
st.setString(3, openaire); |
... | ... | |
478 | 484 |
} else { |
479 | 485 |
if (itemDataType.equals("")) { |
480 | 486 |
//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;"); |
481 |
st = connection.prepareStatement("SELECT us.repository_id, r.title, r.publisher, r.source, rc.type, string_agg(distinct pids.pid, '#!#') AS pid, d.name, us.date, string_agg(distinct oids.orid, '#!#') AS orid, us.downloads, us.views FROM usage_stats us JOIN result r ON us.result_id=r.id JOIN datasource d ON d.id=us.repository_id JOIN result_classifications rc ON rc.id=r.id JOIN result_pids pids ON pids.id=r.id AND pids.type='doi' JOIN result_oids oids ON oids.id=r.id WHERE us.date>=? AND us.date<=? AND us.original_result_id=? AND us.repository_id=? GROUP BY us.repository_id, r.title, r.publisher, r.source, rc.type, d.name, us.date, us.downloads, us.views ORDER BY us.repository_id, us.date;"); |
|
487 |
//st = connection.prepareStatement("SELECT us.repository_id, r.title, r.publisher, r.source, rc.type, string_agg(distinct pids.pid, '#!#') AS pid, d.name, us.date, string_agg(distinct oids.orid, '#!#') AS orid, us.downloads, us.views FROM usage_stats us JOIN result r ON us.result_id=r.id JOIN datasource d ON d.id=us.repository_id JOIN result_classifications rc ON rc.id=r.id JOIN result_pids pids ON pids.id=r.id AND pids.type='doi' JOIN result_oids oids ON oids.id=r.id WHERE us.date>=? AND us.date<=? AND us.original_result_id=? AND us.repository_id=? GROUP BY us.repository_id, r.title, r.publisher, r.source, rc.type, d.name, us.date, us.downloads, us.views ORDER BY us.repository_id, us.date;"); |
|
488 |
st = connection.prepareStatement("select repository_id, r.title, r.publisher, r.source , rc.type, re.pid, name, date,orid,downloads,views from reports re, result_pids pids, result r, result_classifications rc where date>=? AND date<=? and original_result_id=r.original_id and r.id=pids.id and rc.id=r.id and original_result_id=? and repository_id=? and pids.type='doi';"); |
|
482 | 489 |
st.setString(1, beginDateStr); |
483 | 490 |
st.setString(2, endDateStr); |
484 | 491 |
st.setString(3, openaire); |
... | ... | |
491 | 498 |
//st.setString(6, openaire); |
492 | 499 |
} else { |
493 | 500 |
//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;"); |
494 |
st = connection.prepareStatement("SELECT us.repository_id, r.title, r.publisher, r.source, rc.type, string_agg(distinct pids.pid, '#!#') AS pid, d.name, us.date, string_agg(distinct oids.orid, '#!#') AS orid, us.downloads, us.views FROM usage_stats us JOIN result r ON us.result_id=r.id JOIN datasource d ON d.id=us.repository_id JOIN result_classifications rc ON rc.id=r.id JOIN result_pids pids ON pids.id=r.id AND pids.type='doi' JOIN result_oids oids ON oids.id=r.id WHERE us.date>=? AND us.date<=? AND us.original_result_id=? AND us.repository_id=? AND rc.type=? GROUP BY us.repository_id, r.title, r.publisher, r.source, rc.type, d.name, us.date, us.downloads, us.views ORDER BY us.repository_id, us.date;"); |
|
501 |
//st = connection.prepareStatement("SELECT us.repository_id, r.title, r.publisher, r.source, rc.type, string_agg(distinct pids.pid, '#!#') AS pid, d.name, us.date, string_agg(distinct oids.orid, '#!#') AS orid, us.downloads, us.views FROM usage_stats us JOIN result r ON us.result_id=r.id JOIN datasource d ON d.id=us.repository_id JOIN result_classifications rc ON rc.id=r.id JOIN result_pids pids ON pids.id=r.id AND pids.type='doi' JOIN result_oids oids ON oids.id=r.id WHERE us.date>=? AND us.date<=? AND us.original_result_id=? AND us.repository_id=? AND rc.type=? GROUP BY us.repository_id, r.title, r.publisher, r.source, rc.type, d.name, us.date, us.downloads, us.views ORDER BY us.repository_id, us.date;"); |
|
502 |
st = connection.prepareStatement("select repository_id, r.title, r.publisher, r.source , rc.type, re.pid, name, date,orid,downloads,views from reports re, result_pids pids, result r, result_classifications rc where date>=? AND date<=? and original_result_id=r.original_id and r.id=pids.id and rc.id=r.id and original_result_id=? and repository_id=? and rc.type=? and pids.type='doi';"); |
|
495 | 503 |
st.setString(1, beginDateStr); |
496 | 504 |
st.setString(2, endDateStr); |
497 | 505 |
st.setString(3, openaire); |
... | ... | |
524 | 532 |
reportItem = new ReportItem(rs.getString(3), rs.getString(7), rs.getString(5), rs.getString(2)); |
525 | 533 |
reportItem.addIdentifier(new ItemIdentifier("OpenAIRE", openaire)); |
526 | 534 |
reportItem.addIdentifier(new ItemIdentifier("URLs", rs.getString(4))); |
535 |
|
|
536 |
String[] oridsArr = (String[]) rs.getArray(9).getArray(); |
|
537 |
if (oridsArr[0] != null && !oridsArr[0].equals("")) { |
|
538 |
reportItem.addIdentifier(new ItemIdentifier("OAI", oridsArr[0])); |
|
539 |
} |
|
540 |
String[] pidsArr = (String[]) rs.getArray(6).getArray(); |
|
541 |
if (pidsArr[0] != null && !pidsArr[0].equals("")) { |
|
542 |
reportItem.addIdentifier(new ItemIdentifier("OAI", pidsArr[0])); |
|
543 |
} |
|
544 |
/* |
|
527 | 545 |
if (rs.getString(9) != null && !rs.getString(9).equals("")) { |
528 | 546 |
if (rs.getString(9).contains("#!#")) { |
529 | 547 |
reportItem.addIdentifier(new ItemIdentifier("OAI", rs.getString(9).substring(0, rs.getString(9).indexOf("#!#")))); |
... | ... | |
538 | 556 |
reportItem.addIdentifier(new ItemIdentifier("DOI", rs.getString(6))); |
539 | 557 |
} |
540 | 558 |
} |
559 |
*/ |
|
541 | 560 |
ft_total = 0; |
542 | 561 |
abstr = 0; |
543 | 562 |
} |
... | ... | |
564 | 583 |
reportItem = new ReportItem(rs.getString(3), rs.getString(7), rs.getString(5), rs.getString(2)); |
565 | 584 |
reportItem.addIdentifier(new ItemIdentifier("OpenAIRE", openaire)); |
566 | 585 |
reportItem.addIdentifier(new ItemIdentifier("URLs", rs.getString(4))); |
586 |
|
|
587 |
String[] oridsArr = (String[]) rs.getArray(9).getArray(); |
|
588 |
if (oridsArr[0] != null && !oridsArr[0].equals("")) { |
|
589 |
reportItem.addIdentifier(new ItemIdentifier("OAI", oridsArr[0])); |
|
590 |
} |
|
591 |
String[] pidsArr = (String[]) rs.getArray(6).getArray(); |
|
592 |
if (pidsArr[0] != null && !pidsArr[0].equals("")) { |
|
593 |
reportItem.addIdentifier(new ItemIdentifier("OAI", pidsArr[0])); |
|
594 |
} |
|
595 |
/* |
|
567 | 596 |
if (rs.getString(9) != null && !rs.getString(9).equals("")) { |
568 | 597 |
if (rs.getString(9).contains("#!#")) { |
569 | 598 |
reportItem.addIdentifier(new ItemIdentifier("OAI", rs.getString(9).substring(0, rs.getString(9).indexOf("#!#")))); |
... | ... | |
578 | 607 |
reportItem.addIdentifier(new ItemIdentifier("DOI", rs.getString(6))); |
579 | 608 |
} |
580 | 609 |
} |
610 |
*/ |
|
581 | 611 |
} |
582 | 612 |
fillWithZeros(postgresFormat.parse(lastDate), postgresFormat.parse(rs.getString(8)), reportItem); |
583 | 613 |
Calendar endC = Calendar.getInstance(); |
... | ... | |
952 | 982 |
if (itemDataType.equals("")) { |
953 | 983 |
//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;"); |
954 | 984 |
//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;"); |
955 |
st = connection.prepareStatement("SELECT us.original_result_id, r.title, r.publisher, r.source, rc.type, string_agg(distinct pids.pid, '#!#') as pid, d.name, us.date, string_agg(distinct oids.orid, '#!#') AS orid , sum(us.downloads) as downloads, sum(us.views) as views FROM usage_stats us JOIN result r ON us.result_id=r.id JOIN datasource d ON d.id=us.repository_id LEFT OUTER JOIN result_classifications rc ON rc.id=r.id LEFT OUTER JOIN result_pids pids on pids.id=r.id LEFT OUTER JOIN result_oids oids on oids.id=r.id WHERE us.date>=? AND us.date<=? AND us.repository_id=? GROUP BY us.original_result_id, r.title, r.publisher, r.source, rc.type, d.name, us.date;"); |
|
985 |
//st = connection.prepareStatement("SELECT us.original_result_id, r.title, r.publisher, r.source, rc.type, string_agg(distinct pids.pid, '#!#') as pid, d.name, us.date, string_agg(distinct oids.orid, '#!#') AS orid , sum(us.downloads) as downloads, sum(us.views) as views FROM usage_stats us JOIN result r ON us.result_id=r.id JOIN datasource d ON d.id=us.repository_id LEFT OUTER JOIN result_classifications rc ON rc.id=r.id LEFT OUTER JOIN result_pids pids on pids.id=r.id LEFT OUTER JOIN result_oids oids on oids.id=r.id WHERE us.date>=? AND us.date<=? AND us.repository_id=? GROUP BY us.original_result_id, r.title, r.publisher, r.source, rc.type, d.name, us.date;"); |
|
986 |
st = connection.prepareStatement("SELECT original_result_id, re.title, re.publisher, re.source, rc.type, pid, name, date, orid, sum(downloads) as downloads, sum(views) as views FROM reports re, result r, result_classifications rc WHERE original_result_id=r.original_id and rc.id=r.id and date>=? AND date<=? AND repository_id=? GROUP BY original_result_id, re.title, re.publisher, pid, orid, re.source, rc.type, name, date;"); |
|
956 | 987 |
st.setString(1, beginDateStr); |
957 | 988 |
st.setString(2, endDateStr); |
958 | 989 |
st.setString(3, repositoryIdentifier); |
... | ... | |
965 | 996 |
//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;"); |
966 | 997 |
//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;"); |
967 | 998 |
//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;"); |
968 |
st = connection.prepareStatement("SELECT us.original_result_id, r.title, r.publisher, r.source, rc.type, string_agg(distinct pids.pid, '#!#') as pid, d.name, us.date, string_agg(distinct oids.orid, '#!#') AS orid , sum(us.downloads) as downloads, sum(us.views) as views FROM usage_stats us JOIN result r ON us.result_id=r.id JOIN datasource d ON d.id=us.repository_id JOIN result_classifications rc ON rc.id=r.id LEFT OUTER JOIN result_pids pids on pids.id=r.id LEFT OUTER JOIN result_oids oids on oids.id=r.id WHERE us.date>=? AND us.date<=? AND us.repository_id=? AND rc.type=? GROUP BY us.original_result_id, r.title, r.publisher, r.source, rc.type, d.name, us.date;"); |
|
969 |
|
|
999 |
//st = connection.prepareStatement("SELECT us.original_result_id, r.title, r.publisher, r.source, rc.type, string_agg(distinct pids.pid, '#!#') as pid, d.name, us.date, string_agg(distinct oids.orid, '#!#') AS orid , sum(us.downloads) as downloads, sum(us.views) as views FROM usage_stats us JOIN result r ON us.result_id=r.id JOIN datasource d ON d.id=us.repository_id JOIN result_classifications rc ON rc.id=r.id LEFT OUTER JOIN result_pids pids on pids.id=r.id LEFT OUTER JOIN result_oids oids on oids.id=r.id WHERE us.date>=? AND us.date<=? AND us.repository_id=? AND rc.type=? GROUP BY us.original_result_id, r.title, r.publisher, r.source, rc.type, d.name, us.date;");
|
|
1000 |
st = connection.prepareStatement("SELECT original_result_id, re.title, re.publisher, re.source, rc.type, pid, name, date, orid, sum(downloads) as downloads, sum(views) as views FROM reports re, result r, result_classifications rc WHERE original_result_id=r.original_id and rc.id=r.id and date>=? AND date<=? AND repository_id=? and rc.type=? GROUP BY original_result_id, re.title, re.publisher, pid, orid, re.source, rc.type, name, date;"); |
|
970 | 1001 |
st.setString(1, beginDateStr); |
971 | 1002 |
st.setString(2, endDateStr); |
972 | 1003 |
st.setString(3, repositoryIdentifier); |
... | ... | |
1007 | 1038 |
reportItem = new ReportItem(rs.getString(3), rs.getString(7), rs.getString(5), rs.getString(2)); |
1008 | 1039 |
reportItem.addIdentifier(new ItemIdentifier("OpenAIRE", rs.getString(1))); |
1009 | 1040 |
reportItem.addIdentifier(new ItemIdentifier("URLs", rs.getString(4))); |
1041 |
|
|
1042 |
String[] oridsArr = (String[]) rs.getArray(9).getArray(); |
|
1043 |
if (oridsArr[0] != null && !oridsArr[0].equals("")) { |
|
1044 |
reportItem.addIdentifier(new ItemIdentifier("OAI", oridsArr[0])); |
|
1045 |
} |
|
1046 |
String[] pidsArr = (String[]) rs.getArray(6).getArray(); |
|
1047 |
if (pidsArr[0] != null && !pidsArr[0].equals("")) { |
|
1048 |
reportItem.addIdentifier(new ItemIdentifier("OAI", pidsArr[0])); |
|
1049 |
} |
|
1050 |
/* |
|
1010 | 1051 |
if (rs.getString(9) != null && !rs.getString(9).equals("")) { |
1011 | 1052 |
if (rs.getString(9).contains("#!#")) { |
1012 | 1053 |
reportItem.addIdentifier(new ItemIdentifier("OAI", rs.getString(9).substring(0, rs.getString(9).indexOf("#!#")))); |
... | ... | |
1021 | 1062 |
reportItem.addIdentifier(new ItemIdentifier("DOI", rs.getString(6))); |
1022 | 1063 |
} |
1023 | 1064 |
} |
1065 |
*/ |
|
1024 | 1066 |
ft_total = 0; |
1025 | 1067 |
abstr = 0; |
1026 | 1068 |
} |
... | ... | |
1047 | 1089 |
reportItem = new ReportItem(rs.getString(3), rs.getString(7), rs.getString(5), rs.getString(2)); |
1048 | 1090 |
reportItem.addIdentifier(new ItemIdentifier("OpenAIRE", rs.getString(1))); |
1049 | 1091 |
reportItem.addIdentifier(new ItemIdentifier("URLs", rs.getString(4))); |
1092 |
|
|
1093 |
String[] oridsArr = (String[]) rs.getArray(9).getArray(); |
|
1094 |
if (oridsArr[0] != null && !oridsArr[0].equals("")) { |
|
1095 |
reportItem.addIdentifier(new ItemIdentifier("OAI", oridsArr[0])); |
|
1096 |
} |
|
1097 |
String[] pidsArr = (String[]) rs.getArray(6).getArray(); |
|
1098 |
if (pidsArr[0] != null && !pidsArr[0].equals("")) { |
|
1099 |
reportItem.addIdentifier(new ItemIdentifier("OAI", pidsArr[0])); |
|
1100 |
} |
|
1101 |
/* |
|
1050 | 1102 |
if (rs.getString(9) != null && !rs.getString(9).equals("")) { |
1051 | 1103 |
if (rs.getString(9).contains("#!#")) { |
1052 | 1104 |
reportItem.addIdentifier(new ItemIdentifier("OAI", rs.getString(9).substring(0, rs.getString(9).indexOf("#!#")))); |
... | ... | |
1061 | 1113 |
reportItem.addIdentifier(new ItemIdentifier("DOI", rs.getString(6))); |
1062 | 1114 |
} |
1063 | 1115 |
} |
1116 |
*/ |
|
1064 | 1117 |
} |
1065 | 1118 |
fillWithZeros(postgresFormat.parse(lastDate), postgresFormat.parse(rs.getString(8)), reportItem); |
1066 | 1119 |
Calendar endC = Calendar.getInstance(); |
Also available in: Unified diff
Updates to queries and code to support the 'reports' table