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.MonthlyStats;

import eu.dnetlib.usagestats.portal.RepositoryStats;

import eu.dnetlib.usagestats.portal.TotalStats;

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;

import javax.persistence.QueryHint;

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

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

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

            log.info("Query started..." + timestamp1);

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

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

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

            log.info("Query end..." + timestamp);

        }

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

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

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

            log.info("Query started..." + timestamp1);

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

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

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

            log.info("Query end..." + timestamp);

        }

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

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

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

            log.info("Query started..." + timestamp1);

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

            String openaire_id = "-1";

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

                case "openaire":

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

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

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

                    } else {

                        st = connection.prepareStatement("select id from 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 datasource_oids where orid=?");

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

                    } else {

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

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

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

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

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

            log.info("Query end..." + timestamp);

        }

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

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

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

            log.info("Query started..." + timestamp1);

            //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 us.original_result_id FROM result_oids roid, 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);

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

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

            log.info("Query end..." + timestamp);

        }

    }

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

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

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

            log.info("Query started..." + timestamp1);

            //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 us.original_result_id FROM result_pids poid, 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);

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

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

            log.info("Query end..." + timestamp);

        }

    }

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

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

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

            log.info("Query started..." + timestamp1);

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

                    st = connection.prepareStatement("SELECT us.repository_id, r.title, r.publisher, r.source, rc.type, string_agg(distinct pids.pid, '#!#') AS pid, d.name, us.date, string_agg(distinct oids.orid, '#!#') AS orid, us.downloads, us.views FROM usage_stats us JOIN result r ON us.result_id=r.id JOIN datasource d ON d.id=us.repository_id JOIN result_classifications rc ON rc.id=r.id JOIN result_pids pids ON pids.id=r.id AND pids.type='doi' JOIN result_oids oids ON oids.id=r.id WHERE us.date>=? AND us.date<=? AND us.original_result_id=? GROUP BY us.repository_id, r.title, r.publisher, r.source, rc.type, d.name, us.date, us.downloads, us.views ORDER BY us.repository_id, us.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);

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

                    st = connection.prepareStatement("SELECT us.repository_id, r.title, r.publisher, r.source, rc.type, string_agg(distinct pids.pid, '#!#') AS pid, d.name, us.date, string_agg(distinct oids.orid, '#!#') AS orid, us.downloads, us.views FROM usage_stats us JOIN result r ON us.result_id=r.id JOIN datasource d ON d.id=us.repository_id JOIN result_classifications rc ON rc.id=r.id JOIN result_pids pids ON pids.id=r.id AND pids.type='doi' JOIN result_oids oids ON oids.id=r.id WHERE us.date>=? AND us.date<=? AND us.original_result_id=? AND rc.type=? GROUP BY us.repository_id, r.title, r.publisher, r.source, rc.type, d.name, us.date, us.downloads, us.views ORDER BY us.repository_id, us.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 us.repository_id, r.title, r.publisher, r.source, rc.type, string_agg(distinct pids.pid, '#!#') AS pid, d.name, us.date, string_agg(distinct oids.orid, '#!#') AS orid, us.downloads, us.views FROM usage_stats us JOIN result r ON us.result_id=r.id JOIN datasource d ON d.id=us.repository_id JOIN result_classifications rc ON rc.id=r.id JOIN result_pids pids ON pids.id=r.id AND pids.type='doi' JOIN result_oids oids ON oids.id=r.id WHERE us.date>=? AND us.date<=? AND us.original_result_id=? AND us.repository_id=? GROUP BY us.repository_id, r.title, r.publisher, r.source, rc.type, d.name, us.date, us.downloads, us.views ORDER BY us.repository_id, us.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);

                    //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 = connection.prepareStatement("SELECT us.repository_id, r.title, r.publisher, r.source, rc.type, string_agg(distinct pids.pid, '#!#') AS pid, d.name, us.date, string_agg(distinct oids.orid, '#!#') AS orid, us.downloads, us.views FROM usage_stats us JOIN result r ON us.result_id=r.id JOIN datasource d ON d.id=us.repository_id JOIN result_classifications rc ON rc.id=r.id JOIN result_pids pids ON pids.id=r.id AND pids.type='doi' JOIN result_oids oids ON oids.id=r.id WHERE us.date>=? AND us.date<=? AND us.original_result_id=? AND us.repository_id=? AND rc.type=? GROUP BY us.repository_id, r.title, r.publisher, r.source, rc.type, d.name, us.date, us.downloads, us.views ORDER BY us.repository_id, us.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, itemDataType);

                    //st.setString(6, openaire);

                    //st.setString(7, openaire);

                }

            }

            rs = st.executeQuery();

            String repository = "";

            String lastDate = "";

            ReportItem reportItem = null;

            int ft_total = 0;

            int abstr = 0;

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

                while (rs.next()) {

                    if (!rs.getString(1).equals(repository)) {

                        if (reportItem != null) {

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

                            reportItems.add(reportItem);

                        }

                        repository = rs.getString(1);

                        reportItem = new ReportItem(rs.getString(3), rs.getString(7), rs.getString(5), rs.getString(2));

                        reportItem.addIdentifier(new ItemIdentifier("OpenAIRE", openaire));

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

                        if (rs.getString(9) != null && !rs.getString(9).equals("")) {

                            if (rs.getString(9).contains("#!#")) {

                                reportItem.addIdentifier(new ItemIdentifier("OAI", rs.getString(9).substring(0, rs.getString(9).indexOf("#!#"))));

                            } else {

                                reportItem.addIdentifier(new ItemIdentifier("OAI", rs.getString(9)));

                            }

                        }

                        if (rs.getString(6) != null && !rs.getString(6).equals("")) {

                            if (rs.getString(6).contains("#!#")) {

                                reportItem.addIdentifier(new ItemIdentifier("DOI", rs.getString(6).substring(0, rs.getString(6).indexOf("#!#"))));

                            } else {

                                reportItem.addIdentifier(new ItemIdentifier("DOI", rs.getString(6)));

                            }

                        }

                        ft_total = 0;

                        abstr = 0;

                    }

                    ft_total += rs.getInt(10);

                    abstr += rs.getInt(11);

                }

                if (reportItem != null) {

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

                    reportItems.add(reportItem);

                }

            } else if (granularity.equalsIgnoreCase("monthly")) {

                Calendar endCal = Calendar.getInstance();

                endCal.setTime(postgresFormat.parse(endDateStr));

                endCal.add(Calendar.MONTH, 1);

                Date endDateForZeros = endCal.getTime();

                while (rs.next()) {

                    if (!rs.getString(1).equals(repository)) {

                        if (reportItem != null) {

                            fillWithZeros(postgresFormat.parse(lastDate), endDateForZeros, reportItem);

                            reportItems.add(reportItem);

                        }

                        repository = rs.getString(1);

                        lastDate = beginDateStr;

                        reportItem = new ReportItem(rs.getString(3), rs.getString(7), rs.getString(5), rs.getString(2));

                        reportItem.addIdentifier(new ItemIdentifier("OpenAIRE", openaire));

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

                        if (rs.getString(9) != null && !rs.getString(9).equals("")) {

                            if (rs.getString(9).contains("#!#")) {

                                reportItem.addIdentifier(new ItemIdentifier("OAI", rs.getString(9).substring(0, rs.getString(9).indexOf("#!#"))));

                            } else {

                                reportItem.addIdentifier(new ItemIdentifier("OAI", rs.getString(9)));

                            }

                        }

                        if (rs.getString(6) != null && !rs.getString(6).equals("")) {

                            if (rs.getString(6).contains("#!#")) {

                                reportItem.addIdentifier(new ItemIdentifier("DOI", rs.getString(6).substring(0, rs.getString(6).indexOf("#!#"))));

                            } else {

                                reportItem.addIdentifier(new ItemIdentifier("DOI", rs.getString(6)));

                            }

                        }

                    }

                    fillWithZeros(postgresFormat.parse(lastDate), postgresFormat.parse(rs.getString(8)), reportItem);

                    Calendar endC = Calendar.getInstance();

                    endC.setTime(postgresFormat.parse(rs.getString(8)));

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

                    if (reportItem != null) {

                        reportItem.addPerformance(new ItemPerformance(report_dateFormat.format(postgresFormat.parse(rs.getString(8))), report_dateFormat.format(endC.getTime()), rs.getString(10), rs.getString(11)));

                    }

                    endC.setTime(postgresFormat.parse(rs.getString(8)));

                    endC.add(Calendar.MONTH, 1);

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

                }

                if (reportItem != null) {

                    fillWithZeros(postgresFormat.parse(lastDate), endDateForZeros, reportItem);

                    reportItems.add(reportItem);

                }

            }

        } catch (Exception e) {

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

        } finally {

            DbUtils.closeQuietly(rs);

            DbUtils.closeQuietly(st);

            DbUtils.closeQuietly(connection);

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

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

            log.info("Query end..." + timestamp);

        }

    }

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

        String beginDateStr = postgresFormat.format(beginDate);

        String endDateStr = postgresFormat.format(endDate);

        Connection connection = null;

        PreparedStatement st = null;

        ResultSet rs = null;

        try {

            connection = usageStatsDB.getConnection();

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

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

            log.info("Query started..." + timestamp1);

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

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

                    st.setString(1, beginDateStr);

                    st.setString(2, endDateStr);

                    st.setString(3, itemDataType);

                    //st.setString(4, beginDateStr);

                    //st.setString(5, endDateStr);

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

                    st.setString(1, beginDateStr);

                    st.setString(2, endDateStr);

                    st.setString(3, repositoryIdentifier);

                    //st.setString(4, beginDateStr);

                    //st.setString(5, endDateStr);

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

                    st.setString(1, beginDateStr);

                    st.setString(2, endDateStr);

                    st.setString(3, itemDataType);

                    st.setString(4, repositoryIdentifier);

                    //st.setString(5, beginDateStr);

                    //st.setString(6, endDateStr);

                    //st.setString(7, itemDataType);

                    //st.setString(8, repositoryIdentifier);

                }

            }

            //log.error("RR STATEMENT:   " + st);

            /*

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

            if (jedis.hasKey(redis_key, "result")) {

                reportItems.addAll(reportItemsFromJson((String) jedis.get(redis_key, "result")));

                st.close();

                connection.close();

                return;

            }

             */

            rs = st.executeQuery();

            String repository = "";

            String lastDate = "";

            ReportItem reportItem = null;

            /*

            Calendar startCalendar = Calendar.getInstance();

            startCalendar.setTime(beginDate);

            Calendar endCalendar = Calendar.getInstance();

            endCalendar.setTime(endDate);

             */

            int ft_total = 0;

            int abstr = 0;