/ - Diff - D-Net - D-Net project tracking tool

« Previous | Next »

Revision 45522

Added by Tsampikos Livisianos over 7 years ago

rev

     import eu.dnetlib.usagestats.repos.ProjectRepo;
     import eu.dnetlib.usagestats.repos.ResultRepo;
     import org.springframework.beans.factory.annotation.Autowired;
     import org.springframework.stereotype.Controller;
     import org.springframework.transaction.annotation.Transactional;
     import org.springframework.web.bind.annotation.CrossOrigin;
     import org.springframework.web.bind.annotation.PathVariable;
-...
         @Autowired
         UsageReport report;
         @RequestMapping(value = "/", method = RequestMethod.GET)
         public String defaultMap(){
             return "<body><center>" +
                     "<h2>OpenAIRE SUSHI lite Client</h2>" +
                     "<h3> Supported Reports</h3>" +
                     "<ul>" +
                     "<li><a href=\"/stats/AR1/\">AR1 Report</a></li>" +
     	            "<li><a href=\"/stats/IR1/\">IR1 Report</a></li>" +
                     "<li><a href=\"/stats/RR1/\">RR1 Report</a></li>" +
                     "</ul>" +
                     "</center></body>";
+        }
         @RequestMapping(value = "/AR1", method = RequestMethod.GET)
         public String ar1Map(){
             return "<body>" +
                     "<h2>OpenAIRE SUSHI lite Client</h2>" +
     	            "<form action=\"/stats/GetReport/\" method=\"get\">" +
                     "<h3>Report Request</h3>" +
                     "Report Name: " +
                     "<select name='Report'>" +
                     "<option value='AR1'>AR1</option>" +
                     "</select>" +
                     "<br />" +
                     "Release:" +
                     "<select name='Release'>" +
                     "<option value=\"4\">4</option>" +
                     "</select>" +
                     "<br />" +
                     "<input type=\"text\" name=\"RequestorID\" value=\"anonymous\">" +
                     "<h3>Report Filters</h3>" +
                     "<h4>DateRange</h4>" +
                     "<p>Valid date formats: yyyy-mm-dd or yyyy-mm<br />" +
                     "Defaults to the last available month if no dates are provided" +
                     "</p>" +
                     "BeginDate:<input type=\"text\" name=\"BeginDate\" value=\"2016-03\" style=\"width: 80px; padding:2px\"/>" +
                     "<br />" +
                     "EndDate:<input type=\"text\" name=\"EndDate\" value=\"2016-06\"  style=\"width: 80px; padding:2px\"/>" +
                     "<h4>Filters</h4>" +
                     "<p>You must provide either a Repository Identifier or an Item Identifier</p>" +
                     //"<p>Repository Identifier format: namespace:value<br />" +
                     //"namespace must be either be openaire or repoid<br />" +
                     //"value is the integer value assigned by CORE or IRUS-UK" +
                     //"</p>" +
                     "RepositoryIdentifier:" +
                     "<input type=\"text\" name=\"RepositoryIdentifier\">" +
                     "<br />" +
                     "ItemIdentifier:" +
                     "<input type=\"text\" name=\"ItemIdentifier\" value=\"openaire:od_________7::fb90de6f20d79783d05749d8f60417d5\">" +
                     //"<p>You may also limit the results to include or exclude items that have a DOI<br />" +
                     //"Valid values: Yes or No</p>" +
                     //"hasDOI:" +
                     //"<select name='hasDOI'>" +
                     //"<option value=\"\">Not applicable</option>" +
                     //"<option value=\"Yes\">Yes</option>" +
                     //"<option value=\"No\">No</option>" +
                     //"</select>" +
                     "<h3>Report Attributes</h3>" +
                     "<p>Valid Granularity values: Monthly or Totals</p>" +
                     "Granularity:" +
                     "<select name='Granularity'>" +
                     "<option value=\"Monthly\">Monthly</option>" +
                     "<option value=\"Totals\">Totals</option>" +
                     "</select>" +
                     //"<p>The format in which the response is to be returned defaults to JSON unless a callback function name is entered in which case JSONP is returned.</p>" +
                     //"<input type=\"text\" name=\"Callback\">" +
                     "<p>The Pretty attribute is just for humans playing with the API and looking at results in a browser.</p>" +
                     "<p><input type=\"checkbox\" name=\"Pretty\" value=\"Pretty\" checked=\"checked\">Pretty print json(p) for humans</p>" +
                     "<input type=\"submit\" class=\"button\" value=\"GetReport\"/>" +
                     "</form>" +
                     "</body>";
+        }
         @RequestMapping(value = "/IR1", method = RequestMethod.GET)
         public String ir1Map(){
             return "<body>" +
                     "<h2>OpenAIRE SUSHI lite Client</h2>" +
                     "<form action=\"/stats/GetReport/\" method=\"get\">" +
                     "<h3>Report Request</h3>" +
                     "Report Name: " +
                     "<select name='Report'>" +
                     "<option value='IR1'>IR1</option>" +
                     "</select>" +
                     "<br />" +
                     "Release:" +
                     "<select name='Release'>" +
                     "<option value=\"4\">4</option>" +
                     "</select>" +
                     "<br />" +
                     "<input type=\"text\" name=\"RequestorID\" value=\"anonymous\">" +
                     "<h3>Report Filters</h3>" +
                     "<h4>DateRange</h4>" +
                     "<p>Valid date formats: yyyy-mm-dd or yyyy-mm<br />" +
                     "Defaults to the last available month if no dates are provided" +
                     "</p>" +
                     "BeginDate:<input type=\"text\" name=\"BeginDate\" value=\"2016-03\" style=\"width: 80px; padding:2px\"/>" +
                     "<br />" +
                     "EndDate:<input type=\"text\" name=\"EndDate\" value=\"2016-06\"  style=\"width: 80px; padding:2px\"/>" +
                     "<h4>Filters</h4>" +
                     "<p>You must provide either a Repository Identifier or an Item Identifier</p>" +
                     //"<p>Repository Identifier format: namespace:value<br />" +
                     //"namespace must be either be openaire or repoid<br />" +
                     //"value is the integer value assigned by CORE or IRUS-UK" +
                     //"</p>" +
                     "RepositoryIdentifier:" +
                     "<input type=\"text\" name=\"RepositoryIdentifier\" value=\"repoid:20\">" +
                     "<br />" +
                     "ItemIdentifier:" +
                     "<input type=\"text\" name=\"ItemIdentifier\">" +
                     "<p>Optional filter to only show results for a single item type, e.g. article, book, etc.</p>" +
                     "ItemDataType:" +
                     "<select name='ItemDataType'>" +
                     "<option value=\"\">--- Select ItemDataType ---</option>" +
                     "<option value='Annotation'>Annotation</option>" +
                     "<option value='Article'>Article</option>" +
                     "<option value='Bachelor thesis'>Bachelor thesis</option>" +
                     "<option value='Book'>Book</option>" +
                     "<option value='clinical trial'>clinical trial</option>" +
                     "<option value='Collection'>Collection</option>" +
                     "<option value='Conference object'>Conference object</option>" +
                     "<option value='Contribution for newspaper or weekly magazine'>Contribution for newspaper or weekly magazine</option>" +
                     "<option value='Dataset'>Dataset</option>" +
                     "<option value='Doctoral thesis'>Doctoral thesis</option>" +
                     "<option value='Event'>Event</option>" +
                     "<option value='External research report'>External research report</option>" +
                     "<option value='Film'>Film</option>" +
                     "<option value='Image'>Image</option>" +
                     "<option value='InteractiveResource'>InteractiveResource</option>" +
                     "<option value='Internal report'>Internal report</option>" +
                     "<option value='Lecture'>Lecture</option>" +
                     "<option value='Master thesis'>Master thesis</option>" +
                     "<option value='Newsletter'>Newsletter</option>" +
                     "<option value='Other'>Other</option>" +
                     "<option value='Part of book or chapter of book'>Part of book or chapter of book</option>" +
                     "<option value='Patent'>Patent</option>" +
                     "<option value='PhysicalObject'>PhysicalObject</option>" +
                     "<option value='Preprint'>Preprint</option>" +
                     "<option value='Report'>Report</option>" +
                     "<option value='Research'>Research</option>" +
                     "<option value='Review'>Review</option>" +
                     "<option value='Software'>Software</option>" +
                     "<option value='Sound'>Sound</option>" +
                     "<option value='Unknown'>Unknown</option>" +
                     "</select>" +
                     //"<p>You may also limit the results to include or exclude items that have a DOI<br />" +
                     //"Valid values: Yes or No</p>" +
                     //"hasDOI:" +
                     //"<select name='hasDOI'>" +
                     //"<option value=\"\">Not applicable</option>" +
                     //"<option value=\"Yes\">Yes</option>" +
                     //"<option value=\"No\">No</option>" +
                     //"</select>" +
                     "<h3>Report Attributes</h3>" +
                     "<p>Valid Granularity values: Monthly or Totals</p>" +
                     "Granularity:" +
                     "<select name='Granularity'>" +
                     "<option value=\"Monthly\">Monthly</option>" +
                     "<option value=\"Totals\">Totals</option>" +
                     "</select>" +
                     //"<p>The format in which the response is to be returned defaults to JSON unless a callback function name is entered in which case JSONP is returned.</p>" +
                     //"<input type=\"text\" name=\"Callback\">" +
                     "<p>The Pretty attribute is just for humans playing with the API and looking at results in a browser.</p>" +
                     "<p><input type=\"checkbox\" name=\"Pretty\" value=\"Pretty\" checked=\"checked\">Pretty print json(p) for humans</p>" +
                     "<input type=\"submit\" class=\"button\" value=\"GetReport\"/>" +
                     "</form>" +
                     "</body>";
+        }
         @RequestMapping(value = "/RR1", method = RequestMethod.GET)
         public String rr1Map(){
             return "<body>" +
                     "<h2>OpenAIRE SUSHI lite Client</h2>" +
                     "<form action=\"/stats/GetReport/\" method=\"get\">" +
                     "<h3>Report Request</h3>" +
                     "Report Name: " +
                     "<select name='Report'>" +
                     "<option value='RR1'>RR1</option>" +
                     "</select>" +
                     "<br />" +
                     "Release:" +
                     "<select name='Release'>" +
                     "<option value=\"4\">4</option>" +
                     "</select>" +
                     "<br />" +
                     "<input type=\"text\" name=\"RequestorID\" value=\"anonymous\">" +
                     "<h3>Report Filters</h3>" +
                     "<h4>DateRange</h4>" +
                     "<p>Valid date formats: yyyy-mm-dd or yyyy-mm<br />" +
                     "Defaults to the last available month if no dates are provided" +
                     "</p>" +
                     "BeginDate:<input type=\"text\" name=\"BeginDate\" value=\"2016-03\" style=\"width: 80px; padding:2px\"/>" +
                     "<br />" +
                     "EndDate:<input type=\"text\" name=\"EndDate\" value=\"2016-06\"  style=\"width: 80px; padding:2px\"/>" +
                     "<h4>Filters</h4>" +
                     "<p>You must provide either a Repository Identifier or an Item Identifier</p>" +
                     //"<p>Repository Identifier format: namespace:value<br />" +
                     //"namespace must be either be openaire or repoid<br />" +
                     //"value is the integer value assigned by CORE or IRUS-UK" +
                     //"</p>" +
                     "RepositoryIdentifier:" +
                     "<input type=\"text\" name=\"RepositoryIdentifier\">" +
                     "<br />" +
                     "<p>Optional filter to only show results for a single item type, e.g. article, book, etc.</p>" +
                     "ItemDataType:" +
                     "<select name='ItemDataType'>" +
                     "<option value=\"\">--- Select ItemDataType ---</option>" +
                     "<option value='Annotation'>Annotation</option>" +
                     "<option value='Article'>Article</option>" +
                     "<option value='Bachelor thesis'>Bachelor thesis</option>" +
                     "<option value='Book'>Book</option>" +
                     "<option value='clinical trial'>clinical trial</option>" +
                     "<option value='Collection'>Collection</option>" +
                     "<option value='Conference object'>Conference object</option>" +
                     "<option value='Contribution for newspaper or weekly magazine'>Contribution for newspaper or weekly magazine</option>" +
                     "<option value='Dataset'>Dataset</option>" +
                     "<option value='Doctoral thesis'>Doctoral thesis</option>" +
                     "<option value='Event'>Event</option>" +
                     "<option value='External research report'>External research report</option>" +
                     "<option value='Film'>Film</option>" +
                     "<option value='Image'>Image</option>" +
                     "<option value='InteractiveResource'>InteractiveResource</option>" +
                     "<option value='Internal report'>Internal report</option>" +
                     "<option value='Lecture'>Lecture</option>" +
                     "<option value='Master thesis'>Master thesis</option>" +
                     "<option value='Newsletter'>Newsletter</option>" +
                     "<option value='Other'>Other</option>" +
                     "<option value='Part of book or chapter of book'>Part of book or chapter of book</option>" +
                     "<option value='Patent'>Patent</option>" +
                     "<option value='PhysicalObject'>PhysicalObject</option>" +
                     "<option value='Preprint'>Preprint</option>" +
                     "<option value='Report'>Report</option>" +
                     "<option value='Research'>Research</option>" +
                     "<option value='Review'>Review</option>" +
                     "<option value='Software'>Software</option>" +
                     "<option value='Sound'>Sound</option>" +
                     "<option value='Unknown'>Unknown</option>" +
                     "</select>" +
                     //"<p>You may also limit the results to include or exclude items that have a DOI<br />" +
                     //"Valid values: Yes or No</p>" +
                     //"hasDOI:" +
                     //"<select name='hasDOI'>" +
                     //"<option value=\"\">Not applicable</option>" +
                     //"<option value=\"Yes\">Yes</option>" +
                     //"<option value=\"No\">No</option>" +
                     //"</select>" +
                     "<h3>Report Attributes</h3>" +
                     "<p>Valid Granularity values: Monthly or Totals</p>" +
                     "Granularity:" +
                     "<select name='Granularity'>" +
                     "<option value=\"Monthly\">Monthly</option>" +
                     "<option value=\"Totals\">Totals</option>" +
                     "</select>" +
                     //"<p>The format in which the response is to be returned defaults to JSON unless a callback function name is entered in which case JSONP is returned.</p>" +
                     //"<input type=\"text\" name=\"Callback\">" +
                     "<p>The Pretty attribute is just for humans playing with the API and looking at results in a browser.</p>" +
                     "<p><input type=\"checkbox\" name=\"Pretty\" value=\"Pretty\" checked=\"checked\">Pretty print json(p) for humans</p>" +
                     "<input type=\"submit\" class=\"button\" value=\"GetReport\"/>" +
                     "</form>" +
                     "</body>";
+        }
         //Counter Compliant Report kai kala
         @RequestMapping(value = "/GetReport/", method = RequestMethod.GET)
         public ReportResponseWrapper getReport(@RequestParam(value = "Report", defaultValue = "") String reportP, @RequestParam(value = "Release", defaultValue = "4") String release, @RequestParam(value = "RequestorID", defaultValue = "anonymous") String requestorId,
-...
         /*@RequestMapping("/home/{error}")*/
         @CrossOrigin(methods = RequestMethod.GET, origins = "*")
         @RequestMapping(value = "/datasources/{datasourceId}/clicks")
         public ViewStats getDatasourceClicks(@PathVariable(value = "datasourceId") String datasourceId) throws Exception {
         public UsageStats getDatasourceClicks(@PathVariable(value = "datasourceId") String datasourceId) throws Exception {
             return datasourceRepo.getClicks(datasourceId);
+        }
-...
         @RequestMapping(value = "/projects/{projectId}/clicks")
         @CrossOrigin(methods = RequestMethod.GET, origins = "*")
         public ViewStats getProjectClicks(@PathVariable(value = "projectId") String projectId) throws Exception {
         public UsageStats getProjectClicks(@PathVariable(value = "projectId") String projectId) throws Exception {
             return projectRepo.getClicks(projectId);
+        }

         private Logger log = Logger.getLogger(this.getClass());
         public ViewStats getClicks(String id) {
         public UsageStats getClicks(String id) {
             //String query = "select d.id, d.name , sum (ds.numberofviews) from datasource d, usagestats.datasourcesstats ds " +
             //        "where ds.datasourceid=d.id and d.id=? group by d.id, d.name;";
             //String query = "select '5', maxcount from (select ooid.oid, count(*) as maxcount from usagestats.piwiklog pl, datasource_oids doid " +
             //        "where pl.entityid = doid.oid and doid.id=? group by doid.oid order by count(*) DESC limit 1) as temp";
             String query = "select sum(number_of_views) from datasource_stats where id=?";
             //String query = "select sum(number_of_views) from datasource_stats where id=?";
             /*
             String query = "select 'views', rvs.source, sum(rvs.sum) from repo_view_stats_monthly_clean rvs, datasource_results dr where rvs.id=dr.result and dr.id=? group by rvs.source " +
                 "UNION select 'views', '5', sum(rs.sum) from result_stats_monthly_clean rs, datasource_results dr where rs.id=dr.result and dr.id=?" +
                 "UNION select 'downloads', rds.source, sum(rds.sum) from repo_download_stats_monthly_clean rds, datasource_results dr where rds.id=dr.result and dr.id=? group by rds.source";
             */
             String query = "select 'views', dp.openaire_id, dp.repo_name, sum(rvs.sum) from repo_view_stats_monthly_clean rvs, datasource_results dr, datasource_piwik dp where rvs.id=dr.result and dp.piwik_id=rvs.source and dr.id=dp.openaire_id and dr.id=? group by dp.openaire_id, dp.repo_name " +
                 "UNION select 'views', dp.openaire_id, dp.repo_name, sum(rs.sum) from result_stats_monthly_clean rs, datasource_results dr, datasource_piwik dp where rs.id=dr.result and dp.piwik_id='5' and dr.id=? group by dp.openaire_id, dp.repo_name " +
                 "UNION select 'downloads', dp.openaire_id, dp.repo_name, sum(rds.sum) from repo_download_stats_monthly_clean rds, datasource_results dr, datasource_piwik dp where rds.id=dr.result and dp.openaire_id=dr.id and dp.piwik_id=rds.source and dr.id=? group by dp.openaire_id, dp.repo_name";
             List<String> values = new ArrayList<>();
             values.add(id);
             values.add(id);
             values.add(id);
             return executePreparedQuery2(query, values);
             return executePreparedQuery(query, values, "datasource");
+        }

             //        "where r.id=ro.id and pl.entityid=ro.oid and r.id=? and pl.source!= 5 group by  pl.source " +
             //        "UNION select '5', maxcount from (select ro.oid, count(*) as maxcount from result r, result_oids ro, usagestats.piwiklog pl " +
             //        "where r.id=ro.id and pl.entityid=ro.oid and r.id=? and pl.source=5 group by ro.oid order by count(*) DESC limit 1) as temp;";
             /*
             String query = "select 'views', rvs.source, sum(number_of_views) from repo_view_stats rvs, result_oids ro where rvs.id=ro.orid " +
                     "and ro.id=? group by rvs.source " +
                     "UNION select 'views', '5', sum(number_of_views) FROM result_stats rs WHERE rs.id=?" +
                     "UNION select 'downloads', rds.source, sum(number_of_downloads) from repo_download_stats rds, result_oids ro where rds.id=ro.orid " +
                     "and ro.id=? group by rds.source";
             */
             /*
             String query = "select 'views', dpo.\"OpenAIRE_ID\", sum(number_of_views) from repo_view_stats rvs, result_oids ro, \"DatasourcesPiwikOAIDs\" dpo where dpo.\"PiwikID\"=rvs.source and rvs.id=ro.orid " +
                     "and ro.id=? group by dpo.\"OpenAIRE_ID\"" +
                     "UNION select 'views', '5', sum(number_of_views) FROM result_stats rs WHERE rs.id=?" +
                     "UNION select 'downloads', dpo.\"OpenAIRE_ID\", sum(number_of_downloads) from repo_download_stats rds, result_oids ro, \"DatasourcesPiwikOAIDs\" dpo where dpo.\"PiwikID\"=rds.source and rds.id=ro.orid " +
                     "and ro.id=? group by dpo.\"OpenAIRE_ID\"";
                     */
             String query = "select 'views', dp.openaire_id, dp.repo_name, sum(rvs.sum) from repo_view_stats_monthly_clean rvs, datasource_piwik dp where rvs.id=? and dp.piwik_id=rvs.source group by dp.openaire_id, dp.repo_name " +
                     "UNION select 'views', dp.openaire_id, dp.repo_name, sum(rs.sum) FROM result_stats_monthly_clean rs, datasource_piwik dp WHERE rs.id=? and dp.piwik_id='5' group by dp.openaire_id, dp.repo_name " +
                     "UNION select 'downloads', dp.openaire_id, dp.repo_name, sum(rds.sum) from repo_download_stats_monthly_clean rds, datasource_piwik dp where rds.id=? and dp.piwik_id=rds.source group by dp.openaire_id, dp.repo_name";
             List<String> values = new ArrayList<>();
             values.add(id);
             values.add(id);
             values.add(id);
             UsageStats results = executePreparedQuery(query, values);
             UsageStats results = executePreparedQuery(query, values, "result");
             return results;
             //if (!results.isEmpty()) return results.get(0);

         }*/
         protected UsageStats executePreparedQuery(String query, List<String> values) {
         protected UsageStats executePreparedQuery(String query, List<String> values, String type) {
             UsageStats usageStats = new UsageStats();
             int total_views = 0;
             int total_downloads = 0;
-...
                 ResultSet rs = st.executeQuery();
                 while (rs.next()) {
                     if(rs.getString(3) != null && !rs.getString(3).equals("") && !rs.getString(3).equals("null")) {
                         if (rs.getString(1).equals("views")) {
                             //usageStats.addViews(rs.getString(2) + ":" + rs.getString(3));
                             usageStats.addViews(new RepoStats("",rs.getString(2),rs.getString(3)));
                             total_views += Integer.parseInt(rs.getString(3));
                         } else if (rs.getString(1).equals("downloads")) {
                             //usageStats.addDownloads(rs.getString(2) + ":" + rs.getString(3));
                             usageStats.addDownloads(new RepoStats("",rs.getString(2),rs.getString(3)));
                             total_downloads += Integer.parseInt(rs.getString(3));
                 if(type.equals("result") || type.equals("datasource")){
                     while (rs.next()) {
                         //if() {
                             if (rs.getString(1).equals("views") && rs.getString(4) != null && !rs.getString(4).equals("") && !rs.getString(4).equals("null")) {
                                 //usageStats.addViews(rs.getString(2) + ":" + rs.getString(3));
                                 //usageStats.addViews(new RepoStats("",rs.getString(2),rs.getString(3)));
                                 usageStats.addViews(new RepoStats(rs.getString(3),rs.getString(2),rs.getString(4)));
                                 total_views += Integer.parseInt(rs.getString(4));
                             } else if (rs.getString(1).equals("downloads") && rs.getString(4) != null && !rs.getString(4).equals("") && !rs.getString(4).equals("null")) {
                                 //usageStats.addDownloads(rs.getString(2) + ":" + rs.getString(3));
                                 //usageStats.addDownloads(new RepoStats("",rs.getString(2),rs.getString(3)));
                                 usageStats.addDownloads(new RepoStats(rs.getString(3),rs.getString(2),rs.getString(4)));
                                 total_downloads += Integer.parseInt(rs.getString(4));
+                            }
                         //}
                         //UsageStats stats = new UsageStats();
                         //stats.setId(rs.getString(1));
                         //stats.setName(rs.getString(2));
                         //stats.setOid(rs.getString(3));
                         //stats.setSource(rs.getString(1));
                         //stats.setValue(rs.getInt(2));
                         //statsList.add(stats);
+                    }
                     //if (total_views!= 0){
                     //usageStats.addViews(new RepoStats("Total",Integer.toString(total_views)));
                     usageStats.setTotal_views(Integer.toString(total_views));
                     //}
                     //if (total_downloads!= 0){
                     //usageStats.addDownloads(new RepoStats("Total",Integer.toString(total_downloads)));
                     usageStats.setTotal_downloads(Integer.toString(total_downloads));
                     //}
+                }
                 else if(type.equals("project")){
                     while(rs.next()){
                         if (rs.getString(1).equals("views") && rs.getString(2) != null && !rs.getString(2).equals("") && !rs.getString(2).equals("null")) {
                             total_views += Integer.parseInt(rs.getString(2));
                         } else if (rs.getString(1).equals("downloads") && rs.getString(2) != null && !rs.getString(2).equals("") && !rs.getString(2).equals("null")) {
                             total_downloads += Integer.parseInt(rs.getString(2));
+                        }
+                    }
                     //UsageStats stats = new UsageStats();
                     //stats.setId(rs.getString(1));
                     //stats.setName(rs.getString(2));
                     //stats.setOid(rs.getString(3));
                     //stats.setSource(rs.getString(1));
                     //stats.setValue(rs.getInt(2));
                     //statsList.add(stats);
                     usageStats.setTotal_views(Integer.toString(total_views));
                     usageStats.setTotal_downloads(Integer.toString(total_downloads));
+                }
                 //if (total_views!= 0){
                     //usageStats.addViews(new RepoStats("Total",Integer.toString(total_views)));
                 usageStats.setTotal_views(Integer.toString(total_views));
                 //}
                 //if (total_downloads!= 0){
                     //usageStats.addDownloads(new RepoStats("Total",Integer.toString(total_downloads)));
                 usageStats.setTotal_downloads(Integer.toString(total_downloads));
                 //}
                 rs.close();
                 st.close();
-...
                 ResultSet rs;
                 switch (split[0].toLowerCase()) {
                     case "openaire":
                         st = connection.prepareStatement("select \"PiwikID\" from \"DatasourcesPiwikOAIDs\" where \"OpenAIRE_ID\"=?");
                         st = connection.prepareStatement("select piwik_id from datasource_piwik where openaire_id=?");
                         //st = connection.prepareStatement("select piwik_id from datasource where id=?");
                         st.setString(1, repositoryIdentifier.replaceFirst(split[0] + ":", ""));
                         rs = st.executeQuery();
-...
                         return piwikid;
                     case "repoid":
                         st = connection.prepareStatement("select \"PiwikID\" from \"DatasourcesPiwikOAIDs\" where \"PiwikID\"=?");
                         st = connection.prepareStatement("select piwik_id from datasource_piwik where piwik_id=?");
                         //st = connection.prepareStatement("select piwik_id from datasource where id=?");
                         st.setInt(1, Integer.parseInt(repositoryIdentifier.replaceFirst(split[0] + ":", "")));
                         rs = st.executeQuery();
-...
                 PreparedStatement st;
                 if(itemDataType.equals("")) {
                     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");
                     //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");
                     st.setString(1, oid);
+                }
                 else{
                     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");
                     //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");
                     st.setString(1, oid);
                     st.setString(2, itemDataType);
+                }
-...
                 boolean hasData = false;
                 while (rs.next()) {
                     reportItem = new ReportItem(rs.getString(2),"", rs.getString(3),rs.getString(1));
                     //reportItem = new ReportItem(rs.getString(2),rs.getString(6), rs.getString(3),rs.getString(1));
                     reportItem.addIdentifier(new ItemIdentifier("URL",rs.getString(4)));
                     reportItem.addIdentifier(new ItemIdentifier("OAI",oid));
                     reportItem.addIdentifier(new ItemIdentifier("OPENAIRE",rs.getString(5)));
-...
                     //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");
                     if(repositoryIdentifier.equals("")) {
                         //st = connection.prepareStatement("select month, sum from repo_download_stats_monthly where id=? and month>=? and month<=? order by month;");
                         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 where id=? and month>=? and month<=?) rdsm ON d.date=rdsm.month;");
                         //st = connection.prepareStatement("select month, sum from repo_download_stats_monthly_sushi where id=? and month>=? and month<=? order by month;");
                         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;");
+                    }
                     else{
                         //st = connection.prepareStatement("select month, sum from repo_download_stats_monthly where id=? and month>=? and month<=? and source=? order by month;");
                         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 where id=? and month>=? and month<=? and source=?) rdsm ON d.date=rdsm.month;");
                         //st = connection.prepareStatement("select month, sum from repo_download_stats_monthly_sushi where id=? and month>=? and month<=? and source=? order by month;");
                         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;");
                         st.setInt(6, Integer.parseInt(repositoryIdentifier));
+                    }
                     st.setString(1, report_dateFormat.format(beginDate));
-...
+            }
             return 0;
+        }
         protected int executeOidOLD(List<ReportItem> reportItems, String oid, String repositoryIdentifier, String itemDataType, Date beginDate, Date endDate, String granularity){
             ReportItem reportItem = null;
             SimpleDateFormat report_dateFormat = new SimpleDateFormat("yyyy-MM-dd");
             Boolean hasReports = false;
             try {
                 Connection connection = dataSource.getConnection();
                 PreparedStatement st;
                 if(itemDataType.equals("")) {
                     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");
                     st.setString(1, oid);
+                }
                 else{
                     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");
                     st.setString(1, oid);
                     st.setString(2, itemDataType);
+                }
                 ResultSet rs = st.executeQuery();
                 boolean hasData = false;
                 while (rs.next()) {
                     reportItem = new ReportItem(rs.getString(2),"", rs.getString(3),rs.getString(1));
                     reportItem.addIdentifier(new ItemIdentifier("URL",rs.getString(4)));
                     reportItem.addIdentifier(new ItemIdentifier("OAI",oid));
                     reportItem.addIdentifier(new ItemIdentifier("OPENAIRE",rs.getString(5)));
                     hasData = true;
+                }
                 rs.close();
                 st.close();
                 if(!hasData){
                     connection.close();
                     return -1;
+                }
                 //log.error(report_dateFormat.format(c.getTime()));
                 if (granularity.toLowerCase().equals("totals")){
                     if(repositoryIdentifier.equals("")) {
                         st = connection.prepareStatement("select sum(number_of_downloads) from repo_download_stats where id=? and date>=? and date<=?");
+                    }
                     else{
                         st = connection.prepareStatement("select sum(number_of_downloads) from repo_download_stats where id=? and date>=? and date<=? and source=?");
                         st.setInt(4, Integer.parseInt(repositoryIdentifier));
+                    }
                     st.setString(1, oid);
                     //st.setString(2, sql_dateFormat.format(in_beginDate));
                     //st.setString(3, sql_dateFormat.format(in_endDate));
                     st.setDate(2, new java.sql.Date(beginDate.getTime()));
                     st.setDate(3, new java.sql.Date(endDate.getTime()));
                     //log.error(new SimpleDateFormat("yyyy-MM-dd").format(input_dateFormat.parse(beginDate)));
                     log.error("qqqq: " + st);
                     rs = st.executeQuery();
                     while(rs.next()){
                         reportItem.addPerformance(new ItemPerformance(report_dateFormat.format(beginDate), report_dateFormat.format(endDate), rs.getString(1)));
                         hasReports = true;
+                    }
                     rs.close();
                     st.close();
+                }
                 else if (granularity.toLowerCase().equals("monthly")){
                     //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");
                     if(repositoryIdentifier.equals("")) {
                         st = connection.prepareStatement("select month, sum from repo_download_stats_monthly where id=? and month>=? and month<=? order by month;");
+                    }
                     else{
                         st = connection.prepareStatement("select month, sum from repo_download_stats_monthly where id=? and month>=? and month<=? and source=? order by month;");
                         st.setInt(4, Integer.parseInt(repositoryIdentifier));
+                    }
                     st.setString(1, oid);
                     //st.setString(2, sql_dateFormat.format(in_beginDate));
                     //st.setString(3, sql_dateFormat.format(in_endDate));
                     //st.setDate(2, new java.sql.Date(beginDate.getTime()));
                     //st.setDate(3, new java.sql.Date(endDate.getTime()));
                     st.setString(2, new SimpleDateFormat("yyyy-MM-dd").format(beginDate));
                     st.setString(3, new SimpleDateFormat("yyyy-MM-dd").format(endDate));
                     log.error("qqqq: " + st);
                     Calendar start = Calendar.getInstance();
                     start.setTime(beginDate);
                     Calendar end = Calendar.getInstance();
                     end.setTime(endDate);
                     Date date = start.getTime();
                     rs = st.executeQuery();
                     while(rs.next()){
                         hasReports = true;
                         Date rs_date = rs.getDate(1);
                         while(date.before(rs_date) && !date.equals(rs_date)){
                             Calendar temp_c = Calendar.getInstance();
                             temp_c.setTime(date);
                             temp_c.set(Calendar.DAY_OF_MONTH, temp_c.getActualMaximum(Calendar.DAY_OF_MONTH));
                             Date temp_endDate = temp_c.getTime();
                             reportItem.addPerformance(new ItemPerformance(report_dateFormat.format(date), report_dateFormat.format(temp_endDate),"0"));
                             start.add(Calendar.MONTH, 1);
                             date = start.getTime();
+                        }
                         Calendar temp_c = Calendar.getInstance();
                         temp_c.setTime(rs_date);
                         temp_c.set(Calendar.DAY_OF_MONTH, temp_c.getActualMaximum(Calendar.DAY_OF_MONTH));
                         Date temp_endDate = temp_c.getTime();
                         reportItem.addPerformance(new ItemPerformance(report_dateFormat.format(rs_date), report_dateFormat.format(temp_endDate), rs.getString(2)));
                         start.add(Calendar.MONTH, 1);
                         date = start.getTime();
+                    }
                     rs.close();
                     st.close();
                     for (; start.before(end); start.add(Calendar.MONTH, 1), date = start.getTime()) {
                         Calendar temp_c = Calendar.getInstance();
                         temp_c.setTime(date);
                         temp_c.set(Calendar.DAY_OF_MONTH, temp_c.getActualMaximum(Calendar.DAY_OF_MONTH));
                         Date temp_endDate = temp_c.getTime();
                         reportItem.addPerformance(new ItemPerformance(report_dateFormat.format(date), report_dateFormat.format(temp_endDate),"0"));
                         //log.error("DATE: " + date + " TO: " + temp_endDate);
+                    }
+                }
                 connection.close();
             } catch (Exception e) {
                 log.error("Cannot execute oid : " + e);
+            }
             if(hasReports) {
                 reportItems.add(reportItem);
+            }
             return 0;
+        }
         protected int executeRepo(List<ReportItem> reportItems, String repositoryIdentifier, String itemDataType, Date beginDate, Date endDate, String granularity){
             SimpleDateFormat report_dateFormat = new SimpleDateFormat("yyyy-MM-dd");
             try {
-...
                 if (granularity.toLowerCase().equals("totals")) {
                    if(repositoryIdentifier.equals("")){
                        if(itemDataType.equals("")){
                            st = connection.prepareStatement("select source, d.id, d.name, d.websiteurl, sum(number_of_downloads) from repo_download_stats rds, \"DatasourcesPiwikOAIDs\" dpo, datasource d  where rds.source=dpo.\"PiwikID\" and dpo.\"OpenAIRE_ID\"=d.id and rds.date>=? and rds.date<=? group by source, d.id, d.name, d.websiteurl order by source;");
                            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;");
                            //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;");
                            st.setDate(1, new java.sql.Date(beginDate.getTime()));
                            st.setDate(2, new java.sql.Date(endDate.getTime()));
+                       }
                        else{
                            st = connection.prepareStatement("select source, d.id, d.name, d.websiteurl, sum(number_of_downloads) from repo_download_stats rds, \"DatasourcesPiwikOAIDs\" dpo, datasource d, result_oids roid, result_classifications rc where roid.orid=rds.id and roid.id=rc.id and rds.source=dpo.\"PiwikID\" 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;");
                            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;");
                            //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;");
                            st.setDate(1, new java.sql.Date(beginDate.getTime()));
                            st.setDate(2, new java.sql.Date(endDate.getTime()));
                            st.setString(3, itemDataType);
+                       }
                    } else {
                        if(itemDataType.equals("")){
                            st = connection.prepareStatement("select source, d.id, d.name, d.websiteurl, sum(number_of_downloads) from repo_download_stats rds, \"DatasourcesPiwikOAIDs\" dpo, datasource d  where rds.source=dpo.\"PiwikID\" 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;");
                            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;");
                            //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;");
                            st.setDate(1, new java.sql.Date(beginDate.getTime()));
                            st.setDate(2, new java.sql.Date(endDate.getTime()));
                            st.setInt(3, Integer.parseInt(repositoryIdentifier));
+                       }
                        else{
                            st = connection.prepareStatement("select source, d.id, d.name, d.websiteurl, sum(number_of_downloads) from repo_download_stats rds, \"DatasourcesPiwikOAIDs\" dpo, datasource d, result_oids roid, result_classifications rc where roid.orid=rds.id and roid.id=rc.id and rds.source=dpo.\"PiwikID\" 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;");
                            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;");
                            //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;");
                            st.setDate(1, new java.sql.Date(beginDate.getTime()));
                            st.setDate(2, new java.sql.Date(endDate.getTime()));
                            st.setString(3, itemDataType);
-...
                     if(repositoryIdentifier.equals("")){
                         if(itemDataType.equals("")){
                             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.\"PiwikID\" AS piwikid, d.websiteurl, to_char(date_trunc('month', (?::date + interval '1 month'*offs)), 'YYYY-MM-DD') AS new_date FROM \"DatasourcesPiwikOAIDs\" 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 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;");
                             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;");
                             //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;");
                             st.setString(1, report_dateFormat.format(beginDate));
                             st.setInt(2, diffMonth);
                             st.setDate(3, new java.sql.Date(beginDate.getTime()));
                             st.setDate(4, new java.sql.Date(endDate.getTime()));
+                        }
                         else{
                             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.\"PiwikID\" AS piwikid, d.websiteurl, to_char(date_trunc('month', (?::date + interval '1 month'*offs)), 'YYYY-MM-DD') AS new_date FROM \"DatasourcesPiwikOAIDs\" 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 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;");
                             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;");
                             //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;");
                             st.setString(1, report_dateFormat.format(beginDate));
                             st.setInt(2, diffMonth);
                             st.setString(3, itemDataType);
-...
+                        }
                     } else {
                         if(itemDataType.equals("")){
                             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.\"PiwikID\" AS piwikid, d.websiteurl, to_char(date_trunc('month', (?::date + interval '1 month'*offs)), 'YYYY-MM-DD') AS new_date FROM \"DatasourcesPiwikOAIDs\" dpo, datasource d, generate_series(0, ?, 1) AS offs where dpo.\"OpenAIRE_ID\"=d.id and dpo.\"PiwikID\"=? order by d.id, new_date) dd LEFT JOIN (SELECT source, month, SUM(sum) as tsum FROM repo_download_stats_monthly 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;");
                             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;");
                             //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;");
                             st.setString(1, report_dateFormat.format(beginDate));
                             st.setInt(2, diffMonth);
                             st.setInt(3, Integer.parseInt(repositoryIdentifier));
-...
                             st.setDate(6, new java.sql.Date(endDate.getTime()));
+                        }
                         else{
                             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.\"PiwikID\" AS piwikid, d.websiteurl, to_char(date_trunc('month', (?::date + interval '1 month'*offs)), 'YYYY-MM-DD') AS new_date FROM \"DatasourcesPiwikOAIDs\" dpo, datasource d, generate_series(0, ?, 1) AS offs where dpo.\"OpenAIRE_ID\"=d.id and dpo.\"PiwikID\"=? order by d.id, new_date) dd LEFT JOIN (SELECT rds.source, rds.month, SUM(rds.sum) as tsum FROM repo_download_stats_monthly 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;");
                             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;");
                             //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;");
                             st.setString(1, report_dateFormat.format(beginDate));
                             st.setInt(2, diffMonth);
                             st.setInt(3, Integer.parseInt(repositoryIdentifier));
-...
                 if(granularity.toLowerCase().equals("totals")){
                     if(itemDataType.equals("")) {
                         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;");
                         //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;");
                         st.setInt(1, Integer.parseInt(repositoryIdentifier));
                         st.setDate(2, new java.sql.Date(beginDate.getTime()));
                         st.setDate(3, new java.sql.Date(endDate.getTime()));
+                    }
                     else{
                         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;");
                         //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;");
                         st.setInt(1, Integer.parseInt(repositoryIdentifier));
                         st.setDate(2, new java.sql.Date(beginDate.getTime()));
                         st.setDate(3, new java.sql.Date(endDate.getTime()));
-...
                     while (rs.next()) {
                         ReportItem reportItem = new ReportItem(rs.getString(3), "", rs.getString(4), rs.getString(2));
                         //ReportItem reportItem = new ReportItem(rs.getString(3), rs.getString(6), rs.getString(4), rs.getString(2));
                         reportItem.addIdentifier(new ItemIdentifier("URL",rs.getString(5)));
                         reportItem.addIdentifier(new ItemIdentifier("OAI",rs.getString(1)));
                         reportItem.addIdentifier(new ItemIdentifier("OPENAIRE",rs.getString(6)));
                         //reportItem.addIdentifier(new ItemIdentifier("OPENAIRE",rs.getString(7)));
                         reportItem.addPerformance(new ItemPerformance(report_dateFormat.format(beginDate), report_dateFormat.format(endDate), rs.getString(7)));
                         //reportItem.addPerformance(new ItemPerformance(report_dateFormat.format(beginDate), report_dateFormat.format(endDate), rs.getString(8)));
                         reportItems.add(reportItem);
+                    }
                     rs.close();
-...
                     int diffMonth = diffYear * 12 + endCalendar.get(Calendar.MONTH) - startCalendar.get(Calendar.MONTH);
                     if(itemDataType.equals("")){
                         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 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 where source=?) rdm ON d.new_date=rdm.month and d.orid=rdm.id order by d.orid, d.new_date;");
                         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;");
                         //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;");
                         st.setString(1, report_dateFormat.format(beginDate));
                         st.setInt(2, diffMonth);
                         st.setInt(3, Integer.parseInt(repositoryIdentifier));
-...
                         st.setDate(5, new java.sql.Date(endDate.getTime()));
                         st.setInt(6, Integer.parseInt(repositoryIdentifier));
                     } else{
                         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 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 where source=?) rdm ON d.new_date=rdm.month and d.orid=rdm.id order by d.orid, d.new_date;");
                         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;");
                         //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;");
                         st.setString(1, report_dateFormat.format(beginDate));
                         st.setInt(2, diffMonth);
                         st.setInt(3, Integer.parseInt(repositoryIdentifier));
-...
                     while (rs.next()) {
                         ReportItem reportItem = new ReportItem(rs.getString(3), "", rs.getString(4), rs.getString(2));
                         //ReportItem reportItem = new ReportItem(rs.getString(3), rs.getString(6), rs.getString(4), rs.getString(2));
                         reportItem.addIdentifier(new ItemIdentifier("URL",rs.getString(5)));
                         reportItem.addIdentifier(new ItemIdentifier("OAI",rs.getString(1)));
                         reportItem.addIdentifier(new ItemIdentifier("OPENAIRE",rs.getString(6)));
                         //reportItem.addIdentifier(new ItemIdentifier("OPENAIRE",rs.getString(7)));
                         for(int i = 0; i <= diffMonth; i++) {
                             Calendar temp_c = Calendar.getInstance();
                             temp_c.setTime(rs.getDate(7));
                             temp_c.set(Calendar.DAY_OF_MONTH, temp_c.getActualMaximum(Calendar.DAY_OF_MONTH));
                             Date temp_endDate = temp_c.getTime();
                             reportItem.addPerformance(new ItemPerformance(report_dateFormat.format(rs.getDate(7)), report_dateFormat.format(temp_endDate), rs.getString(8)));
                             //reportItem.addPerformance(new ItemPerformance(report_dateFormat.format(rs.getDate(8)), report_dateFormat.format(temp_endDate), rs.getString(9)));
                             if(i != diffMonth) {
                                 rs.next();
+                            }
-...
+            }
             return 0;
+        }
         protected int executeRepoOLD(List<ReportItem> reportItems, String repositoryIdentifier, String itemDataType, Date beginDate, Date endDate, String granularity){
             try {
                 Connection connection = dataSource.getConnection();
                 PreparedStatement st;
                 if(itemDataType.equals("")) {
                     st = connection.prepareStatement("select distinct id from repo_download_stats where source=? order by id");
                     st.setInt(1, Integer.parseInt(repositoryIdentifier));
+                }
                 else{
                     st = connection.prepareStatement("select distinct rds.id from repo_download_stats rds, result_oids roid, result_classifications rc where rds.id=roid.orid and roid.id=rc.id and rc.type=? order by rds.id");
                     st.setInt(1, Integer.parseInt(repositoryIdentifier));
                     st.setString(2, itemDataType);
+                }
                 ResultSet rs = st.executeQuery();
                 while (rs.next()) {
                     executeDoi(reportItems, rs.getString(1), repositoryIdentifier, itemDataType, beginDate, endDate, granularity);
+                }
                 rs.close();
                 st.close();
                 connection.close();
             } catch (Exception e){
                 log.error("Cannot execute repo : " + e);
+            }
             return 0;
+        }
+    }

         private Logger log = Logger.getLogger(this.getClass());
         public ViewStats getClicks(String projectId) {
         //public ViewStats getClicks(String projectId) {
         public UsageStats getClicks(String projectId) {
             //String query = "select p.id, p.title, sum (ps.numberofviews) from project p, usagestats.projectsstats ps " +
             //        "where ps.projectid=p.id and p.id=? group by p.id, p.title;";
             //String query = "select '5', maxcount from (select pl.entityid, count(*) as maxcount from usagestats.piwiklog pl, project_oids poid " +
             //        "where pl.entityid = poid.oid and poid.id=? group by pl.entityid order by count(*) DESC limit 1) as temp;";
             String query = "select sum(number_of_views) from project_stats where id=?";
             //String query = "select sum(number_of_views) from project_stats where id=?";
             String query = "select 'views', sum(rvs.sum) from repo_view_stats_monthly_clean rvs, project_results pr where pr.result=rvs.id and pr.id=?" +
                     "UNION select 'views', sum(rs.sum) from result_stats_monthly_clean rs, project_results pr where pr.result=rs.id and pr.id=?" +
                 "UNION select 'downloads', sum(rds.sum) from repo_download_stats_monthly_clean rds, project_results pr where pr.result=rds.id and pr.id=?";
             List<String> values = new ArrayList<>();
             values.add(projectId);
             values.add(projectId);
             values.add(projectId);
             return executePreparedQuery2(query, values);
             return executePreparedQuery(query, values, "project");
+        }

             List<ReportItem> reportItems = new ArrayList<>();
             List<ReportException> reportExceptions = new ArrayList<>();
             if(!granularity.toLowerCase().equals("totals") && !granularity.toLowerCase().equals("monthly")){
                 reportExceptions.add(new ReportException("3062", "Warning", "Invalid ReportAttribute Value", "Granularity: \'" + granularity + "\' unknown. Defaulting to Monthly"));
                 granularity = "Monthly";
+            }
             Date beginDateParsed = null;
             if(!beginDate.equals("")){
                 beginDateParsed = tryParse(beginDate);
-...
             if(endDateParsed == null){
                 reportExceptions.add(new ReportException("3020", "Error", "Invalid Date Arguments", "End Date: " + endDate + " is not a valid date"));
+            }
             if(beginDateParsed != null && endDateParsed != null && !beginDateParsed.before(endDateParsed)){
                 reportExceptions.add(new ReportException("3020", "Error", "Invalid Date Arguments", "BeginDate \'" + new SimpleDateFormat("yyyy-MM-dd").format(beginDateParsed) + "\' is greater than EndDate \'" + new SimpleDateFormat("yyyy-MM-dd").format(endDateParsed)+ "\'"));
+            }
             String repoid = "";
             if (!repositoryIdentifier.equals("")){
                 repoid = executeRepoId(repositoryIdentifier);
-...
                     reportExceptions.add(new ReportException("3060", "Error", "Invalid Filter Value", "RepositoryIdentifier: " + repositoryIdentifier + " is not valid"));
+                }
+            }
             if(!granularity.toLowerCase().equals("totals") && !granularity.toLowerCase().equals("monthly")){
                 reportExceptions.add(new ReportException("3062", "Warning", "Invalid ReportAttribute Value", "Granularity: \'" + granularity + "\' unknown. Defaulting to Monthly"));
                 granularity = "Monthly";
+            }
             String itemid = "";
             if(!itemIdentifier.equals("")){
                 String[] split = itemIdentifier.split(":");

     package eu.dnetlib.usagestats.report;
     import com.fasterxml.jackson.annotation.JsonInclude;
     import com.fasterxml.jackson.annotation.JsonProperty;
     import java.util.ArrayList;
-...
             return itemIdentifiers;
+        }
         @JsonInclude(JsonInclude.Include.NON_NULL)
         @JsonProperty("ItemPublisher")
         public String getItemPublisher() {
             return itemPublisher;
-...
             return itemDataType;
+        }
         @JsonInclude(JsonInclude.Include.NON_NULL)
         @JsonProperty("ItemName")
         public String getItemName() {
             return itemName;

modules/dnet-openaire-usage-stats-api/src/main/resources/application.properties
9	9	server.port=8080
10	10
11	11	spring.jackson.serialization.INDENT_OUTPUT=true
12
13		vendor.name=OpenAIRE
14		vendor.contact=OpenAIRE Helpdesk
15		vendor.email=helpdesk@openaire.eu

Also available in: Unified diff

Project

General

Profile

D-Net

Revision 45522

Added by Tsampikos Livisianos over 7 years ago