| 191 |
191 |
ResultSet rs;
|
| 192 |
192 |
switch (split[0].toLowerCase()) {
|
| 193 |
193 |
case "openaire":
|
| 194 |
|
st = connection.prepareStatement("select piwik_id from datasource_piwik where openaire_id=?");
|
| 195 |
|
//st = connection.prepareStatement("select piwik_id from datasource where id=?");
|
|
194 |
st = connection.prepareStatement("select piwik_id from datasource where id=?");
|
| 196 |
195 |
st.setString(1, repositoryIdentifier.replaceFirst(split[0] + ":", ""));
|
| 197 |
196 |
|
| 198 |
197 |
rs = st.executeQuery();
|
| ... | ... | |
| 205 |
204 |
return piwikid;
|
| 206 |
205 |
|
| 207 |
206 |
case "repoid":
|
| 208 |
|
st = connection.prepareStatement("select piwik_id from datasource_piwik where piwik_id=?");
|
| 209 |
|
//st = connection.prepareStatement("select piwik_id from datasource where id=?");
|
|
207 |
st = connection.prepareStatement("select piwik_id from datasource where piwik_id=?");
|
| 210 |
208 |
st.setInt(1, Integer.parseInt(repositoryIdentifier.replaceFirst(split[0] + ":", "")));
|
| 211 |
209 |
|
| 212 |
210 |
rs = st.executeQuery();
|
| ... | ... | |
| 295 |
293 |
Connection connection = dataSource.getConnection();
|
| 296 |
294 |
PreparedStatement st;
|
| 297 |
295 |
if(itemDataType.equals("")) {
|
| 298 |
|
st = connection.prepareStatement("select r.title, r.publisher, rc.type, r.source, r.id from result r, result_oids roid, result_classifications rc where r.id=roid.id and r.id=rc.id and roid.orid=? limit 1");
|
| 299 |
|
//st = connection.prepareStatement("select r.title, r.publisher, rc.type, r.source, r.id, d.name from result r, result_oids roid, result_classifications rc, result_datasources rd, datasource d where rd.id=r.id and rd.datasource=d.id and r.id=roid.id and r.id=rc.id and roid.orid=? limit 1");
|
|
296 |
st = connection.prepareStatement("select r.title, r.publisher, rc.type, r.source, r.id, d.name from result r, result_oids roid, result_classifications rc, result_datasources rd, datasource d where rd.id=r.id and rd.datasource=d.id and r.id=roid.id and r.id=rc.id and roid.orid=? limit 1");
|
| 300 |
297 |
st.setString(1, oid);
|
| 301 |
298 |
}
|
| 302 |
299 |
else{
|
| 303 |
|
st = connection.prepareStatement("select r.title, r.publisher, rc.type, r.source, r.id from result r, result_oids roid, result_classifications rc where r.id=roid.id and r.id=rc.id and roid.orid=? and rc.type=? limit 1");
|
| 304 |
|
//st = connection.prepareStatement("select r.title, r.publisher, rc.type, r.source, r.id, d.name from result r, result_oids roid, result_classifications rc, result_datasources rd, datasource d where rd.id=r.id and rd.datasource=d.id and r.id=roid.id and r.id=rc.id and roid.orid=? and rc.type=? limit 1");
|
|
300 |
st = connection.prepareStatement("select r.title, r.publisher, rc.type, r.source, r.id, d.name from result r, result_oids roid, result_classifications rc, result_datasources rd, datasource d where rd.id=r.id and rd.datasource=d.id and r.id=roid.id and r.id=rc.id and roid.orid=? and rc.type=? limit 1");
|
| 305 |
301 |
st.setString(1, oid);
|
| 306 |
302 |
st.setString(2, itemDataType);
|
| 307 |
303 |
}
|
| ... | ... | |
| 310 |
306 |
|
| 311 |
307 |
boolean hasData = false;
|
| 312 |
308 |
while (rs.next()) {
|
| 313 |
|
reportItem = new ReportItem(rs.getString(2),"", rs.getString(3),rs.getString(1));
|
| 314 |
|
//reportItem = new ReportItem(rs.getString(2),rs.getString(6), rs.getString(3),rs.getString(1));
|
|
309 |
//reportItem = new ReportItem(rs.getString(2),"", rs.getString(3),rs.getString(1));
|
|
310 |
reportItem = new ReportItem(rs.getString(2),rs.getString(6), rs.getString(3),rs.getString(1));
|
| 315 |
311 |
reportItem.addIdentifier(new ItemIdentifier("URL",rs.getString(4)));
|
| 316 |
312 |
reportItem.addIdentifier(new ItemIdentifier("OAI",oid));
|
| 317 |
313 |
reportItem.addIdentifier(new ItemIdentifier("OPENAIRE",rs.getString(5)));
|
| ... | ... | |
| 359 |
355 |
|
| 360 |
356 |
//st = connection.prepareStatement("select extract('year' from date) ||'-'|| extract('month' from date) ||'-01' as new_date, sum(number_of_downloads) from repo_download_stats where id=? and date>=? and date<=? group by new_date order by new_date");
|
| 361 |
357 |
if(repositoryIdentifier.equals("")) {
|
| 362 |
|
//st = connection.prepareStatement("select month, sum from repo_download_stats_monthly_sushi where id=? and month>=? and month<=? order by month;");
|
| 363 |
358 |
st = connection.prepareStatement("select d.date, case when sum is null then 0 else sum end as sum from (SELECT to_char(date_trunc('month', (?::date + interval '1 month'*offs)), 'YYYY-MM-DD') AS date FROM generate_series(0, ?, 1) AS offs) d LEFT JOIN (select month, sum from repo_download_stats_monthly_sushi where id=? and month>=? and month<=?) rdsm ON d.date=rdsm.month;");
|
| 364 |
359 |
}
|
| 365 |
360 |
else{
|
| 366 |
|
//st = connection.prepareStatement("select month, sum from repo_download_stats_monthly_sushi where id=? and month>=? and month<=? and source=? order by month;");
|
| 367 |
361 |
st = connection.prepareStatement("select d.date, case when sum is null then 0 else sum end as sum from (SELECT to_char(date_trunc('month', (?::date + interval '1 month'*offs)), 'YYYY-MM-DD') AS date FROM generate_series(0, ?, 1) AS offs) d LEFT JOIN (select month, sum from repo_download_stats_monthly_sushi where id=? and month>=? and month<=? and source=?) rdsm ON d.date=rdsm.month;");
|
| 368 |
362 |
st.setInt(6, Integer.parseInt(repositoryIdentifier));
|
| 369 |
363 |
}
|
| ... | ... | |
| 415 |
409 |
if (granularity.toLowerCase().equals("totals")) {
|
| 416 |
410 |
if(repositoryIdentifier.equals("")){
|
| 417 |
411 |
if(itemDataType.equals("")){
|
| 418 |
|
st = connection.prepareStatement("select source, d.id, d.name, d.websiteurl, sum(number_of_downloads) from repo_download_stats rds, datasource_piwik dpo, datasource d where rds.source=dpo.piwik_id and dpo.openaire_id=d.id and rds.date>=? and rds.date<=? group by source, d.id, d.name, d.websiteurl order by source;");
|
| 419 |
|
//st = connection.prepareStatement("select source, d.id, d.name, d.websiteurl, sum(number_of_downloads) from repo_download_stats rds, datasource d where rds.source=d.piwik_id and rds.date>=? and rds.date<=? group by source, d.id, d.name, d.websiteurl order by source;");
|
|
412 |
st = connection.prepareStatement("select source, d.id, d.name, d.websiteurl, sum(number_of_downloads) from repo_download_stats rds, datasource d where rds.source=d.piwik_id and rds.date>=? and rds.date<=? group by source, d.id, d.name, d.websiteurl order by source;");
|
| 420 |
413 |
st.setDate(1, new java.sql.Date(beginDate.getTime()));
|
| 421 |
414 |
st.setDate(2, new java.sql.Date(endDate.getTime()));
|
| 422 |
415 |
}
|
| 423 |
416 |
else{
|
| 424 |
|
st = connection.prepareStatement("select source, d.id, d.name, d.websiteurl, sum(number_of_downloads) from repo_download_stats rds, datsource_piwik dpo, datasource d, result_oids roid, result_classifications rc where roid.orid=rds.id and roid.id=rc.id and rds.source=dpo.piwik_id and dpo.openaire_id=d.id and rds.date>=? and rds.date<=? and rc.type=? group by source, d.id, d.name, d.websiteurl order by source;");
|
| 425 |
|
//st = connection.prepareStatement("select source, d.id, d.name, d.websiteurl, sum(number_of_downloads) from repo_download_stats rds, datasource d, result_oids roid, result_classifications rc where roid.orid=rds.id and roid.id=rc.id and rds.source=d.piwik_id and rds.date>=? and rds.date<=? and rc.type=? group by source, d.id, d.name, d.websiteurl order by source;");
|
|
417 |
st = connection.prepareStatement("select source, d.id, d.name, d.websiteurl, sum(number_of_downloads) from repo_download_stats rds, datasource d, result_oids roid, result_classifications rc where roid.orid=rds.id and roid.id=rc.id and rds.source=d.piwik_id and rds.date>=? and rds.date<=? and rc.type=? group by source, d.id, d.name, d.websiteurl order by source;");
|
| 426 |
418 |
st.setDate(1, new java.sql.Date(beginDate.getTime()));
|
| 427 |
419 |
st.setDate(2, new java.sql.Date(endDate.getTime()));
|
| 428 |
420 |
st.setString(3, itemDataType);
|
| 429 |
421 |
}
|
| 430 |
422 |
} else {
|
| 431 |
423 |
if(itemDataType.equals("")){
|
| 432 |
|
st = connection.prepareStatement("select source, d.id, d.name, d.websiteurl, sum(number_of_downloads) from repo_download_stats rds, datasource_piwik dpo, datasource d where rds.source=dpo.piwik_id and dpo.openaire_id=d.id and rds.date>=? and rds.date<=? and rds.source=? group by rds.source, d.id, d.name, d.websiteurl;");
|
| 433 |
|
//st = connection.prepareStatement("select source, d.id, d.name, d.websiteurl, sum(number_of_downloads) from repo_download_stats rds, datasource d where rds.source=d.piwik_id and rds.date>=? and rds.date<=? and rds.source=? group by rds.source, d.id, d.name, d.websiteurl;");
|
|
424 |
st = connection.prepareStatement("select source, d.id, d.name, d.websiteurl, sum(number_of_downloads) from repo_download_stats rds, datasource d where rds.source=d.piwik_id and rds.date>=? and rds.date<=? and rds.source=? group by rds.source, d.id, d.name, d.websiteurl;");
|
| 434 |
425 |
st.setDate(1, new java.sql.Date(beginDate.getTime()));
|
| 435 |
426 |
st.setDate(2, new java.sql.Date(endDate.getTime()));
|
| 436 |
427 |
st.setInt(3, Integer.parseInt(repositoryIdentifier));
|
| 437 |
428 |
}
|
| 438 |
429 |
else{
|
| 439 |
|
st = connection.prepareStatement("select source, d.id, d.name, d.websiteurl, sum(number_of_downloads) from repo_download_stats rds, datasource_piwik dpo, datasource d, result_oids roid, result_classifications rc where roid.orid=rds.id and roid.id=rc.id and rds.source=dpo.piwik_id and dpo.openaire_id=d.id and rds.date>=? and rds.date<=? and rc.type=? and rds.source=? group by source, d.id, d.name, d.websiteurl;");
|
| 440 |
|
//st = connection.prepareStatement("select source, d.id, d.name, d.websiteurl, sum(number_of_downloads) from repo_download_stats rds, datasource d, result_oids roid, result_classifications rc where roid.orid=rds.id and roid.id=rc.id and rds.source=d.piwik_id and rds.date>=? and rds.date<=? and rc.type=? and rds.source=? group by source, d.id, d.name, d.websiteurl;");
|
|
430 |
st = connection.prepareStatement("select source, d.id, d.name, d.websiteurl, sum(number_of_downloads) from repo_download_stats rds, datasource d, result_oids roid, result_classifications rc where roid.orid=rds.id and roid.id=rc.id and rds.source=d.piwik_id and rds.date>=? and rds.date<=? and rc.type=? and rds.source=? group by source, d.id, d.name, d.websiteurl;");
|
| 441 |
431 |
st.setDate(1, new java.sql.Date(beginDate.getTime()));
|
| 442 |
432 |
st.setDate(2, new java.sql.Date(endDate.getTime()));
|
| 443 |
433 |
st.setString(3, itemDataType);
|
| ... | ... | |
| 468 |
458 |
|
| 469 |
459 |
if(repositoryIdentifier.equals("")){
|
| 470 |
460 |
if(itemDataType.equals("")){
|
| 471 |
|
st = connection.prepareStatement("select dd.piwikid, dd.id, dd.name, dd.websiteurl, dd.new_date, case when rdm.tsum is null then 0 else rdm.tsum end FROM (select d.id, d.name, dpo.piwik_id AS piwikid, d.websiteurl, to_char(date_trunc('month', (?::date + interval '1 month'*offs)), 'YYYY-MM-DD') AS new_date FROM datasource_piwik dpo, datasource d, generate_series(0, ?, 1) AS offs where dpo.openaire_id=d.id order by d.id, new_date) dd LEFT JOIN (SELECT source, month, SUM(sum) as tsum FROM repo_download_stats_monthly_sushi where month>=? and month<=? group by source, month) rdm ON dd.new_date=rdm.month and rdm.source=dd.piwikid order by dd.piwikid, dd.new_date;");
|
| 472 |
|
//st = connection.prepareStatement("select dd.piwik_id, dd.id, dd.name, dd.websiteurl, dd.new_date, case when rdm.tsum is null then 0 else rdm.tsum end FROM (select d.id, d.name, d.piwik_id, d.websiteurl, to_char(date_trunc('month', (?::date + interval '1 month'*offs)), 'YYYY-MM-DD') AS new_date FROM datasource d, generate_series(0, ?, 1) AS offs where d.piwik_id is not null order by d.id, new_date) dd LEFT JOIN (SELECT source, month, SUM(sum) as tsum FROM repo_download_stats_monthly_sushi where month>=? and month<=? group by source, month) rdm ON dd.new_date=rdm.month and rdm.source=dd.piwik_id order by dd.piwik_id, dd.new_date;");
|
|
461 |
st = connection.prepareStatement("select dd.piwik_id, dd.id, dd.name, dd.websiteurl, dd.new_date, case when rdm.tsum is null then 0 else rdm.tsum end FROM (select d.id, d.name, d.piwik_id, d.websiteurl, to_char(date_trunc('month', (?::date + interval '1 month'*offs)), 'YYYY-MM-DD') AS new_date FROM datasource d, generate_series(0, ?, 1) AS offs where d.piwik_id is not null order by d.id, new_date) dd LEFT JOIN (SELECT source, month, SUM(sum) as tsum FROM repo_download_stats_monthly_sushi where month>=? and month<=? group by source, month) rdm ON dd.new_date=rdm.month and rdm.source=dd.piwik_id order by dd.piwik_id, dd.new_date;");
|
| 473 |
462 |
st.setString(1, report_dateFormat.format(beginDate));
|
| 474 |
463 |
st.setInt(2, diffMonth);
|
| 475 |
464 |
st.setDate(3, new java.sql.Date(beginDate.getTime()));
|
| 476 |
465 |
st.setDate(4, new java.sql.Date(endDate.getTime()));
|
| 477 |
466 |
}
|
| 478 |
467 |
else{
|
| 479 |
|
st = connection.prepareStatement("select dd.piwikid, dd.id, dd.name, dd.websiteurl, dd.new_date, case when rdm.tsum is null then 0 else rdm.tsum end FROM (select d.id, d.name, dpo.piwik_id AS piwikid, d.websiteurl, to_char(date_trunc('month', (?::date + interval '1 month'*offs)), 'YYYY-MM-DD') AS new_date FROM datasource_piwik dpo, datasource d, generate_series(0, ?, 1) AS offs where dpo.openaire_id=d.id order by d.id, new_date) dd LEFT JOIN (SELECT rds.source, rds.month, SUM(rds.sum) as tsum FROM repo_download_stats_monthly_sushi rds, result_oids roid, result_classifications rc where rds.id=roid.orid and roid.id=rc.id and rc.type=? and rds.month>=? and rds.month<=? group by rds.source, rds.month) rdm ON dd.new_date=rdm.month and rdm.source=dd.piwikid order by dd.piwikid, dd.new_date;");
|
| 480 |
|
//st = connection.prepareStatement("select dd.piwik_id, dd.id, dd.name, dd.websiteurl, dd.new_date, case when rdm.tsum is null then 0 else rdm.tsum end FROM (select d.id, d.name, d.piwik_id, d.websiteurl, to_char(date_trunc('month', (?::date + interval '1 month'*offs)), 'YYYY-MM-DD') AS new_date FROM datasource d, generate_series(0, ?, 1) AS offs where d.piwik_id is not null order by d.id, new_date) dd LEFT JOIN (SELECT rds.source, rds.month, SUM(rds.sum) as tsum FROM repo_download_stats_monthly_sushi rds, result_oids roid, result_classifications rc where rds.id=roid.orid and roid.id=rc.id and rc.type=? and rds.month>=? and rds.month<=? group by rds.source, rds.month) rdm ON dd.new_date=rdm.month and rdm.source=dd.piwik_id order by dd.piwik_id, dd.new_date;");
|
|
468 |
st = connection.prepareStatement("select dd.piwik_id, dd.id, dd.name, dd.websiteurl, dd.new_date, case when rdm.tsum is null then 0 else rdm.tsum end FROM (select d.id, d.name, d.piwik_id, d.websiteurl, to_char(date_trunc('month', (?::date + interval '1 month'*offs)), 'YYYY-MM-DD') AS new_date FROM datasource d, generate_series(0, ?, 1) AS offs where d.piwik_id is not null order by d.id, new_date) dd LEFT JOIN (SELECT rds.source, rds.month, SUM(rds.sum) as tsum FROM repo_download_stats_monthly_sushi rds, result_oids roid, result_classifications rc where rds.id=roid.orid and roid.id=rc.id and rc.type=? and rds.month>=? and rds.month<=? group by rds.source, rds.month) rdm ON dd.new_date=rdm.month and rdm.source=dd.piwik_id order by dd.piwik_id, dd.new_date;");
|
| 481 |
469 |
st.setString(1, report_dateFormat.format(beginDate));
|
| 482 |
470 |
st.setInt(2, diffMonth);
|
| 483 |
471 |
st.setString(3, itemDataType);
|
| ... | ... | |
| 486 |
474 |
}
|
| 487 |
475 |
} else {
|
| 488 |
476 |
if(itemDataType.equals("")){
|
| 489 |
|
st = connection.prepareStatement("select dd.piwikid, dd.id, dd.name, dd.websiteurl, dd.new_date, case when rdm.tsum is null then 0 else rdm.tsum end FROM (select d.id, d.name, dpo.piwik_id AS piwikid, d.websiteurl, to_char(date_trunc('month', (?::date + interval '1 month'*offs)), 'YYYY-MM-DD') AS new_date FROM datasource_piwik dpo, datasource d, generate_series(0, ?, 1) AS offs where dpo.openaire_id=d.id and dpo.piwik_id=? order by d.id, new_date) dd LEFT JOIN (SELECT source, month, SUM(sum) as tsum FROM repo_download_stats_monthly_sushi where source=? and month>=? and month<=? group by source, month) rdm ON dd.new_date=rdm.month and rdm.source=dd.piwikid order by dd.piwikid, dd.new_date;");
|
| 490 |
|
//st = connection.prepareStatement("select dd.piwik_id, dd.id, dd.name, dd.websiteurl, dd.new_date, case when rdm.tsum is null then 0 else rdm.tsum end FROM (select d.id, d.name, d.piwik_id, d.websiteurl, to_char(date_trunc('month', (?::date + interval '1 month'*offs)), 'YYYY-MM-DD') AS new_date FROM datasource d, generate_series(0, ?, 1) AS offs where d.piwik_id=? order by d.id, new_date) dd LEFT JOIN (SELECT source, month, SUM(sum) as tsum FROM repo_download_stats_monthly_sushi where source=? and month>=? and month<=? group by source, month) rdm ON dd.new_date=rdm.month and rdm.source=dd.piwik_id order by dd.piwik_id, dd.new_date;");
|
|
477 |
st = connection.prepareStatement("select dd.piwik_id, dd.id, dd.name, dd.websiteurl, dd.new_date, case when rdm.tsum is null then 0 else rdm.tsum end FROM (select d.id, d.name, d.piwik_id, d.websiteurl, to_char(date_trunc('month', (?::date + interval '1 month'*offs)), 'YYYY-MM-DD') AS new_date FROM datasource d, generate_series(0, ?, 1) AS offs where d.piwik_id=? order by d.id, new_date) dd LEFT JOIN (SELECT source, month, SUM(sum) as tsum FROM repo_download_stats_monthly_sushi where source=? and month>=? and month<=? group by source, month) rdm ON dd.new_date=rdm.month and rdm.source=dd.piwik_id order by dd.piwik_id, dd.new_date;");
|
| 491 |
478 |
st.setString(1, report_dateFormat.format(beginDate));
|
| 492 |
479 |
st.setInt(2, diffMonth);
|
| 493 |
480 |
st.setInt(3, Integer.parseInt(repositoryIdentifier));
|
| ... | ... | |
| 496 |
483 |
st.setDate(6, new java.sql.Date(endDate.getTime()));
|
| 497 |
484 |
}
|
| 498 |
485 |
else{
|
| 499 |
|
st = connection.prepareStatement("select dd.piwikid, dd.id, dd.name, dd.websiteurl, dd.new_date, case when rdm.tsum is null then 0 else rdm.tsum end FROM (select d.id, d.name, dpo.piwik_id AS piwikid, d.websiteurl, to_char(date_trunc('month', (?::date + interval '1 month'*offs)), 'YYYY-MM-DD') AS new_date FROM datasource_piwik dpo, datasource d, generate_series(0, ?, 1) AS offs where dpo.openaire_id=d.id and dpo.piwik_id=? order by d.id, new_date) dd LEFT JOIN (SELECT rds.source, rds.month, SUM(rds.sum) as tsum FROM repo_download_stats_monthly_sushi rds, result_oids roid, result_classifications rc where rds.id=roid.orid and roid.id=rc.id and rc.type=? and rds.source=? and rds.month>=? and rds.month<=? group by source, month) rdm ON dd.new_date=rdm.month and rdm.source=dd.piwikid order by dd.piwikid, dd.new_date;");
|
| 500 |
|
//st = connection.prepareStatement("select dd.piwik_id, dd.id, dd.name, dd.websiteurl, dd.new_date, case when rdm.tsum is null then 0 else rdm.tsum end FROM (select d.id, d.name, d.piwik_id, d.websiteurl, to_char(date_trunc('month', (?::date + interval '1 month'*offs)), 'YYYY-MM-DD') AS new_date FROM datasource d, generate_series(0, ?, 1) AS offs where dpo.piwik_id=? order by d.id, new_date) dd LEFT JOIN (SELECT rds.source, rds.month, SUM(rds.sum) as tsum FROM repo_download_stats_monthly_sushi rds, result_oids roid, result_classifications rc where rds.id=roid.orid and roid.id=rc.id and rc.type=? and rds.source=? and rds.month>=? and rds.month<=? group by source, month) rdm ON dd.new_date=rdm.month and rdm.source=dd.piwik_id order by dd.piwik_id, dd.new_date;");
|
|
486 |
st = connection.prepareStatement("select dd.piwik_id, dd.id, dd.name, dd.websiteurl, dd.new_date, case when rdm.tsum is null then 0 else rdm.tsum end FROM (select d.id, d.name, d.piwik_id, d.websiteurl, to_char(date_trunc('month', (?::date + interval '1 month'*offs)), 'YYYY-MM-DD') AS new_date FROM datasource d, generate_series(0, ?, 1) AS offs where dpo.piwik_id=? order by d.id, new_date) dd LEFT JOIN (SELECT rds.source, rds.month, SUM(rds.sum) as tsum FROM repo_download_stats_monthly_sushi rds, result_oids roid, result_classifications rc where rds.id=roid.orid and roid.id=rc.id and rc.type=? and rds.source=? and rds.month>=? and rds.month<=? group by source, month) rdm ON dd.new_date=rdm.month and rdm.source=dd.piwik_id order by dd.piwik_id, dd.new_date;");
|
| 501 |
487 |
st.setString(1, report_dateFormat.format(beginDate));
|
| 502 |
488 |
st.setInt(2, diffMonth);
|
| 503 |
489 |
st.setInt(3, Integer.parseInt(repositoryIdentifier));
|
| ... | ... | |
| 551 |
537 |
|
| 552 |
538 |
if(granularity.toLowerCase().equals("totals")){
|
| 553 |
539 |
if(itemDataType.equals("")) {
|
| 554 |
|
st = connection.prepareStatement("select rds.id, r.title, r.publisher, rc.type, r.source, r.id, sum(rds.number_of_downloads) from repo_download_stats rds, result r, result_oids roid, result_classifications rc where r.id=roid.id and r.id=rc.id and roid.orid=rds.id and rds.source=? and rds.date>=? and rds.date<=? group by rds.id, r.title, r.publisher, rc.type, r.source, r.id;");
|
| 555 |
|
//st = connection.prepareStatement("select rds.id, r.title, r.publisher, rc.type, r.source, d.name r.id, sum(rds.number_of_downloads) from repo_download_stats rds, result r, result_oids roid, result_classifications rc, result_datasources rd, datasource d where rd.id=r.id and rd.datasource=d.id and r.id=roid.id and r.id=rc.id and roid.orid=rds.id and rds.source=? and rds.date>=? and rds.date<=? group by rds.id, r.title, r.publisher, rc.type, r.source, d.name, r.id;");
|
|
540 |
//st = connection.prepareStatement("select rds.id, r.title, r.publisher, rc.type, r.source, d.name, r.id, sum(rds.number_of_downloads) from repo_download_stats rds, result r, result_oids roid, result_classifications rc, result_datasources rd, datasource d where rd.id=r.id and rd.datasource=d.id and r.id=roid.id and r.id=rc.id and roid.orid=rds.id and d.piwik_id=rds.source and rds.source=? and rds.date>=? and rds.date<=? group by rds.id, r.title, r.publisher, rc.type, r.source, d.name, r.id;");
|
|
541 |
st = connection.prepareStatement("select rds.id, r.title, r.publisher, rc.type, r.source, d.name, r.id, sum(rds.sum) from repo_download_stats_monthly_sushi rds, result r, result_oids roid, result_classifications rc, result_datasources rd, datasource d where rd.id=r.id and rd.datasource=d.id and r.id=roid.id and r.id=rc.id and roid.orid=rds.id and d.piwik_id=rds.source and rds.source=? and rds.month>=? and rds.month<=? group by rds.id, r.title, r.publisher, rc.type, r.source, d.name, r.id;");
|
| 556 |
542 |
st.setInt(1, Integer.parseInt(repositoryIdentifier));
|
| 557 |
543 |
st.setDate(2, new java.sql.Date(beginDate.getTime()));
|
| 558 |
544 |
st.setDate(3, new java.sql.Date(endDate.getTime()));
|
| 559 |
545 |
}
|
| 560 |
546 |
else{
|
| 561 |
|
st = connection.prepareStatement("select rds.id, r.title, r.publisher, rc.type, r.source, r.id, sum(rds.number_of_downloads) from repo_download_stats rds, result r, result_oids roid, result_classifications rc where r.id=roid.id and r.id=rc.id and roid.orid=rds.id and rds.source=? and rds.date>=? and rds.date<=? and rc.type=? group by rds.id, r.title, r.publisher, rc.type, r.source, r.id;");
|
| 562 |
|
//st = connection.prepareStatement("select rds.id, r.title, r.publisher, rc.type, r.source, d.name, r.id, sum(rds.number_of_downloads) from repo_download_stats rds, result r, result_oids roid, result_classifications rc, result_datasources rd, datasource d where rd.id=r.id and rd.datasource=d.id and r.id=roid.id and r.id=rc.id and roid.orid=rds.id and rds.source=? and rds.date>=? and rds.date<=? and rc.type=? group by rds.id, r.title, r.publisher, rc.type, r.source, d.name, r.id;");
|
|
547 |
//st = connection.prepareStatement("select rds.id, r.title, r.publisher, rc.type, r.source, d.name, r.id, sum(rds.number_of_downloads) from repo_download_stats rds, result r, result_oids roid, result_classifications rc, result_datasources rd, datasource d where rd.id=r.id and rd.datasource=d.id and r.id=roid.id and r.id=rc.id and roid.orid=rds.id and d.piwik_id=rds.source and rds.source=? and rds.date>=? and rds.date<=? and rc.type=? group by rds.id, r.title, r.publisher, rc.type, r.source, d.name, r.id;");
|
|
548 |
st = connection.prepareStatement("select rds.id, r.title, r.publisher, rc.type, r.source, d.name, r.id, sum(rds.sum) from repo_download_stats_monthly_sushi rds, result r, result_oids roid, result_classifications rc, result_datasources rd, datasource d where rd.id=r.id and rd.datasource=d.id and r.id=roid.id and r.id=rc.id and roid.orid=rds.id and d.piwik_id=rds.source and rds.source=? and rds.month>=? and rds.month<=? and rc.type=? group by rds.id, r.title, r.publisher, rc.type, r.source, d.name, r.id;");
|
| 563 |
549 |
st.setInt(1, Integer.parseInt(repositoryIdentifier));
|
| 564 |
550 |
st.setDate(2, new java.sql.Date(beginDate.getTime()));
|
| 565 |
551 |
st.setDate(3, new java.sql.Date(endDate.getTime()));
|
| ... | ... | |
| 568 |
554 |
ResultSet rs = st.executeQuery();
|
| 569 |
555 |
|
| 570 |
556 |
while (rs.next()) {
|
| 571 |
|
ReportItem reportItem = new ReportItem(rs.getString(3), "", rs.getString(4), rs.getString(2));
|
| 572 |
|
//ReportItem reportItem = new ReportItem(rs.getString(3), rs.getString(6), rs.getString(4), rs.getString(2));
|
|
557 |
ReportItem reportItem = new ReportItem(rs.getString(3), rs.getString(6), rs.getString(4), rs.getString(2));
|
| 573 |
558 |
reportItem.addIdentifier(new ItemIdentifier("URL",rs.getString(5)));
|
| 574 |
559 |
reportItem.addIdentifier(new ItemIdentifier("OAI",rs.getString(1)));
|
| 575 |
560 |
reportItem.addIdentifier(new ItemIdentifier("OPENAIRE",rs.getString(6)));
|
| 576 |
|
//reportItem.addIdentifier(new ItemIdentifier("OPENAIRE",rs.getString(7)));
|
| 577 |
|
reportItem.addPerformance(new ItemPerformance(report_dateFormat.format(beginDate), report_dateFormat.format(endDate), rs.getString(7)));
|
| 578 |
|
//reportItem.addPerformance(new ItemPerformance(report_dateFormat.format(beginDate), report_dateFormat.format(endDate), rs.getString(8)));
|
|
561 |
reportItem.addIdentifier(new ItemIdentifier("OPENAIRE",rs.getString(7)));
|
|
562 |
reportItem.addPerformance(new ItemPerformance(report_dateFormat.format(beginDate), report_dateFormat.format(endDate), rs.getString(8)));
|
| 579 |
563 |
reportItems.add(reportItem);
|
| 580 |
564 |
}
|
| 581 |
565 |
rs.close();
|
| ... | ... | |
| 589 |
573 |
int diffMonth = diffYear * 12 + endCalendar.get(Calendar.MONTH) - startCalendar.get(Calendar.MONTH);
|
| 590 |
574 |
|
| 591 |
575 |
if(itemDataType.equals("")){
|
| 592 |
|
st = connection.prepareStatement("select d.orid, d.title, d.publisher, d.rctype, d.rsource, d.rid, d.new_date, case when rdm.sum is null then 0 else rdm.sum end from (select distinct roid.orid, r.title, r.publisher, rc.type as rctype, r.source as rsource, r.id as rid, to_char(date_trunc('month', (?::date + interval '1 month'*offs)), 'YYYY-MM-DD') AS new_date from generate_series(0, ?, 1) AS offs, repo_download_stats_monthly_sushi rdsm, result r, result_oids roid, result_classifications rc where r.id=roid.id and r.id=rc.id and roid.orid=rdsm.id and rdsm.source=? and rdsm.month>=? and rdsm.month<=?) d LEFT JOIN (select id, month, sum from repo_download_stats_monthly_sushi where source=?) rdm ON d.new_date=rdm.month and d.orid=rdm.id order by d.orid, d.new_date;");
|
| 593 |
|
//st = connection.prepareStatement("select d.orid, d.title, d.publisher, d.rctype, d.rsource, d.name, d.rid, d.new_date, case when rdm.sum is null then 0 else rdm.sum end from (select distinct roid.orid, r.title, r.publisher, rc.type as rctype, r.source as rsource, d.name, r.id as rid, to_char(date_trunc('month', (?::date + interval '1 month'*offs)), 'YYYY-MM-DD') AS new_date from generate_series(0, ?, 1) AS offs, result r, result_oids roid, result_classifications rc, result_datasources rd, datasource ds where rd.id=r.id and rd.datasource=ds.id and r.id=roid.id and r.id=rc.id ds.piwik_id=?) d LEFT JOIN (select id, month, sum from repo_download_stats_monthly_sushi where source=?) rdm ON d.new_date=rdm.month and d.orid=rdm.id order by d.orid, d.new_date;");
|
|
576 |
//st = connection.prepareStatement("select d.orid, d.title, d.publisher, d.rctype, d.rsource, d.rid, d.new_date, case when rdm.sum is null then 0 else rdm.sum end from (select distinct roid.orid, r.title, r.publisher, rc.type as rctype, r.source as rsource, r.id as rid, to_char(date_trunc('month', (?::date + interval '1 month'*offs)), 'YYYY-MM-DD') AS new_date from generate_series(0, ?, 1) AS offs, repo_download_stats_monthly_sushi rdsm, result r, result_oids roid, result_classifications rc where r.id=roid.id and r.id=rc.id and roid.orid=rdsm.id and rdsm.source=? and rdsm.month>=? and rdsm.month<=?) d LEFT JOIN (select id, month, sum from repo_download_stats_monthly_sushi where source=?) rdm ON d.new_date=rdm.month and d.orid=rdm.id order by d.orid, d.new_date;");
|
|
577 |
|
|
578 |
st = connection.prepareStatement("select d.orid, d.title, d.publisher, d.rctype, d.rsource, d.name, d.rid, d.new_date, case when rdm.sum is null then 0 else rdm.sum end from (select distinct roid.orid, r.title, r.publisher, rc.type as rctype, r.source as rsource, ds.name, r.id as rid, to_char(date_trunc('month', (?::date + interval '1 month'*offs)), 'YYYY-MM-DD') AS new_date from generate_series(0, ?, 1) AS offs, result r, result_oids roid, result_classifications rc, result_datasources rd, datasource ds, repo_download_stats_monthly_sushi rdsm where rd.id=r.id and rd.datasource=ds.id and r.id=roid.id and r.id=rc.id and ds.piwik_id=? and ds.piwik_id=rdsm.source and roid.orid=rdsm.id and rdsm.month>=? and rdsm.month<=?) d LEFT JOIN (select id, month, sum from repo_download_stats_monthly_sushi where source=?) rdm ON d.new_date=rdm.month and d.orid=rdm.id order by d.orid, d.new_date;");
|
| 594 |
579 |
st.setString(1, report_dateFormat.format(beginDate));
|
| 595 |
580 |
st.setInt(2, diffMonth);
|
| 596 |
581 |
st.setInt(3, Integer.parseInt(repositoryIdentifier));
|
| ... | ... | |
| 598 |
583 |
st.setDate(5, new java.sql.Date(endDate.getTime()));
|
| 599 |
584 |
st.setInt(6, Integer.parseInt(repositoryIdentifier));
|
| 600 |
585 |
} else{
|
| 601 |
|
st = connection.prepareStatement("select d.orid, d.title, d.publisher, d.rctype, d.rsource, d.rid, d.new_date, case when rdm.sum is null then 0 else rdm.sum end from (select distinct roid.orid, r.title, r.publisher, rc.type as rctype, r.source as rsource, r.id as rid, to_char(date_trunc('month', (?::date + interval '1 month'*offs)), 'YYYY-MM-DD') AS new_date from generate_series(0, ?, 1) AS offs, repo_download_stats_monthly_sushi rdsm, result r, result_oids roid, result_classifications rc where r.id=roid.id and r.id=rc.id and roid.orid=rdsm.id and rdsm.source=? and rc.type=? and rdsm.month>=? and rdsm.month<=?) d LEFT JOIN (select id, month, sum from repo_download_stats_monthly_sushi where source=?) rdm ON d.new_date=rdm.month and d.orid=rdm.id order by d.orid, d.new_date;");
|
| 602 |
|
//st = connection.prepareStatement("select d.orid, d.title, d.publisher, d.rctype, d.rsource, d.name, d.rid, d.new_date, case when rdm.sum is null then 0 else rdm.sum end from (select distinct roid.orid, r.title, r.publisher, rc.type as rctype, r.source as rsource, d.name, r.id as rid, to_char(date_trunc('month', (?::date + interval '1 month'*offs)), 'YYYY-MM-DD') AS new_date from generate_series(0, ?, 1) AS offs, result r, result_oids roid, result_classifications rc, result_datasources rd, datasource ds where rd.id=r.id and rd.datasource=ds.id and r.id=roid.id and r.id=rc.id ds.piwik_id=? and rc.type=?) d LEFT JOIN (select id, month, sum from repo_download_stats_monthly_sushi where source=?) rdm ON d.new_date=rdm.month and d.orid=rdm.id order by d.orid, d.new_date;");
|
|
586 |
//st = connection.prepareStatement("select d.orid, d.title, d.publisher, d.rctype, d.rsource, d.rid, d.new_date, case when rdm.sum is null then 0 else rdm.sum end from (select distinct roid.orid, r.title, r.publisher, rc.type as rctype, r.source as rsource, r.id as rid, to_char(date_trunc('month', (?::date + interval '1 month'*offs)), 'YYYY-MM-DD') AS new_date from generate_series(0, ?, 1) AS offs, repo_download_stats_monthly_sushi rdsm, result r, result_oids roid, result_classifications rc where r.id=roid.id and r.id=rc.id and roid.orid=rdsm.id and rdsm.source=? and rc.type=? and rdsm.month>=? and rdsm.month<=?) d LEFT JOIN (select id, month, sum from repo_download_stats_monthly_sushi where source=?) rdm ON d.new_date=rdm.month and d.orid=rdm.id order by d.orid, d.new_date;");
|
|
587 |
|
|
588 |
st = connection.prepareStatement("select d.orid, d.title, d.publisher, d.rctype, d.rsource, d.name, d.rid, d.new_date, case when rdm.sum is null then 0 else rdm.sum end from (select distinct roid.orid, r.title, r.publisher, rc.type as rctype, r.source as rsource, ds.name, r.id as rid, to_char(date_trunc('month', (?::date + interval '1 month'*offs)), 'YYYY-MM-DD') AS new_date from generate_series(0, ?, 1) AS offs, result r, result_oids roid, result_classifications rc, result_datasources rd, datasource ds, repo_download_stats_monthly_sushi rdsm where rd.id=r.id and rd.datasource=ds.id and r.id=roid.id and r.id=rc.id and ds.piwik_id=? and rc.type=? and ds.piwik_id=rdsm.source and roid.orid=rdsm.id and rdsm.month>=? and rdsm.month<=?) d LEFT JOIN (select id, month, sum from repo_download_stats_monthly_sushi where source=?) rdm ON d.new_date=rdm.month and d.orid=rdm.id order by d.orid, d.new_date;");
|
| 603 |
589 |
st.setString(1, report_dateFormat.format(beginDate));
|
| 604 |
590 |
st.setInt(2, diffMonth);
|
| 605 |
591 |
st.setInt(3, Integer.parseInt(repositoryIdentifier));
|
| ... | ... | |
| 611 |
597 |
ResultSet rs = st.executeQuery();
|
| 612 |
598 |
|
| 613 |
599 |
while (rs.next()) {
|
| 614 |
|
ReportItem reportItem = new ReportItem(rs.getString(3), "", rs.getString(4), rs.getString(2));
|
| 615 |
|
//ReportItem reportItem = new ReportItem(rs.getString(3), rs.getString(6), rs.getString(4), rs.getString(2));
|
|
600 |
ReportItem reportItem = new ReportItem(rs.getString(3), rs.getString(6), rs.getString(4), rs.getString(2));
|
| 616 |
601 |
reportItem.addIdentifier(new ItemIdentifier("URL",rs.getString(5)));
|
| 617 |
602 |
reportItem.addIdentifier(new ItemIdentifier("OAI",rs.getString(1)));
|
| 618 |
|
reportItem.addIdentifier(new ItemIdentifier("OPENAIRE",rs.getString(6)));
|
| 619 |
|
//reportItem.addIdentifier(new ItemIdentifier("OPENAIRE",rs.getString(7)));
|
|
603 |
//reportItem.addIdentifier(new ItemIdentifier("OPENAIRE",rs.getString(6)));
|
|
604 |
reportItem.addIdentifier(new ItemIdentifier("OPENAIRE",rs.getString(7)));
|
| 620 |
605 |
for(int i = 0; i <= diffMonth; i++) {
|
| 621 |
606 |
Calendar temp_c = Calendar.getInstance();
|
| 622 |
|
temp_c.setTime(rs.getDate(7));
|
|
607 |
temp_c.setTime(rs.getDate(8));
|
| 623 |
608 |
temp_c.set(Calendar.DAY_OF_MONTH, temp_c.getActualMaximum(Calendar.DAY_OF_MONTH));
|
| 624 |
609 |
Date temp_endDate = temp_c.getTime();
|
| 625 |
|
reportItem.addPerformance(new ItemPerformance(report_dateFormat.format(rs.getDate(7)), report_dateFormat.format(temp_endDate), rs.getString(8)));
|
| 626 |
|
//reportItem.addPerformance(new ItemPerformance(report_dateFormat.format(rs.getDate(8)), report_dateFormat.format(temp_endDate), rs.getString(9)));
|
|
610 |
reportItem.addPerformance(new ItemPerformance(report_dateFormat.format(rs.getDate(8)), report_dateFormat.format(temp_endDate), rs.getString(9)));
|
| 627 |
611 |
if(i != diffMonth) {
|
| 628 |
612 |
rs.next();
|
| 629 |
613 |
}
|
| ... | ... | |
| 635 |
619 |
}
|
| 636 |
620 |
connection.close();
|
| 637 |
621 |
} catch (Exception e){
|
| 638 |
|
log.error("Cannot execute repo : " + e);
|
|
622 |
log.error("Cannot execute batch : " + e);
|
| 639 |
623 |
}
|
| 640 |
624 |
return 0;
|
| 641 |
625 |
}
|
Added by Tsampikos Livisianos almost 8 years ago
only ft_totals