Project

General

Profile

« Previous | Next » 

Revision 57121

Updates to queries, remove schema prefix

View differences:

modules/dnet-openaire-usage-stats-api/branches/usage-stats-api-compression/src/main/java/eu/dnetlib/usagestats/repositories/UsageStatsRepository.java
74 74
        ObjectMapper objectMapper = new ObjectMapper();
75 75
        return objectMapper.readValue(string, objectMapper.getTypeFactory().constructCollectionType(List.class, ReportItem.class));
76 76
    }
77
    */
78

  
77
     */
79 78
    public UsageStats executeUsageStats(String query, List<String> values, String type) {
80 79

  
81 80
        UsageStats usageStats = new UsageStats();
......
89 88
        ResultSet rs = null;
90 89
        try {
91 90
            connection = usageStatsDB.getConnection();
91
            java.sql.Timestamp timestamp1 = new java.sql.Timestamp(System.currentTimeMillis());
92
            //System.out.println("DB start "+timestamp1);
93
            log.info("Query started..." + timestamp1);
92 94
            st = connection.prepareStatement(query);
93 95
            int i = 1;
94 96
            for (String s : values) {
......
138 140
                    else if (rs.getString(1).equals("openaire") && rs.getString(2) != null && !rs.getString(2).equals("") && !rs.getString(2).equals("null")) {
139 141
                        openaire = Integer.parseInt(rs.getString(2));
140 142
                    }
141
                    */
143
                     */
142 144

  
143 145
                }
144 146
                usageStats.setTotal_views(Integer.toString(total_views));
......
161 163
            DbUtils.closeQuietly(rs);
162 164
            DbUtils.closeQuietly(st);
163 165
            DbUtils.closeQuietly(connection);
166
            java.sql.Timestamp timestamp = new java.sql.Timestamp(System.currentTimeMillis());
167
            //System.out.println("DB end "+timestamp);
168
            log.info("Query end..." + timestamp);
169

  
164 170
        }
165 171
        return usageStats;
166 172
    }
......
170 176
        try {
171 177
            String redis_result = jedis.get("total_stats", "result");
172 178
            if (redis_result != null) {
173
                totalStats =  fromJsonTotalStats(redis_result);
179
                totalStats = fromJsonTotalStats(redis_result);
174 180
            } else {
175 181
                return updateTotalStats();
176 182
            }
......
189 195

  
190 196
        try {
191 197
            connection = usageStatsDB.getConnection();
198
            java.sql.Timestamp timestamp1 = new java.sql.Timestamp(System.currentTimeMillis());
199
            //System.out.println("DB start "+timestamp1);
200
            log.info("Query started..." + timestamp1);
201

  
192 202
            //st = connection.prepareStatement("SELECT count(distinct d.repository_id) AS repository, count(distinct d.result_id) AS items, sum(d.count) AS downloads, sum(v.count) AS views from public.downloads_stats d FULL OUTER JOIN public.views_stats v ON d.source=v.source AND d.repository_id=v.repository_id AND d.result_id=v.result_id AND d.date=v.date;");
193 203
            st = connection.prepareStatement("SELECT count(distinct repository_id) AS repository, count(distinct result_id) AS items, sum(downloads) AS downloads, sum(views) AS views from usage_stats");
194 204
            rs = st.executeQuery();
......
201 211
            st.close();
202 212

  
203 213
            //st = connection.prepareStatement("select coalesce(d.date,v.date) as month, count(distinct d.repository_id) as repository, count(distinct d.result_id) as items, sum(d.count) as downloads, sum(v.count) as views from public.downloads_stats d FULL OUTER JOIN public.views_stats v ON d.source=v.source AND d.repository_id=v.repository_id AND d.result_id=v.result_id AND d.date=v.date group by month order by month;");
204
            st = connection.prepareStatement("SELECT date, count(distinct repository_id) AS repository, count(distinct result_id) AS items, sum(downloads) AS downloads, sum(views) AS views FROM shadow.usage_stats GROUP BY date ORDER BY date;");
214
            st = connection.prepareStatement("SELECT date, count(distinct repository_id) AS repository, count(distinct result_id) AS items, sum(downloads) AS downloads, sum(views) AS views FROM usage_stats GROUP BY date ORDER BY date;");
205 215
            rs = st.executeQuery();
206 216
            while (rs.next()) {
207 217
                int year = Integer.parseInt(rs.getString(1).substring(0, 4));
......
226 236
            st.close();
227 237

  
228 238
            //st = connection.prepareStatement("SELECT COALESCE(SUBSTRING(d.date FROM 1 FOR 4), SUBSTRING(v.date FROM 1 FOR 4)) AS year, COUNT(DISTINCT d.repository_id) AS repository, COUNT(DISTINCT d.result_id) AS items, SUM(d.count) AS downloads, SUM(v.count) AS views FROM public.downloads_stats d FULL OUTER JOIN public.views_stats v ON d.source=v.source AND d.repository_id=v.repository_id AND d.result_id=v.result_id AND d.date=v.date GROUP BY year ORDER BY year;");
229
            st = connection.prepareStatement("SELECT SUBSTRING(date FROM 1 FOR 4) AS year, COUNT(DISTINCT repository_id) AS repository, COUNT(DISTINCT result_id) AS items, SUM(downloads) AS downloads, SUM(views) AS views FROM shadow.usage_stats GROUP BY year ORDER BY year;");
239
            st = connection.prepareStatement("SELECT SUBSTRING(date FROM 1 FOR 4) AS year, COUNT(DISTINCT repository_id) AS repository, COUNT(DISTINCT result_id) AS items, SUM(downloads) AS downloads, SUM(views) AS views FROM usage_stats GROUP BY year ORDER BY year;");
230 240
            rs = st.executeQuery();
231 241
            List<YearlyStats> yearlyStatsList = new ArrayList<>();
232 242
            while (rs.next()) {
......
250 260
            DbUtils.closeQuietly(rs);
251 261
            DbUtils.closeQuietly(st);
252 262
            DbUtils.closeQuietly(connection);
263
            java.sql.Timestamp timestamp = new java.sql.Timestamp(System.currentTimeMillis());
264
            //System.out.println("DB end "+timestamp);
265
            log.info("Query end..." + timestamp);
266

  
253 267
        }
254 268
        return totalStats;
255 269
    }
......
259 273
        return objectMapper.readValue(string, TotalStats.class);
260 274
    }
261 275

  
262

  
263 276
    public String executeRepoId(String repositoryIdentifier, String report) {
264 277
        PreparedStatement st = null;
265 278
        Connection connection = null;
266 279
        ResultSet rs = null;
267 280
        try {
268 281
            connection = usageStatsDB.getConnection();
282
            java.sql.Timestamp timestamp1 = new java.sql.Timestamp(System.currentTimeMillis());
283
            //System.out.println("DB start "+timestamp1);
284
            log.info("Query started..." + timestamp1);
285

  
269 286
            String[] split = repositoryIdentifier.split(":");
270 287
            String openaire_id = "-1";
271 288
            switch (split[0].toLowerCase()) {
272 289
                case "openaire":
273
                    if(!report.equals("jr1")) {
274
                        //st = connection.prepareStatement("select id from public.datasource where id=?");
275
                        st = connection.prepareStatement("select id from shadow.datasource where id=?");
290
                    if (!report.equals("jr1")) {
291
                        st = connection.prepareStatement("select id from datasource where id=?");
276 292
                        st.setString(1, repositoryIdentifier.replaceFirst(split[0] + ":", ""));
277 293
                    } else {
278
                        //st = connection.prepareStatement("select id from public.datasource where id=? AND (type='Journal' OR type='Journal Aggregator/Publisher')");
279
                        st = connection.prepareStatement("select id from shadow.datasource where id=? AND (type='Journal' OR type='Journal Aggregator/Publisher')");
294
                        st = connection.prepareStatement("select id from datasource where id=? AND (type='Journal' OR type='Journal Aggregator/Publisher')");
280 295
                        st.setString(1, repositoryIdentifier.replaceFirst(split[0] + ":", ""));
281 296
                    }
282 297

  
......
287 302
                    return openaire_id;
288 303

  
289 304
                case "opendoar":
290
                    if(!report.equals("jr1")) {
291
                        //st = connection.prepareStatement("select id from public.datasource_oids where orid=?");
292
                        st = connection.prepareStatement("select id from shadow.datasource_oids where orid=?");
305
                    if (!report.equals("jr1")) {
306
                        st = connection.prepareStatement("select id from datasource_oids where orid=?");
293 307
                        st.setString(1, "opendoar____::" + repositoryIdentifier.replaceFirst(split[0] + ":", ""));
294 308
                    } else {
295
                        //st = connection.prepareStatement("select distinct d.id from public.datasource d, public.datasource_oids di where di.orid=? and d.id=di.id and (type='Journal' OR type='Journal Aggregator/Publisher')");
296
                        st = connection.prepareStatement("select distinct d.id from shadow.datasource d, shadow.datasource_oids di where di.orid=? and d.id=di.id and (type='Journal' OR type='Journal Aggregator/Publisher')");
309
                        st = connection.prepareStatement("select distinct d.id from datasource d, datasource_oids di where di.orid=? and d.id=di.id and (type='Journal' OR type='Journal Aggregator/Publisher')");
297 310
                        st.setString(1, "opendoar____::" + repositoryIdentifier.replaceFirst(split[0] + ":", ""));
298 311
                    }
299 312

  
......
303 316
                    }
304 317
                    return openaire_id;
305 318
                case "issn":
306
                    //st = connection.prepareStatement("select distinct d.id from public.datasource d, public.datasource_oids di, public.datasource_results dr where d.id=dr.id and di.orid like ? and d.id=di.id and (type='Journal' OR type='Journal Aggregator/Publisher')");
307
                    st = connection.prepareStatement("select distinct d.id from shadow.datasource d, shadow.datasource_oids di, shadow.datasource_results dr where d.id=dr.id and di.orid like ? and d.id=di.id and (type='Journal' OR type='Journal Aggregator/Publisher')");
319
                    st = connection.prepareStatement("select distinct d.id from datasource d, datasource_oids di, datasource_results dr where d.id=dr.id and di.orid like ? and d.id=di.id and (type='Journal' OR type='Journal Aggregator/Publisher')");
308 320
                    st.setString(1, "%" + repositoryIdentifier.replaceFirst(split[0] + ":", "") + "%");
309 321

  
310 322
                    rs = st.executeQuery();
......
321 333
            DbUtils.closeQuietly(rs);
322 334
            DbUtils.closeQuietly(st);
323 335
            DbUtils.closeQuietly(connection);
336
            java.sql.Timestamp timestamp = new java.sql.Timestamp(System.currentTimeMillis());
337
            //System.out.println("DB end "+timestamp);
338
            log.info("Query end..." + timestamp);
339

  
324 340
        }
325 341
        return "-1";
326 342
    }
......
347 363
        ResultSet rs = null;
348 364
        try {
349 365
            connection = usageStatsDB.getConnection();
366
            java.sql.Timestamp timestamp1 = new java.sql.Timestamp(System.currentTimeMillis());
367
            //System.out.println("DB start "+timestamp1);
368
            log.info("Query started..." + timestamp1);
369

  
350 370
            //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=?");
351
            st = connection.prepareStatement("SELECT DISTINCT roid.id FROM shadow.result_oids roid, shadow.usage_stats us WHERE us.result_id=roid.id AND roid.orid=?");
371
            st = connection.prepareStatement("SELECT DISTINCT roid.id FROM result_oids roid, usage_stats us WHERE us.result_id=roid.id AND roid.orid=?");
352 372
            st.setString(1, oid);
353 373
            st.setString(2, oid);
354 374

  
......
364 384
            DbUtils.closeQuietly(rs);
365 385
            DbUtils.closeQuietly(st);
366 386
            DbUtils.closeQuietly(connection);
387
            java.sql.Timestamp timestamp = new java.sql.Timestamp(System.currentTimeMillis());
388
            //System.out.println("DB end "+timestamp);
389
            log.info("Query end..." + timestamp);
390

  
367 391
        }
368 392
    }
369 393

  
......
373 397
        ResultSet rs = null;
374 398
        try {
375 399
            connection = usageStatsDB.getConnection();
400
            java.sql.Timestamp timestamp1 = new java.sql.Timestamp(System.currentTimeMillis());
401
            //System.out.println("DB start "+timestamp1);
402
            log.info("Query started..." + timestamp1);
403

  
376 404
            //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=?");
377
            st = connection.prepareStatement("SELECT DISTINCT poid.id FROM shadow.result_pids poid, shadow.usage_stats us WHERE us.result_id=poid.id AND poid.type='doi' AND poid.pid=?");
405
            st = connection.prepareStatement("SELECT DISTINCT poid.id FROM result_pids poid, usage_stats us WHERE us.result_id=poid.id AND poid.type='doi' AND poid.pid=?");
378 406
            st.setString(1, doi);
379 407
            st.setString(2, doi);
380 408

  
......
389 417
            DbUtils.closeQuietly(rs);
390 418
            DbUtils.closeQuietly(st);
391 419
            DbUtils.closeQuietly(connection);
420
            java.sql.Timestamp timestamp = new java.sql.Timestamp(System.currentTimeMillis());
421
            //System.out.println("DB end "+timestamp);
422
            log.info("Query end..." + timestamp);
423

  
392 424
        }
393 425
    }
394 426

  
......
409 441
        endCalendar.setTime(endDate);
410 442
        int diffYear = endCalendar.get(Calendar.YEAR) - startCalendar.get(Calendar.YEAR);
411 443
        int diffMonth = diffYear * 12 + endCalendar.get(Calendar.MONTH) - startCalendar.get(Calendar.MONTH);
412
        */
413

  
444
         */
414 445
        try {
415 446
            connection = usageStatsDB.getConnection();
447
            java.sql.Timestamp timestamp1 = new java.sql.Timestamp(System.currentTimeMillis());
448
            //System.out.println("DB start "+timestamp1);
449
            log.info("Query started..." + timestamp1);
450

  
416 451
            if (repositoryIdentifier.equals("")) {
417 452
                if (itemDataType.equals("")) {
418 453
                    //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;");
419
                    st = connection.prepareStatement("SELECT res.repository_id, r.publisher, 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 shadow.usage_stats us WHERE us.date>=? AND us.date<=? AND us.result_id=?) AS res JOIN shadow..result r ON res.result_id=r.id JOIN shadow..datasource d ON d.id=res.repository_id JOIN shadow.result_classifications rc ON rc.id=r.id LEFT JOIN (SELECT pids.id, string_agg(pids.pid, '#!#') AS pid FROM shadow.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 shadow.result_oids oids WHERE oids.id=? GROUP BY oids.id) AS oids ON oids.id=r.id ORDER BY res.repository_id, res.date;");
454
                    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 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.date;");
420 455
                    st.setString(1, beginDateStr);
421 456
                    st.setString(2, endDateStr);
422 457
                    st.setString(3, openaire);
......
427 462
                    st.setString(5, openaire);
428 463
                } else {
429 464
                    //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;");
430
                    st = connection.prepareStatement("SELECT res.repository_id, r.publisher, 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 shadow.result r ON res.result_id=r.id JOIN shadow.datasource d ON d.id=res.repository_id JOIN shadow.result_classifications rc ON rc.id=r.id AND rc.type=? LEFT JOIN (SELECT pids.id, string_agg(pids.pid, '#!#') AS pid FROM shadow.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 shadow.result_oids oids WHERE oids.id=? GROUP BY oids.id) AS oids ON oids.id=r.id ORDER BY res.repository_id, res.date;");
465
                    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 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.date;");
431 466
                    st.setString(1, beginDateStr);
432 467
                    st.setString(2, endDateStr);
433 468
                    st.setString(3, openaire);
......
441 476
            } else {
442 477
                if (itemDataType.equals("")) {
443 478
                    //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;");
444
                    st = connection.prepareStatement("SELECT res.repository_id, r.publisher, 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 shadow.result r ON res.result_id=r.id JOIN shadow.datasource d ON d.id=res.repository_id JOIN shadow.result_classifications rc ON rc.id=r.id LEFT JOIN (SELECT pids.id, string_agg(pids.pid, '#!#') AS pid FROM shadow.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 shadow.result_oids oids WHERE oids.id=? GROUP BY oids.id) AS oids ON oids.id=r.id ORDER BY res.repository_id, res.date;");
479
                    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;");
445 480
                    st.setString(1, beginDateStr);
446 481
                    st.setString(2, endDateStr);
447 482
                    st.setString(3, openaire);
......
454 489
                    st.setString(6, openaire);
455 490
                } else {
456 491
                    //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;");
457
                    st = connection.prepareStatement("SELECT res.repository_id, r.publisher, 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 shadow.result r ON res.result_id=r.id JOIN shadow.datasource d ON d.id=res.repository_id JOIN shadow.result_classifications rc ON rc.id=r.id AND rc.type=? LEFT JOIN (SELECT pids.id, string_agg(pids.pid, '#!#') AS pid FROM shadow.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 shadow.result_oids oids WHERE oids.id=? GROUP BY oids.id) AS oids ON oids.id=r.id ORDER BY res.repository_id, res.date;");
492
                    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 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.date;");
458 493
                    st.setString(1, beginDateStr);
459 494
                    st.setString(2, endDateStr);
460 495
                    st.setString(3, openaire);
......
564 599
            DbUtils.closeQuietly(rs);
565 600
            DbUtils.closeQuietly(st);
566 601
            DbUtils.closeQuietly(connection);
602
            java.sql.Timestamp timestamp = new java.sql.Timestamp(System.currentTimeMillis());
603
            //System.out.println("DB end "+timestamp);
604
            log.info("Query end..." + timestamp);
605

  
567 606
        }
568 607
    }
608

  
569 609
    public void executeRepo(List<ReportItem> reportItems, String repositoryIdentifier, String itemDataType, Date beginDate, Date endDate, String granularity) {
570 610
        SimpleDateFormat report_dateFormat = new SimpleDateFormat("yyyy-MM-dd");
571 611
        SimpleDateFormat postgresFormat = new SimpleDateFormat("yyyy/MM");
......
578 618

  
579 619
        try {
580 620
            connection = usageStatsDB.getConnection();
621
            java.sql.Timestamp timestamp1 = new java.sql.Timestamp(System.currentTimeMillis());
622
            //System.out.println("DB start "+timestamp1);
623
            log.info("Query started..." + timestamp1);
581 624

  
582 625
            if (repositoryIdentifier.equals("")) {
583 626
                if (itemDataType.equals("")) {
584 627
                    //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 public.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 public.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 public.datasource d ON d.id=res.repository_id JOIN public.datasource_oids dois ON d.id=dois.id WHERE dois.orid LIKE 'opendoar%' ORDER BY d.id, d.name, res.ddate ASC;");
585
                    st = connection.prepareStatement("SELECT d.id, d.name, d.websiteurl, dois.orid, res.date, res.downloads, res.views FROM (SELECT us.source, us.repository_id, us.date, sum(us.downloads) AS downloads, sum(us.views) AS views FROM shadow.usage_stats us WHERE us.date>=? AND us.date<=? GROUP BY us.source, us.repository_id, us.date) AS res JOIN shadow.datasource d ON d.id=res.repository_id JOIN shadow.datasource_oids dois ON d.id=dois.id WHERE dois.orid LIKE 'opendoar%' ORDER BY d.id, d.name, res.date ASC;");
628
                    st = connection.prepareStatement("SELECT d.id, d.name, d.websiteurl, dois.orid, res.date, res.downloads, res.views FROM (SELECT us.source, us.repository_id, us.date, sum(us.downloads) AS downloads, sum(us.views) AS views FROM usage_stats us WHERE us.date>=? AND us.date<=? GROUP BY us.source, us.repository_id, us.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.date ASC;");
586 629
                    st.setString(1, beginDateStr);
587 630
                    st.setString(2, endDateStr);
588 631
                    //st.setString(3, beginDateStr);
589 632
                    //st.setString(4, endDateStr);
590 633
                } else {
591 634
                    //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 public.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 public.views_stats s, public.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 public.datasource d ON d.id=res.repository_id JOIN public.datasource_oids dois ON d.id=dois.id WHERE dois.orid LIKE 'opendoar%' ORDER BY d.id, d.name, res.ddate ASC;");
592
                    //st = connection.prepareStatement("SELECT d.id, d.name, d.websiteurl, dois.orid, res.date, res.downloads, res.views FROM (SELECT us.source, us.repository_id, us.date, sum(us.downloads) AS downloads, sum(us.views) AS views FROM public.usage_stats us, result_classifications rc WHERE rc.id=us.result_id AND us.date>=? AND us.date<=? AND rc.type=? GROUP BY us.source, us.repository_id, us.date) AS res JOIN public.datasource d ON d.id=res.repository_id JOIN public.datasource_oids dois ON d.id=dois.id WHERE dois.orid LIKE 'opendoar%' ORDER BY d.id, d.name, res.date ASC;");
593
                    st = connection.prepareStatement("SELECT d.id, d.name, d.websiteurl, dois.orid, res.date, res.downloads, res.views FROM (SELECT us.source, us.repository_id, us.date, sum(us.downloads) AS downloads, sum(us.views) AS views FROM shadow.usage_stats us, result_classifications rc WHERE rc.id=us.result_id AND us.date>=? AND us.date<=? AND rc.type=? GROUP BY us.source, us.repository_id, us.date) AS res JOIN shadow.datasource d ON d.id=res.repository_id JOIN shadow.datasource_oids dois ON d.id=dois.id WHERE dois.orid LIKE 'opendoar%' ORDER BY d.id, d.name, res.date ASC;");
635
                    st = connection.prepareStatement("SELECT d.id, d.name, d.websiteurl, dois.orid, res.date, res.downloads, res.views FROM (SELECT us.source, us.repository_id, us.date, sum(us.downloads) AS downloads, sum(us.views) AS views FROM usage_stats us, result_classifications rc WHERE rc.id=us.result_id AND us.date>=? AND us.date<=? AND rc.type=? GROUP BY us.source, us.repository_id, us.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.date ASC;");
594 636
                    st.setString(1, beginDateStr);
595 637
                    st.setString(2, endDateStr);
596 638
                    st.setString(3, itemDataType);
......
601 643
            } else {
602 644
                if (itemDataType.equals("")) {
603 645
                    //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 public.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 public.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 public.datasource d ON d.id=res.repository_id JOIN public.datasource_oids dois ON d.id=dois.id WHERE dois.orid LIKE 'opendoar%' ORDER BY d.id, d.name, res.ddate ASC;");
604
                    st = connection.prepareStatement("SELECT d.id, d.name, d.websiteurl, dois.orid, res.date, res.downloads, res.views FROM (SELECT us.source, us.repository_id, us.date, sum(us.downloads) AS downloads, sum(us.views) AS views FROM shadow.usage_stats us WHERE us.date>=? AND us.date<=? AND us.repository_id=? GROUP BY us.source, us.repository_id, us.date) AS res JOIN shadow.datasource d ON d.id=res.repository_id JOIN shadow.datasource_oids dois ON d.id=dois.id WHERE dois.orid LIKE 'opendoar%' ORDER BY d.id, d.name, res.date ASC;");
646
                    st = connection.prepareStatement("SELECT d.id, d.name, d.websiteurl, dois.orid, res.date, res.downloads, res.views FROM (SELECT us.source, us.repository_id, us.date, sum(us.downloads) AS downloads, sum(us.views) AS views FROM usage_stats us WHERE us.date>=? AND us.date<=? AND us.repository_id=? GROUP BY us.source, us.repository_id, us.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.date ASC;");
605 647
                    st.setString(1, beginDateStr);
606 648
                    st.setString(2, endDateStr);
607 649
                    st.setString(3, repositoryIdentifier);
......
610 652
                    //st.setString(6, repositoryIdentifier);
611 653
                } else {
612 654
                    //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 public.downloads_stats s, public.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 public.views_stats s, public.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 public.datasource d ON d.id=res.repository_id JOIN public.datasource_oids dois ON d.id=dois.id WHERE dois.orid LIKE 'opendoar%' ORDER BY d.id, d.name, res.ddate ASC;");
613
                    st = connection.prepareStatement("SELECT d.id, d.name, d.websiteurl, dois.orid, res.date, res.downloads, res.views FROM (SELECT us.source, us.repository_id, us.date, sum(us.downloads) AS downloads, sum(us.views) AS views FROM shadow.usage_stats us, shadow.result_classifications rc WHERE rc.id=us.result_id AND us.date>=? AND us.date<=? AND rc.type=? AND us.repository_id=? GROUP BY us.source, us.repository_id, us.date) AS res JOIN shadow.datasource d ON d.id=res.repository_id JOIN shadow.datasource_oids dois ON d.id=dois.id WHERE dois.orid LIKE 'opendoar%' ORDER BY d.id, d.name, res.date ASC;");
655
                    st = connection.prepareStatement("SELECT d.id, d.name, d.websiteurl, dois.orid, res.date, res.downloads, res.views FROM (SELECT us.source, us.repository_id, us.date, sum(us.downloads) AS downloads, sum(us.views) AS views FROM usage_stats us, result_classifications rc WHERE rc.id=us.result_id AND us.date>=? AND us.date<=? AND rc.type=? AND us.repository_id=? GROUP BY us.source, us.repository_id, us.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.date ASC;");
614 656
                    st.setString(1, beginDateStr);
615 657
                    st.setString(2, endDateStr);
616 658
                    st.setString(3, itemDataType);
......
632 674
                connection.close();
633 675
                return;
634 676
            }
635
            */
636

  
677
             */
637 678
            rs = st.executeQuery();
638 679
            String repository = "";
639 680
            String lastDate = "";
......
644 685
            startCalendar.setTime(beginDate);
645 686
            Calendar endCalendar = Calendar.getInstance();
646 687
            endCalendar.setTime(endDate);
647
            */
648

  
688
             */
649 689
            int ft_total = 0;
650 690
            int abstr = 0;
651 691
            if (granularity.equalsIgnoreCase("totals")) {
......
710 750
            jedis.put(redis_key, "query", st.toString());
711 751
            jedis.put(redis_key, "result", toJson(reportItems));
712 752
            jedis.put(redis_key, "fetchMode", "3");
713
            */
714

  
753
             */
715 754
            rs.close();
716 755
            st.close();
717 756
            connection.close();
......
721 760
            DbUtils.closeQuietly(rs);
722 761
            DbUtils.closeQuietly(st);
723 762
            DbUtils.closeQuietly(connection);
763
            java.sql.Timestamp timestamp = new java.sql.Timestamp(System.currentTimeMillis());
764
            //System.out.println("DB end "+timestamp);
765
            log.info("Query end..." + timestamp);
766

  
724 767
        }
725 768
    }
769

  
726 770
    public void executeJournal(List<ReportItem> reportItems, String repositoryIdentifier, String itemDataType, Date beginDate, Date endDate, String granularity) {
727 771
        SimpleDateFormat report_dateFormat = new SimpleDateFormat("yyyy-MM-dd");
728 772
        SimpleDateFormat postgresFormat = new SimpleDateFormat("yyyy/MM");
......
735 779

  
736 780
        try {
737 781
            connection = usageStatsDB.getConnection();
782
            java.sql.Timestamp timestamp1 = new java.sql.Timestamp(System.currentTimeMillis());
783
            //System.out.println("DB start "+timestamp1);
784
            log.info("Query started..." + timestamp1);
738 785

  
739 786
            if (repositoryIdentifier.equals("")) {
740 787
                if (itemDataType.equals("")) {
741 788
                    //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 public.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 public.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 public.datasource d ON d.id=res.repository_id JOIN public.datasource_oids dois ON d.id=dois.id WHERE (d.type='Journal' OR d.type='Journal Aggregator/Publisher') ORDER BY d.id, d.name, res.ddate ASC;");
742
                    st = connection.prepareStatement("SELECT d.id, d.name, d.websiteurl, dois.orid, res.date, res.downloads, res.views FROM (SELECT us.source, us.repository_id, us.date, sum(us.downloads) as downloads, sum(us.views) as views FROM shadow.usage_stats us WHERE us.date>=? AND us.date<=? GROUP BY us.source, us.repository_id, us.date) AS res JOIN shadow.datasource d ON d.id=res.repository_id JOIN shadow.datasource_oids dois ON d.id=dois.id WHERE (d.type='Journal' OR d.type='Journal Aggregator/Publisher') ORDER BY d.id, d.name, res.date ASC;");
789
                    st = connection.prepareStatement("SELECT d.id, d.name, d.websiteurl, dois.orid, res.date, res.downloads, res.views FROM (SELECT us.source, us.repository_id, us.date, sum(us.downloads) as downloads, sum(us.views) as views FROM usage_stats us WHERE us.date>=? AND us.date<=? GROUP BY us.source, us.repository_id, us.date) AS res JOIN datasource d ON d.id=res.repository_id JOIN datasource_oids dois ON d.id=dois.id WHERE (d.type='Journal' OR d.type='Journal Aggregator/Publisher') ORDER BY d.id, d.name, res.date ASC;");
743 790
                    st.setString(1, beginDateStr);
744 791
                    st.setString(2, endDateStr);
745 792
                    //st.setString(3, beginDateStr);
746 793
                    //st.setString(4, endDateStr);
747 794
                } else {
748 795
                    //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 public.downloads_stats s, public.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 public.views_stats s, public.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 public.datasource d ON d.id=res.repository_id JOIN public.datasource_oids dois ON d.id=dois.id WHERE (d.type='Journal' OR d.type='Journal Aggregator/Publisher') ORDER BY d.id, d.name, res.ddate ASC;");
749
                    st = connection.prepareStatement("SELECT d.id, d.name, d.websiteurl, dois.orid, res.date, res.downloads, res.views FROM (SELECT us.source, us.repository_id, us.date, sum(us.downloads) as downloads, sum(us.views) as views FROM shadow.usage_stats us, shadow.result_classifications rc WHERE rc.id=us.result_id AND us.date>=? AND us.date<=? AND rc.type=? GROUP BY us.source, us.repository_id, us.date) AS res JOIN shadow.datasource d ON d.id=res.repository_id JOIN shadow.datasource_oids dois ON d.id=dois.id WHERE (d.type='Journal' OR d.type='Journal Aggregator/Publisher') ORDER BY d.id, d.name, res.date ASC;");
796
                    st = connection.prepareStatement("SELECT d.id, d.name, d.websiteurl, dois.orid, res.date, res.downloads, res.views FROM (SELECT us.source, us.repository_id, us.date, sum(us.downloads) as downloads, sum(us.views) as views FROM usage_stats us, result_classifications rc WHERE rc.id=us.result_id AND us.date>=? AND us.date<=? AND rc.type=? GROUP BY us.source, us.repository_id, us.date) AS res JOIN datasource d ON d.id=res.repository_id JOIN datasource_oids dois ON d.id=dois.id WHERE (d.type='Journal' OR d.type='Journal Aggregator/Publisher') ORDER BY d.id, d.name, res.date ASC;");
750 797
                    st.setString(1, beginDateStr);
751 798
                    st.setString(2, endDateStr);
752 799
                    st.setString(3, itemDataType);
......
757 804
            } else {
758 805
                if (itemDataType.equals("")) {
759 806
                    //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 public.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 public.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 public.datasource d ON d.id=res.repository_id JOIN public.datasource_oids dois ON d.id=dois.id WHERE (d.type='Journal' OR d.type='Journal Aggregator/Publisher') ORDER BY d.id, d.name, res.ddate ASC;");
760
                    st = connection.prepareStatement("SELECT d.id, d.name, d.websiteurl, dois.orid, res.date, res.downloads, res.views FROM (SELECT us.source, us.repository_id, us.date, sum(us.downloads) as downloads, sum(us.views) as views FROM shadow.usage_stats us WHERE us.date>=? AND us.date<=? AND us.repository_id=? GROUP BY us.source, us.repository_id, us.date) AS res JOIN shadow.datasource d ON d.id=res.repository_id JOIN shadow.datasource_oids dois ON d.id=dois.id WHERE (d.type='Journal' OR d.type='Journal Aggregator/Publisher') ORDER BY d.id, d.name, res.date ASC;");
807
                    st = connection.prepareStatement("SELECT d.id, d.name, d.websiteurl, dois.orid, res.date, res.downloads, res.views FROM (SELECT us.source, us.repository_id, us.date, sum(us.downloads) as downloads, sum(us.views) as views FROM usage_stats us WHERE us.date>=? AND us.date<=? AND us.repository_id=? GROUP BY us.source, us.repository_id, us.date) AS res JOIN datasource d ON d.id=res.repository_id JOIN datasource_oids dois ON d.id=dois.id WHERE (d.type='Journal' OR d.type='Journal Aggregator/Publisher') ORDER BY d.id, d.name, res.date ASC;");
761 808
                    st.setString(1, beginDateStr);
762 809
                    st.setString(2, endDateStr);
763 810
                    st.setString(3, repositoryIdentifier);
......
766 813
                    //st.setString(6, repositoryIdentifier);
767 814
                } else {
768 815
                    //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 public.downloads_stats s, public.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 public.views_stats s, public.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 public.datasource d ON d.id=res.repository_id JOIN public.datasource_oids dois ON d.id=dois.id WHERE (d.type='Journal' OR d.type='Journal Aggregator/Publisher') ORDER BY d.id, d.name, res.ddate ASC;");
769
                    st = connection.prepareStatement("SELECT d.id, d.name, d.websiteurl, dois.orid, res.date, res.downloads, res.views FROM (SELECT us.source, us.repository_id, us.date, sum(us.downloads) as downloads, sum(us.views) as views FROM shadow.usage_stats us, shadow.result_classifications rc WHERE rc.id=us.result_id AND us.date>=? AND us.date<=? AND rc.type=? AND us.repository_id=? GROUP BY us.source, us.repository_id, us.date) AS res JOIN shadow.datasource d ON d.id=res.repository_id JOIN shadow.datasource_oids dois ON d.id=dois.id WHERE (d.type='Journal' OR d.type='Journal Aggregator/Publisher') ORDER BY d.id, d.name, res.date ASC;");
816
                    st = connection.prepareStatement("SELECT d.id, d.name, d.websiteurl, dois.orid, res.date, res.downloads, res.views FROM (SELECT us.source, us.repository_id, us.date, sum(us.downloads) as downloads, sum(us.views) as views FROM usage_stats us, result_classifications rc WHERE rc.id=us.result_id AND us.date>=? AND us.date<=? AND rc.type=? AND us.repository_id=? GROUP BY us.source, us.repository_id, us.date) AS res JOIN datasource d ON d.id=res.repository_id JOIN datasource_oids dois ON d.id=dois.id WHERE (d.type='Journal' OR d.type='Journal Aggregator/Publisher') ORDER BY d.id, d.name, res.date ASC;");
770 817
                    st.setString(1, beginDateStr);
771 818
                    st.setString(2, endDateStr);
772 819
                    st.setString(3, itemDataType);
......
788 835
                connection.close();
789 836
                return;
790 837
            }
791
            */
792

  
838
             */
793 839
            rs = st.executeQuery();
794 840
            String repository = "";
795 841
            String lastDate = "";
......
800 846
            startCalendar.setTime(beginDate);
801 847
            Calendar endCalendar = Calendar.getInstance();
802 848
            endCalendar.setTime(endDate);
803
            */
804

  
849
             */
805 850
            int ft_total = 0;
806 851
            int abstr = 0;
807 852
            if (granularity.equalsIgnoreCase("totals")) {
......
815 860
                        reportItem = new ReportItem(null, rs.getString(2), "Platform", null);
816 861
                        reportItem.addIdentifier(new ItemIdentifier("OpenAIRE", rs.getString(1)));
817 862
                        reportItem.addIdentifier(new ItemIdentifier("ISSN", rs.getString(4).substring(rs.getString(4).lastIndexOf(":") + 1)));
818
                        if(rs.getString(3) != null) {
863
                        if (rs.getString(3) != null) {
819 864
                            reportItem.addIdentifier(new ItemIdentifier("URL", rs.getString(3)));
820 865
                        }
821 866
                        ft_total = 0;
......
844 889
                        reportItem = new ReportItem(null, rs.getString(2), "Platform", null);
845 890
                        reportItem.addIdentifier(new ItemIdentifier("OpenAIRE", rs.getString(1)));
846 891
                        reportItem.addIdentifier(new ItemIdentifier("ISSN", rs.getString(4).substring(rs.getString(4).lastIndexOf(":") + 1)));
847
                        if(rs.getString(3) != null) {
892
                        if (rs.getString(3) != null) {
848 893
                            reportItem.addIdentifier(new ItemIdentifier("URL", rs.getString(3)));
849 894
                        }
850 895
                    }
......
870 915
            jedis.put(redis_key, "query", st.toString());
871 916
            jedis.put(redis_key, "result", toJson(reportItems));
872 917
            jedis.put(redis_key, "fetchMode", "3");
873
            */
874

  
918
             */
875 919
            rs.close();
876 920
            st.close();
877 921
            connection.close();
......
881 925
            DbUtils.closeQuietly(rs);
882 926
            DbUtils.closeQuietly(st);
883 927
            DbUtils.closeQuietly(connection);
928
            java.sql.Timestamp timestamp = new java.sql.Timestamp(System.currentTimeMillis());
929
            //System.out.println("DB end "+timestamp);
930
            log.info("Query end..." + timestamp);
931

  
884 932
        }
885 933
    }
886 934

  
......
889 937
        SimpleDateFormat postgresFormat = new SimpleDateFormat("yyyy/MM");
890 938
        String beginDateStr = postgresFormat.format(beginDate);
891 939
        String endDateStr = postgresFormat.format(endDate);
892
        
940

  
893 941
        Connection connection = null;
894 942
        PreparedStatement st = null;
895 943
        ResultSet rs = null;
......
898 946
            connection = usageStatsDB.getConnection();
899 947
            java.sql.Timestamp timestamp1 = new java.sql.Timestamp(System.currentTimeMillis());
900 948
            //System.out.println("DB start "+timestamp1);
901
            log.info("Query started..."+timestamp1);
949
            log.info("Query started..." + timestamp1);
902 950
            if (itemDataType.equals("")) {
903 951
                //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;");
904
                st = connection.prepareStatement("SELECT res.result_id, r.publisher, 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 shadow.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 shadow.result r ON res.result_id=r.id JOIN shadow.datasource d ON d.id=res.repository_id JOIN shadow.result_classifications rc ON rc.id=r.id LEFT JOIN (SELECT pids.id, string_agg(pids.pid, '#!#') AS pid FROM shadow.result_pids pids, shadow.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 shadow.result_oids oids, shadow.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;");
905
                
952
                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 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 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.date;");
906 953
                st.setString(1, beginDateStr);
907 954
                st.setString(2, endDateStr);
908 955
                st.setString(3, repositoryIdentifier);
......
914 961
            } else {
915 962
                //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;");
916 963
                //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;");
917
                st = connection.prepareStatement("SELECT res.result_id, r.publisher, 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 shadow.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 shadow.result r ON res.result_id=r.id JOIN shadow.datasource d ON d.id=res.repository_id JOIN shadow.result_classifications rc ON rc.id=r.id LEFT JOIN (SELECT pids.id, string_agg(pids.pid, '#!#') AS pid FROM shadow.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 shadow.result_oids oids, shadow.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;");
964
                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 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 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.date;");
965

  
918 966
                st.setString(1, beginDateStr);
919 967
                st.setString(2, endDateStr);
920 968
                st.setString(3, repositoryIdentifier);
......
936 984
                connection.close();
937 985
                return;
938 986
            }
939
            */
987
             */
940 988
            //connection.setAutoCommit(false);
941 989
            //st.setFetchSize(5000);
942
            
943 990
            rs = st.executeQuery();
944 991
            String result = "";
945 992
            String lastDate = "";
......
955 1002
                            reportItems.add(reportItem);
956 1003
                        }
957 1004
                        result = rs.getString(1);
958
                        //reportItem = new ReportItem(rs.getString(3), rs.getString(7), rs.getString(5), rs.getString(2));
959
                        reportItem = new ReportItem(rs.getString(2), rs.getString(5), " ", " ");
1005
                        reportItem = new ReportItem(rs.getString(3), rs.getString(7), rs.getString(5), rs.getString(2));
960 1006
                        reportItem.addIdentifier(new ItemIdentifier("OpenAIRE", rs.getString(1)));
961
                        //reportItem.addIdentifier(new ItemIdentifier("URLs", rs.getString(4)));
962
                        reportItem.addIdentifier(new ItemIdentifier("URLs", " "));
963
                        //if (rs.getString(9) != null && !rs.getString(9).equals("")) {
964
                        if (rs.getString(7) != null && !rs.getString(7).equals("")) {
965
                            //if (rs.getString(9).contains("#!#")) {
966
                            if (rs.getString(7).contains("#!#")) {
967
                                //reportItem.addIdentifier(new ItemIdentifier("OAI", rs.getString(9).substring(0, rs.getString(9).indexOf("#!#"))));
968
                                reportItem.addIdentifier(new ItemIdentifier("OAI", rs.getString(7).substring(0, rs.getString(7).indexOf("#!#"))));
1007
                        reportItem.addIdentifier(new ItemIdentifier("URLs", rs.getString(4)));
1008
                        if (rs.getString(9) != null && !rs.getString(9).equals("")) {
1009
                            if (rs.getString(9).contains("#!#")) {
1010
                                reportItem.addIdentifier(new ItemIdentifier("OAI", rs.getString(9).substring(0, rs.getString(9).indexOf("#!#"))));
969 1011
                            } else {
970
                                //reportItem.addIdentifier(new ItemIdentifier("OAI", rs.getString(9)));
971
                                reportItem.addIdentifier(new ItemIdentifier("OAI", rs.getString(7)));
1012
                                reportItem.addIdentifier(new ItemIdentifier("OAI", rs.getString(9)));
972 1013
                            }
973 1014
                        }
974
                        //if (rs.getString(6) != null && !rs.getString(6).equals("")) {
975
                        if (rs.getString(4) != null && !rs.getString(6).equals("")) {
976
                            //if (rs.getString(6).contains("#!#")) {
977
                            if (rs.getString(4).contains("#!#")) {
978
                                //reportItem.addIdentifier(new ItemIdentifier("DOI", rs.getString(6).substring(0, rs.getString(6).indexOf("#!#"))));
979
                                reportItem.addIdentifier(new ItemIdentifier("DOI", rs.getString(4).substring(0, rs.getString(4).indexOf("#!#"))));
1015
                        if (rs.getString(6) != null && !rs.getString(6).equals("")) {
1016
                            if (rs.getString(6).contains("#!#")) {
1017
                                reportItem.addIdentifier(new ItemIdentifier("DOI", rs.getString(6).substring(0, rs.getString(6).indexOf("#!#"))));
980 1018
                            } else {
981
                                //reportItem.addIdentifier(new ItemIdentifier("DOI", rs.getString(6)));
982
                                reportItem.addIdentifier(new ItemIdentifier("DOI", rs.getString(4)));
1019
                                reportItem.addIdentifier(new ItemIdentifier("DOI", rs.getString(6)));
983 1020
                            }
984 1021
                        }
985 1022
                        ft_total = 0;
986 1023
                        abstr = 0;
987 1024
                    }
988
                    //ft_total += rs.getInt(10);
989
                    ft_total += rs.getInt(8);
990
                    //abstr += rs.getInt(11);
991
                    abstr += rs.getInt(9);
1025
                    ft_total += rs.getInt(10);
1026
                    abstr += rs.getInt(11);
992 1027
                }
993 1028
                if (reportItem != null) {
994 1029
                    reportItem.addPerformance(new ItemPerformance(report_dateFormat.format(beginDate), report_dateFormat.format(endDate), Integer.toString(ft_total), Integer.toString(abstr)));
......
1007 1042
                        }
1008 1043
                        result = rs.getString(1);
1009 1044
                        lastDate = beginDateStr;
1010
                        //reportItem = new ReportItem(rs.getString(3), rs.getString(7), rs.getString(5), rs.getString(2));
1011
                        reportItem = new ReportItem(rs.getString(2), rs.getString(5), rs.getString(3), " ");
1045
                        reportItem = new ReportItem(rs.getString(3), rs.getString(7), rs.getString(5), rs.getString(2));
1012 1046
                        reportItem.addIdentifier(new ItemIdentifier("OpenAIRE", rs.getString(1)));
1013 1047
                        reportItem.addIdentifier(new ItemIdentifier("URLs", rs.getString(4)));
1014
                        //if (rs.getString(9) != null && !rs.getString(9).equals("")) {
1015
                        if (rs.getString(7) != null && !rs.getString(7).equals("")) {
1016
                            //if (rs.getString(9).contains("#!#")) {
1017
                            if (rs.getString(7).contains("#!#")) {
1018
                                //reportItem.addIdentifier(new ItemIdentifier("OAI", rs.getString(9).substring(0, rs.getString(9).indexOf("#!#"))));
1019
                                reportItem.addIdentifier(new ItemIdentifier("OAI", rs.getString(7).substring(0, rs.getString(7).indexOf("#!#"))));
1048
                        if (rs.getString(9) != null && !rs.getString(9).equals("")) {
1049
                            if (rs.getString(9).contains("#!#")) {
1050
                                reportItem.addIdentifier(new ItemIdentifier("OAI", rs.getString(9).substring(0, rs.getString(9).indexOf("#!#"))));
1020 1051
                            } else {
1021
                                //reportItem.addIdentifier(new ItemIdentifier("OAI", rs.getString(9)));
1022
                                reportItem.addIdentifier(new ItemIdentifier("OAI", rs.getString(7)));
1052
                                reportItem.addIdentifier(new ItemIdentifier("OAI", rs.getString(9)));
1023 1053
                            }
1024 1054
                        }
1025
                        //if (rs.getString(6) != null && !rs.getString(6).equals("")) {
1026
                        if (rs.getString(4) != null && !rs.getString(4).equals("")) {
1027
                            if (rs.getString(4).contains("#!#")) {
1028
                                reportItem.addIdentifier(new ItemIdentifier("DOI", rs.getString(4).substring(0, rs.getString(4).indexOf("#!#"))));
1055
                        if (rs.getString(6) != null && !rs.getString(6).equals("")) {
1056
                            if (rs.getString(6).contains("#!#")) {
1057
                                reportItem.addIdentifier(new ItemIdentifier("DOI", rs.getString(6).substring(0, rs.getString(6).indexOf("#!#"))));
1029 1058
                            } else {
1030
                                reportItem.addIdentifier(new ItemIdentifier("DOI", rs.getString(4)));
1059
                                reportItem.addIdentifier(new ItemIdentifier("DOI", rs.getString(6)));
1031 1060
                            }
1032 1061
                        }
1033 1062
                    }
1034
                    fillWithZeros(postgresFormat.parse(lastDate), postgresFormat.parse(rs.getString(6)), reportItem);
1063
                    fillWithZeros(postgresFormat.parse(lastDate), postgresFormat.parse(rs.getString(8)), reportItem);
1035 1064
                    Calendar endC = Calendar.getInstance();
1036
                    endC.setTime(postgresFormat.parse(rs.getString(6)));
1065
                    endC.setTime(postgresFormat.parse(rs.getString(8)));
1037 1066
                    endC.set(Calendar.DATE, endC.getActualMaximum(Calendar.DATE));
1038 1067
                    if (reportItem != null) {
1039
                        reportItem.addPerformance(new ItemPerformance(report_dateFormat.format(postgresFormat.parse(rs.getString(6))), report_dateFormat.format(endC.getTime()), rs.getString(8), rs.getString(9)));
1068
                        reportItem.addPerformance(new ItemPerformance(report_dateFormat.format(postgresFormat.parse(rs.getString(8))), report_dateFormat.format(endC.getTime()), rs.getString(10), rs.getString(11)));
1040 1069
                    }
1041
                    endC.setTime(postgresFormat.parse(rs.getString(6)));
1070
                    endC.setTime(postgresFormat.parse(rs.getString(8)));
1042 1071
                    endC.add(Calendar.MONTH, 1);
1043 1072
                    lastDate = postgresFormat.format(endC.getTime());
1044 1073
                }
......
1054 1083
            jedis.put(redis_key, "query", st.toString());
1055 1084
            jedis.put(redis_key, "result", toJson(reportItems));
1056 1085
            jedis.put(redis_key, "fetchMode", "3");
1057
            */
1058

  
1086
             */
1059 1087
        } catch (Exception e) {
1060 1088
            log.error("Batch Item Report failed: ", e);
1061 1089
        } finally {
......
1064 1092
            DbUtils.closeQuietly(connection);
1065 1093
            java.sql.Timestamp timestamp = new java.sql.Timestamp(System.currentTimeMillis());
1066 1094
            //System.out.println("DB end "+timestamp);
1067
            log.info("Query end..."+timestamp);
1095
            log.info("Query end..." + timestamp);
1068 1096

  
1069 1097
        }
1070 1098
    }
modules/dnet-openaire-usage-stats-api/branches/usage-stats-api-compression/src/main/resources/usageStatsAPI.properties
5 5
name=usageStatsAPI
6 6
logging.config=log4j.properties
7 7
spring.database.driverClassName=org.postgresql.Driver
8
spring.datasource.url=jdbc:postgresql://88.197.53.70:5432/stats
8
#spring.datasource.url=jdbc:postgresql://88.197.53.70:5432/stats
9
spring.datasource.url=jdbc:postgresql://statsdb-beta.openaire.eu:5432/stats
9 10
spring.datasource.username=sqoop
10 11
spring.datasource.password=sqoop
11 12
usagestats.driverClassName=org.postgresql.Driver
12
usagestats.url=jdbc:postgresql://88.197.53.70:5432/stats
13
#usagestats.url=jdbc:postgresql://88.197.53.70:5432/stats
14
usagestats.url=jdbc:postgresql://statsdb-beta.openaire.eu:5432/stats
13 15
usagestats.username=sqoop
14 16
usagestats.password=sqoop
15 17
#server.port=8080
16 18
server.compression.enabled=true
17
compression.max_number_of_records=40
19
compression.max_number_of_records=10
18 20
usagestats.redis.hostname=localhost
19 21
usagestats.redis.port=6379
20 22
spring.jackson.serialization.INDENT_OUTPUT=true

Also available in: Unified diff