D-Net

        ObjectMapper objectMapper = new ObjectMapper();

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

    }

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

        UsageStats usageStats = new UsageStats();

        ResultSet rs = null;

        try {

            connection = usageStatsDB.getConnection();

            st = connection.prepareStatement(query);

            int i = 1;

            for (String s : values) {

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

            DbUtils.closeQuietly(rs);

            DbUtils.closeQuietly(st);

            DbUtils.closeQuietly(connection);

        }

        return usageStats;

    }

        try {

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

            if (redis_result != null) {

            } else {

                return updateTotalStats();

            }

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

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

            rs = st.executeQuery();

            while (rs.next()) {

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

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

            rs = st.executeQuery();

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

            while (rs.next()) {

            DbUtils.closeQuietly(rs);

            DbUtils.closeQuietly(st);

            DbUtils.closeQuietly(connection);

        }

        return totalStats;

    }

        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":

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

                    } else {

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

                    }

                    return openaire_id;

                case "opendoar":

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

                    } else {

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

                    }

                    }

                    return openaire_id;

                case "issn":

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

                    rs = st.executeQuery();

            DbUtils.closeQuietly(rs);

            DbUtils.closeQuietly(st);

            DbUtils.closeQuietly(connection);

        }

        return "-1";

    }

        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.setString(1, oid);

            st.setString(2, oid);

            DbUtils.closeQuietly(rs);

            DbUtils.closeQuietly(st);

            DbUtils.closeQuietly(connection);

        }

    }

        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.setString(1, doi);

            st.setString(2, doi);

            DbUtils.closeQuietly(rs);

            DbUtils.closeQuietly(st);

            DbUtils.closeQuietly(connection);

        }

    }

        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.setString(1, beginDateStr);

                    st.setString(2, endDateStr);

                    st.setString(3, openaire);

                    st.setString(5, 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.setString(1, beginDateStr);

                    st.setString(2, endDateStr);

                    st.setString(3, 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.setString(1, beginDateStr);

                    st.setString(2, endDateStr);

                    st.setString(3, 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=? 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;");

                    st.setString(1, beginDateStr);

                    st.setString(2, endDateStr);

                    st.setString(3, openaire);

            DbUtils.closeQuietly(rs);

            DbUtils.closeQuietly(st);

            DbUtils.closeQuietly(connection);

        }

    }

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

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

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

        try {

            connection = usageStatsDB.getConnection();

            if (repositoryIdentifier.equals("")) {

                if (itemDataType.equals("")) {

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

                    st.setString(1, beginDateStr);

                    st.setString(2, endDateStr);

                    //st.setString(3, beginDateStr);

                    //st.setString(4, endDateStr);

                } else {

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

                    st.setString(1, beginDateStr);

                    st.setString(2, endDateStr);

                    st.setString(3, itemDataType);

            } else {

                if (itemDataType.equals("")) {

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

                    st.setString(1, beginDateStr);

                    st.setString(2, endDateStr);

                    st.setString(3, repositoryIdentifier);

                    //st.setString(6, repositoryIdentifier);

                } else {

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

                    st.setString(1, beginDateStr);

                    st.setString(2, endDateStr);

                    st.setString(3, itemDataType);

                connection.close();

                return;

            }

            rs = st.executeQuery();

            String repository = "";

            String lastDate = "";

            startCalendar.setTime(beginDate);

            Calendar endCalendar = Calendar.getInstance();

            endCalendar.setTime(endDate);

            int ft_total = 0;

            int abstr = 0;

            if (granularity.equalsIgnoreCase("totals")) {

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

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

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

            rs.close();

            st.close();

            connection.close();

            DbUtils.closeQuietly(rs);

            DbUtils.closeQuietly(st);

            DbUtils.closeQuietly(connection);

        }

    }

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

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

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

        try {

            connection = usageStatsDB.getConnection();

            if (repositoryIdentifier.equals("")) {

                if (itemDataType.equals("")) {

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

                    st.setString(1, beginDateStr);

                    st.setString(2, endDateStr);

                    //st.setString(3, beginDateStr);

                    //st.setString(4, endDateStr);

                } else {

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

                    st.setString(1, beginDateStr);

                    st.setString(2, endDateStr);

                    st.setString(3, itemDataType);

            } else {

                if (itemDataType.equals("")) {

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

                    st.setString(1, beginDateStr);

                    st.setString(2, endDateStr);

                    st.setString(3, repositoryIdentifier);

                    //st.setString(6, repositoryIdentifier);

                } else {

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

                    st.setString(1, beginDateStr);

                    st.setString(2, endDateStr);

                    st.setString(3, itemDataType);

                connection.close();

                return;

            }

            rs = st.executeQuery();

            String repository = "";

            String lastDate = "";

            startCalendar.setTime(beginDate);

            Calendar endCalendar = Calendar.getInstance();

            endCalendar.setTime(endDate);

            int ft_total = 0;

            int abstr = 0;

            if (granularity.equalsIgnoreCase("totals")) {

                        reportItem = new ReportItem(null, rs.getString(2), "Platform", null);

                        reportItem.addIdentifier(new ItemIdentifier("OpenAIRE", rs.getString(1)));

                        reportItem.addIdentifier(new ItemIdentifier("ISSN", rs.getString(4).substring(rs.getString(4).lastIndexOf(":") + 1)));

                            reportItem.addIdentifier(new ItemIdentifier("URL", rs.getString(3)));

                        }

                        ft_total = 0;

                        reportItem = new ReportItem(null, rs.getString(2), "Platform", null);

                        reportItem.addIdentifier(new ItemIdentifier("OpenAIRE", rs.getString(1)));

                        reportItem.addIdentifier(new ItemIdentifier("ISSN", rs.getString(4).substring(rs.getString(4).lastIndexOf(":") + 1)));

                            reportItem.addIdentifier(new ItemIdentifier("URL", rs.getString(3)));

                        }

                    }

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

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

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

            rs.close();

            st.close();

            connection.close();

            DbUtils.closeQuietly(rs);

            DbUtils.closeQuietly(st);

            DbUtils.closeQuietly(connection);

        }

    }

        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;

            connection = usageStatsDB.getConnection();

            java.sql.Timestamp timestamp1 = new java.sql.Timestamp(System.currentTimeMillis());

            //System.out.println("DB start "+timestamp1);

            if (itemDataType.equals("")) {

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

                st.setString(1, beginDateStr);

                st.setString(2, endDateStr);

                st.setString(3, repositoryIdentifier);

            } else {

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

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

                st.setString(1, beginDateStr);

                st.setString(2, endDateStr);

                st.setString(3, repositoryIdentifier);

                connection.close();

                return;

            }

            //connection.setAutoCommit(false);

            //st.setFetchSize(5000);

            rs = st.executeQuery();

            String result = "";

            String lastDate = "";

                            reportItems.add(reportItem);

                        }

                        result = rs.getString(1);

                        reportItem.addIdentifier(new ItemIdentifier("OpenAIRE", rs.getString(1)));

                            } else {

                            }

                        }

                            } else {

                            }

                        }

                        ft_total = 0;

                        abstr = 0;

                    }

                }

                if (reportItem != null) {

                    reportItem.addPerformance(new ItemPerformance(report_dateFormat.format(beginDate), report_dateFormat.format(endDate), Integer.toString(ft_total), Integer.toString(abstr)));

                        }

                        result = rs.getString(1);

                        lastDate = beginDateStr;

                        reportItem.addIdentifier(new ItemIdentifier("OpenAIRE", rs.getString(1)));

                        reportItem.addIdentifier(new ItemIdentifier("URLs", rs.getString(4)));

                            } else {

                            }

                        }

                            } else {

                            }

                        }

                    }

                    Calendar endC = Calendar.getInstance();

                    endC.set(Calendar.DATE, endC.getActualMaximum(Calendar.DATE));

                    if (reportItem != null) {

                    }

                    endC.add(Calendar.MONTH, 1);

                    lastDate = postgresFormat.format(endC.getTime());

                }

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

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

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

        } catch (Exception e) {

            log.error("Batch Item Report failed: ", e);

        } finally {

            DbUtils.closeQuietly(connection);

            java.sql.Timestamp timestamp = new java.sql.Timestamp(System.currentTimeMillis());

            //System.out.println("DB end "+timestamp);

        }

    }

name=usageStatsAPI

logging.config=log4j.properties

spring.database.driverClassName=org.postgresql.Driver

spring.datasource.username=sqoop

spring.datasource.password=sqoop

usagestats.driverClassName=org.postgresql.Driver

usagestats.username=sqoop

usagestats.password=sqoop

#server.port=8080

server.compression.enabled=true

usagestats.redis.hostname=localhost

usagestats.redis.port=6379

spring.jackson.serialization.INDENT_OUTPUT=true