Revision 59374
Added by Dimitris Pierrakos over 3 years ago
UsageStatsRepository.java | ||
---|---|---|
6 | 6 |
import org.springframework.stereotype.Repository; |
7 | 7 |
|
8 | 8 |
import com.fasterxml.jackson.databind.ObjectMapper; |
9 |
import eu.dnetlib.usagestats.portal.CountryRepositories; |
|
10 |
import eu.dnetlib.usagestats.portal.CountryUsageStats; |
|
11 |
import eu.dnetlib.usagestats.portal.CountryUsageStatsAll; |
|
9 | 12 |
|
10 | 13 |
import eu.dnetlib.usagestats.portal.MonthlyStats; |
14 |
import eu.dnetlib.usagestats.portal.MonthlyUsageStats; |
|
11 | 15 |
import eu.dnetlib.usagestats.portal.RepositoryStats; |
12 | 16 |
import eu.dnetlib.usagestats.portal.TotalStats; |
17 |
import eu.dnetlib.usagestats.portal.TotalStatsReposViewsDownloads; |
|
13 | 18 |
import eu.dnetlib.usagestats.portal.UsageStats; |
14 | 19 |
import eu.dnetlib.usagestats.portal.YearlyStats; |
15 | 20 |
import eu.dnetlib.usagestats.sushilite.domain.ItemIdentifier; |
... | ... | |
73 | 78 |
ObjectMapper objectMapper = new ObjectMapper(); |
74 | 79 |
return objectMapper.readValue(string, objectMapper.getTypeFactory().constructCollectionType(List.class, ReportItem.class)); |
75 | 80 |
} |
76 |
*/ |
|
81 |
*/ |
|
82 |
public List<MonthlyUsageStats> executeMontlyUsageStats(String query) { |
|
83 |
List<MonthlyUsageStats> montlhyList = new ArrayList<MonthlyUsageStats>(); |
|
77 | 84 |
|
85 |
Connection connection = null; |
|
86 |
PreparedStatement st = null; |
|
87 |
ResultSet rs = null; |
|
88 |
try { |
|
89 |
connection = usageStatsDB.getConnection(); |
|
90 |
log.info(connection.toString()); |
|
91 |
st = connection.prepareStatement(query); |
|
92 |
log.info(st.toString()); |
|
93 |
rs = st.executeQuery(); |
|
94 |
while (rs.next()) { |
|
95 |
MonthlyUsageStats monthlyUsageStats = new MonthlyUsageStats(); |
|
96 |
monthlyUsageStats.addDate(rs.getString(1)); |
|
97 |
monthlyUsageStats.addDownloads(rs.getString(2)); |
|
98 |
monthlyUsageStats.addViews(rs.getString(3)); |
|
99 |
montlhyList.add(monthlyUsageStats); |
|
100 |
} |
|
101 |
|
|
102 |
} catch (Exception e) { |
|
103 |
System.out.println(e); |
|
104 |
} |
|
105 |
|
|
106 |
try { |
|
107 |
jedis.put("test", "result", toJson(montlhyList)); |
|
108 |
jedis.put("test", "persistent", "false"); |
|
109 |
jedis.put("test", "fetchMode", "3"); |
|
110 |
} catch (Exception e) { |
|
111 |
System.out.println(e); |
|
112 |
} |
|
113 |
|
|
114 |
return montlhyList; |
|
115 |
} |
|
116 |
public TotalStatsReposViewsDownloads executeTotalStatsReposViewsDownloads(String query) { |
|
117 |
TotalStatsReposViewsDownloads totalStatsReposViewsDownlads = new TotalStatsReposViewsDownloads(); |
|
118 |
|
|
119 |
String total_repos = " "; |
|
120 |
String views = " "; |
|
121 |
String downloads = " "; |
|
122 |
String redis_key = ""; |
|
123 |
Connection connection = null; |
|
124 |
PreparedStatement st = null; |
|
125 |
ResultSet rs = null; |
|
126 |
try { |
|
127 |
connection = usageStatsDB.getConnection(); |
|
128 |
log.info(connection.toString()); |
|
129 |
st = connection.prepareStatement(query); |
|
130 |
log.info(st.toString()); |
|
131 |
rs = st.executeQuery(); |
|
132 |
redis_key = MD5(st.toString()); |
|
133 |
while (rs.next()) { |
|
134 |
totalStatsReposViewsDownlads.addRepositories(rs.getString(1)); |
|
135 |
totalStatsReposViewsDownlads.addViews(rs.getString(2)); |
|
136 |
totalStatsReposViewsDownlads.addDownloads(rs.getString(3)); |
|
137 |
} |
|
138 |
|
|
139 |
} catch (Exception e) { |
|
140 |
System.out.println(e); |
|
141 |
} |
|
142 |
|
|
143 |
try { |
|
144 |
jedis.put(redis_key, "result", toJson(totalStatsReposViewsDownlads)); |
|
145 |
jedis.put(redis_key, "persistent", "false"); |
|
146 |
jedis.put(redis_key, "fetchMode", "3"); |
|
147 |
} catch (Exception e) { |
|
148 |
System.out.println(e); |
|
149 |
} |
|
150 |
|
|
151 |
return totalStatsReposViewsDownlads; |
|
152 |
} |
|
153 |
public CountryUsageStatsAll executeCountryUsageStats(String query) { |
|
154 |
CountryUsageStatsAll countryListAll = new CountryUsageStatsAll(); |
|
155 |
|
|
156 |
List<CountryUsageStats> countryList = new ArrayList<CountryUsageStats>(); |
|
157 |
|
|
158 |
String date = " "; |
|
159 |
String total_repos = " "; |
|
160 |
String views = " "; |
|
161 |
String downloads = " "; |
|
162 |
String redis_key = "redis_key"; |
|
163 |
Connection connection = null; |
|
164 |
PreparedStatement st = null; |
|
165 |
ResultSet rs = null; |
|
166 |
int total_views = 0; |
|
167 |
int total_downloads = 0; |
|
168 |
|
|
169 |
try { |
|
170 |
connection = usageStatsDB.getConnection(); |
|
171 |
log.info(connection.toString()); |
|
172 |
st = connection.prepareStatement(query); |
|
173 |
log.info(st.toString()); |
|
174 |
rs = st.executeQuery(); |
|
175 |
redis_key = MD5(st.toString()); |
|
176 |
while (rs.next()) { |
|
177 |
CountryUsageStats countryUsageStats = new CountryUsageStats(); |
|
178 |
countryUsageStats.addCountry(rs.getString(1)); |
|
179 |
countryUsageStats.addTotalRepos(rs.getString(2)); |
|
180 |
countryUsageStats.addViews(rs.getString(3)); |
|
181 |
countryUsageStats.addDownloads(rs.getString(4)); |
|
182 |
total_views += Integer.parseInt(rs.getString(3)); |
|
183 |
total_downloads += Integer.parseInt(rs.getString(4)); |
|
184 |
|
|
185 |
countryList.add(countryUsageStats); |
|
186 |
} |
|
187 |
countryListAll.addViewsAll(Integer.toString(total_views)); |
|
188 |
countryListAll.addDownloadsAll(Integer.toString(total_downloads)); |
|
189 |
|
|
190 |
countryListAll.addCountryUsageStats(countryList); |
|
191 |
|
|
192 |
} catch (Exception e) { |
|
193 |
System.out.println(e); |
|
194 |
} |
|
195 |
|
|
196 |
try { |
|
197 |
jedis.put(redis_key, "result", toJson(countryListAll)); |
|
198 |
jedis.put(redis_key, "persistent", "false"); |
|
199 |
jedis.put(redis_key, "fetchMode", "3"); |
|
200 |
} catch (Exception e) { |
|
201 |
System.out.println(e); |
|
202 |
} |
|
203 |
|
|
204 |
return countryListAll; |
|
205 |
} |
|
206 |
public CountryUsageStats executeCountryUsageStats(String query, String country) { |
|
207 |
CountryUsageStats countryUsageStats = new CountryUsageStats(); |
|
208 |
|
|
209 |
String total_repos = " "; |
|
210 |
String views = " "; |
|
211 |
String downloads = " "; |
|
212 |
String redis_key = ""; |
|
213 |
Connection connection = null; |
|
214 |
PreparedStatement st = null; |
|
215 |
ResultSet rs = null; |
|
216 |
int total_views = 0; |
|
217 |
int total_downloads = 0; |
|
218 |
|
|
219 |
try { |
|
220 |
connection = usageStatsDB.getConnection(); |
|
221 |
log.info(connection.toString()); |
|
222 |
st = connection.prepareStatement(query); |
|
223 |
redis_key = MD5(st.toString()); |
|
224 |
st.setString(1, country); |
|
225 |
log.info(st.toString()); |
|
226 |
rs = st.executeQuery(); |
|
227 |
while (rs.next()) { |
|
228 |
countryUsageStats.addCountry(country); |
|
229 |
countryUsageStats.addTotalRepos(rs.getString(1)); |
|
230 |
countryUsageStats.addViews(rs.getString(2)); |
|
231 |
countryUsageStats.addDownloads(rs.getString(3)); |
|
232 |
|
|
233 |
} |
|
234 |
|
|
235 |
} catch (Exception e) { |
|
236 |
System.out.println(e); |
|
237 |
} |
|
238 |
|
|
239 |
try { |
|
240 |
jedis.put(redis_key, "result", toJson(countryUsageStats)); |
|
241 |
jedis.put(redis_key, "persistent", "false"); |
|
242 |
jedis.put(redis_key, "fetchMode", "3"); |
|
243 |
} catch (Exception e) { |
|
244 |
System.out.println(e); |
|
245 |
} |
|
246 |
|
|
247 |
return countryUsageStats; |
|
248 |
} |
|
249 |
|
|
250 |
public List<CountryRepositories> executeCountryRepositories(String query) { |
|
251 |
|
|
252 |
List<CountryRepositories> countryReposList = new ArrayList<CountryRepositories>(); |
|
253 |
|
|
254 |
String country = " "; |
|
255 |
String repository = " "; |
|
256 |
String redis_key = ""; |
|
257 |
Connection connection = null; |
|
258 |
PreparedStatement st = null; |
|
259 |
ResultSet rs = null; |
|
260 |
try { |
|
261 |
connection = usageStatsDB.getConnection(); |
|
262 |
log.info(connection.toString()); |
|
263 |
st = connection.prepareStatement(query); |
|
264 |
log.info(st.toString()); |
|
265 |
rs = st.executeQuery(); |
|
266 |
redis_key = MD5(st.toString()); |
|
267 |
while (rs.next()) { |
|
268 |
CountryRepositories countryRepository = new CountryRepositories(); |
|
269 |
countryRepository.addCountry(rs.getString(1)); |
|
270 |
countryRepository.addRepository(rs.getString(2)); |
|
271 |
countryReposList.add(countryRepository); |
|
272 |
} |
|
273 |
|
|
274 |
} catch (Exception e) { |
|
275 |
System.out.println(e); |
|
276 |
} |
|
277 |
|
|
278 |
try { |
|
279 |
jedis.put(redis_key, "result", toJson(countryReposList)); |
|
280 |
jedis.put(redis_key, "persistent", "false"); |
|
281 |
jedis.put(redis_key, "fetchMode", "3"); |
|
282 |
} catch (Exception e) { |
|
283 |
System.out.println(e); |
|
284 |
} |
|
285 |
|
|
286 |
return countryReposList; |
|
287 |
} |
|
288 |
|
|
289 |
public List<MonthlyUsageStats> executeMontlyUsageStatsForRepo(String query, String datasourceId) { |
|
290 |
List<MonthlyUsageStats> montlhyList = new ArrayList<MonthlyUsageStats>(); |
|
291 |
|
|
292 |
String redis_key = ""; |
|
293 |
Connection connection = null; |
|
294 |
PreparedStatement st = null; |
|
295 |
ResultSet rs = null; |
|
296 |
try { |
|
297 |
connection = usageStatsDB.getConnection(); |
|
298 |
st = connection.prepareStatement(query); |
|
299 |
redis_key = MD5(st.toString()); |
|
300 |
st.setString(1, datasourceId); |
|
301 |
log.info(connection.toString()); |
|
302 |
rs = st.executeQuery(); |
|
303 |
while (rs.next()) { |
|
304 |
MonthlyUsageStats monthlyUsageStats = new MonthlyUsageStats(); |
|
305 |
monthlyUsageStats.addDate(rs.getString(1)); |
|
306 |
monthlyUsageStats.addDownloads(rs.getString(2)); |
|
307 |
monthlyUsageStats.addViews(rs.getString(3)); |
|
308 |
montlhyList.add(monthlyUsageStats); |
|
309 |
} |
|
310 |
|
|
311 |
} catch (Exception e) { |
|
312 |
System.out.println(e); |
|
313 |
} |
|
314 |
|
|
315 |
try { |
|
316 |
jedis.put(redis_key, "result", toJson(montlhyList)); |
|
317 |
jedis.put(redis_key, "persistent", "false"); |
|
318 |
jedis.put(redis_key, "fetchMode", "3"); |
|
319 |
} catch (Exception e) { |
|
320 |
System.out.println(e); |
|
321 |
} |
|
322 |
|
|
323 |
return montlhyList; |
|
324 |
} |
|
325 |
|
|
78 | 326 |
public UsageStats executeUsageStats(String query, List<String> values, String type) { |
79 | 327 |
|
80 | 328 |
UsageStats usageStats = new UsageStats(); |
... | ... | |
137 | 385 |
else if (rs.getString(1).equals("openaire") && rs.getString(2) != null && !rs.getString(2).equals("") && !rs.getString(2).equals("null")) { |
138 | 386 |
openaire = Integer.parseInt(rs.getString(2)); |
139 | 387 |
} |
140 |
*/ |
|
388 |
*/
|
|
141 | 389 |
|
142 | 390 |
} |
143 | 391 |
usageStats.setTotal_views(Integer.toString(total_views)); |
... | ... | |
169 | 417 |
try { |
170 | 418 |
String redis_result = jedis.get("total_stats", "result"); |
171 | 419 |
if (redis_result != null) { |
172 |
totalStats = fromJsonTotalStats(redis_result);
|
|
420 |
totalStats = fromJsonTotalStats(redis_result); |
|
173 | 421 |
} else { |
174 | 422 |
return updateTotalStats(); |
175 | 423 |
} |
... | ... | |
258 | 506 |
return objectMapper.readValue(string, TotalStats.class); |
259 | 507 |
} |
260 | 508 |
|
261 |
|
|
262 | 509 |
public String executeRepoId(String repositoryIdentifier, String report) { |
263 | 510 |
PreparedStatement st = null; |
264 | 511 |
Connection connection = null; |
... | ... | |
269 | 516 |
String openaire_id = "-1"; |
270 | 517 |
switch (split[0].toLowerCase()) { |
271 | 518 |
case "openaire": |
272 |
if(!report.equals("jr1")) { |
|
519 |
if (!report.equals("jr1")) {
|
|
273 | 520 |
st = connection.prepareStatement("select id from public.datasource where id=?"); |
274 | 521 |
st.setString(1, repositoryIdentifier.replaceFirst(split[0] + ":", "")); |
275 | 522 |
} else { |
... | ... | |
284 | 531 |
return openaire_id; |
285 | 532 |
|
286 | 533 |
case "opendoar": |
287 |
if(!report.equals("jr1")) { |
|
534 |
if (!report.equals("jr1")) {
|
|
288 | 535 |
st = connection.prepareStatement("select id from public.datasource_oids where orid=?"); |
289 | 536 |
st.setString(1, "opendoar____::" + repositoryIdentifier.replaceFirst(split[0] + ":", "")); |
290 | 537 |
} else { |
... | ... | |
344 | 591 |
//st = connection.prepareStatement("SELECT DISTINCT roid.id FROM public.result_oids roid, public.downloads_stats s WHERE s.result_id=roid.id AND roid.orid=? UNION SELECT DISTINCT roid.id FROM public.result_oids roid, public.views_stats s WHERE s.result_id=roid.id AND roid.orid=?"); |
345 | 592 |
st = connection.prepareStatement("SELECT DISTINCT roid.id FROM public.result_oids roid, public.usage_stats us WHERE us.result_id=roid.id AND roid.orid=?"); |
346 | 593 |
st.setString(1, oid); |
347 |
st.setString(2, oid); |
|
594 |
//st.setString(2, oid);
|
|
348 | 595 |
|
349 | 596 |
rs = st.executeQuery(); |
350 | 597 |
|
... | ... | |
370 | 617 |
//st = connection.prepareStatement("SELECT DISTINCT poid.id FROM public.result_pids poid, public.downloads_stats s WHERE s.result_id=poid.id AND poid.type='doi' AND poid.pid=? UNION SELECT DISTINCT poid.id FROM public.result_pids poid, public.views_stats s WHERE s.result_id=poid.id AND poid.type='doi' AND poid.pid=?"); |
371 | 618 |
st = connection.prepareStatement("SELECT DISTINCT poid.id FROM public.result_pids poid, public.usage_stats us WHERE us.result_id=poid.id AND poid.type='doi' AND poid.pid=?"); |
372 | 619 |
st.setString(1, doi); |
373 |
st.setString(2, doi); |
|
620 |
//st.setString(2, doi);
|
|
374 | 621 |
|
375 | 622 |
rs = st.executeQuery(); |
376 | 623 |
|
... | ... | |
403 | 650 |
endCalendar.setTime(endDate); |
404 | 651 |
int diffYear = endCalendar.get(Calendar.YEAR) - startCalendar.get(Calendar.YEAR); |
405 | 652 |
int diffMonth = diffYear * 12 + endCalendar.get(Calendar.MONTH) - startCalendar.get(Calendar.MONTH); |
406 |
*/ |
|
407 |
|
|
653 |
*/ |
|
408 | 654 |
try { |
409 | 655 |
connection = usageStatsDB.getConnection(); |
410 | 656 |
if (repositoryIdentifier.equals("")) { |
411 | 657 |
if (itemDataType.equals("")) { |
412 | 658 |
//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 public.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 public.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 public.result r ON res.result_id=r.id JOIN public.datasource d ON d.id=res.repository_id JOIN public.result_classifications rc ON rc.id=r.id LEFT JOIN (SELECT pids.id, string_agg(pids.pid, '#!#') AS pid FROM public.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 public.result_oids oids WHERE oids.id=? GROUP BY oids.id) AS oids ON oids.id=r.id ORDER BY res.repository_id, res.ddate;"); |
413 |
st = connection.prepareStatement("SELECT res.repository_id, r.title, r.publisher, r.source, rc.type, pids.pid, d.name, res.date, oids.orid, res.downloads, res.views FROM (SELECT us.repository_id, us.result_id, us.date, us.downloads, us.views FROM usage_stats us WHERE us.date>=? AND us.date<=? AND us.result_id=?) AS res JOIN public.result r ON res.result_id=r.id JOIN public.datasource d ON d.id=res.repository_id JOIN public.result_classifications rc ON rc.id=r.id LEFT JOIN (SELECT pids.id, string_agg(pids.pid, '#!#') AS pid FROM public.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 public.result_oids oids WHERE oids.id=? GROUP BY oids.id) AS oids ON oids.id=r.id ORDER BY res.repository_id, res.date;"); |
|
659 |
//st = connection.prepareStatement("SELECT res.repository_id, r.title, r.publisher, r.source, rc.type, pids.pid, d.name, res.date, oids.orid, res.downloads, res.views FROM (SELECT us.repository_id, us.result_id, us.date, us.downloads, us.views FROM usage_stats us WHERE us.date>=? AND us.date<=? AND us.result_id=?) AS res JOIN public.result r ON res.result_id=r.id JOIN public.datasource d ON d.id=res.repository_id JOIN public.result_classifications rc ON rc.id=r.id LEFT JOIN (SELECT pids.id, string_agg(pids.pid, '#!#') AS pid FROM public.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 public.result_oids oids WHERE oids.id=? GROUP BY oids.id) AS oids ON oids.id=r.id ORDER BY res.repository_id, res.date;"); |
|
660 |
st = connection.prepareStatement("SELECT distinct res.repository_id, r.title, r.publisher, r.source, rc.type, pids.pid, d.name, res.date, oids.orid, res.downloads, res.views FROM (SELECT us.repository_id, us.result_id, us.date, us.downloads, us.views FROM usage_stats us WHERE us.date>=? AND us.date<=? AND us.result_id=?) AS res JOIN public.result r ON res.result_id=r.id JOIN public.datasource d ON d.id=res.repository_id JOIN (select id, string_agg(type,',') as type FROM public.result_classifications where id=? group by id) rc ON rc.id=r.id LEFT JOIN (SELECT pids.id, string_agg(pids.pid, '#!#') AS pid FROM public.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 public.result_oids oids WHERE oids.id=? GROUP BY oids.id) AS oids ON oids.id=r.id ORDER BY res.repository_id, res.date;"); |
|
414 | 661 |
st.setString(1, beginDateStr); |
415 | 662 |
st.setString(2, endDateStr); |
416 | 663 |
st.setString(3, openaire); |
... | ... | |
419 | 666 |
//st.setString(6, openaire); |
420 | 667 |
st.setString(4, openaire); |
421 | 668 |
st.setString(5, openaire); |
669 |
st.setString(6, openaire); |
|
422 | 670 |
} else { |
423 | 671 |
//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 public.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 public.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 public.result r ON res.result_id=r.id JOIN public.datasource d ON d.id=res.repository_id JOIN public.result_classifications rc ON rc.id=r.id AND rc.type=? LEFT JOIN (SELECT pids.id, string_agg(pids.pid, '#!#') AS pid FROM public.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 public.result_oids oids WHERE oids.id=? GROUP BY oids.id) AS oids ON oids.id=r.id ORDER BY res.repository_id, res.ddate;"); |
424 |
st = connection.prepareStatement("SELECT res.repository_id, r.title, r.publisher, r.source, rc.type, pids.pid, d.name, res.date, oids.orid, res.downloads, res.views FROM (SELECT us.repository_id, us.result_id, us.date, us.downloads, us.views FROM usage_stats us WHERE us.date>=? AND us.date<=? AND us.result_id=?) AS res JOIN public.result r ON res.result_id=r.id JOIN public.datasource d ON d.id=res.repository_id JOIN public.result_classifications rc ON rc.id=r.id AND rc.type=? LEFT JOIN (SELECT pids.id, string_agg(pids.pid, '#!#') AS pid FROM public.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 public.result_oids oids WHERE oids.id=? GROUP BY oids.id) AS oids ON oids.id=r.id ORDER BY res.repository_id, res.date;"); |
|
672 |
st = connection.prepareStatement("SELECT distinct res.repository_id, r.title, r.publisher, r.source, rc.type, pids.pid, d.name, res.date, oids.orid, res.downloads, res.views FROM (SELECT us.repository_id, us.result_id, us.date, us.downloads, us.views FROM usage_stats us WHERE us.date>=? AND us.date<=? AND us.result_id=?) AS res JOIN public.result r ON res.result_id=r.id JOIN public.datasource d ON d.id=res.repository_id JOIN public.result_classifications rc ON rc.id=r.id AND rc.type=? LEFT JOIN (SELECT pids.id, string_agg(pids.pid, '#!#') AS pid FROM public.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 public.result_oids oids WHERE oids.id=? GROUP BY oids.id) AS oids ON oids.id=r.id ORDER BY res.repository_id, res.date;");
|
|
425 | 673 |
st.setString(1, beginDateStr); |
426 | 674 |
st.setString(2, endDateStr); |
427 | 675 |
st.setString(3, openaire); |
... | ... | |
435 | 683 |
} else { |
436 | 684 |
if (itemDataType.equals("")) { |
437 | 685 |
//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 public.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 public.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 public.result r ON res.result_id=r.id JOIN public.datasource d ON d.id=res.repository_id JOIN public.result_classifications rc ON rc.id=r.id LEFT JOIN (SELECT pids.id, string_agg(pids.pid, '#!#') AS pid FROM public.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 public.result_oids oids WHERE oids.id=? GROUP BY oids.id) AS oids ON oids.id=r.id ORDER BY res.repository_id, res.ddate;"); |
438 |
st = connection.prepareStatement("SELECT res.repository_id, r.title, r.publisher, r.source, rc.type, pids.pid, d.name, res.date, oids.orid, res.downloads, res.views FROM (SELECT us.repository_id, us.result_id, us.date, us.downloads, us.views FROM usage_stats us WHERE us.date>=? AND us.date<=? AND us.result_id=? AND us.repository_id=?) AS res JOIN public.result r ON res.result_id=r.id JOIN public.datasource d ON d.id=res.repository_id JOIN public.result_classifications rc ON rc.id=r.id LEFT JOIN (SELECT pids.id, string_agg(pids.pid, '#!#') AS pid FROM public.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 public.result_oids oids WHERE oids.id=? GROUP BY oids.id) AS oids ON oids.id=r.id ORDER BY res.repository_id, res.date;");
|
|
686 |
st = connection.prepareStatement("SELECT distinct res.repository_id, r.title, r.publisher, r.source, rc.type, pids.pid, d.name, res.date, oids.orid, res.downloads, res.views FROM (SELECT us.repository_id, us.result_id, us.date, us.downloads, us.views FROM usage_stats us WHERE us.date>=? AND us.date<=? AND us.result_id=? AND us.repository_id=?) AS res JOIN public.result r ON res.result_id=r.id JOIN public.datasource d ON d.id=res.repository_id JOIN (select id, string_agg(type,',') as type from public.result_classifications where id=? group by id) rc ON rc.id=r.id LEFT JOIN (SELECT pids.id, string_agg(pids.pid, '#!#') AS pid FROM public.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 public.result_oids oids WHERE oids.id=? GROUP BY oids.id) AS oids ON oids.id=r.id ORDER BY res.repository_id, res.date;");
|
|
439 | 687 |
st.setString(1, beginDateStr); |
440 | 688 |
st.setString(2, endDateStr); |
441 | 689 |
st.setString(3, openaire); |
... | ... | |
446 | 694 |
//st.setString(8, repositoryIdentifier); |
447 | 695 |
st.setString(5, openaire); |
448 | 696 |
st.setString(6, openaire); |
697 |
st.setString(7, openaire); |
|
449 | 698 |
} else { |
450 | 699 |
//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 public.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 public.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 public.result r ON res.result_id=r.id JOIN public.datasource d ON d.id=res.repository_id JOIN public.result_classifications rc ON rc.id=r.id AND rc.type=? LEFT JOIN (SELECT pids.id, string_agg(pids.pid, '#!#') AS pid FROM public.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 public.result_oids oids WHERE oids.id=? GROUP BY oids.id) AS oids ON oids.id=r.id ORDER BY res.repository_id, res.ddate;"); |
451 |
st = connection.prepareStatement("SELECT res.repository_id, r.title, r.publisher, r.source, rc.type, pids.pid, d.name, res.date, oids.orid, res.downloads, res.views FROM (SELECT us.repository_id, us.result_id, us.date, us.downloads, us.views FROM usage_stats us WHERE us.date>=? AND us.date<=? AND us.result_id=? AND us.repository_id=?) AS res JOIN public.result r ON res.result_id=r.id JOIN public.datasource d ON d.id=res.repository_id JOIN public.result_classifications rc ON rc.id=r.id AND rc.type=? LEFT JOIN (SELECT pids.id, string_agg(pids.pid, '#!#') AS pid FROM public.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 public.result_oids oids WHERE oids.id=? GROUP BY oids.id) AS oids ON oids.id=r.id ORDER BY res.repository_id, res.date;"); |
|
700 |
st = connection.prepareStatement("SELECT distinctres.repository_id, r.title, r.publisher, r.source, rc.type, pids.pid, d.name, res.date, oids.orid, res.downloads, res.views FROM (SELECT us.repository_id, us.result_id, us.date, us.downloads, us.views FROM usage_stats us WHERE us.date>=? AND us.date<=? AND us.result_id=? AND us.repository_id=?) AS res JOIN public.result r ON res.result_id=r.id JOIN public.datasource d ON d.id=res.repository_id JOIN public.result_classifications rc ON rc.id=r.id AND rc.type=? LEFT JOIN (SELECT pids.id, string_agg(pids.pid, '#!#') AS pid FROM public.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 public.result_oids oids WHERE oids.id=? GROUP BY oids.id) AS oids ON oids.id=r.id ORDER BY res.repository_id, res.date;");
|
|
452 | 701 |
st.setString(1, beginDateStr); |
453 | 702 |
st.setString(2, endDateStr); |
454 | 703 |
st.setString(3, openaire); |
... | ... | |
523 | 772 |
reportItem.addIdentifier(new ItemIdentifier("URLs", rs.getString(4))); |
524 | 773 |
if (rs.getString(9) != null && !rs.getString(9).equals("")) { |
525 | 774 |
if (rs.getString(9).contains("#!#")) { |
526 |
reportItem.addIdentifier(new ItemIdentifier("OAI", rs.getString(9).substring(0, rs.getString(9).indexOf("#!#")))); |
|
775 |
String allOAIs = rs.getString(9); |
|
776 |
String[] oaiArray = allOAIs.split("#!#"); |
|
777 |
for (int i = 0; i < oaiArray.length; i++) { |
|
778 |
reportItem.addIdentifier(new ItemIdentifier("OAI", oaiArray[i])); |
|
779 |
} |
|
780 |
//reportItem.addIdentifier(new ItemIdentifier("OAI", rs.getString(9).substring(0, rs.getString(9).indexOf("#!#")))); |
|
781 |
|
|
782 |
//reportItem.addIdentifier(new ItemIdentifier("OAI", rs.getString(9).substring(0, rs.getString(9).indexOf("#!#")))); |
|
527 | 783 |
} else { |
528 | 784 |
reportItem.addIdentifier(new ItemIdentifier("OAI", rs.getString(9))); |
529 | 785 |
} |
... | ... | |
560 | 816 |
DbUtils.closeQuietly(connection); |
561 | 817 |
} |
562 | 818 |
} |
819 |
|
|
563 | 820 |
public void executeRepo(List<ReportItem> reportItems, String repositoryIdentifier, String itemDataType, Date beginDate, Date endDate, String granularity) { |
564 | 821 |
SimpleDateFormat report_dateFormat = new SimpleDateFormat("yyyy-MM-dd"); |
565 | 822 |
SimpleDateFormat postgresFormat = new SimpleDateFormat("yyyy/MM"); |
... | ... | |
625 | 882 |
connection.close(); |
626 | 883 |
return; |
627 | 884 |
} |
628 |
*/ |
|
629 |
|
|
885 |
*/ |
|
630 | 886 |
rs = st.executeQuery(); |
631 | 887 |
String repository = ""; |
632 | 888 |
String lastDate = ""; |
... | ... | |
637 | 893 |
startCalendar.setTime(beginDate); |
638 | 894 |
Calendar endCalendar = Calendar.getInstance(); |
639 | 895 |
endCalendar.setTime(endDate); |
640 |
*/ |
|
641 |
|
|
896 |
*/ |
|
642 | 897 |
int ft_total = 0; |
643 | 898 |
int abstr = 0; |
644 | 899 |
if (granularity.equalsIgnoreCase("totals")) { |
... | ... | |
703 | 958 |
jedis.put(redis_key, "query", st.toString()); |
704 | 959 |
jedis.put(redis_key, "result", toJson(reportItems)); |
705 | 960 |
jedis.put(redis_key, "fetchMode", "3"); |
706 |
*/ |
|
707 |
|
|
961 |
*/ |
|
708 | 962 |
rs.close(); |
709 | 963 |
st.close(); |
710 | 964 |
connection.close(); |
... | ... | |
716 | 970 |
DbUtils.closeQuietly(connection); |
717 | 971 |
} |
718 | 972 |
} |
973 |
|
|
719 | 974 |
public void executeJournal(List<ReportItem> reportItems, String repositoryIdentifier, String itemDataType, Date beginDate, Date endDate, String granularity) { |
720 | 975 |
SimpleDateFormat report_dateFormat = new SimpleDateFormat("yyyy-MM-dd"); |
721 | 976 |
SimpleDateFormat postgresFormat = new SimpleDateFormat("yyyy/MM"); |
... | ... | |
781 | 1036 |
connection.close(); |
782 | 1037 |
return; |
783 | 1038 |
} |
784 |
*/ |
|
785 |
|
|
1039 |
*/ |
|
786 | 1040 |
rs = st.executeQuery(); |
787 | 1041 |
String repository = ""; |
788 | 1042 |
String lastDate = ""; |
... | ... | |
793 | 1047 |
startCalendar.setTime(beginDate); |
794 | 1048 |
Calendar endCalendar = Calendar.getInstance(); |
795 | 1049 |
endCalendar.setTime(endDate); |
796 |
*/ |
|
797 |
|
|
1050 |
*/ |
|
798 | 1051 |
int ft_total = 0; |
799 | 1052 |
int abstr = 0; |
800 | 1053 |
if (granularity.equalsIgnoreCase("totals")) { |
... | ... | |
808 | 1061 |
reportItem = new ReportItem(null, rs.getString(2), "Platform", null); |
809 | 1062 |
reportItem.addIdentifier(new ItemIdentifier("OpenAIRE", rs.getString(1))); |
810 | 1063 |
reportItem.addIdentifier(new ItemIdentifier("ISSN", rs.getString(4).substring(rs.getString(4).lastIndexOf(":") + 1))); |
811 |
if(rs.getString(3) != null) { |
|
1064 |
if (rs.getString(3) != null) {
|
|
812 | 1065 |
reportItem.addIdentifier(new ItemIdentifier("URL", rs.getString(3))); |
813 | 1066 |
} |
814 | 1067 |
ft_total = 0; |
... | ... | |
837 | 1090 |
reportItem = new ReportItem(null, rs.getString(2), "Platform", null); |
838 | 1091 |
reportItem.addIdentifier(new ItemIdentifier("OpenAIRE", rs.getString(1))); |
839 | 1092 |
reportItem.addIdentifier(new ItemIdentifier("ISSN", rs.getString(4).substring(rs.getString(4).lastIndexOf(":") + 1))); |
840 |
if(rs.getString(3) != null) { |
|
1093 |
if (rs.getString(3) != null) {
|
|
841 | 1094 |
reportItem.addIdentifier(new ItemIdentifier("URL", rs.getString(3))); |
842 | 1095 |
} |
843 | 1096 |
} |
... | ... | |
863 | 1116 |
jedis.put(redis_key, "query", st.toString()); |
864 | 1117 |
jedis.put(redis_key, "result", toJson(reportItems)); |
865 | 1118 |
jedis.put(redis_key, "fetchMode", "3"); |
866 |
*/ |
|
867 |
|
|
1119 |
*/ |
|
868 | 1120 |
rs.close(); |
869 | 1121 |
st.close(); |
870 | 1122 |
connection.close(); |
... | ... | |
892 | 1144 |
|
893 | 1145 |
if (itemDataType.equals("")) { |
894 | 1146 |
//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 public.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 public.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 public.result r ON res.result_id=r.id JOIN public.datasource d ON d.id=res.repository_id JOIN public.result_classifications rc ON rc.id=r.id LEFT JOIN (SELECT pids.id, string_agg(pids.pid, '#!#') AS pid FROM public.result_pids pids, public.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 public.result_oids oids, public.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;"); |
895 |
st = connection.prepareStatement("SELECT res.result_id, r.title, r.publisher, r.source, rc.type, pids.pid, d.name, res.date, oids.orid, res.downloads, res.views FROM (SELECT us.repository_id, us.result_id, us.date, sum(us.downloads) as downloads, sum(us.views) as views FROM public.usage_stats us WHERE us.date>=? AND us.date<=? AND us.repository_id=? GROUP BY us.repository_id, us.result_id, us.date) AS res JOIN public.result r ON res.result_id=r.id JOIN public.datasource d ON d.id=res.repository_id JOIN public.result_classifications rc ON rc.id=r.id LEFT JOIN (SELECT pids.id, string_agg(pids.pid, '#!#') AS pid FROM public.result_pids pids, public.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 public.result_oids oids, public.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.date;"); |
|
1147 |
//st = connection.prepareStatement("SELECT res.result_id, r.title, r.publisher, r.source, rc.type, pids.pid, d.name, res.date, oids.orid, res.downloads, res.views FROM (SELECT us.repository_id, us.result_id, us.date, sum(us.downloads) as downloads, sum(us.views) as views FROM public.usage_stats us WHERE us.date>=? AND us.date<=? AND us.repository_id=? GROUP BY us.repository_id, us.result_id, us.date) AS res JOIN public.result r ON res.result_id=r.id JOIN public.datasource d ON d.id=res.repository_id JOIN public.result_classifications rc ON rc.id=r.id LEFT JOIN (SELECT pids.id, string_agg(pids.pid, '#!#') AS pid FROM public.result_pids pids, public.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 public.result_oids oids, public.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.date;"); |
|
1148 |
st = connection.prepareStatement("SELECT distinct res.result_id, r.title, r.publisher, r.source, rc.type, pids.pid, d.name, res.date, oids.orid, res.downloads, res.views FROM (SELECT us.repository_id, us.result_id, us.date, sum(us.downloads) as downloads, sum(us.views) as views FROM public.usage_stats us WHERE us.date>=? AND us.date<=? AND us.repository_id=? GROUP BY us.repository_id, us.result_id, us.date) AS res JOIN public.result r ON res.result_id=r.id JOIN public.datasource d ON d.id=res.repository_id JOIN public.result_classifications rc ON rc.id=r.id LEFT JOIN (SELECT pids.id, string_agg(pids.pid, '#!#') AS pid FROM public.result_pids pids, public.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 public.result_oids oids, public.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.date;"); |
|
896 | 1149 |
st.setString(1, beginDateStr); |
897 | 1150 |
st.setString(2, endDateStr); |
898 | 1151 |
st.setString(3, repositoryIdentifier); |
... | ... | |
903 | 1156 |
st.setString(5, repositoryIdentifier); |
904 | 1157 |
} else { |
905 | 1158 |
//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 public.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 public.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 public.result r ON res.result_id=r.id JOIN public.datasource d ON d.id=res.repository_id JOIN public.result_classifications rc ON rc.id=r.id LEFT JOIN (SELECT pids.id, string_agg(pids.pid, '#!#') AS pid FROM public.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 public.result_oids oids, public.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;"); |
906 |
st = connection.prepareStatement("SELECT res.result_id, r.title, r.publisher, r.source, rc.type, pids.pid, d.name, res.date, oids.orid, res.downloads, res.views FROM (SELECT us.repository_id, us.result_id, us.date, sum(us.downloads) as downloads, sum(us.views) as views FROM public.usage_stats us WHERE us.date>=? AND us.date<=? AND us.repository_id=? GROUP BY us.repository_id, us.result_id, us.date) AS res JOIN public.result r ON res.result_id=r.id JOIN public.datasource d ON d.id=res.repository_id JOIN public.result_classifications rc ON rc.id=r.id LEFT JOIN (SELECT pids.id, string_agg(pids.pid, '#!#') AS pid FROM public.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 public.result_oids oids, public.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.date;"); |
|
1159 |
st = connection.prepareStatement("SELECT distinct res.result_id, r.title, r.publisher, r.source, rc.type, pids.pid, d.name, res.date, oids.orid, res.downloads, res.views FROM (SELECT us.repository_id, us.result_id, us.date, sum(us.downloads) as downloads, sum(us.views) as views FROM public.usage_stats us WHERE us.date>=? AND us.date<=? AND us.repository_id=? GROUP BY us.repository_id, us.result_id, us.date) AS res JOIN public.result r ON res.result_id=r.id JOIN public.datasource d ON d.id=res.repository_id JOIN public.result_classifications rc ON rc.id=r.id LEFT JOIN (SELECT pids.id, string_agg(pids.pid, '#!#') AS pid FROM public.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 public.result_oids oids, public.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.date;");
|
|
907 | 1160 |
st.setString(1, beginDateStr); |
908 | 1161 |
st.setString(2, endDateStr); |
909 | 1162 |
st.setString(3, repositoryIdentifier); |
... | ... | |
925 | 1178 |
connection.close(); |
926 | 1179 |
return; |
927 | 1180 |
} |
928 |
*/ |
|
929 |
|
|
1181 |
*/ |
|
930 | 1182 |
rs = st.executeQuery(); |
931 | 1183 |
String result = ""; |
932 | 1184 |
String lastDate = ""; |
... | ... | |
1022 | 1274 |
jedis.put(redis_key, "query", st.toString()); |
1023 | 1275 |
jedis.put(redis_key, "result", toJson(reportItems)); |
1024 | 1276 |
jedis.put(redis_key, "fetchMode", "3"); |
1025 |
*/ |
|
1026 |
|
|
1277 |
*/ |
|
1027 | 1278 |
} catch (Exception e) { |
1028 | 1279 |
log.error("Batch Item Report failed: ", e); |
1029 | 1280 |
} finally { |
Also available in: Unified diff
V2