Revision 46514
Added by Tsampikos Livisianos over 7 years ago
| modules/dnet-openaire-usage-stats-api/src/main/java/eu/dnetlib/usagestats/domain/UsageStats.java | ||
|---|---|---|
| 26 | 26 |
|
| 27 | 27 |
String total_downloads = "0"; |
| 28 | 28 |
String total_views = "0"; |
| 29 |
String pageviews = "0"; |
|
| 29 | 30 |
|
| 30 | 31 |
public UsageStats() {
|
| 31 | 32 |
} |
| ... | ... | |
| 66 | 67 |
this.total_views = total_views; |
| 67 | 68 |
} |
| 68 | 69 |
|
| 70 |
@JsonProperty("pageviews")
|
|
| 71 |
public String getPageViews() {
|
|
| 72 |
return pageviews; |
|
| 73 |
} |
|
| 74 |
|
|
| 75 |
public void setPageViews(String pageviews) {
|
|
| 76 |
this.pageviews = pageviews; |
|
| 77 |
} |
|
| 78 |
|
|
| 69 | 79 |
/* @Id |
| 70 | 80 |
@GeneratedValue(strategy = GenerationType.IDENTITY)*/ |
| 71 | 81 |
//private String id; |
| modules/dnet-openaire-usage-stats-api/src/main/java/eu/dnetlib/usagestats/repos/DatasourceRepo.java | ||
|---|---|---|
| 1 |
/** |
|
| 2 |
* Created by Glykeria Katsari on 2/20/2016. |
|
| 3 |
*/ |
|
| 4 | 1 |
package eu.dnetlib.usagestats.repos; |
| 5 | 2 |
|
| 6 | 3 |
|
| ... | ... | |
| 22 | 19 |
|
| 23 | 20 |
|
| 24 | 21 |
public UsageStats getClicks(String id) {
|
| 22 |
/* |
|
| 25 | 23 |
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 " + |
| 26 | 24 |
//"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 " + |
| 27 | 25 |
"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 " + |
| 28 | 26 |
"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"; |
| 29 |
/* |
|
| 30 |
query = "select 'views', d.id, d.name, sum(rvs.sum) from repo_view_stats_monthly_clean rvs, datasource_results dr, datasource d where rvs.id=dr.result and d.piwik_id=rvs.source and dr.id=d.id and dr.id=? group by d.id, d.name " + |
|
| 31 |
"UNION select 'views', d.id, d.name, sum(rs.sum) from result_stats_monthly_clean rs, datasource_results dr, datasource d where rs.id=dr.result and d.piwik_id='5' and dr.id=? group by d.id, d.name " + |
|
| 32 |
"UNION select 'downloads', d.id, d.name, sum(rds.sum) from repo_download_stats_monthly_clean rds, datasource_results dr, datasource d where rds.id=dr.result and d.id=dr.id and d.piwik_id=rds.source and dr.id=? group by d.id, d.name"; |
|
| 33 | 27 |
*/ |
| 34 | 28 |
|
| 29 |
/* |
|
| 30 |
String query = "SELECT 'views', sum(s.count) FROM views_stats s, result_datasources rd where rd.id=s.result_id and rd.datasource=? " + |
|
| 31 |
"UNION ALL SELECT 'downloads', sum(s.count) FROM downloads_stats s, result_datasources rd where rd.id=s.result_id and rd.datasource=? " + |
|
| 32 |
"UNION ALL SELECT 'pageviews', sum(s.count) FROM pageviews_stats s, result_datasources rd where rd.id=s.result_id and rd.datasource=? "; |
|
| 33 |
*/ |
|
| 34 |
|
|
| 35 |
String query = "SELECT 'views', sum(s.count) FROM views_stats s where s.repository_id=? " + |
|
| 36 |
"UNION ALL SELECT 'downloads', sum(s.count) FROM downloads_stats s where s.repository_id=? " + |
|
| 37 |
"UNION ALL SELECT 'pageviews', (sum(s.count)/2)::int FROM pageviews_stats s, result_datasources rd where rd.id=s.result_id and rd.datasource=? "; |
|
| 38 |
|
|
| 35 | 39 |
List<String> values = new ArrayList<>(); |
| 36 | 40 |
values.add(id); |
| 37 | 41 |
values.add(id); |
| 38 | 42 |
values.add(id); |
| 39 | 43 |
|
| 40 |
return executePreparedQuery(query, values, "datasource");
|
|
| 44 |
return executeUsageStats(query, values, "datasource");
|
|
| 41 | 45 |
} |
| 42 |
|
|
| 43 |
|
|
| 44 |
/* public List<UsageStats> getMostPopular(String limit) {
|
|
| 45 |
String query = "select d.id, d.name , sum (ds.numberofviews) from datasource d, usagestats.datasourcesstats ds where " + |
|
| 46 |
"ds.datasourceid=d.id group by d.id, d.name order by sum (ds.numberofviews) desc limit " + limit + " ;"; |
|
| 47 |
|
|
| 48 |
return executeQuery(query); |
|
| 49 |
} |
|
| 50 |
|
|
| 51 |
public List<UsageStats> getMostPopularPubs(String datasourceId) {
|
|
| 52 |
|
|
| 53 |
String query = "select r.id, r.title , sum(rs.numberofviews) from result r, usagestats.resultsstats rs, result_datasources rd where r.id=rd.id and rd.datasource=? and rs.resultid=r.id group by r.id, r. title order by sum(rs.numberofviews) desc limit 10;"; |
|
| 54 |
|
|
| 55 |
|
|
| 56 |
List<String> values = new ArrayList<String>(); |
|
| 57 |
values.add(datasourceId); |
|
| 58 |
return executePreparedQuery(query, values); |
|
| 59 |
|
|
| 60 |
} |
|
| 61 |
|
|
| 62 |
|
|
| 63 |
public List<UsageStats> getMostPopularProjects(String datasourceId) {
|
|
| 64 |
|
|
| 65 |
String query = "select p.id, p.title, sum(ps.numberofviews) from project p, result_datasources rd, project_results pr , usagestats.projectsstats ps " + |
|
| 66 |
" where rd.datasource=? and p.id=pr.id and pr.result=rd.id and ps.projectid=p.id " + |
|
| 67 |
" group by p.id, p.title order by sum (ps.numberofviews) desc limit 10;"; |
|
| 68 |
List<String> values = new ArrayList<String>(); |
|
| 69 |
values.add(datasourceId); |
|
| 70 |
return executePreparedQuery(query, values); |
|
| 71 |
|
|
| 72 |
} |
|
| 73 |
|
|
| 74 |
public List<UsageStats> getPopularityOverTime(String datasourceId) {
|
|
| 75 |
|
|
| 76 |
String query = "select ds.datasourceid, ds.timestamp_month , ds.numberofviews from usagestats.datasourcesstats ds " + |
|
| 77 |
"where ds.datasourceid=? order by to_date(ds.timestamp_month,'MM/YYYY') ;"; |
|
| 78 |
List<String> values = new ArrayList<String>(); |
|
| 79 |
values.add(datasourceId); |
|
| 80 |
return executePreparedQuery(query, values); |
|
| 81 |
|
|
| 82 |
}*/ |
|
| 83 |
|
|
| 84 |
|
|
| 85 | 46 |
} |
| 86 | 47 |
|
| 87 | 48 |
|
| modules/dnet-openaire-usage-stats-api/src/main/java/eu/dnetlib/usagestats/repos/ResultRepo.java | ||
|---|---|---|
| 20 | 20 |
|
| 21 | 21 |
//public UsageStats getClicks(String id) {
|
| 22 | 22 |
public UsageStats getClicks(String id) {
|
| 23 |
/* |
|
| 23 | 24 |
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 " + |
| 24 | 25 |
"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;"; |
| 25 |
/* |
|
| 26 |
query = "select 'views', d.id, d.name, sum(rvs.sum) from repo_view_stats_monthly_clean rvs, datasource d where rvs.id=? and d.piwik_id=rvs.source group by d.id, d.name " + |
|
| 27 |
"UNION select 'views', d.id, d.name, sum(rs.sum) FROM result_stats_monthly_clean rs, datasource d WHERE rs.id=? and d.piwik_id='5' group by d.id, d.name " + |
|
| 28 |
"UNION select 'downloads', d.id, d.name, sum(rds.sum) from repo_download_stats_monthly_clean rds, datasource d where rds.id=? and d.piwik_id=rds.source group by d.id, d.name"; |
|
| 29 | 26 |
*/ |
| 30 | 27 |
|
| 28 |
|
|
| 29 |
String query = "SELECT 'views', s.repository_id, CASE WHEN s.source='OpenAIRE' THEN d.name ELSE d.name ||' - '|| s.source END, sum(count) 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 " + |
|
| 30 |
"UNION ALL SELECT 'downloads', s.repository_id, CASE WHEN s.source='OpenAIRE' THEN d.name ELSE d.name ||' - '|| s.source END, sum(count) 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 " + |
|
| 31 |
"UNION ALL SELECT 'pageviews', 'OpenAIRE id', 'OpenAIRE', sum(count) FROM pageviews_stats s WHERE result_id=?;"; |
|
| 31 | 32 |
|
| 33 |
|
|
| 32 | 34 |
List<String> values = new ArrayList<>(); |
| 33 | 35 |
values.add(id); |
| 34 | 36 |
values.add(id); |
| 35 |
//values.add(id);
|
|
| 37 |
values.add(id); |
|
| 36 | 38 |
|
| 37 |
return executePreparedQuery(query, values, "result");
|
|
| 39 |
return executeUsageStats(query, values, "result");
|
|
| 38 | 40 |
} |
| 39 |
|
|
| 40 |
/* public List<UsageStats> getMostPopular(String limit) {
|
|
| 41 |
|
|
| 42 |
String query = "select r.id, r.title , sum (rs.numberofviews) from result r, usagestats.resultsstats rs where " + |
|
| 43 |
"rs.resultid=r.id group by r.id, r. title order by sum (rs.numberofviews) desc limit " + limit + " ;"; |
|
| 44 |
|
|
| 45 |
return executeQuery(query); |
|
| 46 |
|
|
| 47 |
} |
|
| 48 |
|
|
| 49 |
public List<UsageStats> getPopularityOverTime(String resultId) {
|
|
| 50 |
|
|
| 51 |
String query = "select rs.resultid, rs.timestamp_month , rs.numberofviews from usagestats.resultsstats rs " + |
|
| 52 |
" where rs.resultid=? order by to_date(rs.timestamp_month,'MM/YYYY') ;"; |
|
| 53 |
List<String> values = new ArrayList<String>(); |
|
| 54 |
values.add(resultId); |
|
| 55 |
return executePreparedQuery(query, values); |
|
| 56 |
|
|
| 57 |
}*/ |
|
| 58 |
|
|
| 59 | 41 |
} |
| 60 | 42 |
|
| 61 | 43 |
|
| modules/dnet-openaire-usage-stats-api/src/main/java/eu/dnetlib/usagestats/repos/BaseRepository.java | ||
|---|---|---|
| 81 | 81 |
}*/ |
| 82 | 82 |
|
| 83 | 83 |
|
| 84 |
protected UsageStats executePreparedQuery(String query, List<String> values, String type) {
|
|
| 84 |
protected UsageStats executeUsageStats(String query, List<String> values, String type) {
|
|
| 85 | 85 |
UsageStats usageStats = new UsageStats(); |
| 86 | 86 |
int total_views = 0; |
| 87 | 87 |
int total_downloads = 0; |
| 88 |
int page_views = 0; |
|
| 88 | 89 |
|
| 89 | 90 |
try {
|
| 90 | 91 |
//DataSource usageStatsDB = dataSourceBean.getDataSource(); |
| 91 | 92 |
Connection connection = usageStatsDB.getConnection(); |
| 92 | 93 |
PreparedStatement st = connection.prepareStatement(query); |
| 93 | 94 |
int i = 1; |
| 94 |
|
|
| 95 | 95 |
for (String s : values) {
|
| 96 | 96 |
st.setString(i, s); |
| 97 | 97 |
i++; |
| 98 | 98 |
} |
| 99 | 99 |
|
| 100 |
|
|
| 101 | 100 |
ResultSet rs = st.executeQuery(); |
| 102 | 101 |
|
| 103 |
if(type.equals("result") || type.equals("datasource")){
|
|
| 102 |
if(type.equals("result")){
|
|
| 104 | 103 |
while (rs.next()) {
|
| 105 | 104 |
//if() {
|
| 106 |
if (rs.getString(1).equals("views") && rs.getString(4) != null && !rs.getString(4).equals("") && !rs.getString(4).equals("null")) {
|
|
| 107 |
//usageStats.addViews(rs.getString(2) + ":" + rs.getString(3)); |
|
| 108 |
//usageStats.addViews(new RepoStats("",rs.getString(2),rs.getString(3)));
|
|
| 109 |
usageStats.addViews(new RepoStats(rs.getString(3),rs.getString(2),rs.getString(4))); |
|
| 110 |
total_views += Integer.parseInt(rs.getString(4)); |
|
| 111 |
} else if (rs.getString(1).equals("downloads") && rs.getString(4) != null && !rs.getString(4).equals("") && !rs.getString(4).equals("null")) {
|
|
| 112 |
//usageStats.addDownloads(rs.getString(2) + ":" + rs.getString(3)); |
|
| 113 |
//usageStats.addDownloads(new RepoStats("",rs.getString(2),rs.getString(3)));
|
|
| 114 |
usageStats.addDownloads(new RepoStats(rs.getString(3),rs.getString(2),rs.getString(4))); |
|
| 115 |
total_downloads += Integer.parseInt(rs.getString(4)); |
|
| 116 |
} |
|
| 117 |
//} |
|
| 118 |
//UsageStats stats = new UsageStats(); |
|
| 119 |
//stats.setId(rs.getString(1)); |
|
| 120 |
//stats.setName(rs.getString(2)); |
|
| 121 |
//stats.setOid(rs.getString(3)); |
|
| 122 |
//stats.setSource(rs.getString(1)); |
|
| 123 |
//stats.setValue(rs.getInt(2)); |
|
| 124 |
//statsList.add(stats); |
|
| 105 |
if (rs.getString(1).equals("views") && rs.getString(4) != null && !rs.getString(4).equals("") && !rs.getString(4).equals("null")) {
|
|
| 106 |
//usageStats.addViews(rs.getString(2) + ":" + rs.getString(3)); |
|
| 107 |
//usageStats.addViews(new RepoStats("",rs.getString(2),rs.getString(3)));
|
|
| 108 |
usageStats.addViews(new RepoStats(rs.getString(3),rs.getString(2),rs.getString(4))); |
|
| 109 |
total_views += Integer.parseInt(rs.getString(4)); |
|
| 110 |
} else if (rs.getString(1).equals("downloads") && rs.getString(4) != null && !rs.getString(4).equals("") && !rs.getString(4).equals("null")) {
|
|
| 111 |
//usageStats.addDownloads(rs.getString(2) + ":" + rs.getString(3)); |
|
| 112 |
//usageStats.addDownloads(new RepoStats("",rs.getString(2),rs.getString(3)));
|
|
| 113 |
usageStats.addDownloads(new RepoStats(rs.getString(3),rs.getString(2),rs.getString(4))); |
|
| 114 |
total_downloads += Integer.parseInt(rs.getString(4)); |
|
| 115 |
} else if (rs.getString(1).equals("pageviews") && rs.getString(4) != null && !rs.getString(4).equals("") && !rs.getString(4).equals("null")) {
|
|
| 116 |
page_views = Integer.parseInt(rs.getString(4)); |
|
| 117 |
} |
|
| 125 | 118 |
} |
| 126 |
|
|
| 127 |
//if (total_views!= 0){
|
|
| 128 |
//usageStats.addViews(new RepoStats("Total",Integer.toString(total_views)));
|
|
| 129 | 119 |
usageStats.setTotal_views(Integer.toString(total_views)); |
| 130 |
//} |
|
| 131 |
//if (total_downloads!= 0){
|
|
| 132 |
//usageStats.addDownloads(new RepoStats("Total",Integer.toString(total_downloads)));
|
|
| 133 | 120 |
usageStats.setTotal_downloads(Integer.toString(total_downloads)); |
| 134 |
//}
|
|
| 121 |
usageStats.setPageViews(Integer.toString(page_views));
|
|
| 135 | 122 |
} |
| 136 |
else if(type.equals("project")){
|
|
| 123 |
else if(type.equals("project") || type.equals("datasource")){
|
|
| 137 | 124 |
while(rs.next()){
|
| 138 | 125 |
if (rs.getString(1).equals("views") && rs.getString(2) != null && !rs.getString(2).equals("") && !rs.getString(2).equals("null")) {
|
| 139 | 126 |
total_views += Integer.parseInt(rs.getString(2)); |
| 140 | 127 |
} else if (rs.getString(1).equals("downloads") && rs.getString(2) != null && !rs.getString(2).equals("") && !rs.getString(2).equals("null")) {
|
| 141 | 128 |
total_downloads += Integer.parseInt(rs.getString(2)); |
| 129 |
} else if (rs.getString(1).equals("pageviews") && rs.getString(2) != null && !rs.getString(2).equals("") && !rs.getString(2).equals("null")) {
|
|
| 130 |
page_views = Integer.parseInt(rs.getString(2)); |
|
| 142 | 131 |
} |
| 143 | 132 |
} |
| 144 | 133 |
usageStats.setTotal_views(Integer.toString(total_views)); |
| 145 | 134 |
usageStats.setTotal_downloads(Integer.toString(total_downloads)); |
| 135 |
usageStats.setPageViews(Integer.toString(page_views)); |
|
| 146 | 136 |
} |
| 147 | 137 |
|
| 148 | 138 |
rs.close(); |
| modules/dnet-openaire-usage-stats-api/src/main/java/eu/dnetlib/usagestats/repos/OrganizationRepo.java | ||
|---|---|---|
| 1 |
/** |
|
| 2 |
* Created by Glykeria Katsari on 2/20/2016. |
|
| 3 |
*/ |
|
| 4 | 1 |
package eu.dnetlib.usagestats.repos; |
| 5 | 2 |
|
| 6 | 3 |
|
| ... | ... | |
| 34 | 31 |
return executePreparedQuery2(query, values); |
| 35 | 32 |
|
| 36 | 33 |
} |
| 37 |
|
|
| 38 |
|
|
| 39 |
/* public List<UsageStats> getMostPopular(String limit) {
|
|
| 40 |
String query = "select o.id, o.name , sum (os.numberofviews) from organization o, usagestats.organizationsstats os where " + |
|
| 41 |
"os.organizationid=o.id group by o.id, o.name order by sum(os.numberofviews) desc limit " + limit + " ;"; |
|
| 42 |
|
|
| 43 |
return executeQuery(query); |
|
| 44 |
} |
|
| 45 |
|
|
| 46 |
|
|
| 47 |
public List<UsageStats> getMostPopularProjects(String organizationId) {
|
|
| 48 |
|
|
| 49 |
String query = "select p.id, p.title, sum(ps.numberofviews) from project p, project_organizations por, usagestats.projectsstats ps " + |
|
| 50 |
" where por.organization=? and p.id=por.id and ps.projectid=p.id " + |
|
| 51 |
" group by p.id, p.title order by sum (ps.numberofviews) desc limit 10;"; |
|
| 52 |
List<String> values = new ArrayList<String>(); |
|
| 53 |
|
|
| 54 |
values.add(organizationId); |
|
| 55 |
return executePreparedQuery(query, values); |
|
| 56 |
} |
|
| 57 |
|
|
| 58 |
public List<UsageStats> getPopularityOverTime(String organizationId) {
|
|
| 59 |
|
|
| 60 |
String query = "select os.organizationid, os.timestamp_month , os.numberofviews from usagestats.organizationsstats os " + |
|
| 61 |
" where os.organizationid=? order by to_date(os.timestamp_month,'MM/YYYY') ;"; |
|
| 62 |
List<String> values = new ArrayList<String>(); |
|
| 63 |
values.add(organizationId); |
|
| 64 |
return executePreparedQuery(query, values); |
|
| 65 |
|
|
| 66 |
}*/ |
|
| 67 |
|
|
| 68 |
|
|
| 69 | 34 |
} |
| 70 | 35 |
|
| 71 | 36 |
|
| modules/dnet-openaire-usage-stats-api/src/main/java/eu/dnetlib/usagestats/repos/ProjectRepo.java | ||
|---|---|---|
| 1 |
/** |
|
| 2 |
* Created by Glykeria Katsari on 2/20/2016. |
|
| 3 |
*/ |
|
| 4 | 1 |
package eu.dnetlib.usagestats.repos; |
| 5 | 2 |
|
| 6 | 3 |
|
| ... | ... | |
| 23 | 20 |
|
| 24 | 21 |
//public ViewStats getClicks(String projectId) {
|
| 25 | 22 |
public UsageStats getClicks(String projectId) {
|
| 23 |
/* |
|
| 26 | 24 |
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' " + |
| 27 | 25 |
"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'"; |
| 28 |
/* |
|
| 29 |
query = "select 'views', sum(rvs.sum) from repo_view_stats_monthly_clean rvs, project_results pr where pr.result=rvs.id and pr.id=?" + |
|
| 30 |
"UNION select 'views', sum(rs.sum) from result_stats_monthly_clean rs, project_results pr where pr.result=rs.id and pr.id=?" + |
|
| 31 |
"UNION select 'downloads', sum(rds.sum) from repo_download_stats_monthly_clean rds, project_results pr where pr.result=rds.id and pr.id=?"; |
|
| 32 | 26 |
*/ |
| 33 | 27 |
|
| 28 |
String query = "SELECT 'views', sum(s.count) FROM views_stats s, project_results pr where pr.result=s.result_id and pr.id=? " + |
|
| 29 |
"UNION ALL SELECT 'downloads', sum(s.count) FROM downloads_stats s, project_results pr where pr.result=s.result_id and pr.id=? " + |
|
| 30 |
"UNION ALL SELECT 'pageviews', sum(s.count) FROM pageviews_stats s, project_results pr where pr.result=s.result_id and pr.id=?;"; |
|
| 31 |
|
|
| 34 | 32 |
List<String> values = new ArrayList<>(); |
| 35 | 33 |
values.add(projectId); |
| 36 | 34 |
values.add(projectId); |
| 37 |
//values.add(projectId); |
|
| 38 |
|
|
| 39 |
return executePreparedQuery(query, values, "project"); |
|
| 40 |
} |
|
| 41 |
|
|
| 42 |
|
|
| 43 |
/* public List<UsageStats> getMostPopular(String limit) {
|
|
| 44 |
String query = "select p.id, p.title , sum (ps.numberofviews) from project p, usagestats.projectsstats ps where " + |
|
| 45 |
"ps.projectid=p.id group by p.id, p.title order by sum(ps.numberofviews) desc limit " + limit + " ;"; |
|
| 46 |
|
|
| 47 |
return executeQuery(query); |
|
| 48 |
} |
|
| 49 |
|
|
| 50 |
|
|
| 51 |
public List<UsageStats> getMostPopularPubs(String projectId) {
|
|
| 52 |
|
|
| 53 |
String query = "select r.id, r.title , sum(rs.numberofviews) from result r, usagestats.resultsstats rs, " + |
|
| 54 |
"project_results pr where r.id=pr.result and pr.id=? and rs.resultid=r.id group by r.id, r. title order by sum(rs.numberofviews) desc limit 10;"; |
|
| 55 |
|
|
| 56 |
|
|
| 57 |
List<String> values = new ArrayList<String>(); |
|
| 58 | 35 |
values.add(projectId); |
| 59 |
return executePreparedQuery(query, values); |
|
| 60 | 36 |
|
| 37 |
return executeUsageStats(query, values, "project"); |
|
| 61 | 38 |
} |
| 62 |
|
|
| 63 |
|
|
| 64 |
public List<UsageStats> getPopularityOverTime(String projectId) {
|
|
| 65 |
|
|
| 66 |
String query = "select ps.projectid, ps.timestamp_month , ps.numberofviews from usagestats.projectsstats ps " + |
|
| 67 |
" where ps.projectid=? order by to_date(ps.timestamp_month,'MM/YYYY');"; |
|
| 68 |
List<String> values = new ArrayList<String>(); |
|
| 69 |
values.add(projectId); |
|
| 70 |
return executePreparedQuery(query, values); |
|
| 71 |
|
|
| 72 |
}*/ |
|
| 73 |
|
|
| 74 |
|
|
| 75 | 39 |
} |
| 76 | 40 |
|
| 77 | 41 |
|
Also available in: Unified diff
update usage stats queries