368 |
368 |
log.info("Query started..." + timestamp1);
|
369 |
369 |
|
370 |
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=?");
|
371 |
|
st = connection.prepareStatement("SELECT DISTINCT roid.id FROM result_oids roid, usage_stats us WHERE us.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 |
372 |
st.setString(1, oid);
|
373 |
|
st.setString(2, oid);
|
|
373 |
//st.setString(2, oid);
|
374 |
374 |
|
375 |
375 |
rs = st.executeQuery();
|
376 |
376 |
|
... | ... | |
402 |
402 |
log.info("Query started..." + timestamp1);
|
403 |
403 |
|
404 |
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=?");
|
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=?");
|
|
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=?");
|
406 |
406 |
st.setString(1, doi);
|
407 |
|
st.setString(2, doi);
|
|
407 |
//st.setString(2, doi);
|
408 |
408 |
|
409 |
409 |
rs = st.executeQuery();
|
410 |
410 |
|
... | ... | |
451 |
451 |
if (repositoryIdentifier.equals("")) {
|
452 |
452 |
if (itemDataType.equals("")) {
|
453 |
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;");
|
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;");
|
|
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;");
|
|
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;");
|
455 |
456 |
st.setString(1, beginDateStr);
|
456 |
457 |
st.setString(2, endDateStr);
|
457 |
458 |
st.setString(3, openaire);
|
458 |
459 |
//st.setString(4, beginDateStr);
|
459 |
460 |
//st.setString(5, endDateStr);
|
460 |
461 |
//st.setString(6, openaire);
|
461 |
|
st.setString(4, openaire);
|
462 |
|
st.setString(5, openaire);
|
|
462 |
//st.setString(4, openaire);
|
|
463 |
//st.setString(5, openaire);
|
463 |
464 |
} else {
|
464 |
465 |
//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;");
|
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;");
|
|
466 |
//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;");
|
466 |
468 |
st.setString(1, beginDateStr);
|
467 |
469 |
st.setString(2, endDateStr);
|
468 |
470 |
st.setString(3, openaire);
|
... | ... | |
470 |
472 |
//st.setString(5, endDateStr);
|
471 |
473 |
//st.setString(6, openaire);
|
472 |
474 |
st.setString(4, itemDataType);
|
473 |
|
st.setString(5, openaire);
|
474 |
|
st.setString(6, openaire);
|
|
475 |
//st.setString(5, openaire);
|
|
476 |
//st.setString(6, openaire);
|
475 |
477 |
}
|
476 |
478 |
} else {
|
477 |
479 |
if (itemDataType.equals("")) {
|
478 |
480 |
//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;");
|
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;");
|
|
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;");
|
480 |
482 |
st.setString(1, beginDateStr);
|
481 |
483 |
st.setString(2, endDateStr);
|
482 |
484 |
st.setString(3, openaire);
|
... | ... | |
485 |
487 |
//st.setString(6, endDateStr);
|
486 |
488 |
//st.setString(7, openaire);
|
487 |
489 |
//st.setString(8, repositoryIdentifier);
|
488 |
|
st.setString(5, openaire);
|
489 |
|
st.setString(6, openaire);
|
|
490 |
//st.setString(5, openaire);
|
|
491 |
//st.setString(6, openaire);
|
490 |
492 |
} else {
|
491 |
493 |
//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;");
|
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;");
|
|
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;");
|
493 |
495 |
st.setString(1, beginDateStr);
|
494 |
496 |
st.setString(2, endDateStr);
|
495 |
497 |
st.setString(3, openaire);
|
... | ... | |
499 |
501 |
//st.setString(7, openaire);
|
500 |
502 |
//st.setString(8, repositoryIdentifier);
|
501 |
503 |
st.setString(5, itemDataType);
|
502 |
|
st.setString(6, openaire);
|
503 |
|
st.setString(7, openaire);
|
|
504 |
//st.setString(6, openaire);
|
|
505 |
//st.setString(7, openaire);
|
504 |
506 |
}
|
505 |
507 |
}
|
506 |
508 |
|
... | ... | |
949 |
951 |
log.info("Query started..." + timestamp1);
|
950 |
952 |
if (itemDataType.equals("")) {
|
951 |
953 |
//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;");
|
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;");
|
|
954 |
//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;");
|
953 |
956 |
st.setString(1, beginDateStr);
|
954 |
957 |
st.setString(2, endDateStr);
|
955 |
958 |
st.setString(3, repositoryIdentifier);
|
956 |
959 |
//st.setString(4, beginDateStr);
|
957 |
960 |
//st.setString(5, endDateStr);
|
958 |
961 |
//st.setString(6, repositoryIdentifier);
|
959 |
|
st.setString(4, repositoryIdentifier);
|
960 |
|
st.setString(5, repositoryIdentifier);
|
|
962 |
//st.setString(4, repositoryIdentifier);
|
|
963 |
//st.setString(5, repositoryIdentifier);
|
961 |
964 |
} else {
|
962 |
965 |
//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;");
|
963 |
966 |
//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;");
|
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;");
|
|
967 |
//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;");
|
965 |
969 |
|
966 |
970 |
st.setString(1, beginDateStr);
|
967 |
971 |
st.setString(2, endDateStr);
|
... | ... | |
969 |
973 |
//st.setString(4, beginDateStr);
|
970 |
974 |
//st.setString(5, endDateStr);
|
971 |
975 |
//st.setString(6, repositoryIdentifier);
|
972 |
|
st.setString(4, repositoryIdentifier);
|
973 |
|
st.setString(5, repositoryIdentifier);
|
974 |
|
st.setString(6, itemDataType);
|
|
976 |
st.setString(4, itemDataType);
|
975 |
977 |
}
|
976 |
978 |
//log.error("IR STATEMENT: " + st);
|
977 |
979 |
|
Queries modification