D-Net

package eu.dnetlib.usagestats.repositories;

import org.apache.log4j.Logger;

import org.springframework.data.redis.core.HashOperations;

import org.springframework.data.redis.core.RedisTemplate;

import org.springframework.stereotype.Repository;

import com.fasterxml.jackson.databind.ObjectMapper;

import eu.dnetlib.usagestats.portal.CountryRepositories;

import eu.dnetlib.usagestats.portal.CountryUsageStats;

import eu.dnetlib.usagestats.portal.CountryUsageStatsAll;

import eu.dnetlib.usagestats.portal.MonthlyStats;

import eu.dnetlib.usagestats.portal.MonthlyUsageStats;

import eu.dnetlib.usagestats.portal.RepositoryStats;

import eu.dnetlib.usagestats.portal.TotalStats;

import eu.dnetlib.usagestats.portal.TotalStatsReposViewsDownloads;

import eu.dnetlib.usagestats.portal.UsageStats;

import eu.dnetlib.usagestats.portal.YearlyStats;

import eu.dnetlib.usagestats.sushilite.domain.ItemIdentifier;

import eu.dnetlib.usagestats.sushilite.domain.ItemPerformance;

import eu.dnetlib.usagestats.sushilite.domain.ReportItem;

import org.apache.commons.dbutils.DbUtils;

import javax.sql.DataSource;

import java.security.MessageDigest;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.text.SimpleDateFormat;

import java.util.ArrayList;

import java.util.Calendar;

import java.util.Date;

import java.util.HashMap;

import java.util.List;

@Repository

public class UsageStatsRepository {

    private final DataSource usageStatsDB;

    private final HashOperations<String, String, String> jedis;

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

    public UsageStatsRepository(DataSource usageStatsDB, RedisTemplate<String, String> redisTemplate) {

        this.usageStatsDB = usageStatsDB;

        this.jedis = redisTemplate.opsForHash();

    }

    private static String MD5(String string) throws java.security.NoSuchAlgorithmException {

        MessageDigest md = MessageDigest.getInstance("MD5");

        md.update(string.getBytes());

        byte byteData[] = md.digest();

        StringBuilder sb = new StringBuilder();

        for (byte aByteData : byteData) {

            sb.append(Integer.toString((aByteData & 0xff) + 0x100, 16).substring(1));

        }

        return sb.toString();

    }

    private static String toJson(Object o) throws com.fasterxml.jackson.core.JsonProcessingException {

        ObjectMapper objectMapper = new ObjectMapper();

        return objectMapper.writeValueAsString(o);

    }

    private static UsageStats fromJson(String string) throws java.io.IOException {

        ObjectMapper objectMapper = new ObjectMapper();

        return objectMapper.readValue(string, UsageStats.class);

    }

    /*

    private static List<ReportItem> reportItemsFromJson(String string) throws Exception {

        ObjectMapper objectMapper = new ObjectMapper();

        return objectMapper.readValue(string, objectMapper.getTypeFactory().constructCollectionType(List.class, ReportItem.class));

    }

     */

    public List<MonthlyUsageStats> executeMontlyUsageStats(String query) {

        List<MonthlyUsageStats> montlhyList = new ArrayList<MonthlyUsageStats>();

        Connection connection = null;

        PreparedStatement st = null;

        ResultSet rs = null;

        try {

            connection = usageStatsDB.getConnection();

            log.info(connection.toString());

            st = connection.prepareStatement(query);

            log.info(st.toString());

            rs = st.executeQuery();

            while (rs.next()) {

                MonthlyUsageStats monthlyUsageStats = new MonthlyUsageStats();

                monthlyUsageStats.addDate(rs.getString(1));

                monthlyUsageStats.addDownloads(rs.getString(2));

                monthlyUsageStats.addViews(rs.getString(3));

                montlhyList.add(monthlyUsageStats);

            }

        } catch (Exception e) {

            System.out.println(e);

        }

        try {

            jedis.put("test", "result", toJson(montlhyList));

            jedis.put("test", "persistent", "false");

            jedis.put("test", "fetchMode", "3");

        } catch (Exception e) {

            System.out.println(e);

        }finally {

            DbUtils.closeQuietly(rs);

            DbUtils.closeQuietly(st);

            DbUtils.closeQuietly(connection);

        }

        return montlhyList;

    }

    public TotalStatsReposViewsDownloads executeTotalStatsReposViewsDownloads(String query) {

        TotalStatsReposViewsDownloads totalStatsReposViewsDownlads = new TotalStatsReposViewsDownloads();

        String total_repos = " ";

        String views = " ";

        String downloads = " ";

        String redis_key = "";

        Connection connection = null;

        PreparedStatement st = null;

        ResultSet rs = null;

        try {

            connection = usageStatsDB.getConnection();

            log.info(connection.toString());

            st = connection.prepareStatement(query);

            log.info(st.toString());

            rs = st.executeQuery();

            redis_key = MD5(st.toString());

            while (rs.next()) {

                totalStatsReposViewsDownlads.addRepositories(rs.getString(1));

                totalStatsReposViewsDownlads.addViews(rs.getString(2));

                totalStatsReposViewsDownlads.addDownloads(rs.getString(3));

            }

        } catch (Exception e) {

            System.out.println(e);

        }

        try {

            jedis.put(redis_key, "result", toJson(totalStatsReposViewsDownlads));

            jedis.put(redis_key, "persistent", "false");

            jedis.put(redis_key, "fetchMode", "3");

        } catch (Exception e) {

            System.out.println(e);

        }finally {

            DbUtils.closeQuietly(rs);

            DbUtils.closeQuietly(st);

            DbUtils.closeQuietly(connection);

        }

        return totalStatsReposViewsDownlads;

    }

    public CountryUsageStatsAll executeCountryUsageStats(String query) {

        CountryUsageStatsAll countryListAll = new CountryUsageStatsAll();

        List<CountryUsageStats> countryList = new ArrayList<CountryUsageStats>();

        String date = " ";

        String total_repos = " ";

        String views = " ";

        String downloads = " ";

        String redis_key = "redis_key";

        Connection connection = null;

        PreparedStatement st = null;

        ResultSet rs = null;

        int total_views = 0;

        int total_downloads = 0;

        try {

            connection = usageStatsDB.getConnection();

            log.info(connection.toString());

            st = connection.prepareStatement(query);

            log.info(st.toString());

            rs = st.executeQuery();

            redis_key = MD5(st.toString());

            while (rs.next()) {

                CountryUsageStats countryUsageStats = new CountryUsageStats();

                countryUsageStats.addCountry(rs.getString(1));

                countryUsageStats.addTotalRepos(rs.getString(2));

                countryUsageStats.addViews(rs.getString(3));

                countryUsageStats.addDownloads(rs.getString(4));

                total_views += Integer.parseInt(rs.getString(3));

                total_downloads += Integer.parseInt(rs.getString(4));

                countryList.add(countryUsageStats);

            }

            countryListAll.addViewsAll(Integer.toString(total_views));

            countryListAll.addDownloadsAll(Integer.toString(total_downloads));

            countryListAll.addCountryUsageStats(countryList);

        } catch (Exception e) {

            System.out.println(e);

        }

        try {

            jedis.put(redis_key, "result", toJson(countryListAll));

            jedis.put(redis_key, "persistent", "false");

            jedis.put(redis_key, "fetchMode", "3");

        } catch (Exception e) {

            System.out.println(e);

        } finally {

            DbUtils.closeQuietly(rs);

            DbUtils.closeQuietly(st);

            DbUtils.closeQuietly(connection);

        }

        return countryListAll;

    }

    public CountryUsageStats executeCountryUsageStats(String query, String country) {

        CountryUsageStats countryUsageStats = new CountryUsageStats();

        String total_repos = " ";

        String views = " ";

        String downloads = " ";

        String redis_key = "";

        Connection connection = null;

        PreparedStatement st = null;

        ResultSet rs = null;

        int total_views = 0;

        int total_downloads = 0;

        try {

            connection = usageStatsDB.getConnection();

            log.info(connection.toString());

            st = connection.prepareStatement(query);

            redis_key = MD5(st.toString());

            st.setString(1, country);

            log.info(st.toString());

            rs = st.executeQuery();

            while (rs.next()) {

                countryUsageStats.addCountry(country);

                countryUsageStats.addTotalRepos(rs.getString(1));

                countryUsageStats.addViews(rs.getString(2));

                countryUsageStats.addDownloads(rs.getString(3));

            }

        } catch (Exception e) {

            System.out.println(e);

        }

        try {

            jedis.put(redis_key, "result", toJson(countryUsageStats));

            jedis.put(redis_key, "persistent", "false");

            jedis.put(redis_key, "fetchMode", "3");

        } catch (Exception e) {

            System.out.println(e);

        } finally {

            DbUtils.closeQuietly(rs);

            DbUtils.closeQuietly(st);

            DbUtils.closeQuietly(connection);

        }

        return countryUsageStats;

    }

    public List<CountryRepositories> executeCountryRepositories(String query) {

        List<CountryRepositories> countryReposList = new ArrayList<CountryRepositories>();

        String country = " ";

        String repository = " ";

        String redis_key = "";

        Connection connection = null;

        PreparedStatement st = null;

        ResultSet rs = null;

        try {

            connection = usageStatsDB.getConnection();

            log.info(connection.toString());

            st = connection.prepareStatement(query);

            log.info(st.toString());

            rs = st.executeQuery();

            redis_key = MD5(st.toString());

            while (rs.next()) {

                CountryRepositories countryRepository = new CountryRepositories();

                countryRepository.addCountry(rs.getString(1));

                countryRepository.addRepository(rs.getString(2));

                countryReposList.add(countryRepository);

            }

        } catch (Exception e) {

            System.out.println(e);

        }

        try {

            jedis.put(redis_key, "result", toJson(countryReposList));

            jedis.put(redis_key, "persistent", "false");

            jedis.put(redis_key, "fetchMode", "3");

        } catch (Exception e) {

            System.out.println(e);

        } finally {

            DbUtils.closeQuietly(rs);

            DbUtils.closeQuietly(st);

            DbUtils.closeQuietly(connection);

        }

        return countryReposList;

    }

    public List<MonthlyUsageStats> executeMontlyUsageStatsForRepo(String query, String datasourceId) {

        List<MonthlyUsageStats> montlhyList = new ArrayList<MonthlyUsageStats>();

        String redis_key = "";

        Connection connection = null;

        PreparedStatement st = null;

        ResultSet rs = null;

        try {

            connection = usageStatsDB.getConnection();

            st = connection.prepareStatement(query);

            redis_key = MD5(st.toString());

            st.setString(1, datasourceId);

            log.info(connection.toString());

            rs = st.executeQuery();

            while (rs.next()) {

                MonthlyUsageStats monthlyUsageStats = new MonthlyUsageStats();

                monthlyUsageStats.addDate(rs.getString(1));

                monthlyUsageStats.addDownloads(rs.getString(2));

                monthlyUsageStats.addViews(rs.getString(3));

                montlhyList.add(monthlyUsageStats);

            }

        } catch (Exception e) {

            System.out.println(e);

        }

        try {

            jedis.put(redis_key, "result", toJson(montlhyList));

            jedis.put(redis_key, "persistent", "false");

            jedis.put(redis_key, "fetchMode", "3");

        } catch (Exception e) {

            System.out.println(e);

        } finally {

            DbUtils.closeQuietly(rs);

            DbUtils.closeQuietly(st);

            DbUtils.closeQuietly(connection);

        }

        return montlhyList;

    }

    public UsageStats executeUsageStats(String query, List<String> values, String type) {

        UsageStats usageStats = new UsageStats();

        int total_views = 0;

        int total_downloads = 0;

        int page_views = 0;

        int openaire_downloads = 0;

        int openaire_views = 0;

        Connection connection = null;

        PreparedStatement st = null;

        ResultSet rs = null;

        try {

            connection = usageStatsDB.getConnection();

            st = connection.prepareStatement(query);

            int i = 1;

            for (String s : values) {

                st.setString(i, s);

                i++;

            }

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

            String redis_result = jedis.get(redis_key, "result");

            if (redis_result != null) {

                return fromJson(redis_result);

            }

            rs = st.executeQuery();

            if (type.equals("result")) {

                while (rs.next()) {

                    if (rs.getString(1).equals("views") && rs.getString(4) != null && !rs.getString(4).equals("") && !rs.getString(4).equals("null")) {

                        usageStats.addViews(new RepositoryStats(rs.getString(3), rs.getString(2), rs.getString(4), rs.getString(5)));

                        total_views += Integer.parseInt(rs.getString(4));

                        openaire_views += Integer.parseInt(rs.getString(5));

                    } else if (rs.getString(1).equals("downloads") && rs.getString(4) != null && !rs.getString(4).equals("") && !rs.getString(4).equals("null")) {

                        usageStats.addDownloads(new RepositoryStats(rs.getString(3), rs.getString(2), rs.getString(4), "0"));

                        total_downloads += Integer.parseInt(rs.getString(4));

                        openaire_downloads += Integer.parseInt(rs.getString(5));

                    } else if (rs.getString(1).equals("pageviews") && rs.getString(4) != null && !rs.getString(4).equals("") && !rs.getString(4).equals("null")) {

                        page_views = Integer.parseInt(rs.getString(4));

                    }

                }

                usageStats.setTotal_views(Integer.toString(total_views));

                usageStats.setTotal_downloads(Integer.toString(total_downloads));

                usageStats.setPageViews(Integer.toString(page_views));

                usageStats.setTotal_openaire_views(Integer.toString(openaire_views));

                usageStats.setTotal_openaire_downloads(Integer.toString(openaire_downloads));

            } else if (type.equals("project") || type.equals("datasource")) {

                while (rs.next()) {

                    if (rs.getString(1).equals("views") && rs.getString(2) != null && !rs.getString(2).equals("") && !rs.getString(2).equals("null")) {

                        total_views += Integer.parseInt(rs.getString(2));

                        openaire_views += Integer.parseInt(rs.getString(3));

                    } else if (rs.getString(1).equals("downloads") && rs.getString(2) != null && !rs.getString(2).equals("") && !rs.getString(2).equals("null")) {

                        total_downloads += Integer.parseInt(rs.getString(2));

                        openaire_downloads += Integer.parseInt(rs.getString(3));

                    } else if (rs.getString(1).equals("pageviews") && rs.getString(2) != null && !rs.getString(2).equals("") && !rs.getString(2).equals("null")) {

                        page_views = Integer.parseInt(rs.getString(2));

                    }

                    /*

                    else if (rs.getString(1).equals("openaire") && rs.getString(2) != null && !rs.getString(2).equals("") && !rs.getString(2).equals("null")) {

                        openaire = Integer.parseInt(rs.getString(2));

                    }

                     */

                }

                usageStats.setTotal_views(Integer.toString(total_views));

                usageStats.setTotal_downloads(Integer.toString(total_downloads));

                usageStats.setPageViews(Integer.toString(page_views));

                usageStats.setTotal_openaire_views(Integer.toString(openaire_views));

                usageStats.setTotal_openaire_downloads(Integer.toString(openaire_downloads));

            }

            jedis.put(redis_key, "persistent", "false");

            jedis.put(redis_key, "query", st.toString());

            //jedis.put(redis_key, "result", toString(usageStats));

            jedis.put(redis_key, "result", toJson(usageStats));

            jedis.put(redis_key, "fetchMode", "3");

        } catch (Exception e) {

            log.error("Cannot execute query2 : ", e);

        } finally {

            DbUtils.closeQuietly(rs);

            DbUtils.closeQuietly(st);

            DbUtils.closeQuietly(connection);

        }

        return usageStats;

    }

    public TotalStats executeTotalStats() {

        TotalStats totalStats = null;

        try {

            String redis_result = jedis.get("total_stats", "result");

            if (redis_result != null) {

                totalStats = fromJsonTotalStats(redis_result);

            } else {

                return updateTotalStats();

            }

        } catch (Exception e) {

            log.error("Cannot execute totalStats : ", e);

        }

        return totalStats;

    }

    public TotalStats updateTotalStats() {

        TotalStats totalStats = new TotalStats();

        Connection connection = null;

        PreparedStatement st = null;

        ResultSet rs = null;

        HashMap<Integer, List<MonthlyStats>> monthlyStatsMap = new HashMap<>();

        try {

            connection = usageStatsDB.getConnection();

            //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;");

            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");

            rs = st.executeQuery();

            rs.next();

            totalStats.setRepositories(rs.getInt(1));

            totalStats.setItems(rs.getInt(2));

            totalStats.setDownloads(rs.getInt(3));

            totalStats.setViews(rs.getInt(4));

            rs.close();

            st.close();

            //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;");

            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 public.usage_stats GROUP BY date ORDER BY date;");

            rs = st.executeQuery();

            while (rs.next()) {

                int year = Integer.parseInt(rs.getString(1).substring(0, 4));

                int month = Integer.parseInt(rs.getString(1).substring(5));

                MonthlyStats monthlyStats = new MonthlyStats();

                monthlyStats.setMonth(month);

                monthlyStats.setRepositories(rs.getInt(2));

                monthlyStats.setItems(rs.getInt(3));

                monthlyStats.setDownloads(rs.getInt(4));

                monthlyStats.setViews(rs.getInt(5));

                if (monthlyStatsMap.get(year) != null) {

                    monthlyStatsMap.get(year).add(monthlyStats);

                } else {

                    List<MonthlyStats> newList = new ArrayList<>();

                    newList.add(monthlyStats);

                    monthlyStatsMap.put(year, newList);

                }

            }

            rs.close();

            st.close();

            //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;");

            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 public.usage_stats GROUP BY year ORDER BY year;");

            rs = st.executeQuery();

            List<YearlyStats> yearlyStatsList = new ArrayList<>();

            while (rs.next()) {

                YearlyStats yearlyStats = new YearlyStats();

                yearlyStats.setYear(rs.getInt(1));

                yearlyStats.setRepositories(rs.getInt(2));

                yearlyStats.setItems(rs.getInt(3));

                yearlyStats.setDownloads(rs.getInt(4));

                yearlyStats.setViews(rs.getInt(5));

                yearlyStats.setMonthlyStats(monthlyStatsMap.get(rs.getInt(1)));

                yearlyStatsList.add(yearlyStats);

            }

            totalStats.setYearlyStats(yearlyStatsList);

            jedis.put("total_stats", "result", toJson(totalStats));

            jedis.put("total_stats", "persistent", "false");

        } catch (Exception e) {

            log.error("Cannot execute totalStats : ", e);

        } finally {

            DbUtils.closeQuietly(rs);

            DbUtils.closeQuietly(st);

            DbUtils.closeQuietly(connection);

        }

        return totalStats;

    }

    private static TotalStats fromJsonTotalStats(String string) throws java.io.IOException {

        ObjectMapper objectMapper = new ObjectMapper();

        return objectMapper.readValue(string, TotalStats.class);

    }

    public String executeRepoId(String repositoryIdentifier, String report) {

        PreparedStatement st = null;

        Connection connection = null;

        ResultSet rs = null;

        try {

            connection = usageStatsDB.getConnection();

            String[] split = repositoryIdentifier.split(":");

            String openaire_id = "-1";

            switch (split[0].toLowerCase()) {

                case "openaire":

                    if (!report.equals("jr1")) {

                        st = connection.prepareStatement("select id from public.datasource where id=?");

                        st.setString(1, repositoryIdentifier.replaceFirst(split[0] + ":", ""));

                    } else {

                        st = connection.prepareStatement("select id from public.datasource where id=? AND (type='Journal' OR type='Journal Aggregator/Publisher')");

                        st.setString(1, repositoryIdentifier.replaceFirst(split[0] + ":", ""));

                    }

                    rs = st.executeQuery();

                    while (rs.next()) {

                        openaire_id = rs.getString(1);

                    }

                    return openaire_id;

                case "opendoar":

                    if (!report.equals("jr1")) {

                        st = connection.prepareStatement("select id from public.datasource_oids where orid=?");

                        st.setString(1, "opendoar____::" + repositoryIdentifier.replaceFirst(split[0] + ":", ""));

                    } else {

                        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')");

                        st.setString(1, "opendoar____::" + repositoryIdentifier.replaceFirst(split[0] + ":", ""));

                    }

                    rs = st.executeQuery();

                    while (rs.next()) {

                        openaire_id = rs.getString(1);

                    }

                    return openaire_id;

                case "issn":

                    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')");

                    st.setString(1, "%" + repositoryIdentifier.replaceFirst(split[0] + ":", "") + "%");

                    rs = st.executeQuery();

                    while (rs.next()) {

                        openaire_id = rs.getString(1);

                    }

                    return openaire_id;

                default:

                    return "-1";

            }

        } catch (Exception e) {

            log.error("Repository id failed: ", e);

        } finally {

            DbUtils.closeQuietly(rs);

            DbUtils.closeQuietly(st);

            DbUtils.closeQuietly(connection);

        }

        return "-1";

    }

    public void executeItem(List<ReportItem> reportItems, String itemIdentifier, String repositoryIdentifier, String itemDataType, Date beginDate, Date endDate, String granularity) {

        String[] split = itemIdentifier.split(":");

        switch (split[0].toLowerCase()) {

            case "oid":

                executeOid(reportItems, itemIdentifier.replaceFirst(split[0] + ":", ""), repositoryIdentifier, itemDataType, beginDate, endDate, granularity);

                break;

            case "doi":

                executeDoi(reportItems, itemIdentifier.replaceFirst(split[0] + ":", ""), repositoryIdentifier, itemDataType, beginDate, endDate, granularity);

                break;

            case "openaire":

                executeOpenaire(reportItems, itemIdentifier.replaceFirst(split[0] + ":", ""), repositoryIdentifier, itemDataType, beginDate, endDate, granularity);

                break;

            default:

        }

    }

    private void executeOid(List<ReportItem> reportItems, String oid, String repositoryIdentifier, String itemDataType, Date beginDate, Date endDate, String granularity) {

        Connection connection = null;

        PreparedStatement st = null;

        ResultSet rs = null;

        try {

            connection = usageStatsDB.getConnection();

            //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=?");

            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=?");

            st.setString(1, oid);

            //st.setString(2, oid);

            rs = st.executeQuery();

            while (rs.next()) {

                executeOpenaire(reportItems, rs.getString(1), repositoryIdentifier, itemDataType, beginDate, endDate, granularity);

            }

            connection.close();

        } catch (Exception e) {

            log.error("Oid to OpenAIRE id failed: ", e);

        } finally {

            DbUtils.closeQuietly(rs);

            DbUtils.closeQuietly(st);

            DbUtils.closeQuietly(connection);

        }

    }

    private void executeDoi(List<ReportItem> reportItems, String doi, String repositoryIdentifier, String itemDataType, Date beginDate, Date endDate, String granularity) {

        Connection connection = null;

        PreparedStatement st = null;

        ResultSet rs = null;

        try {

            connection = usageStatsDB.getConnection();

            //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=?");

            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=?");

            st.setString(1, doi);

            //st.setString(2, doi);

            rs = st.executeQuery();

            while (rs.next()) {

                executeOpenaire(reportItems, rs.getString(1), repositoryIdentifier, itemDataType, beginDate, endDate, granularity);

            }

        } catch (Exception e) {

            log.error("Doi to OpenAIRE id failed: ", e);

        } finally {

            DbUtils.closeQuietly(rs);

            DbUtils.closeQuietly(st);

            DbUtils.closeQuietly(connection);

        }

    }

    private void executeOpenaire(List<ReportItem> reportItems, String openaire, String repositoryIdentifier, String itemDataType, Date beginDate, Date endDate, String granularity) {

        SimpleDateFormat report_dateFormat = new SimpleDateFormat("yyyy-MM-dd");

        SimpleDateFormat postgresFormat = new SimpleDateFormat("yyyy/MM");

        String beginDateStr = postgresFormat.format(beginDate);

        String endDateStr = postgresFormat.format(endDate);

        Connection connection = null;

        PreparedStatement st = null;

        ResultSet rs = null;

        /*

        Calendar startCalendar = Calendar.getInstance();

        startCalendar.setTime(beginDate);

        Calendar endCalendar = Calendar.getInstance();

        endCalendar.setTime(endDate);

        int diffYear = endCalendar.get(Calendar.YEAR) - startCalendar.get(Calendar.YEAR);

        int diffMonth = diffYear * 12 + endCalendar.get(Calendar.MONTH) - startCalendar.get(Calendar.MONTH);

         */

        try {

            connection = usageStatsDB.getConnection();

            if (repositoryIdentifier.equals("")) {

                if (itemDataType.equals("")) {

                    //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;");

                    //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;");

                    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;");

                    st.setString(1, beginDateStr);

                    st.setString(2, endDateStr);

                    st.setString(3, openaire);

                    //st.setString(4, beginDateStr);

                    //st.setString(5, endDateStr);

                    //st.setString(6, openaire);

                    st.setString(4, openaire);

                    st.setString(5, openaire);

                    st.setString(6, openaire);

                } else {

                    //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;");

                    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;");

                    st.setString(1, beginDateStr);

                    st.setString(2, endDateStr);

                    st.setString(3, openaire);

                    //st.setString(4, beginDateStr);

                    //st.setString(5, endDateStr);

                    //st.setString(6, openaire);

                    st.setString(4, itemDataType);

                    st.setString(5, openaire);

                    st.setString(6, openaire);

                }

            } else {

                if (itemDataType.equals("")) {

                    //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;");

                    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;");

                    st.setString(1, beginDateStr);

                    st.setString(2, endDateStr);

                    st.setString(3, openaire);

                    st.setString(4, repositoryIdentifier);

                    //st.setString(5, beginDateStr);

                    //st.setString(6, endDateStr);

                    //st.setString(7, openaire);

                    //st.setString(8, repositoryIdentifier);

                    st.setString(5, openaire);