Revision 47317
Added by Tsampikos Livisianos almost 7 years ago
| modules/dnet-openaire-usage-stats-api/src/main/java/eu/dnetlib/usagestats/repos/DatasourceRepo.java | ||
|---|---|---|
| 13 | 13 |
public class DatasourceRepo extends BaseRepository {
|
| 14 | 14 |
|
| 15 | 15 |
public UsageStats getClicks(String id) {
|
| 16 |
/* |
|
| 17 |
String query = "select 'views', d.id, d.name, sum(rvs.views) from result_views rvs, datasource d where d.piwik_id=rvs.source and d.id=? and rvs.views!='0' group by d.id, d.name " + |
|
| 18 |
//"UNION ALL select 'views', d.id, d.name, (sum(rvs.views)/2)::int from result_views rvs, datasource d, result_datasources rd where d.piwik_id=rvs.source and d.piwik_id='5' and rd.datasource=? and rd.id=rvs.id and rvs.views!='0' group by d.id, d.name " + |
|
| 19 |
"UNION ALL select 'views', d.id, d.name, sum(rvs.views) from datasource d, result_views rvs where d.piwik_id='5' and d.piwik_id=rvs.source and rvs.views!='0' and rvs.id in (select distinct id from result_datasources where datasource=?) group by d.id, d.name " + |
|
| 20 |
"UNION ALL select 'downloads', d.id, d.name, sum(rds.downloads) from result_downloads rds, datasource d where d.piwik_id=rds.source and d.id=? and rds.downloads!='0' group by d.id, d.name"; |
|
| 21 |
*/ |
|
| 22 |
|
|
| 23 |
/* |
|
| 24 |
String query = "SELECT 'views', sum(s.count) FROM views_stats s, result_datasources rd where rd.id=s.result_id and rd.datasource=? " + |
|
| 25 |
"UNION ALL SELECT 'downloads', sum(s.count) FROM downloads_stats s, result_datasources rd where rd.id=s.result_id and rd.datasource=? " + |
|
| 26 |
"UNION ALL SELECT 'pageviews', sum(s.count) FROM pageviews_stats s, result_datasources rd where rd.id=s.result_id and rd.datasource=? "; |
|
| 27 |
*/ |
|
| 28 |
|
|
| 29 | 16 |
String query = "SELECT 'views', sum(s.count), sum(s.openaire) FROM views_stats s where s.repository_id=? " + |
| 30 | 17 |
"UNION ALL SELECT 'downloads', sum(s.count), sum(s.openaire) FROM downloads_stats s where s.repository_id=? " + |
| 31 | 18 |
"UNION ALL SELECT 'pageviews', sum(s.count), '0' FROM pageviews_stats s, result_datasources rd where rd.id=s.result_id and rd.datasource=? "; |
| 32 |
//"UNION ALL SELECT 'openaire', sum(s.openaire) FROM views_stats s where s.repository_id=? "; |
|
| 33 |
//"UNION ALL SELECT 'openaire', count(*) FROM piwiklog p, datasource d WHERE p.source=d.piwik_id AND p.referrer_name='www.openaire.eu' AND d.id=? "; |
|
| 34 | 19 |
|
| 35 | 20 |
List<String> values = new ArrayList<>(); |
| 36 | 21 |
values.add(id); |
| 37 | 22 |
values.add(id); |
| 38 | 23 |
values.add(id); |
| 39 |
//values.add(id); |
|
| 40 | 24 |
|
| 41 | 25 |
return executeUsageStats(query, values, "datasource"); |
| 42 | 26 |
} |
| modules/dnet-openaire-usage-stats-api/src/main/java/eu/dnetlib/usagestats/repos/ResultRepo.java | ||
|---|---|---|
| 9 | 9 |
|
| 10 | 10 |
@Repository |
| 11 | 11 |
public class ResultRepo extends BaseRepository {
|
| 12 |
//public UsageStats getClicks(String id) {
|
|
| 12 |
|
|
| 13 | 13 |
public UsageStats getClicks(String id) {
|
| 14 |
/* |
|
| 15 |
String query = "select 'views', d.id, d.name, sum(rvs.views) from result_views rvs, datasource d where rvs.id=? and d.piwik_id=rvs.source group by d.id, d.name " + |
|
| 16 |
"UNION ALL select 'downloads', d.id, d.name, sum(rds.downloads) from result_downloads rds, datasource d where rds.id=? and d.piwik_id=rds.source group by d.id, d.name;"; |
|
| 17 |
*/ |
|
| 18 |
|
|
| 19 | 14 |
String query = "SELECT 'views', s.repository_id, CASE WHEN s.source='OpenAIRE' THEN d.name ELSE d.name ||' - '|| s.source END, sum(count), sum(openaire) FROM views_stats s, datasource d WHERE s.repository_id=d.id AND s.result_id=? GROUP BY s.source, s.repository_id, d.name " + |
| 20 | 15 |
"UNION ALL SELECT 'downloads', s.repository_id, CASE WHEN s.source='OpenAIRE' THEN d.name ELSE d.name ||' - '|| s.source END, sum(count), sum(s.openaire) FROM downloads_stats s, datasource d WHERE s.repository_id=d.id AND s.result_id=? GROUP BY s.source, s.repository_id, d.name " + |
| 21 | 16 |
"UNION ALL SELECT 'pageviews', 'OpenAIRE id', 'OpenAIRE', sum(count), '0' FROM pageviews_stats s WHERE result_id=?;"; |
| 22 | 17 |
|
| 23 |
|
|
| 24 | 18 |
List<String> values = new ArrayList<>(); |
| 25 | 19 |
values.add(id); |
| 26 | 20 |
values.add(id); |
| modules/dnet-openaire-usage-stats-api/src/main/java/eu/dnetlib/usagestats/repos/ProjectRepo.java | ||
|---|---|---|
| 12 | 12 |
@Repository |
| 13 | 13 |
public class ProjectRepo extends BaseRepository {
|
| 14 | 14 |
|
| 15 |
//public ViewStats getClicks(String projectId) {
|
|
| 16 | 15 |
public UsageStats getClicks(String projectId) {
|
| 17 |
/* |
|
| 18 |
String query = "SELECT 'views', sum(rvs.views) from result_views rvs, project_results pr where pr.result=rvs.id and pr.id=? and rvs.views!='0' " + |
|
| 19 |
"UNION ALL SELECT 'downloads', sum(rds.downloads) from result_downloads rds, project_results pr where pr.result=rds.id and pr.id=? and rds.downloads!='0'"; |
|
| 20 |
*/ |
|
| 21 |
|
|
| 22 | 16 |
String query = "SELECT 'views', sum(s.count), sum(s.openaire) FROM views_stats s, project_results pr where pr.result=s.result_id and pr.id=? " + |
| 23 | 17 |
"UNION ALL SELECT 'downloads', sum(s.count), sum(s.openaire) FROM downloads_stats s, project_results pr where pr.result=s.result_id and pr.id=? " + |
| 24 | 18 |
"UNION ALL SELECT 'pageviews', sum(s.count), '0' FROM pageviews_stats s, project_results pr where pr.result=s.result_id and pr.id=?;"; |
Also available in: Unified diff
cleanup comments