Project

General

Profile

« Previous | Next » 

Revision 47500

Added by Tsampikos Livisianos about 7 years ago

dnet45

View differences:

modules/dnet-openaire-usage-stats-api/src/main/java/eu/dnetlib/usagestats/portal/UsageStats.java
1
package eu.dnetlib.usagestats.portal;
2

  
3
import com.fasterxml.jackson.annotation.JsonProperty;
4

  
5
import java.io.Serializable;
6
import java.util.ArrayList;
7
import java.util.List;
8

  
9

  
10
//@Entity(name = "usageStats")
11
public class UsageStats implements Serializable {
12

  
13
    private final static long serialVersionUID = 1;
14

  
15
    private final List<RepositoryStats> downloads = new ArrayList<>();
16
    private final List<RepositoryStats> views = new ArrayList<>();
17

  
18
    private String total_downloads = "0";
19
    private String total_views = "0";
20
    private String pageviews = "0";
21
    private String total_openaire_views = "0";
22
    private String total_openaire_downloads = "0";
23

  
24
    public UsageStats() {
25
    }
26

  
27
    @JsonProperty("downloads")
28
    public List<RepositoryStats> getDownloads() {
29
        return downloads;
30
    }
31

  
32
    @JsonProperty("views")
33
    public List<RepositoryStats> getViews() {
34
        return views;
35
    }
36

  
37
    public void addViews(RepositoryStats view) {
38
        views.add(view);
39
    }
40

  
41
    public void addDownloads(RepositoryStats download) {
42
        downloads.add(download);
43
    }
44

  
45
    @JsonProperty("total_downloads")
46
    public String getTotal_downloads() {
47
        return total_downloads;
48
    }
49

  
50
    public void setTotal_downloads(String total_downloads) {
51
        this.total_downloads = total_downloads;
52
    }
53

  
54
    @JsonProperty("total_views")
55
    public String getTotal_views() {
56
        return total_views;
57
    }
58

  
59
    public void setTotal_views(String total_views) {
60
        this.total_views = total_views;
61
    }
62

  
63
    @JsonProperty("pageviews")
64
    public String getPageViews() {
65
        return pageviews;
66
    }
67

  
68
    public void setPageViews(String pageviews) {
69
        this.pageviews = pageviews;
70
    }
71

  
72
    @JsonProperty("total_openaire_views")
73
    public String getTotal_openaire_views() {
74
        return total_openaire_views;
75
    }
76

  
77
    public void setTotal_openaire_views(String total_openaire_views) {
78
        this.total_openaire_views = total_openaire_views;
79
    }
80

  
81
    @JsonProperty("total_openaire_downloads")
82
    public String getTotal_openaire_downloads() {
83
        return total_openaire_downloads;
84
    }
85

  
86
    public void setTotal_openaire_downloads(String total_openaire_downloads) {
87
        this.total_openaire_downloads = total_openaire_downloads;
88
    }
89

  
90

  
91
    /*
92
    public String toString(){
93
        String string;
94
        string = total_downloads + " ";
95
        string += total_views + " ";
96
        string += pageviews + " ";
97
        string += total_openaire + " ";
98

  
99
        for(RepositoryStats repositoryStats : downloads){
100
            string += repositoryStats.toString() + " ";
101
        }
102

  
103
        for(RepositoryStats repositoryStats : views){
104
            string += repositoryStats.toString() + " ";
105
        }
106
        return string;
107
    }
108
    */
109
}
modules/dnet-openaire-usage-stats-api/src/main/java/eu/dnetlib/usagestats/portal/RepositoryStats.java
1
package eu.dnetlib.usagestats.portal;
2

  
3
import com.fasterxml.jackson.annotation.JsonProperty;
4

  
5
import java.io.Serializable;
6

  
7
/**
8
 * Created by tsampikos on 8/11/2016.
9
 */
10
public class RepositoryStats implements Serializable {
11
    private final static long serialVersionUID = 1;
12
    private String datasource_name = "";
13
    private String datasource_id = "";
14
    private String value = "";
15
    private String openaire = "";
16

  
17
    public RepositoryStats() {
18
    }
19

  
20
    public RepositoryStats(String datasource_name, String datasource_id, String value, String openaire) {
21
        this.datasource_name = datasource_name;
22
        this.datasource_id = datasource_id;
23
        this.value = value;
24
        this.openaire = openaire;
25
    }
26

  
27
    @JsonProperty("datasource_name")
28
    public String getDatasource_name() {
29
        return datasource_name;
30
    }
31

  
32
    @JsonProperty("datasource_id")
33
    public String getDatasource_id() {
34
        return datasource_id;
35
    }
36

  
37
    @JsonProperty("value")
38
    public String getValue() {
39
        return value;
40
    }
41

  
42
    @JsonProperty("openaire")
43
    public String getOpenaire() {
44
        return openaire;
45
    }
46

  
47
    /*
48
    public String toString(){
49
        return datasource_name + " " + datasource_id + " " + value + " " + openaire;
50
    }
51
    */
52

  
53
}
modules/dnet-openaire-usage-stats-api/src/main/java/eu/dnetlib/usagestats/controllers/CustomWebMvcConfigurerAdapter.java
1
package eu.dnetlib.usagestats.controllers;
2

  
3
import org.springframework.context.annotation.Configuration;
4
import org.springframework.web.servlet.config.annotation.ViewControllerRegistry;
5
import org.springframework.web.servlet.config.annotation.WebMvcConfigurerAdapter;
6

  
7
/**
8
 * Created by tsampikos on 12/4/2017.
9
 */
10
@Configuration
11
public class CustomWebMvcConfigurerAdapter extends WebMvcConfigurerAdapter {
12

  
13
    @Override
14
    public void addViewControllers(ViewControllerRegistry registry) {
15
        registry.addViewController("/sushilite").setViewName("redirect:/sushilite/");
16
        registry.addViewController("/sushilite/").setViewName("forward:/sushilite/index.html");
17

  
18
        registry.addViewController("/sushilite/AR1").setViewName("redirect:/sushilite/AR1/");
19
        registry.addViewController("/sushilite/AR1/").setViewName("forward:/sushilite/AR1/index.html");
20

  
21
        registry.addViewController("/sushilite/IR1").setViewName("redirect:/sushilite/IR1/");
22
        registry.addViewController("/sushilite/IR1/").setViewName("forward:/sushilite/IR1/index.html");
23

  
24
        registry.addViewController("/sushilite/RR1").setViewName("redirect:/sushilite/RR1/");
25
        registry.addViewController("/sushilite/RR1/").setViewName("forward:/sushilite/RR1/index.html");
26

  
27
        /*
28
        registry.addViewController("/sushilite/JR1").setViewName("redirect:/sushilite/JR1/");
29
        registry.addViewController("/sushilite/JR1/").setViewName("forward:/sushilite/JR1/index.html");
30
        */
31

  
32
        super.addViewControllers(registry);
33
    }
34
}
modules/dnet-openaire-usage-stats-api/src/main/java/eu/dnetlib/usagestats/controllers/UsageStatsController.java
1
package eu.dnetlib.usagestats.controllers;
2

  
3
import eu.dnetlib.usagestats.portal.UsageStats;
4
import eu.dnetlib.usagestats.repos.DatasourceRepo;
5
import eu.dnetlib.usagestats.repos.OrganizationRepo;
6
import eu.dnetlib.usagestats.repos.ProjectRepo;
7
import eu.dnetlib.usagestats.repos.ResultRepo;
8

  
9
import org.apache.log4j.Logger;
10
import org.springframework.beans.factory.annotation.Autowired;
11
import org.springframework.web.bind.annotation.CrossOrigin;
12
import org.springframework.web.bind.annotation.PathVariable;
13
import org.springframework.web.bind.annotation.RequestMapping;
14
import org.springframework.web.bind.annotation.RequestMethod;
15
import org.springframework.web.bind.annotation.RestController;
16

  
17

  
18
@RestController
19
//@Transactional
20
public class UsageStatsController {
21
    @Autowired
22
    private DatasourceRepo datasourceRepo;
23

  
24
    @Autowired
25
    private ResultRepo resultRepo;
26

  
27
    @Autowired
28
    private OrganizationRepo organizationRepo;
29

  
30
    @Autowired
31
    private ProjectRepo projectRepo;
32

  
33
    private final Logger log = Logger.getLogger(this.getClass());
34

  
35
    @CrossOrigin(methods = RequestMethod.GET, origins = "*")
36
    @RequestMapping(value = "/datasources/{datasourceId}/clicks")
37
    public UsageStats getDatasourceClicks(@PathVariable(value = "datasourceId") String datasourceId) {
38
        log.info("stats request for datasource: " + datasourceId);
39
        return datasourceRepo.getClicks(datasourceId);
40
    }
41

  
42
    @RequestMapping(value = "/projects/{projectId}/clicks")
43
    @CrossOrigin(methods = RequestMethod.GET, origins = "*")
44
    public UsageStats getProjectClicks(@PathVariable(value = "projectId") String projectId) {
45
        log.info("stats request for project: " + projectId);
46
        return projectRepo.getClicks(projectId);
47
    }
48

  
49
    @RequestMapping(value = "/organizations/{organizationId}/clicks")
50
    @CrossOrigin(methods = RequestMethod.GET, origins = "*")
51
    public UsageStats getOrganizationClicks(@PathVariable(value = "organizationId") String organizationId) {
52
        log.info("stats request for organization: " + organizationId);
53
        return organizationRepo.getClicks(organizationId);
54
    }
55

  
56
    @RequestMapping(value = "/results/{resultId}/clicks")
57
    @CrossOrigin(methods = RequestMethod.GET, origins = "*")
58
    public UsageStats getResultClicks(@PathVariable(value = "resultId") String resultId) {
59
        log.info("stats request for result: " + resultId);
60
        return resultRepo.getClicks(resultId);
61
    }
62
}
modules/dnet-openaire-usage-stats-api/src/main/java/eu/dnetlib/usagestats/controllers/SushiLiteController.java
1
package eu.dnetlib.usagestats.controllers;
2

  
3
import com.fasterxml.jackson.databind.ObjectMapper;
4

  
5
import eu.dnetlib.usagestats.sushilite.UsageReport;
6

  
7
import org.apache.log4j.Logger;
8
import org.springframework.beans.factory.annotation.Autowired;
9
import org.springframework.web.bind.annotation.RequestMapping;
10
import org.springframework.web.bind.annotation.RequestMethod;
11
import org.springframework.web.bind.annotation.RequestParam;
12
import org.springframework.web.bind.annotation.RestController;
13

  
14
/**
15
 * Created by tsampikos on 10/4/2017.
16
 */
17

  
18
@RestController
19
class SushiLiteController {
20

  
21
    private final Logger log = Logger.getLogger(this.getClass());
22

  
23
    @Autowired
24
    private UsageReport report;
25

  
26
    /*
27
    @RequestMapping(value = "/", method = RequestMethod.GET)
28
    String defaultMap(){
29
        return "<body><center>" +
30
                "<a href=\"/usagestats/sushilite\"><h2>OpenAIRE SUSHI Lite Client</h2></a>" +
31
                "</center></body>";
32
    }
33
    */
34

  
35
    /*
36
    @RequestMapping(value = "/sushilite", method = RequestMethod.GET)
37
    public String sushilite(){
38
        return "<body><center>" +
39
                "<h2>OpenAIRE SUSHI Lite Client</h2>" +
40
                "<h3> Supported Reports</h3>" +
41
                "<ul>" +
42
                "<li><a href=\"/usagestats/sushilite/AR1/\">AR1 Report</a></li>" +
43
                "<li><a href=\"/usagestats/sushilite/IR1/\">IR1 Report</a></li>" +
44
                "<li><a href=\"/usagestats/sushilite/RR1/\">RR1 Report</a></li>" +
45
                "</ul>" +
46
                "</center></body>";
47
    }
48
    */
49

  
50
    /*
51
    @RequestMapping(value = "/sushilite/AR1", method = RequestMethod.GET)
52
    public String ar1Map(){
53
        return "<body>" +
54
                "<h2>OpenAIRE SUSHI Lite Client</h2>" +
55
                "<form action=\"/usagestats/sushilite/GetReport/\" method=\"get\">" +
56
                "<h3>Report Request</h3>" +
57
                "Report Name: " +
58
                "<select name='Report'>" +
59
                "<option value='AR1'>AR1</option>" +
60
                "</select>" +
61
                "<br />" +
62
                "Release: " +
63
                "<select name='Release'>" +
64
                "<option value=\"4\">4</option>" +
65
                "</select>" +
66
                "<br />" +
67
                "Requestor: " +
68
                "<input type=\"text\" name=\"RequestorID\" value=\"anonymous\">" +
69
                "<h3>Report Filters</h3>" +
70
                "<h4>DateRange</h4>" +
71
                "<p>Valid date formats: yyyy-mm-dd or yyyy-mm<br />" +
72
                "Defaults to the last available month if no dates are provided" +
73
                "</p>" +
74
                "BeginDate:<input type=\"text\" name=\"BeginDate\" value=\"2016-03\" style=\"width: 80px; padding:2px\"/>" +
75
                "<br />" +
76
                "EndDate:<input type=\"text\" name=\"EndDate\" value=\"2016-06\"  style=\"width: 80px; padding:2px\"/>" +
77
                "<h4>Filters</h4>" +
78
                "<p>You must provide either a Repository Identifier(available prefix: openaire and opendoar) or an Item Identifier(available prefix: openaire, oid and doi)</p>" +
79
                //"<p>Repository Identifier format: namespace:value<br />" +
80
                //"namespace must be either be openaire or repoid<br />" +
81
                //"value is the integer value assigned by CORE or IRUS-UK" +
82
                //"</p>" +
83

  
84
                "RepositoryIdentifier:" +
85
                "<input type=\"text\" name=\"RepositoryIdentifier\">" +
86
                "<br />" +
87
                "ItemIdentifier:" +
88
                "<input type=\"text\" name=\"ItemIdentifier\" value=\"openaire:od_________7::fb90de6f20d79783d05749d8f60417d5\">" +
89

  
90
                //"<p>You may also limit the results to include or exclude items that have a DOI<br />" +
91
                //"Valid values: Yes or No</p>" +
92
                //"hasDOI:" +
93
                //"<select name='hasDOI'>" +
94
                //"<option value=\"\">Not applicable</option>" +
95
                //"<option value=\"Yes\">Yes</option>" +
96
                //"<option value=\"No\">No</option>" +
97
                //"</select>" +
98

  
99
                "<h3>Report Attributes</h3>" +
100
                "<p>Valid Granularity values: Monthly or Totals</p>" +
101
                "Granularity:" +
102
                "<select name='Granularity'>" +
103
                "<option value=\"Monthly\">Monthly</option>" +
104
                "<option value=\"Totals\">Totals</option>" +
105
                "</select>" +
106
                //"<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>" +
107
                //"<input type=\"text\" name=\"Callback\">" +
108
                "<p>The Pretty attribute is just for humans playing with the API and looking at results in a browser.</p>" +
109
                "<p><input type=\"checkbox\" name=\"Pretty\" value=\"Pretty\" checked=\"checked\">Pretty print json(p) for humans</p>" +
110
                "<input type=\"submit\" class=\"button\" value=\"GetReport\"/>" +
111
                "</form>" +
112
                "</body>";
113
    }
114
    */
115

  
116
    /*
117
    @RequestMapping(value = "/sushilite/IR1", method = RequestMethod.GET)
118
    public String ir1Map(){
119
        return "<body>" +
120
                "<h2>OpenAIRE SUSHI Lite Client</h2>" +
121
                "<form action=\"/usagestats/sushilite/GetReport/\" method=\"get\">" +
122
                "<h3>Report Request</h3>" +
123
                "Report Name: " +
124
                "<select name='Report'>" +
125
                "<option value='IR1'>IR1</option>" +
126
                "</select>" +
127
                "<br />" +
128
                "Release: " +
129
                "<select name='Release'>" +
130
                "<option value=\"4\">4</option>" +
131
                "</select>" +
132
                "<br />" +
133
                "Requestor: " +
134
                "<input type=\"text\" name=\"RequestorID\" value=\"anonymous\">" +
135
                "<h3>Report Filters</h3>" +
136
                "<h4>DateRange</h4>" +
137
                "<p>Valid date formats: yyyy-mm-dd or yyyy-mm<br />" +
138
                "Defaults to the last available month if no dates are provided" +
139
                "</p>" +
140
                "BeginDate:<input type=\"text\" name=\"BeginDate\" value=\"2016-03\" style=\"width: 80px; padding:2px\"/>" +
141
                "<br />" +
142
                "EndDate:<input type=\"text\" name=\"EndDate\" value=\"2016-06\"  style=\"width: 80px; padding:2px\"/>" +
143
                "<h4>Filters</h4>" +
144
                "<p>You must provide either a Repository Identifier(available prefix: openaire and opendoar) or an Item Identifier(available prefix: openaire, oid and doi)</p>" +
145
                //"<p>Repository Identifier format: namespace:value<br />" +
146
                //"namespace must be either be openaire or repoid<br />" +
147
                //"value is the integer value assigned by CORE or IRUS-UK" +
148
                //"</p>" +
149

  
150
                "RepositoryIdentifier:" +
151
                "<input type=\"text\" name=\"RepositoryIdentifier\" value=\"openaire:opendoar____::7eabe3a1649ffa2b3ff8c02ebfd5659f\">" +
152
                "<br />" +
153
                "ItemIdentifier:" +
154
                "<input type=\"text\" name=\"ItemIdentifier\">" +
155

  
156
                "<p>Optional filter to only show results for a single item type, e.g. article, book, etc.</p>" +
157
                "ItemDataType:" +
158
                "<select name='ItemDataType'>" +
159
                "<option value=\"\">--- Select ItemDataType ---</option>" +
160
                "<option value='Annotation'>Annotation</option>" +
161
                "<option value='Article'>Article</option>" +
162
                "<option value='Bachelor thesis'>Bachelor thesis</option>" +
163
                "<option value='Book'>Book</option>" +
164
                "<option value='clinical trial'>clinical trial</option>" +
165
                "<option value='Collection'>Collection</option>" +
166
                "<option value='Conference object'>Conference object</option>" +
167
                "<option value='Contribution for newspaper or weekly magazine'>Contribution for newspaper or weekly magazine</option>" +
168
                "<option value='Dataset'>Dataset</option>" +
169
                "<option value='Doctoral thesis'>Doctoral thesis</option>" +
170
                "<option value='Event'>Event</option>" +
171
                "<option value='External research sushilite'>External research sushilite</option>" +
172
                "<option value='Film'>Film</option>" +
173
                "<option value='Image'>Image</option>" +
174
                "<option value='InteractiveResource'>InteractiveResource</option>" +
175
                "<option value='Internal sushilite'>Internal sushilite</option>" +
176
                "<option value='Lecture'>Lecture</option>" +
177
                "<option value='Master thesis'>Master thesis</option>" +
178
                "<option value='Newsletter'>Newsletter</option>" +
179
                "<option value='Other'>Other</option>" +
180
                "<option value='Part of book or chapter of book'>Part of book or chapter of book</option>" +
181
                "<option value='Patent'>Patent</option>" +
182
                "<option value='PhysicalObject'>PhysicalObject</option>" +
183
                "<option value='Preprint'>Preprint</option>" +
184
                "<option value='Report'>Report</option>" +
185
                "<option value='Research'>Research</option>" +
186
                "<option value='Review'>Review</option>" +
187
                "<option value='Software'>Software</option>" +
188
                "<option value='Sound'>Sound</option>" +
189
                "<option value='Unknown'>Unknown</option>" +
190
                "</select>" +
191

  
192
                //"<p>You may also limit the results to include or exclude items that have a DOI<br />" +
193
                //"Valid values: Yes or No</p>" +
194
                //"hasDOI:" +
195
                //"<select name='hasDOI'>" +
196
                //"<option value=\"\">Not applicable</option>" +
197
                //"<option value=\"Yes\">Yes</option>" +
198
                //"<option value=\"No\">No</option>" +
199
                //"</select>" +
200

  
201
                "<h3>Report Attributes</h3>" +
202
                "<p>Valid Granularity values: Monthly or Totals</p>" +
203
                "Granularity:" +
204
                "<select name='Granularity'>" +
205
                "<option value=\"Monthly\">Monthly</option>" +
206
                "<option value=\"Totals\">Totals</option>" +
207
                "</select>" +
208
                //"<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>" +
209
                //"<input type=\"text\" name=\"Callback\">" +
210
                "<p>The Pretty attribute is just for humans playing with the API and looking at results in a browser.</p>" +
211
                "<p><input type=\"checkbox\" name=\"Pretty\" value=\"Pretty\" checked=\"checked\">Pretty print json(p) for humans</p>" +
212
                "<input type=\"submit\" class=\"button\" value=\"GetReport\"/>" +
213
                "</form>" +
214
                "</body>";
215
    }
216
    */
217

  
218
    /*
219
    @RequestMapping(value = "/sushilite/RR1", method = RequestMethod.GET)
220
    public String rr1Map(){
221
        return "<body>" +
222
                "<h2>OpenAIRE SUSHI Lite Client</h2>" +
223
                "<form action=\"/usagestats/sushilite/GetReport/\" method=\"get\">" +
224
                "<h3>Report Request</h3>" +
225
                "Report Name: " +
226
                "<select name='Report'>" +
227
                "<option value='RR1'>RR1</option>" +
228
                "</select>" +
229
                "<br />" +
230
                "Release: " +
231
                "<select name='Release'>" +
232
                "<option value=\"4\">4</option>" +
233
                "</select>" +
234
                "<br />" +
235
                "Requestor: " +
236
                "<input type=\"text\" name=\"RequestorID\" value=\"anonymous\">" +
237
                "<h3>Report Filters</h3>" +
238
                "<h4>DateRange</h4>" +
239
                "<p>Valid date formats: yyyy-mm-dd or yyyy-mm<br />" +
240
                "Defaults to the last available month if no dates are provided" +
241
                "</p>" +
242
                "BeginDate:<input type=\"text\" name=\"BeginDate\" value=\"2016-03\" style=\"width: 80px; padding:2px\"/>" +
243
                "<br />" +
244
                "EndDate:<input type=\"text\" name=\"EndDate\" value=\"2016-06\"  style=\"width: 80px; padding:2px\"/>" +
245
                "<h4>Filters</h4>" +
246
                //"<p>You must provide either a Repository Identifier or an Item Identifier</p>" +
247
                //"<p>Repository Identifier format: namespace:value<br />" +
248
                //"namespace must be either be openaire or repoid<br />" +
249
                //"value is the integer value assigned by CORE or IRUS-UK" +
250
                //"</p>" +
251

  
252
                "RepositoryIdentifier(available prefix: openaire and opendoar):" +
253
                "<input type=\"text\" name=\"RepositoryIdentifier\">" +
254
                "<br />" +
255

  
256
                "<p>Optional filter to only show results for a single item type, e.g. article, book, etc.</p>" +
257
                "ItemDataType:" +
258
                "<select name='ItemDataType'>" +
259
                "<option value=\"\">--- Select ItemDataType ---</option>" +
260
                "<option value='Annotation'>Annotation</option>" +
261
                "<option value='Article'>Article</option>" +
262
                "<option value='Bachelor thesis'>Bachelor thesis</option>" +
263
                "<option value='Book'>Book</option>" +
264
                "<option value='clinical trial'>clinical trial</option>" +
265
                "<option value='Collection'>Collection</option>" +
266
                "<option value='Conference object'>Conference object</option>" +
267
                "<option value='Contribution for newspaper or weekly magazine'>Contribution for newspaper or weekly magazine</option>" +
268
                "<option value='Dataset'>Dataset</option>" +
269
                "<option value='Doctoral thesis'>Doctoral thesis</option>" +
270
                "<option value='Event'>Event</option>" +
271
                "<option value='External research sushilite'>External research sushilite</option>" +
272
                "<option value='Film'>Film</option>" +
273
                "<option value='Image'>Image</option>" +
274
                "<option value='InteractiveResource'>InteractiveResource</option>" +
275
                "<option value='Internal sushilite'>Internal sushilite</option>" +
276
                "<option value='Lecture'>Lecture</option>" +
277
                "<option value='Master thesis'>Master thesis</option>" +
278
                "<option value='Newsletter'>Newsletter</option>" +
279
                "<option value='Other'>Other</option>" +
280
                "<option value='Part of book or chapter of book'>Part of book or chapter of book</option>" +
281
                "<option value='Patent'>Patent</option>" +
282
                "<option value='PhysicalObject'>PhysicalObject</option>" +
283
                "<option value='Preprint'>Preprint</option>" +
284
                "<option value='Report'>Report</option>" +
285
                "<option value='Research'>Research</option>" +
286
                "<option value='Review'>Review</option>" +
287
                "<option value='Software'>Software</option>" +
288
                "<option value='Sound'>Sound</option>" +
289
                "<option value='Unknown'>Unknown</option>" +
290
                "</select>" +
291

  
292
                //"<p>You may also limit the results to include or exclude items that have a DOI<br />" +
293
                //"Valid values: Yes or No</p>" +
294
                //"hasDOI:" +
295
                //"<select name='hasDOI'>" +
296
                //"<option value=\"\">Not applicable</option>" +
297
                //"<option value=\"Yes\">Yes</option>" +
298
                //"<option value=\"No\">No</option>" +
299
                //"</select>" +
300

  
301
                "<h3>Report Attributes</h3>" +
302
                "<p>Valid Granularity values: Monthly or Totals</p>" +
303
                "Granularity:" +
304
                "<select name='Granularity'>" +
305
                "<option value=\"Monthly\">Monthly</option>" +
306
                "<option value=\"Totals\">Totals</option>" +
307
                "</select>" +
308
                //"<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>" +
309
                //"<input type=\"text\" name=\"Callback\">" +
310
                "<p>The Pretty attribute is just for humans playing with the API and looking at results in a browser.</p>" +
311
                "<p><input type=\"checkbox\" name=\"Pretty\" value=\"Pretty\" checked=\"checked\">Pretty print json(p) for humans</p>" +
312
                "<input type=\"submit\" class=\"button\" value=\"GetReport\"/>" +
313
                "</form>" +
314
                "</body>";
315
    }
316
    */
317

  
318
    @RequestMapping(value = "/sushilite/GetReport/", method = RequestMethod.GET)
319
    public String getReport(@RequestParam(value = "Report", defaultValue = "") String reportP, @RequestParam(value = "Release", defaultValue = "4") String release, @RequestParam(value = "RequestorID", defaultValue = "anonymous") String requestorId,
320
                            @RequestParam(value = "BeginDate", defaultValue = "") String beginDate, @RequestParam(value = "EndDate", defaultValue = "") String endDate, @RequestParam(value = "RepositoryIdentifier", defaultValue = "") String repositoryIdentifier,
321
                            @RequestParam(value = "ItemIdentifier", defaultValue = "") String itemIdentifier, @RequestParam(value = "ItemDataType", defaultValue = "") String itemDataType,
322
                            @RequestParam(value = "hasDOI", defaultValue = "") String hasDoi, @RequestParam(value = "Granularity", defaultValue = "Monthly") String granularity, @RequestParam(value = "Callback", defaultValue = "") String callback,
323
                            @RequestParam(value = "Pretty", defaultValue = "") String pretty) throws com.fasterxml.jackson.core.JsonProcessingException{
324
        log.info("Sushi Report request: " + reportP + " from " + requestorId);
325
        log.info("repository identifier: " + repositoryIdentifier + " - item identifier: " + itemIdentifier);
326

  
327
        ObjectMapper objectMapper = new ObjectMapper();
328
        if (pretty.equalsIgnoreCase("pretty")) {
329
            //objectMapper.enable(SerializationFeature.INDENT_OUTPUT);
330
            return "<pre>" + objectMapper.writerWithDefaultPrettyPrinter().writeValueAsString(report.getReport(reportP, release, requestorId, beginDate, endDate, repositoryIdentifier, itemIdentifier, itemDataType, hasDoi, granularity, callback)).replaceAll("/", "\\\\/") + "</pre>";
331
        }
332
        return objectMapper.writeValueAsString(report.getReport(reportP, release, requestorId, beginDate, endDate, repositoryIdentifier, itemIdentifier, itemDataType, hasDoi, granularity, callback)).replaceAll("/", "\\\\/");
333
        //return report.getReport(reportP, release, requestorId, beginDate, endDate, repositoryIdentifier, itemIdentifier, itemDataType, hasDoi, granularity, callback, pretty);
334
    }
335
}
modules/dnet-openaire-usage-stats-api/src/main/java/eu/dnetlib/usagestats/repos/DataSourceBean.java
1
package eu.dnetlib.usagestats.repos;
2

  
3
import org.springframework.boot.autoconfigure.jdbc.DataSourceBuilder;
4
import org.springframework.boot.context.properties.ConfigurationProperties;
5
import org.springframework.context.annotation.Bean;
6
import org.springframework.context.annotation.Configuration;
7
import org.springframework.context.annotation.Primary;
8
import org.springframework.stereotype.Component;
9

  
10
import javax.sql.DataSource;
11

  
12
/**
13
 * Created by tsampikos on 8/3/2017.
14
 */
15
@Configuration
16
@Component
17
class DataSourceBean {
18

  
19
    @ConfigurationProperties(prefix = "usagestats")
20
    @Bean
21
    @Primary
22
    public DataSource getDataSource() {
23
        return DataSourceBuilder
24
                .create()
25
                .build();
26
    }
27
}
modules/dnet-openaire-usage-stats-api/src/main/java/eu/dnetlib/usagestats/repos/DatasourceRepo.java
1
package eu.dnetlib.usagestats.repos;
2

  
3

  
4
import eu.dnetlib.usagestats.portal.UsageStats;
5

  
6
import org.springframework.stereotype.Repository;
7

  
8
import java.util.ArrayList;
9
import java.util.List;
10

  
11

  
12
@Repository
13
public class DatasourceRepo extends BaseRepository {
14

  
15
    public UsageStats getClicks(String id) {
16
        String query = "SELECT 'views', sum(s.count), sum(s.openaire) FROM views_stats s where s.repository_id=? " +
17
                "UNION ALL SELECT 'downloads', sum(s.count), sum(s.openaire) FROM downloads_stats s where s.repository_id=? " +
18
                "UNION ALL SELECT 'pageviews', sum(s.count), '0' FROM pageviews_stats s, result_datasources rd where rd.id=s.result_id and rd.datasource=? ";
19

  
20
        List<String> values = new ArrayList<>();
21
        values.add(id);
22
        values.add(id);
23
        values.add(id);
24

  
25
        return executeUsageStats(query, values, "datasource");
26
    }
27
}
28

  
29

  
modules/dnet-openaire-usage-stats-api/src/main/java/eu/dnetlib/usagestats/repos/SpringRedisConfiguration.java
1
package eu.dnetlib.usagestats.repos;
2

  
3
import org.apache.log4j.Logger;
4
import org.springframework.beans.factory.annotation.Autowired;
5
import org.springframework.beans.factory.annotation.Value;
6
import org.springframework.boot.context.properties.ConfigurationProperties;
7
import org.springframework.context.annotation.Bean;
8
import org.springframework.context.annotation.Configuration;
9
import org.springframework.context.annotation.PropertySource;
10
import org.springframework.data.redis.connection.jedis.JedisConnection;
11
import org.springframework.data.redis.connection.jedis.JedisConnectionFactory;
12
import org.springframework.data.redis.core.RedisTemplate;
13
import org.springframework.data.redis.serializer.StringRedisSerializer;
14
import org.springframework.stereotype.Component;
15

  
16
/**
17
 * Created by tsampikos on 20/4/2017.
18
 */
19
@Component
20
@Configuration
21
@ConfigurationProperties(prefix = "usagestats.redis")
22
class SpringRedisConfiguration {
23

  
24
    private final Logger log = Logger.getLogger(this.getClass());
25

  
26
    @Value("${usagestats.redis.hostname}")
27
    String hostname;
28

  
29
    @Value("${usagestats.redis.port}")
30
    int port;
31

  
32
    @Bean
33
    public JedisConnectionFactory connectionFactory() {
34
        JedisConnectionFactory connectionFactory = new JedisConnectionFactory();
35
        connectionFactory.setHostName(hostname);
36
        connectionFactory.setPort(port);
37
        log.info("Opening redis connection to : " + connectionFactory.getHostName() + ":" + connectionFactory.getPort());
38
        return connectionFactory;
39
    }
40

  
41
    @Bean
42
    @Autowired
43
    public RedisTemplate<String, String> redisTemplate() {
44
        RedisTemplate<String, String> redisTemplate = new RedisTemplate<>();
45
        redisTemplate.setConnectionFactory(connectionFactory());
46
        redisTemplate.setKeySerializer(new StringRedisSerializer());
47
        redisTemplate.setValueSerializer(new StringRedisSerializer());
48
        redisTemplate.setHashKeySerializer(new StringRedisSerializer());
49
        redisTemplate.setHashValueSerializer(new StringRedisSerializer());
50
        return redisTemplate;
51
    }
52
}
modules/dnet-openaire-usage-stats-api/src/main/java/eu/dnetlib/usagestats/repos/ResultRepo.java
1
package eu.dnetlib.usagestats.repos;
2

  
3
import eu.dnetlib.usagestats.portal.UsageStats;
4

  
5
import org.springframework.stereotype.Repository;
6

  
7
import java.util.ArrayList;
8
import java.util.List;
9

  
10
@Repository
11
public class ResultRepo extends BaseRepository {
12

  
13
    public UsageStats getClicks(String id) {
14
        String query = "SELECT 'views', s.repository_id, CASE WHEN s.source='OpenAIRE' THEN d.name ELSE d.name ||' - '|| s.source END, sum(count), sum(openaire) FROM views_stats s, datasource d WHERE s.repository_id=d.id AND s.result_id=? GROUP BY s.source, s.repository_id, d.name " +
15
                "UNION ALL SELECT 'downloads', s.repository_id, CASE WHEN s.source='OpenAIRE' THEN d.name ELSE d.name ||' - '|| s.source END, sum(count), sum(s.openaire) FROM downloads_stats s, datasource d WHERE s.repository_id=d.id AND s.result_id=? GROUP BY s.source, s.repository_id, d.name " +
16
                "UNION ALL SELECT 'pageviews', 'OpenAIRE id', 'OpenAIRE', sum(count), '0' FROM pageviews_stats s WHERE result_id=?;";
17

  
18
        List<String> values = new ArrayList<>();
19
        values.add(id);
20
        values.add(id);
21
        values.add(id);
22

  
23
        return executeUsageStats(query, values, "result");
24
    }
25
}
26

  
27

  
modules/dnet-openaire-usage-stats-api/src/main/java/eu/dnetlib/usagestats/repos/BaseRepository.java
1
package eu.dnetlib.usagestats.repos;
2

  
3
import com.fasterxml.jackson.databind.ObjectMapper;
4

  
5
import eu.dnetlib.usagestats.portal.RepositoryStats;
6
import eu.dnetlib.usagestats.portal.UsageStats;
7
import eu.dnetlib.usagestats.sushilite.ItemIdentifier;
8
import eu.dnetlib.usagestats.sushilite.ItemPerformance;
9
import eu.dnetlib.usagestats.sushilite.ReportItem;
10

  
11
import org.apache.commons.dbutils.DbUtils;
12
import org.apache.log4j.Logger;
13
import org.springframework.beans.factory.annotation.Autowired;
14
import org.springframework.data.redis.core.HashOperations;
15

  
16
import javax.annotation.PostConstruct;
17
import javax.sql.DataSource;
18

  
19
import java.security.MessageDigest;
20
import java.sql.Connection;
21
import java.sql.PreparedStatement;
22
import java.sql.ResultSet;
23
import java.text.SimpleDateFormat;
24
import java.util.Calendar;
25
import java.util.Date;
26
import java.util.List;
27

  
28

  
29
public class BaseRepository {
30
    @Autowired
31
    private DataSourceBean dataSourceBean;
32

  
33
    private DataSource usageStatsDB;
34

  
35
    @Autowired
36
    private SpringRedisConfiguration springRedisConfiguration;
37

  
38
    //private RedisTemplate redisTemplate;
39

  
40
    private HashOperations<String, String, String> jedis;
41

  
42
    private final Logger log = Logger.getLogger(this.getClass());
43

  
44
    @PostConstruct
45
    public void initDB() {
46
        usageStatsDB = dataSourceBean.getDataSource();
47
        //redisTemplate = springRedisConfiguration.redisTemplate();
48
        //jedis = redisTemplate.opsForHash();
49
        jedis = springRedisConfiguration.redisTemplate().opsForHash();
50
    }
51

  
52
    /*
53
    private static Object fromString( String s ) throws Exception {
54
        byte [] data = Base64.getDecoder().decode( s );
55
        ObjectInputStream ois = new ObjectInputStream(
56
                new ByteArrayInputStream(  data ) );
57
        Object o  = ois.readObject();
58
        ois.close();
59
        return o;
60
    }
61

  
62
    private static String toString( Serializable o ) throws Exception {
63
        ByteArrayOutputStream baos = new ByteArrayOutputStream();
64
        ObjectOutputStream oos = new ObjectOutputStream( baos );
65
        oos.writeObject( o );
66
        oos.close();
67
        return Base64.getEncoder().encodeToString(baos.toByteArray());
68
    }
69
    */
70

  
71
    private static String MD5(String string) throws java.security.NoSuchAlgorithmException {
72
        MessageDigest md = MessageDigest.getInstance("MD5");
73
        md.update(string.getBytes());
74

  
75
        byte byteData[] = md.digest();
76
        StringBuilder sb = new StringBuilder();
77
        for (byte aByteData : byteData) {
78
            sb.append(Integer.toString((aByteData & 0xff) + 0x100, 16).substring(1));
79
        }
80

  
81
        return sb.toString();
82
    }
83

  
84
    private static String toJson(Object o) throws com.fasterxml.jackson.core.JsonProcessingException {
85
        ObjectMapper objectMapper = new ObjectMapper();
86
        return objectMapper.writeValueAsString(o);
87
    }
88

  
89
    private static UsageStats fromJson(String string) throws java.io.IOException {
90
        ObjectMapper objectMapper = new ObjectMapper();
91
        return objectMapper.readValue(string, UsageStats.class);
92
    }
93

  
94
    /*
95
    private static List<ReportItem> reportItemsFromJson(String string) throws Exception {
96
        ObjectMapper objectMapper = new ObjectMapper();
97
        return objectMapper.readValue(string, objectMapper.getTypeFactory().constructCollectionType(List.class, ReportItem.class));
98
    }
99
    */
100

  
101
    UsageStats executeUsageStats(String query, List<String> values, String type) {
102
        //HashOperations jedis = redisTemplate.opsForHash();
103

  
104
        UsageStats usageStats = new UsageStats();
105
        int total_views = 0;
106
        int total_downloads = 0;
107
        int page_views = 0;
108
        int openaire_downloads = 0;
109
        int openaire_views = 0;
110
        Connection connection = null;
111
        PreparedStatement st = null;
112
        ResultSet rs = null;
113
        try {
114
            connection = usageStatsDB.getConnection();
115
            st = connection.prepareStatement(query);
116
            int i = 1;
117
            for (String s : values) {
118
                st.setString(i, s);
119
                i++;
120
            }
121

  
122
            String redis_key = MD5(st.toString());
123

  
124
            String redis_result = jedis.get(redis_key, "result");
125
            if (redis_result != null) {
126
                return fromJson(redis_result);
127
            }
128
            /*
129
            if (jedis.hasKey(redis_key, "result")) {
130
                //usageStats = (UsageStats) fromString((String) jedis.get(redis_key, "result"));
131
                usageStats = fromJson(jedis.get(redis_key, "result"));
132
                return usageStats;
133
            }
134
            */
135

  
136
            rs = st.executeQuery();
137

  
138
            if (type.equals("result")) {
139
                while (rs.next()) {
140
                    if (rs.getString(1).equals("views") && rs.getString(4) != null && !rs.getString(4).equals("") && !rs.getString(4).equals("null")) {
141
                        usageStats.addViews(new RepositoryStats(rs.getString(3), rs.getString(2), rs.getString(4), rs.getString(5)));
142
                        total_views += Integer.parseInt(rs.getString(4));
143
                        openaire_views += Integer.parseInt(rs.getString(5));
144
                    } else if (rs.getString(1).equals("downloads") && rs.getString(4) != null && !rs.getString(4).equals("") && !rs.getString(4).equals("null")) {
145
                        usageStats.addDownloads(new RepositoryStats(rs.getString(3), rs.getString(2), rs.getString(4), "0"));
146
                        total_downloads += Integer.parseInt(rs.getString(4));
147
                        openaire_downloads += Integer.parseInt(rs.getString(5));
148
                    } else if (rs.getString(1).equals("pageviews") && rs.getString(4) != null && !rs.getString(4).equals("") && !rs.getString(4).equals("null")) {
149
                        page_views = Integer.parseInt(rs.getString(4));
150
                    }
151
                }
152
                usageStats.setTotal_views(Integer.toString(total_views));
153
                usageStats.setTotal_downloads(Integer.toString(total_downloads));
154
                usageStats.setPageViews(Integer.toString(page_views));
155
                usageStats.setTotal_openaire_views(Integer.toString(openaire_views));
156
                usageStats.setTotal_openaire_downloads(Integer.toString(openaire_downloads));
157
            } else if (type.equals("project") || type.equals("datasource")) {
158
                while (rs.next()) {
159
                    if (rs.getString(1).equals("views") && rs.getString(2) != null && !rs.getString(2).equals("") && !rs.getString(2).equals("null")) {
160
                        total_views += Integer.parseInt(rs.getString(2));
161
                        openaire_views += Integer.parseInt(rs.getString(3));
162
                    } else if (rs.getString(1).equals("downloads") && rs.getString(2) != null && !rs.getString(2).equals("") && !rs.getString(2).equals("null")) {
163
                        total_downloads += Integer.parseInt(rs.getString(2));
164
                        openaire_downloads += Integer.parseInt(rs.getString(3));
165
                    } else if (rs.getString(1).equals("pageviews") && rs.getString(2) != null && !rs.getString(2).equals("") && !rs.getString(2).equals("null")) {
166
                        page_views = Integer.parseInt(rs.getString(2));
167
                    }
168
                    /*
169
                    else if (rs.getString(1).equals("openaire") && rs.getString(2) != null && !rs.getString(2).equals("") && !rs.getString(2).equals("null")) {
170
                        openaire = Integer.parseInt(rs.getString(2));
171
                    }
172
                    */
173

  
174
                }
175
                usageStats.setTotal_views(Integer.toString(total_views));
176
                usageStats.setTotal_downloads(Integer.toString(total_downloads));
177
                usageStats.setPageViews(Integer.toString(page_views));
178
                usageStats.setTotal_openaire_views(Integer.toString(openaire_views));
179
                usageStats.setTotal_openaire_downloads(Integer.toString(openaire_downloads));
180
            }
181

  
182
            jedis.put(redis_key, "persistent", "false");
183
            jedis.put(redis_key, "query", st.toString());
184
            //jedis.put(redis_key, "result", toString(usageStats));
185
            jedis.put(redis_key, "result", toJson(usageStats));
186
            jedis.put(redis_key, "fetchMode", "3");
187

  
188
        } catch (Exception e) {
189
            log.error("Cannot execute query2 : ", e);
190

  
191
        } finally {
192
            DbUtils.closeQuietly(rs);
193
            DbUtils.closeQuietly(st);
194
            DbUtils.closeQuietly(connection);
195
        }
196
        return usageStats;
197
    }
198

  
199
    protected String executeRepoId(String repositoryIdentifier) {
200
        PreparedStatement st = null;
201
        Connection connection = null;
202
        ResultSet rs = null;
203
        try {
204
            connection = usageStatsDB.getConnection();
205
            String[] split = repositoryIdentifier.split(":");
206
            String openaire_id = "-1";
207
            switch (split[0].toLowerCase()) {
208
                case "openaire":
209
                    st = connection.prepareStatement("select id from datasource where id=?");
210
                    st.setString(1, repositoryIdentifier.replaceFirst(split[0] + ":", ""));
211

  
212
                    rs = st.executeQuery();
213
                    while (rs.next()) {
214
                        openaire_id = rs.getString(1);
215
                    }
216
                    return openaire_id;
217

  
218
                case "opendoar":
219
                    st = connection.prepareStatement("select id from datasource_oids where orid=?");
220
                    st.setString(1, "opendoar____::" + repositoryIdentifier.replaceFirst(split[0] + ":", ""));
221

  
222
                    rs = st.executeQuery();
223
                    while (rs.next()) {
224
                        openaire_id = rs.getString(1);
225
                    }
226
                    return openaire_id;
227
                default:
228
                    return "-1";
229
            }
230
        } catch (Exception e) {
231
            log.error("Repository id failed: ", e);
232
        } finally {
233
            DbUtils.closeQuietly(rs);
234
            DbUtils.closeQuietly(st);
235
            DbUtils.closeQuietly(connection);
236
        }
237
        return "-1";
238
    }
239

  
240
    protected void executeItem(List<ReportItem> reportItems, String itemIdentifier, String repositoryIdentifier, String itemDataType, Date beginDate, Date endDate, String granularity) {
241
        String[] split = itemIdentifier.split(":");
242
        switch (split[0].toLowerCase()) {
243
            case "oid":
244
                executeOid(reportItems, itemIdentifier.replaceFirst(split[0] + ":", ""), repositoryIdentifier, itemDataType, beginDate, endDate, granularity);
245
                break;
246
            case "doi":
247
                executeDoi(reportItems, itemIdentifier.replaceFirst(split[0] + ":", ""), repositoryIdentifier, itemDataType, beginDate, endDate, granularity);
248
                break;
249
            case "openaire":
250
                executeOpenaire(reportItems, itemIdentifier.replaceFirst(split[0] + ":", ""), repositoryIdentifier, itemDataType, beginDate, endDate, granularity);
251
                break;
252
            default:
253
        }
254
    }
255

  
256
    private void executeOid(List<ReportItem> reportItems, String oid, String repositoryIdentifier, String itemDataType, Date beginDate, Date endDate, String granularity) {
257
        Connection connection = null;
258
        PreparedStatement st = null;
259
        ResultSet rs = null;
260
        try {
261
            connection = usageStatsDB.getConnection();
262
            st = connection.prepareStatement("SELECT DISTINCT roid.id FROM result_oids roid, downloads_stats s WHERE s.result_id=roid.id AND roid.orid=? UNION SELECT DISTINCT roid.id FROM result_oids roid, views_stats s WHERE s.result_id=roid.id AND roid.orid=?");
263
            st.setString(1, oid);
264
            st.setString(2, oid);
265

  
266
            rs = st.executeQuery();
267

  
268
            while (rs.next()) {
269
                executeOpenaire(reportItems, rs.getString(1), repositoryIdentifier, itemDataType, beginDate, endDate, granularity);
270
            }
271
            connection.close();
272
        } catch (Exception e) {
273
            log.error("Oid to OpenAIRE id failed: ", e);
274
        } finally {
275
            DbUtils.closeQuietly(rs);
276
            DbUtils.closeQuietly(st);
277
            DbUtils.closeQuietly(connection);
278
        }
279
    }
280

  
281
    private void executeDoi(List<ReportItem> reportItems, String doi, String repositoryIdentifier, String itemDataType, Date beginDate, Date endDate, String granularity) {
282
        Connection connection = null;
283
        PreparedStatement st = null;
284
        ResultSet rs = null;
285
        try {
286
            connection = usageStatsDB.getConnection();
287
            st = connection.prepareStatement("SELECT DISTINCT poid.id FROM result_pids poid, downloads_stats s WHERE s.result_id=poid.id AND poid.type='doi' AND poid.pid=? UNION SELECT DISTINCT poid.id FROM result_pids poid, views_stats s WHERE s.result_id=poid.id AND poid.type='doi' AND poid.pid=?");
288
            st.setString(1, doi);
289
            st.setString(2, doi);
290

  
291
            rs = st.executeQuery();
292

  
293
            while (rs.next()) {
294
                executeOpenaire(reportItems, rs.getString(1), repositoryIdentifier, itemDataType, beginDate, endDate, granularity);
295
            }
296
        } catch (Exception e) {
297
            log.error("Doi to OpenAIRE id failed: ", e);
298
        } finally {
299
            DbUtils.closeQuietly(rs);
300
            DbUtils.closeQuietly(st);
301
            DbUtils.closeQuietly(connection);
302
        }
303
    }
304

  
305
    private void executeOpenaire(List<ReportItem> reportItems, String openaire, String repositoryIdentifier, String itemDataType, Date beginDate, Date endDate, String granularity) {
306
        SimpleDateFormat report_dateFormat = new SimpleDateFormat("yyyy-MM-dd");
307
        SimpleDateFormat postgresFormat = new SimpleDateFormat("yyyy/MM");
308
        String beginDateStr = postgresFormat.format(beginDate);
309
        String endDateStr = postgresFormat.format(endDate);
310

  
311
        Connection connection = null;
312
        PreparedStatement st = null;
313
        ResultSet rs = null;
314

  
315
        /*
316
        Calendar startCalendar = Calendar.getInstance();
317
        startCalendar.setTime(beginDate);
318
        Calendar endCalendar = Calendar.getInstance();
319
        endCalendar.setTime(endDate);
320
        int diffYear = endCalendar.get(Calendar.YEAR) - startCalendar.get(Calendar.YEAR);
321
        int diffMonth = diffYear * 12 + endCalendar.get(Calendar.MONTH) - startCalendar.get(Calendar.MONTH);
322
        */
323

  
324
        try {
325
            connection = usageStatsDB.getConnection();
326
            if (repositoryIdentifier.equals("")) {
327
                if (itemDataType.equals("")) {
328
                    st = connection.prepareStatement("SELECT res.repository_id, r.title, r.publisher, r.source, rc.type, pids.pid, d.name, res.ddate, oids.orid, res.downloads, res.views FROM (SELECT coalesce(ds.repository_id, vs.repository_id) AS repository_id, coalesce(ds.result_id, vs.result_id) AS result_id, coalesce(ds.date, vs.date) AS ddate, coalesce(ds.sum, 0) AS downloads, coalesce(vs.sum,0) AS views FROM (SELECT s.repository_id, s.result_id, s.date, sum(s.count) FROM downloads_stats s WHERE s.date>=? AND s.date<=? AND s.result_id=? GROUP BY s.repository_id, s.result_id, s.date) AS ds FULL OUTER JOIN (SELECT s.repository_id, s.result_id, s.date, sum(s.count) FROM views_stats s WHERE s.date>=? AND s.date<=? AND s.result_id=? GROUP BY s.repository_id, s.result_id, s.date) AS vs ON ds.result_id=vs.result_id AND ds.date=vs.date) AS res JOIN result r ON res.result_id=r.id JOIN datasource d ON d.id=res.repository_id JOIN result_classifications rc ON rc.id=r.id LEFT JOIN (SELECT pids.id, string_agg(pids.pid, '#!#') AS pid FROM result_pids pids WHERE pids.id=? AND type='doi' GROUP BY pids.id) AS pids ON pids.id=r.id LEFT JOIN (SELECT oids.id, string_agg(oids.orid, '#!#') AS orid FROM result_oids oids WHERE oids.id=? GROUP BY oids.id) AS oids ON oids.id=r.id ORDER BY res.repository_id, res.ddate;");
329
                    st.setString(1, beginDateStr);
330
                    st.setString(2, endDateStr);
331
                    st.setString(3, openaire);
332
                    st.setString(4, beginDateStr);
333
                    st.setString(5, endDateStr);
334
                    st.setString(6, openaire);
335
                    st.setString(7, openaire);
336
                    st.setString(8, openaire);
337
                } else {
338
                    st = connection.prepareStatement("SELECT res.repository_id, r.title, r.publisher, r.source, rc.type, pids.pid, d.name, res.ddate, oids.orid, res.downloads, res.views FROM (SELECT coalesce(ds.repository_id, vs.repository_id) AS repository_id, coalesce(ds.result_id, vs.result_id) AS result_id, coalesce(ds.date, vs.date) AS ddate, coalesce(ds.sum, 0) AS downloads, coalesce(vs.sum,0) AS views FROM (SELECT s.repository_id, s.result_id, s.date, sum(s.count) FROM downloads_stats s WHERE s.date>=? AND s.date<=? AND s.result_id=? GROUP BY s.repository_id, s.result_id, s.date) AS ds FULL OUTER JOIN (SELECT s.repository_id, s.result_id, s.date, sum(s.count) FROM views_stats s WHERE s.date>=? AND s.date<=? AND s.result_id=? GROUP BY s.repository_id, s.result_id, s.date) AS vs ON ds.result_id=vs.result_id AND ds.date=vs.date) AS res JOIN result r ON res.result_id=r.id JOIN datasource d ON d.id=res.repository_id JOIN result_classifications rc ON rc.id=r.id AND rc.type=? LEFT JOIN (SELECT pids.id, string_agg(pids.pid, '#!#') AS pid FROM result_pids pids WHERE pids.id=? AND type='doi' GROUP BY pids.id) AS pids ON pids.id=r.id LEFT JOIN (SELECT oids.id, string_agg(oids.orid, '#!#') AS orid FROM result_oids oids WHERE oids.id=? GROUP BY oids.id) AS oids ON oids.id=r.id ORDER BY res.repository_id, res.ddate;");
339
                    st.setString(1, beginDateStr);
340
                    st.setString(2, endDateStr);
341
                    st.setString(3, openaire);
342
                    st.setString(4, beginDateStr);
343
                    st.setString(5, endDateStr);
344
                    st.setString(6, openaire);
345
                    st.setString(7, itemDataType);
346
                    st.setString(8, openaire);
347
                    st.setString(9, openaire);
348
                }
349
            } else {
350
                if (itemDataType.equals("")) {
351
                    st = connection.prepareStatement("SELECT res.repository_id, r.title, r.publisher, r.source, rc.type, pids.pid, d.name, res.ddate, oids.orid, res.downloads, res.views FROM (SELECT coalesce(ds.repository_id, vs.repository_id) AS repository_id, coalesce(ds.result_id, vs.result_id) AS result_id, coalesce(ds.date, vs.date) AS ddate, coalesce(ds.sum, 0) AS downloads, coalesce(vs.sum,0) AS views FROM (SELECT s.repository_id, s.result_id, s.date, sum(s.count) FROM downloads_stats s WHERE s.date>=? AND s.date<=? AND s.result_id=? AND s.repository_id=? GROUP BY s.repository_id, s.result_id, s.date) AS ds FULL OUTER JOIN (SELECT s.repository_id, s.result_id, s.date, sum(s.count) FROM views_stats s WHERE s.date>=? AND s.date<=? AND s.result_id=? AND s.repository_id=? GROUP BY s.repository_id, s.result_id, s.date) AS vs ON ds.result_id=vs.result_id AND ds.date=vs.date) AS res JOIN result r ON res.result_id=r.id JOIN datasource d ON d.id=res.repository_id JOIN result_classifications rc ON rc.id=r.id LEFT JOIN (SELECT pids.id, string_agg(pids.pid, '#!#') AS pid FROM result_pids pids WHERE pids.id=? AND type='doi' GROUP BY pids.id) AS pids ON pids.id=r.id LEFT JOIN (SELECT oids.id, string_agg(oids.orid, '#!#') AS orid FROM result_oids oids WHERE oids.id=? GROUP BY oids.id) AS oids ON oids.id=r.id ORDER BY res.repository_id, res.ddate;");
352
                    st.setString(1, beginDateStr);
353
                    st.setString(2, endDateStr);
354
                    st.setString(3, openaire);
355
                    st.setString(4, repositoryIdentifier);
356
                    st.setString(5, beginDateStr);
357
                    st.setString(6, endDateStr);
358
                    st.setString(7, openaire);
359
                    st.setString(8, repositoryIdentifier);
360
                    st.setString(9, openaire);
361
                    st.setString(10, openaire);
362
                } else {
363
                    st = connection.prepareStatement("SELECT res.repository_id, r.title, r.publisher, r.source, rc.type, pids.pid, d.name, res.ddate, oids.orid, res.downloads, res.views FROM (SELECT coalesce(ds.repository_id, vs.repository_id) AS repository_id, coalesce(ds.result_id, vs.result_id) AS result_id, coalesce(ds.date, vs.date) AS ddate, coalesce(ds.sum, 0) AS downloads, coalesce(vs.sum,0) AS views FROM (SELECT s.repository_id, s.result_id, s.date, sum(s.count) FROM downloads_stats s WHERE s.date>=? AND s.date<=? AND s.result_id=? AND s.repository_id=? GROUP BY s.repository_id, s.result_id, s.date) AS ds FULL OUTER JOIN (SELECT s.repository_id, s.result_id, s.date, sum(s.count) FROM views_stats s WHERE s.date>=? AND s.date<=? AND s.result_id=? AND s.repository_id=? GROUP BY s.repository_id, s.result_id, s.date) AS vs ON ds.result_id=vs.result_id AND ds.date=vs.date) AS res JOIN result r ON res.result_id=r.id JOIN datasource d ON d.id=res.repository_id JOIN result_classifications rc ON rc.id=r.id AND rc.type=? LEFT JOIN (SELECT pids.id, string_agg(pids.pid, '#!#') AS pid FROM result_pids pids WHERE pids.id=? AND type='doi' GROUP BY pids.id) AS pids ON pids.id=r.id LEFT JOIN (SELECT oids.id, string_agg(oids.orid, '#!#') AS orid FROM result_oids oids WHERE oids.id=? GROUP BY oids.id) AS oids ON oids.id=r.id ORDER BY res.repository_id, res.ddate;");
364
                    st.setString(1, beginDateStr);
365
                    st.setString(2, endDateStr);
366
                    st.setString(3, openaire);
367
                    st.setString(4, repositoryIdentifier);
368
                    st.setString(5, beginDateStr);
369
                    st.setString(6, endDateStr);
370
                    st.setString(7, openaire);
371
                    st.setString(8, repositoryIdentifier);
372
                    st.setString(9, itemDataType);
373
                    st.setString(10, openaire);
374
                    st.setString(11, openaire);
375
                }
376
            }
377

  
378
            rs = st.executeQuery();
379
            String repository = "";
380
            String lastDate = "";
381
            ReportItem reportItem = null;
382
            int ft_total = 0;
383
            int abstr = 0;
384

  
385
            if (granularity.equalsIgnoreCase("totals")) {
386
                while (rs.next()) {
387
                    if (!rs.getString(1).equals(repository)) {
388
                        if (reportItem != null) {
389
                            reportItem.addPerformance(new ItemPerformance(report_dateFormat.format(beginDate), report_dateFormat.format(endDate), Integer.toString(ft_total), Integer.toString(abstr)));
390
                            reportItems.add(reportItem);
391
                        }
392
                        repository = rs.getString(1);
393
                        reportItem = new ReportItem(rs.getString(3), rs.getString(7), rs.getString(5), rs.getString(2));
394
                        reportItem.addIdentifier(new ItemIdentifier("OpenAIRE", openaire));
395
                        reportItem.addIdentifier(new ItemIdentifier("URLs", rs.getString(4)));
396
                        if (rs.getString(9) != null && !rs.getString(9).equals("")) {
397
                            if (rs.getString(9).contains("#!#")) {
398
                                reportItem.addIdentifier(new ItemIdentifier("OAI", rs.getString(9).substring(0, rs.getString(9).indexOf("#!#"))));
399
                            } else {
400
                                reportItem.addIdentifier(new ItemIdentifier("OAI", rs.getString(9)));
401
                            }
402
                        }
403
                        if (rs.getString(6) != null && !rs.getString(6).equals("")) {
404
                            if (rs.getString(6).contains("#!#")) {
405
                                reportItem.addIdentifier(new ItemIdentifier("DOI", rs.getString(6).substring(0, rs.getString(6).indexOf("#!#"))));
406
                            } else {
407
                                reportItem.addIdentifier(new ItemIdentifier("DOI", rs.getString(6)));
408
                            }
409
                        }
410
                        ft_total = 0;
411
                        abstr = 0;
412
                    }
413
                    ft_total += rs.getInt(10);
414
                    abstr += rs.getInt(11);
415
                }
416
                if (reportItem != null) {
417
                    reportItem.addPerformance(new ItemPerformance(report_dateFormat.format(beginDate), report_dateFormat.format(endDate), Integer.toString(ft_total), Integer.toString(abstr)));
418
                    reportItems.add(reportItem);
419
                }
420
            } else if (granularity.equalsIgnoreCase("monthly")) {
421
                Calendar endCal = Calendar.getInstance();
422
                endCal.setTime(postgresFormat.parse(endDateStr));
423
                endCal.add(Calendar.MONTH, 1);
424
                Date endDateForZeros = endCal.getTime();
425
                while (rs.next()) {
426
                    if (!rs.getString(1).equals(repository)) {
427
                        if (reportItem != null) {
428
                            fillWithZeros(postgresFormat.parse(lastDate), endDateForZeros, reportItem);
429
                            reportItems.add(reportItem);
430
                        }
431
                        repository = rs.getString(1);
432
                        lastDate = beginDateStr;
433
                        reportItem = new ReportItem(rs.getString(3), rs.getString(7), rs.getString(5), rs.getString(2));
434
                        reportItem.addIdentifier(new ItemIdentifier("OpenAIRE", openaire));
435
                        reportItem.addIdentifier(new ItemIdentifier("URLs", rs.getString(4)));
436
                        if (rs.getString(9) != null && !rs.getString(9).equals("")) {
437
                            if (rs.getString(9).contains("#!#")) {
438
                                reportItem.addIdentifier(new ItemIdentifier("OAI", rs.getString(9).substring(0, rs.getString(9).indexOf("#!#"))));
439
                            } else {
440
                                reportItem.addIdentifier(new ItemIdentifier("OAI", rs.getString(9)));
441
                            }
442
                        }
443
                        if (rs.getString(6) != null && !rs.getString(6).equals("")) {
444
                            if (rs.getString(6).contains("#!#")) {
445
                                reportItem.addIdentifier(new ItemIdentifier("DOI", rs.getString(6).substring(0, rs.getString(6).indexOf("#!#"))));
446
                            } else {
447
                                reportItem.addIdentifier(new ItemIdentifier("DOI", rs.getString(6)));
448
                            }
449
                        }
450
                    }
451
                    fillWithZeros(postgresFormat.parse(lastDate), postgresFormat.parse(rs.getString(8)), reportItem);
452
                    Calendar endC = Calendar.getInstance();
453
                    endC.setTime(postgresFormat.parse(rs.getString(8)));
454
                    endC.set(Calendar.DATE, endC.getActualMaximum(Calendar.DATE));
455
                    if (reportItem != null) {
456
                        reportItem.addPerformance(new ItemPerformance(report_dateFormat.format(postgresFormat.parse(rs.getString(8))), report_dateFormat.format(endC.getTime()), rs.getString(10), rs.getString(11)));
457
                    }
458
                    endC.setTime(postgresFormat.parse(rs.getString(8)));
459
                    endC.add(Calendar.MONTH, 1);
460
                    lastDate = postgresFormat.format(endC.getTime());
461
                }
462
                if (reportItem != null) {
463
                    fillWithZeros(postgresFormat.parse(lastDate), endDateForZeros, reportItem);
464
                    reportItems.add(reportItem);
465
                }
466
            }
467
        } catch (Exception e) {
468
            log.error("Single Item Report failed: ", e);
469
        } finally {
470
            DbUtils.closeQuietly(rs);
471
            DbUtils.closeQuietly(st);
472
            DbUtils.closeQuietly(connection);
473
        }
474
    }
475

  
476
    protected void executeRepo(List<ReportItem> reportItems, String repositoryIdentifier, String itemDataType, Date beginDate, Date endDate, String granularity) {
477
        SimpleDateFormat report_dateFormat = new SimpleDateFormat("yyyy-MM-dd");
478
        SimpleDateFormat postgresFormat = new SimpleDateFormat("yyyy/MM");
479
        String beginDateStr = postgresFormat.format(beginDate);
480
        String endDateStr = postgresFormat.format(endDate);
481

  
482
        Connection connection = null;
483
        PreparedStatement st = null;
484
        ResultSet rs = null;
485

  
486
        try {
487
            connection = usageStatsDB.getConnection();
488

  
489
            if (repositoryIdentifier.equals("")) {
490
                if (itemDataType.equals("")) {
491
                    st = connection.prepareStatement("SELECT d.id, d.name, d.websiteurl, dois.orid, res.ddate, res.downloads, res.views FROM (SELECT coalesce(ds.source, vs.source), coalesce(ds.repository_id, vs.repository_id) AS repository_id, coalesce(ds.date, vs.date) AS ddate, coalesce(ds.sum, 0) AS downloads, coalesce(vs.sum,0) AS views FROM (SELECT s.source, s.repository_id, s.date, sum(s.count) FROM downloads_stats s WHERE s.date>=? AND s.date<=? GROUP BY s.source, s.repository_id, s.date) AS ds FULL OUTER JOIN (SELECT s.source, s.repository_id, s.date, sum(s.count) FROM views_stats s WHERE s.date>=? AND s.date<=? GROUP BY s.source, s.repository_id, s.date) AS vs ON ds.source=vs.source AND ds.repository_id=vs.repository_id AND ds.date=vs.date) AS res JOIN datasource d ON d.id=res.repository_id JOIN datasource_oids dois ON d.id=dois.id WHERE dois.orid LIKE 'opendoar%' ORDER BY d.id, d.name, res.ddate ASC;");
492
                    st.setString(1, beginDateStr);
493
                    st.setString(2, endDateStr);
494
                    st.setString(3, beginDateStr);
495
                    st.setString(4, endDateStr);
496
                } else {
497
                    st = connection.prepareStatement("SELECT d.id, d.name, d.websiteurl, dois.orid, res.ddate, res.downloads, res.views FROM (SELECT coalesce(ds.source, vs.source), coalesce(ds.repository_id, vs.repository_id) AS repository_id, coalesce(ds.date, vs.date) AS ddate, coalesce(ds.sum, 0) AS downloads, coalesce(vs.sum,0) AS views FROM (SELECT s.source, s.repository_id, s.date, sum(s.count) FROM downloads_stats s, result_classifications rc WHERE rc.id=s.result_id AND s.date>=? AND s.date<=? AND rc.type=? GROUP BY s.source, s.repository_id, s.date) AS ds FULL OUTER JOIN (SELECT s.source, s.repository_id, s.date, sum(s.count) FROM views_stats s, result_classifications rc WHERE rc.id=s.result_id AND s.date>=? AND s.date<=? AND rc.type=? GROUP BY s.source, s.repository_id, s.date) AS vs ON ds.source=vs.source AND ds.repository_id=vs.repository_id AND ds.date=vs.date) AS res JOIN datasource d ON d.id=res.repository_id JOIN datasource_oids dois ON d.id=dois.id WHERE dois.orid LIKE 'opendoar%' ORDER BY d.id, d.name, res.ddate ASC;");
498
                    st.setString(1, beginDateStr);
499
                    st.setString(2, endDateStr);
500
                    st.setString(3, itemDataType);
501
                    st.setString(4, beginDateStr);
502
                    st.setString(5, endDateStr);
503
                    st.setString(6, itemDataType);
504
                }
505
            } else {
506
                if (itemDataType.equals("")) {
507
                    st = connection.prepareStatement("SELECT d.id, d.name, d.websiteurl, dois.orid, res.ddate, res.downloads, res.views FROM (SELECT coalesce(ds.source, vs.source), coalesce(ds.repository_id, vs.repository_id) AS repository_id, coalesce(ds.date, vs.date) AS ddate, coalesce(ds.sum, 0) AS downloads, coalesce(vs.sum,0) AS views FROM (SELECT s.source, s.repository_id, s.date, sum(s.count) FROM downloads_stats s WHERE s.date>=? AND s.date<=? AND s.repository_id=? GROUP BY s.source, s.repository_id, s.date) AS ds FULL OUTER JOIN (SELECT s.source, s.repository_id, s.date, sum(s.count) FROM views_stats s WHERE s.date>=? AND s.date<=? AND s.repository_id=? GROUP BY s.source, s.repository_id, s.date) AS vs ON ds.source=vs.source AND ds.repository_id=vs.repository_id AND ds.date=vs.date) AS res JOIN datasource d ON d.id=res.repository_id JOIN datasource_oids dois ON d.id=dois.id WHERE dois.orid LIKE 'opendoar%' ORDER BY d.id, d.name, res.ddate ASC;");
508
                    st.setString(1, beginDateStr);
509
                    st.setString(2, endDateStr);
510
                    st.setString(3, repositoryIdentifier);
511
                    st.setString(4, beginDateStr);
512
                    st.setString(5, endDateStr);
513
                    st.setString(6, repositoryIdentifier);
514
                } else {
515
                    st = connection.prepareStatement("SELECT d.id, d.name, d.websiteurl, dois.orid, res.ddate, res.downloads, res.views FROM (SELECT coalesce(ds.source, vs.source), coalesce(ds.repository_id, vs.repository_id) AS repository_id, coalesce(ds.date, vs.date) AS ddate, coalesce(ds.sum, 0) AS downloads, coalesce(vs.sum,0) AS views FROM (SELECT s.source, s.repository_id, s.date, sum(s.count) FROM downloads_stats s, result_classifications rc WHERE rc.id=s.result_id AND s.date>=? AND s.date<=? AND rc.type=? AND s.repository_id=? GROUP BY s.source, s.repository_id, s.date) AS ds FULL OUTER JOIN (SELECT s.source, s.repository_id, s.date, sum(s.count) FROM views_stats s, result_classifications rc WHERE rc.id=s.result_id AND s.date>=? AND s.date<=? AND rc.type=? AND s.repository_id=? GROUP BY s.source, s.repository_id, s.date) AS vs ON ds.source=vs.source AND ds.repository_id=vs.repository_id AND ds.date=vs.date) AS res JOIN datasource d ON d.id=res.repository_id JOIN datasource_oids dois ON d.id=dois.id WHERE dois.orid LIKE 'opendoar%' ORDER BY d.id, d.name, res.ddate ASC;");
516
                    st.setString(1, beginDateStr);
517
                    st.setString(2, endDateStr);
518
                    st.setString(3, itemDataType);
519
                    st.setString(4, repositoryIdentifier);
520
                    st.setString(5, beginDateStr);
521
                    st.setString(6, endDateStr);
522
                    st.setString(7, itemDataType);
523
                    st.setString(8, repositoryIdentifier);
524
                }
525
            }
526
            //log.error("RR STATEMENT:   " + st);
527

  
528
            /*
529
            String redis_key = MD5(st.toString());
530

  
531
            if (jedis.hasKey(redis_key, "result")) {
532
                reportItems.addAll(reportItemsFromJson((String) jedis.get(redis_key, "result")));
533
                st.close();
534
                connection.close();
535
                return;
536
            }
537
            */
538

  
539
            rs = st.executeQuery();
540
            String repository = "";
541
            String lastDate = "";
542
            ReportItem reportItem = null;
543

  
544
            /*
545
            Calendar startCalendar = Calendar.getInstance();
546
            startCalendar.setTime(beginDate);
547
            Calendar endCalendar = Calendar.getInstance();
548
            endCalendar.setTime(endDate);
549
            */
550

  
551
            int ft_total = 0;
552
            int abstr = 0;
553
            if (granularity.equalsIgnoreCase("totals")) {
554
                while (rs.next()) {
555
                    if (!rs.getString(1).equals(repository)) {
556
                        if (reportItem != null) {
557
                            reportItem.addPerformance(new ItemPerformance(report_dateFormat.format(beginDate), report_dateFormat.format(endDate), Integer.toString(ft_total), Integer.toString(abstr)));
558
                            reportItems.add(reportItem);
559
                        }
560
                        repository = rs.getString(1);
561
                        reportItem = new ReportItem(null, rs.getString(2), "Platform", null);
562
                        reportItem.addIdentifier(new ItemIdentifier("OpenAIRE", rs.getString(1)));
563
                        reportItem.addIdentifier(new ItemIdentifier("OpenDOAR", rs.getString(4).substring(rs.getString(4).lastIndexOf(":") + 1)));
564
                        reportItem.addIdentifier(new ItemIdentifier("URL", rs.getString(3)));
565
                        ft_total = 0;
566
                        abstr = 0;
567
                    }
568
                    ft_total += rs.getInt(6);
569
                    abstr += rs.getInt(7);
570
                }
571
                if (reportItem != null) {
572
                    reportItem.addPerformance(new ItemPerformance(report_dateFormat.format(beginDate), report_dateFormat.format(endDate), Integer.toString(ft_total), Integer.toString(abstr)));
573
                    reportItems.add(reportItem);
574
                }
575
            } else if (granularity.equalsIgnoreCase("monthly")) {
576
                Calendar endCal = Calendar.getInstance();
577
                endCal.setTime(postgresFormat.parse(endDateStr));
578
                endCal.add(Calendar.MONTH, 1);
579
                Date endDateForZeros = endCal.getTime();
580
                while (rs.next()) {
581
                    if (!rs.getString(1).equals(repository)) {
582
                        if (reportItem != null) {
583
                            fillWithZeros(postgresFormat.parse(lastDate), endDateForZeros, reportItem);
584
                            reportItems.add(reportItem);
585
                        }
586
                        repository = rs.getString(1);
587
                        lastDate = beginDateStr;
588
                        reportItem = new ReportItem(null, rs.getString(2), "Platform", null);
589
                        reportItem.addIdentifier(new ItemIdentifier("OpenAIRE", rs.getString(1)));
590
                        reportItem.addIdentifier(new ItemIdentifier("OpenDOAR", rs.getString(4).substring(rs.getString(4).lastIndexOf(":") + 1)));
591
                        reportItem.addIdentifier(new ItemIdentifier("URL", rs.getString(3)));
592
                    }
593
                    fillWithZeros(postgresFormat.parse(lastDate), postgresFormat.parse(rs.getString(5)), reportItem);
594
                    Calendar endC = Calendar.getInstance();
595
                    endC.setTime(postgresFormat.parse(rs.getString(5)));
596
                    endC.set(Calendar.DATE, endC.getActualMaximum(Calendar.DATE));
597
                    if (reportItem != null) {
598
                        reportItem.addPerformance(new ItemPerformance(report_dateFormat.format(postgresFormat.parse(rs.getString(5))), report_dateFormat.format(endC.getTime()), rs.getString(6), rs.getString(7)));
599
                    }
600
                    endC.setTime(postgresFormat.parse(rs.getString(5)));
601
                    endC.add(Calendar.MONTH, 1);
602
                    lastDate = postgresFormat.format(endC.getTime());
603
                }
604
                if (reportItem != null) {
605
                    fillWithZeros(postgresFormat.parse(lastDate), endDateForZeros, reportItem);
606
                    reportItems.add(reportItem);
607
                }
608
            }
609

  
610
            /*
611
            jedis.put(redis_key, "persistent", "false");
612
            jedis.put(redis_key, "query", st.toString());
613
            jedis.put(redis_key, "result", toJson(reportItems));
614
            jedis.put(redis_key, "fetchMode", "3");
615
            */
616

  
617
            rs.close();
618
            st.close();
619
            connection.close();
620
        } catch (Exception e) {
621
            log.error("Repository Report failed: ", e);
622
        } finally {
623
            DbUtils.closeQuietly(rs);
624
            DbUtils.closeQuietly(st);
625
            DbUtils.closeQuietly(connection);
626
        }
627
    }
628

  
629
    protected void executeBatchItems(List<ReportItem> reportItems, String repositoryIdentifier, String itemDataType, Date beginDate, Date endDate, String granularity) {
630
        SimpleDateFormat report_dateFormat = new SimpleDateFormat("yyyy-MM-dd");
631
        SimpleDateFormat postgresFormat = new SimpleDateFormat("yyyy/MM");
632
        String beginDateStr = postgresFormat.format(beginDate);
633
        String endDateStr = postgresFormat.format(endDate);
634

  
635
        Connection connection = null;
636
        PreparedStatement st = null;
637
        ResultSet rs = null;
638

  
639
        try {
640
            connection = usageStatsDB.getConnection();
641

  
642
            if (itemDataType.equals("")) {
643
                st = connection.prepareStatement("SELECT res.result_id, r.title, r.publisher, r.source, rc.type, pids.pid, d.name, res.ddate, oids.orid, res.downloads, res.views FROM (SELECT coalesce(ds.repository_id, vs.repository_id) AS repository_id, coalesce(ds.result_id, vs.result_id) AS result_id, coalesce(ds.date, vs.date) AS ddate, coalesce(ds.sum, 0) AS downloads, coalesce(vs.sum,0) AS views FROM (SELECT s.repository_id, s.result_id, s.date, sum(s.count) FROM downloads_stats s WHERE s.date>=? AND s.date<=? AND s.repository_id=? GROUP BY s.repository_id, s.result_id, s.date) AS ds FULL OUTER JOIN (SELECT s.repository_id, s.result_id, s.date, sum(s.count) FROM views_stats s WHERE s.date>=? AND s.date<=? AND s.repository_id=? GROUP BY s.repository_id, s.result_id, s.date) AS vs ON ds.result_id=vs.result_id AND ds.date=vs.date) AS res JOIN result r ON res.result_id=r.id JOIN datasource d ON d.id=res.repository_id JOIN result_classifications rc ON rc.id=r.id LEFT JOIN (SELECT pids.id, string_agg(pids.pid, '#!#') AS pid FROM result_pids pids, result_datasources rd WHERE rd.id=pids.id AND type='doi' AND rd.datasource=? GROUP BY pids.id) AS pids ON pids.id=r.id LEFT JOIN (SELECT oids.id, string_agg(oids.orid, '#!#') AS orid FROM result_oids oids, result_datasources rd WHERE rd.id=oids.id AND rd.datasource=? GROUP BY oids.id) AS oids ON oids.id=r.id ORDER BY res.result_id, res.ddate;");
644
                st.setString(1, beginDateStr);
645
                st.setString(2, endDateStr);
646
                st.setString(3, repositoryIdentifier);
647
                st.setString(4, beginDateStr);
648
                st.setString(5, endDateStr);
649
                st.setString(6, repositoryIdentifier);
650
                st.setString(7, repositoryIdentifier);
651
                st.setString(8, repositoryIdentifier);
652
            } else {
653
                st = connection.prepareStatement("SELECT res.result_id, r.title, r.publisher, r.source, rc.type, pids.pid, d.name, res.ddate, oids.orid, res.downloads, res.views FROM (SELECT coalesce(ds.repository_id, vs.repository_id) AS repository_id, coalesce(ds.result_id, vs.result_id) AS result_id, coalesce(ds.date, vs.date) AS ddate, coalesce(ds.sum, 0) AS downloads, coalesce(vs.sum,0) AS views FROM (SELECT s.repository_id, s.result_id, s.date, sum(s.count) FROM downloads_stats s WHERE s.date>=? AND s.date<=? AND s.repository_id=? GROUP BY s.repository_id, s.result_id, s.date) AS ds FULL OUTER JOIN (SELECT s.repository_id, s.result_id, s.date, sum(s.count) FROM views_stats s WHERE s.date>=? AND s.date<=? AND s.repository_id=? GROUP BY s.repository_id, s.result_id, s.date) AS vs ON ds.result_id=vs.result_id AND ds.date=vs.date) AS res JOIN result r ON res.result_id=r.id JOIN datasource d ON d.id=res.repository_id JOIN result_classifications rc ON rc.id=r.id LEFT JOIN (SELECT pids.id, string_agg(pids.pid, '#!#') AS pid FROM result_pids pids, result_datasources rd WHERE rd.id=pids.id AND type='doi' AND rd.datasource=? GROUP BY pids.id) AS pids ON pids.id=r.id LEFT JOIN (SELECT oids.id, string_agg(oids.orid, '#!#') AS orid FROM result_oids oids, result_datasources rd WHERE rd.id=oids.id AND rd.datasource=? GROUP BY oids.id) AS oids ON oids.id=r.id WHERE rc.type=? ORDER BY res.result_id, res.ddate;");
654
                st.setString(1, beginDateStr);
655
                st.setString(2, endDateStr);
656
                st.setString(3, repositoryIdentifier);
657
                st.setString(4, beginDateStr);
658
                st.setString(5, endDateStr);
659
                st.setString(6, repositoryIdentifier);
660
                st.setString(7, repositoryIdentifier);
661
                st.setString(8, repositoryIdentifier);
662
                st.setString(9, itemDataType);
663
            }
664
            //log.error("IR STATEMENT:   " + st);
665

  
666
            /*
667
            String redis_key = MD5(st.toString());
668

  
669
            if (jedis.hasKey(redis_key, "result")) {
670
                reportItems.addAll(reportItemsFromJson((String) jedis.get(redis_key, "result")));
671
                st.close();
672
                connection.close();
673
                return;
674
            }
675
            */
676

  
677
            rs = st.executeQuery();
678
            String result = "";
679
            String lastDate = "";
680
            ReportItem reportItem = null;
681

  
682
            int ft_total = 0;
683
            int abstr = 0;
684
            if (granularity.equalsIgnoreCase("totals")) {
685
                while (rs.next()) {
686
                    if (!rs.getString(1).equals(result)) {
687
                        if (reportItem != null) {
688
                            reportItem.addPerformance(new ItemPerformance(report_dateFormat.format(beginDate), report_dateFormat.format(endDate), Integer.toString(ft_total), Integer.toString(abstr)));
689
                            reportItems.add(reportItem);
690
                        }
691
                        result = rs.getString(1);
692
                        reportItem = new ReportItem(rs.getString(3), rs.getString(7), rs.getString(5), rs.getString(2));
693
                        reportItem.addIdentifier(new ItemIdentifier("OpenAIRE", rs.getString(1)));
694
                        reportItem.addIdentifier(new ItemIdentifier("URLs", rs.getString(4)));
695
                        if (rs.getString(9) != null && !rs.getString(9).equals("")) {
696
                            if (rs.getString(9).contains("#!#")) {
697
                                reportItem.addIdentifier(new ItemIdentifier("OAI", rs.getString(9).substring(0, rs.getString(9).indexOf("#!#"))));
698
                            } else {
699
                                reportItem.addIdentifier(new ItemIdentifier("OAI", rs.getString(9)));
700
                            }
701
                        }
702
                        if (rs.getString(6) != null && !rs.getString(6).equals("")) {
703
                            if (rs.getString(6).contains("#!#")) {
704
                                reportItem.addIdentifier(new ItemIdentifier("DOI", rs.getString(6).substring(0, rs.getString(6).indexOf("#!#"))));
705
                            } else {
706
                                reportItem.addIdentifier(new ItemIdentifier("DOI", rs.getString(6)));
707
                            }
708
                        }
709
                        ft_total = 0;
710
                        abstr = 0;
711
                    }
712
                    ft_total += rs.getInt(10);
713
                    abstr += rs.getInt(11);
714
                }
715
                if (reportItem != null) {
716
                    reportItem.addPerformance(new ItemPerformance(report_dateFormat.format(beginDate), report_dateFormat.format(endDate), Integer.toString(ft_total), Integer.toString(abstr)));
717
                    reportItems.add(reportItem);
718
                }
719
            } else if (granularity.equalsIgnoreCase("monthly")) {
720
                Calendar endCal = Calendar.getInstance();
721
                endCal.setTime(postgresFormat.parse(endDateStr));
722
                endCal.add(Calendar.MONTH, 1);
723
                Date endDateForZeros = endCal.getTime();
... This diff was truncated because it exceeds the maximum size that can be displayed.

Also available in: Unified diff