Revision 57356
Added by Dimitris Pierrakos over 4 years ago
| 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