Revision 57605
Added by Dimitris Pierrakos over 4 years ago
UsageStatsRepository.java | ||
---|---|---|
202 | 202 |
log.info("Query started..." + timestamp1); |
203 | 203 |
|
204 | 204 |
//st = connection.prepareStatement("SELECT count(distinct d.repository_id) AS repository, count(distinct d.result_id) AS items, sum(d.count) AS downloads, sum(v.count) AS views from public.downloads_stats d FULL OUTER JOIN public.views_stats v ON d.source=v.source AND d.repository_id=v.repository_id AND d.result_id=v.result_id AND d.date=v.date;"); |
205 |
st = connection.prepareStatement("SELECT count(distinct repository_id) AS repository, count(distinct result_id) AS items, sum(downloads) AS downloads, sum(views) AS views from usage_stats");
|
|
205 |
st = connection.prepareStatement("SELECT count(distinct repository_id) AS repository, count(distinct result_id) AS items, sum(downloads) AS downloads, sum(views) AS views from reports");
|
|
206 | 206 |
rs = st.executeQuery(); |
207 | 207 |
rs.next(); |
208 | 208 |
totalStats.setRepositories(rs.getInt(1)); |
... | ... | |
213 | 213 |
st.close(); |
214 | 214 |
|
215 | 215 |
//st = connection.prepareStatement("select coalesce(d.date,v.date) as month, count(distinct d.repository_id) as repository, count(distinct d.result_id) as items, sum(d.count) as downloads, sum(v.count) as views from public.downloads_stats d FULL OUTER JOIN public.views_stats v ON d.source=v.source AND d.repository_id=v.repository_id AND d.result_id=v.result_id AND d.date=v.date group by month order by month;"); |
216 |
st = connection.prepareStatement("SELECT date, count(distinct repository_id) AS repository, count(distinct result_id) AS items, sum(downloads) AS downloads, sum(views) AS views FROM usage_stats GROUP BY date ORDER BY date;");
|
|
216 |
st = connection.prepareStatement("SELECT date, count(distinct repository_id) AS repository, count(distinct result_id) AS items, sum(downloads) AS downloads, sum(views) AS views FROM reports GROUP BY date ORDER BY date;");
|
|
217 | 217 |
rs = st.executeQuery(); |
218 | 218 |
while (rs.next()) { |
219 | 219 |
int year = Integer.parseInt(rs.getString(1).substring(0, 4)); |
... | ... | |
238 | 238 |
st.close(); |
239 | 239 |
|
240 | 240 |
//st = connection.prepareStatement("SELECT COALESCE(SUBSTRING(d.date FROM 1 FOR 4), SUBSTRING(v.date FROM 1 FOR 4)) AS year, COUNT(DISTINCT d.repository_id) AS repository, COUNT(DISTINCT d.result_id) AS items, SUM(d.count) AS downloads, SUM(v.count) AS views FROM public.downloads_stats d FULL OUTER JOIN public.views_stats v ON d.source=v.source AND d.repository_id=v.repository_id AND d.result_id=v.result_id AND d.date=v.date GROUP BY year ORDER BY year;"); |
241 |
st = connection.prepareStatement("SELECT SUBSTRING(date FROM 1 FOR 4) AS year, COUNT(DISTINCT repository_id) AS repository, COUNT(DISTINCT result_id) AS items, SUM(downloads) AS downloads, SUM(views) AS views FROM usage_stats GROUP BY year ORDER BY year;");
|
|
241 |
st = connection.prepareStatement("SELECT SUBSTRING(date FROM 1 FOR 4) AS year, COUNT(DISTINCT repository_id) AS repository, COUNT(DISTINCT result_id) AS items, SUM(downloads) AS downloads, SUM(views) AS views FROM reports GROUP BY year ORDER BY year;");
|
|
242 | 242 |
rs = st.executeQuery(); |
243 | 243 |
List<YearlyStats> yearlyStatsList = new ArrayList<>(); |
244 | 244 |
while (rs.next()) { |
... | ... | |
371 | 371 |
|
372 | 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=?"); |
373 | 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 |
st = connection.prepareStatement("SELECT DISTINCT original_result_id FROM reports WHERE ?=ANY(orid);"); |
|
375 |
st.setString(1, oid); |
|
377 | 376 |
|
378 | 377 |
rs = st.executeQuery(); |
379 | 378 |
|
... | ... | |
406 | 405 |
|
407 | 406 |
//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=?"); |
408 | 407 |
//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=?;");
|
|
408 |
st = connection.prepareStatement("SELECT DISTINCT original_result_id FROM reports WHERE ?=ANY(pid);");
|
|
410 | 409 |
st.setString(1, doi); |
411 | 410 |
//st.setString(2, doi); |
412 | 411 |
|
... | ... | |
457 | 456 |
//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;"); |
458 | 457 |
//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;"); |
459 | 458 |
//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';");
|
|
459 |
st = connection.prepareStatement("select repository_id, title, publisher, source , types, pid, name, date,orid,downloads,views from reports where date>=? AND date<=? and original_result_id=?;");
|
|
461 | 460 |
st.setString(1, beginDateStr); |
462 | 461 |
st.setString(2, endDateStr); |
463 | 462 |
st.setString(3, openaire); |
... | ... | |
470 | 469 |
//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;"); |
471 | 470 |
//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;"); |
472 | 471 |
//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';");
|
|
472 |
st = connection.prepareStatement("select repository_id, title, publisher, source , types, pid, name, date,orid,downloads,views from reports where date>=? AND date<=? and original_result_id=? and ?=ANY(type);");
|
|
474 | 473 |
st.setString(1, beginDateStr); |
475 | 474 |
st.setString(2, endDateStr); |
476 | 475 |
st.setString(3, openaire); |
... | ... | |
485 | 484 |
if (itemDataType.equals("")) { |
486 | 485 |
//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;"); |
487 | 486 |
//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';");
|
|
487 |
st = connection.prepareStatement("select repository_id, title, publisher, source, types, pid name, date,orid,downloads,views from reports where date>=? AND date<=? and original_result_id=? and repository_id=?;");
|
|
489 | 488 |
st.setString(1, beginDateStr); |
490 | 489 |
st.setString(2, endDateStr); |
491 | 490 |
st.setString(3, openaire); |
... | ... | |
499 | 498 |
} else { |
500 | 499 |
//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;"); |
501 | 500 |
//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';");
|
|
501 |
st = connection.prepareStatement("select repository_id, title, publisher, source, types, pid, name, date, orid,downloads,views from reports where date>? AND date<=? and original_result_id=? and repository_id=? and ?=Any(types);");
|
|
503 | 502 |
st.setString(1, beginDateStr); |
504 | 503 |
st.setString(2, endDateStr); |
505 | 504 |
st.setString(3, openaire); |
... | ... | |
529 | 528 |
reportItems.add(reportItem); |
530 | 529 |
} |
531 | 530 |
repository = rs.getString(1); |
532 |
reportItem = new ReportItem(rs.getString(3), rs.getString(7), rs.getString(5), rs.getString(2)); |
|
531 |
String[] typesArr = (String[]) rs.getArray(5).getArray(); |
|
532 |
String type = ""; |
|
533 |
if (typesArr[0] != null && !typesArr[0].equals("")) { |
|
534 |
type = typesArr[0]; |
|
535 |
} |
|
536 |
|
|
537 |
reportItem = new ReportItem(rs.getString(3), rs.getString(7), type, rs.getString(2)); |
|
533 | 538 |
reportItem.addIdentifier(new ItemIdentifier("OpenAIRE", openaire)); |
534 | 539 |
reportItem.addIdentifier(new ItemIdentifier("URLs", rs.getString(4))); |
535 | 540 |
|
... | ... | |
580 | 585 |
} |
581 | 586 |
repository = rs.getString(1); |
582 | 587 |
lastDate = beginDateStr; |
583 |
reportItem = new ReportItem(rs.getString(3), rs.getString(7), rs.getString(5), rs.getString(2)); |
|
588 |
|
|
589 |
String[] typesArr = (String[]) rs.getArray(5).getArray(); |
|
590 |
String type=""; |
|
591 |
if (typesArr[0] != null && !typesArr[0].equals("")) { |
|
592 |
type = typesArr[0]; |
|
593 |
} |
|
594 |
|
|
595 |
reportItem = new ReportItem(rs.getString(3), rs.getString(7), type, rs.getString(2)); |
|
584 | 596 |
reportItem.addIdentifier(new ItemIdentifier("OpenAIRE", openaire)); |
585 | 597 |
reportItem.addIdentifier(new ItemIdentifier("URLs", rs.getString(4))); |
586 | 598 |
|
... | ... | |
983 | 995 |
//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;"); |
984 | 996 |
//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;"); |
985 | 997 |
//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;");
|
|
998 |
st = connection.prepareStatement("SELECT original_result_id, title, publisher, source, types, pid, name, date, orid, sum(downloads) as downloads, sum(views) as views FROM reports WHERE date>=? AND date<=? AND repository_id=? GROUP BY original_result_id, title, publisher, pid, orid, source, types, name, date;");
|
|
987 | 999 |
st.setString(1, beginDateStr); |
988 | 1000 |
st.setString(2, endDateStr); |
989 | 1001 |
st.setString(3, repositoryIdentifier); |
... | ... | |
997 | 1009 |
//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;"); |
998 | 1010 |
//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;"); |
999 | 1011 |
//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;");
|
|
1012 |
st = connection.prepareStatement("SELECT original_result_id, title, publisher, source, types, pid, name, date, orid, sum(downloads) as downloads, sum(views) as views FROM reports WHERE date>=? AND date<=? AND repository_id=? AND ?=ANY(types) GROUP BY original_result_id, title, publisher, pid, orid, source, types, name, date;");
|
|
1001 | 1013 |
st.setString(1, beginDateStr); |
1002 | 1014 |
st.setString(2, endDateStr); |
1003 | 1015 |
st.setString(3, repositoryIdentifier); |
... | ... | |
1035 | 1047 |
reportItems.add(reportItem); |
1036 | 1048 |
} |
1037 | 1049 |
result = rs.getString(1); |
1038 |
reportItem = new ReportItem(rs.getString(3), rs.getString(7), rs.getString(5), rs.getString(2)); |
|
1050 |
|
|
1051 |
String[] typesArr = (String[]) rs.getArray(5).getArray(); |
|
1052 |
String type = ""; |
|
1053 |
if (typesArr[0] != null && !typesArr[0].equals("")) { |
|
1054 |
type = typesArr[0]; |
|
1055 |
} |
|
1056 |
reportItem = new ReportItem(rs.getString(3), rs.getString(7), type, rs.getString(2)); |
|
1039 | 1057 |
reportItem.addIdentifier(new ItemIdentifier("OpenAIRE", rs.getString(1))); |
1040 | 1058 |
reportItem.addIdentifier(new ItemIdentifier("URLs", rs.getString(4))); |
1041 | 1059 |
|
... | ... | |
1086 | 1104 |
} |
1087 | 1105 |
result = rs.getString(1); |
1088 | 1106 |
lastDate = beginDateStr; |
1089 |
reportItem = new ReportItem(rs.getString(3), rs.getString(7), rs.getString(5), rs.getString(2)); |
|
1107 |
|
|
1108 |
String[] typesArr = (String[]) rs.getArray(5).getArray(); |
|
1109 |
String type = ""; |
|
1110 |
if (typesArr[0] != null && !typesArr[0].equals("")) { |
|
1111 |
type = typesArr[0]; |
|
1112 |
} |
|
1113 |
|
|
1114 |
reportItem = new ReportItem(rs.getString(3), rs.getString(7), type, rs.getString(2)); |
|
1090 | 1115 |
reportItem.addIdentifier(new ItemIdentifier("OpenAIRE", rs.getString(1))); |
1091 | 1116 |
reportItem.addIdentifier(new ItemIdentifier("URLs", rs.getString(4))); |
1092 | 1117 |
|
Also available in: Unified diff
Query chganges