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 |
|
Query chganges