D-Net

package eu.dnetlib.usagestats.portal;

import com.fasterxml.jackson.annotation.JsonProperty;

import java.io.Serializable;

import java.util.ArrayList;

import java.util.List;

public class CountryUsageStatsAll implements Serializable {

    private final static long serialVersionUID = 1;

    private String country_all = "all";

    private String downloads_all = "0";

    private String views_all = "0";

    private List<CountryUsageStats> countryUsageStats = new ArrayList<CountryUsageStats>();

    //private String pageviews = "0";

    public CountryUsageStatsAll() {

    }

    @JsonProperty("country")

    public String getCountry() {

        return country_all;

    }

    @JsonProperty("downloads_all")

    public String getDownloads() {

        return downloads_all;

    }

    @JsonProperty("views_all")

    public String getViews() {

        return views_all;

    }

    @JsonProperty("CountriesList")

    public List<CountryUsageStats> getCountryUsageStats() {

        return countryUsageStats;

    }

    public void addViewsAll(String views_all) {

        this.views_all=views_all;

    }

    public void addDownloadsAll(String downloads_all) {

        this.downloads_all=downloads_all;

    }

    public void addCountryUsageStats(List<CountryUsageStats> countryUsageStats) {

        this.countryUsageStats = countryUsageStats;

    }

}

package eu.dnetlib.usagestats.portal;

import com.fasterxml.jackson.annotation.JsonProperty;

import java.util.List;

public class TotalStatsReposViewsDownloads {

    private String repositories;

    private String downloads;

    private String views;

    @JsonProperty("repositories")

    public String getRepositories() {

        return repositories;

    }

    public void addRepositories(String repositories) {

        this.repositories=repositories;

    }

    @JsonProperty("total_views")

    public String getViews() {

        return views;

    }

    public void addViews(String views) {

        this.views=views;

    }

    @JsonProperty("total_downloads")

    public String getDownloads() {

        return downloads;

    }

    public void addDownloads(String downloads) {

        this.downloads=downloads;

    }

}

package eu.dnetlib.usagestats.portal;

import com.fasterxml.jackson.annotation.JsonProperty;

import java.io.Serializable;

import java.util.ArrayList;

import java.util.List;

public class CountryUsageStats implements Serializable {

    private final static long serialVersionUID = 1;

    private String country = "";

    private String total_repos = "";

    private String downloads = "";

    private String views = "";

    //private String pageviews = "0";

    public CountryUsageStats() {

    }

    @JsonProperty("country")

    public String getCountry() {

        return country;

    }

    @JsonProperty("total_repos")

    public String getTotalRepos() {

        return total_repos;

    }

    @JsonProperty("downloads")

    public String getDownloads() {

        return downloads;

    }

    @JsonProperty("views")

    public String getViews() {

        return views;

    }

    public void addCountry(String country) {

        this.country=country;

    }

    public void addTotalRepos(String total_repos) {

        this.total_repos=total_repos;

    }

    public void addViews(String views) {

        this.views=views;

    }

    public void addDownloads(String downloads) {

        this.downloads=downloads;

    }

}

package eu.dnetlib.usagestats.portal;

import com.fasterxml.jackson.annotation.JsonProperty;

import java.io.Serializable;

import java.util.ArrayList;

import java.util.List;

public class CountryRepositories implements Serializable {

    private final static long serialVersionUID = 1;

    private String country = "";

    private String repository = "";

    //private String pageviews = "0";

    public CountryRepositories() {

    }

    @JsonProperty("country")

    public String getCountry() {

        return country;

    }

    @JsonProperty("repository")

    public String getRepository() {

        return repository;

    }

    public void addCountry(String country) {

        this.country=country;

    }

    public void addRepository(String repository) {

        this.repository=repository;

    }

}

package eu.dnetlib.usagestats.portal;

import com.fasterxml.jackson.annotation.JsonProperty;

import java.io.Serializable;

import java.util.ArrayList;

import java.util.List;

public class MonthlyUsageStats implements Serializable {

    private final static long serialVersionUID = 1;

    private String date = "0";

    private String downloads = "0";

    private String views = "0";

    //private String pageviews = "0";

    public MonthlyUsageStats() {

    }

    @JsonProperty("date")

    public String getDate() {

        return date;

    }

    @JsonProperty("downloads")

    public String getDownloads() {

        return downloads;

    }

    @JsonProperty("views")

    public String getViews() {

        return views;

    }

    public void addDate(String date) {

        this.date=date;

    }

    public void addViews(String views) {

        this.views=views;

    }

    public void addDownloads(String downloads) {

        this.downloads=downloads;

    }

}

import eu.dnetlib.usagestats.portal.CountryRepositories;

import eu.dnetlib.usagestats.portal.CountryUsageStats;

import eu.dnetlib.usagestats.portal.CountryUsageStatsAll;

import eu.dnetlib.usagestats.portal.MonthlyUsageStats;

import eu.dnetlib.usagestats.portal.TotalStatsReposViewsDownloads;

import java.util.List;

    List<MonthlyUsageStats> getMonthlyUsageStats();

    List<MonthlyUsageStats> getMonthlyUsageStatsForRepo(String id);

    CountryUsageStatsAll getCountryUsageStatsAll();

    CountryUsageStats getCountryUsageStats(String country);

    List<CountryRepositories> getCountryRepositories();

    TotalStatsReposViewsDownloads getTotalStatsReposViewsDownloads();

import eu.dnetlib.usagestats.portal.CountryRepositories;

import eu.dnetlib.usagestats.portal.CountryUsageStats;

import eu.dnetlib.usagestats.portal.CountryUsageStatsAll;

import eu.dnetlib.usagestats.portal.MonthlyUsageStats;

import eu.dnetlib.usagestats.portal.TotalStatsReposViewsDownloads;

public class UsageStatsServiceImpl implements UsageStatsService {

        String query = "SELECT 'views', sum(s.count), sum(s.openaire) FROM views_stats s where s.repository_id=? "

                + "UNION ALL SELECT 'downloads', sum(s.count), sum(s.openaire) FROM downloads_stats s where s.repository_id=? "

                + "UNION ALL SELECT 'pageviews', sum(s.count), '0' FROM pageviews_stats s, result_datasources rd where rd.id=s.result_id and rd.datasource=? ";

     */

        String query = "SELECT 'views', sum(s.count), sum(s.openaire) FROM views_stats s, project_results pr where pr.result=s.result_id and pr.id=? "

                + "UNION ALL SELECT 'downloads', sum(s.count), sum(s.openaire) FROM downloads_stats s, project_results pr where pr.result=s.result_id and pr.id=? "

                + "UNION ALL SELECT 'pageviews', sum(s.count), '0' FROM pageviews_stats s, project_results pr where pr.result=s.result_id and pr.id=?;";

        String query = "SELECT 'views', s.repository_id, CASE WHEN s.source='OpenAIRE' THEN d.name ELSE d.name ||' - '|| s.source END, sum(count), sum(openaire) FROM views_stats s, datasource d WHERE s.repository_id=d.id AND s.result_id=? GROUP BY s.source, s.repository_id, d.name "

                + "UNION ALL SELECT 'downloads', s.repository_id, CASE WHEN s.source='OpenAIRE' THEN d.name ELSE d.name ||' - '|| s.source END, sum(count), sum(s.openaire) FROM downloads_stats s, datasource d WHERE s.repository_id=d.id AND s.result_id=? GROUP BY s.source, s.repository_id, d.name "

                + "UNION ALL SELECT 'pageviews', 'OpenAIRE id', 'OpenAIRE', sum(count), '0' FROM pageviews_stats s WHERE result_id=?;";

    @Override

    public List<MonthlyUsageStats> getMonthlyUsageStats() {

        String query = "select date, sum(downloads) as downloads, sum(views) as views from usage_stats group by date order by date asc";

        return usageStatsRepository.executeMontlyUsageStats(query);

    }

    @Override

    public List<MonthlyUsageStats> getMonthlyUsageStatsForRepo(String id) {

        String query = "select date, sum(downloads) as downloads, sum(views) as views from usage_stats where repository_id=? group by date order by date asc";

        return usageStatsRepository.executeMontlyUsageStatsForRepo(query,id);

    }

   /* @Override

    public List<CountryUsageStats> getCountryUsageStats() {

        String query = "select c.name, sum(views) as views, sum(downloads) as downloads from public.torganization t, public.organization_datasources o, public.country c, usage_stats where o.datasource=t.id\n" +

                        "and c.code=t.country and o.id=repository_id group by c.name ";

        return usageStatsRepository.executeCountryUsageStats(query);

    }*/

    @Override

    public CountryUsageStatsAll getCountryUsageStatsAll() {

        String query = "select c.name, count(distinct repository_id) as total_repos, sum(views) as views, sum(downloads) as downloads from public.torganization t, public.organization_datasources o, public.country c, usage_stats where o.datasource=t.id\n" +

                        "and c.code=t.country and o.id=repository_id group by c.name ";

        return usageStatsRepository.executeCountryUsageStats(query);

    }

    @Override

    public CountryUsageStats getCountryUsageStats(String country) {

        String query = "select count(distinct repository_id) as total_repos, sum(views) as views, sum(downloads) as downloads from public.torganization t, public.organization_datasources o, public.country c, usage_stats where o.datasource=t.id\n" +

                        "and c.code=t.country and o.id=repository_id and c.name=?";

        return usageStatsRepository.executeCountryUsageStats(query, country);

    }

    @Override

    public List<CountryRepositories> getCountryRepositories() {

        String query = "select c.name, d.name from public.datasource d, public.torganization t, public.organization_datasources o, public.country c, usage_stats \n" +

"where o.datasource=t.id and c.code=t.country and o.id=repository_id and repository_id=d.id group by d.name, c.name order by c.name";

        return usageStatsRepository.executeCountryRepositories(query);

    }

    @Override

    public TotalStatsReposViewsDownloads getTotalStatsReposViewsDownloads() {

        String query = "select count(distinct repository_id) as repositories, sum(views) as views, sum(downloads) as downloads from usage_stats";

        return usageStatsRepository.executeTotalStatsReposViewsDownloads(query);

    }

import eu.dnetlib.usagestats.portal.CountryRepositories;

import eu.dnetlib.usagestats.portal.CountryUsageStats;

import eu.dnetlib.usagestats.portal.CountryUsageStatsAll;

import eu.dnetlib.usagestats.portal.MonthlyUsageStats;

import eu.dnetlib.usagestats.portal.TotalStatsReposViewsDownloads;

import java.util.List;

    @RequestMapping(value = "/monthlyusagestats")

    public List<MonthlyUsageStats> getMonthlyUsageStats() {

        log.info("stats request for months");

        return usageStatsService.getMonthlyUsageStats();

    }

    @RequestMapping(value = "/countryusagestats")

    public CountryUsageStatsAll getCountryUsageStatsAll() {

        log.info("stats request for countries");

        return usageStatsService.getCountryUsageStatsAll();

    }

    @RequestMapping(value = "/countryusagestats/{country}")

    public CountryUsageStats getCountryUsageStats(@PathVariable(value = "country") String country) {

        log.info("stats request for country "+country);

        return usageStatsService.getCountryUsageStats(country);

    }

    @RequestMapping(value = "/countryrepositories")

    public List<CountryRepositories> getCountryRepositories() {

        log.info("stats request for countries/repos");

        return usageStatsService.getCountryRepositories();

    }

    @RequestMapping(value = "/totals")

    public TotalStats getTotalStats() {

        log.info("total stats request");

        return usageStatsService.getTotalStats();

    }

    @RequestMapping(value = "/monthlyusagestats/{datasourceId}")

    public List<MonthlyUsageStats> getMonthlyUsageStatsForRepo(@PathVariable(value = "datasourceId") String datasourceId) {

        log.info("stats request for datasource: " + datasourceId);

        return usageStatsService.getMonthlyUsageStatsForRepo(datasourceId);

    }

    @RequestMapping(value = "/allmetrics")

    public TotalStatsReposViewsDownloads getTotalStatsReposViewsDownloads() {

        return usageStatsService.getTotalStatsReposViewsDownloads();

import eu.dnetlib.usagestats.portal.CountryRepositories;

import eu.dnetlib.usagestats.portal.CountryUsageStats;

import eu.dnetlib.usagestats.portal.CountryUsageStatsAll;

import eu.dnetlib.usagestats.portal.MonthlyUsageStats;

import eu.dnetlib.usagestats.portal.TotalStatsReposViewsDownloads;

     */

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

        }

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

        }

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

        }

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

        }

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

        }

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

        }

        return montlhyList;

    }

                     */

                totalStats = fromJsonTotalStats(redis_result);

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

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

            //st.setString(2, oid);

            //st.setString(2, doi);

         */

                    //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(6, openaire);

                    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 = 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(7, openaire);

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

                                String allOAIs = rs.getString(9);

                                String[] oaiArray = allOAIs.split("#!#");

                                for (int i = 0; i < oaiArray.length; i++) {

                                    reportItem.addIdentifier(new ItemIdentifier("OAI", oaiArray[i]));

                                }

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

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

             */

             */

             */

             */

             */

                        if (rs.getString(3) != null) {

                        if (rs.getString(3) != null) {

             */

                //st = connection.prepareStatement("SELECT res.result_id, r.title, r.publisher, r.source, rc.type, pids.pid, d.name, res.date, oids.orid, res.downloads, res.views FROM (SELECT us.repository_id, us.result_id, us.date, sum(us.downloads) as downloads, sum(us.views) as views FROM public.usage_stats us WHERE us.date>=? AND us.date<=? AND us.repository_id=? GROUP BY us.repository_id, us.result_id, us.date) AS res JOIN public.result r ON res.result_id=r.id JOIN public.datasource d ON d.id=res.repository_id JOIN public.result_classifications rc ON rc.id=r.id LEFT JOIN (SELECT pids.id, string_agg(pids.pid, '#!#') AS pid FROM public.result_pids pids, public.result_datasources rd WHERE rd.id=pids.id AND type='doi' AND rd.datasource=? GROUP BY pids.id) AS pids ON pids.id=r.id LEFT JOIN (SELECT oids.id, string_agg(oids.orid, '#!#') AS orid FROM public.result_oids oids, public.result_datasources rd WHERE rd.id=oids.id AND rd.datasource=? GROUP BY oids.id) AS oids ON oids.id=r.id ORDER BY res.result_id, res.date;");

                st = connection.prepareStatement("SELECT distinct res.result_id, r.title, r.publisher, r.source, rc.type, pids.pid, d.name, res.date, oids.orid, res.downloads, res.views FROM (SELECT us.repository_id, us.result_id, us.date, sum(us.downloads) as downloads, sum(us.views) as views FROM public.usage_stats us WHERE us.date>=? AND us.date<=? AND us.repository_id=? GROUP BY us.repository_id, us.result_id, us.date) AS res JOIN public.result r ON res.result_id=r.id JOIN public.datasource d ON d.id=res.repository_id JOIN public.result_classifications rc ON rc.id=r.id LEFT JOIN (SELECT pids.id, string_agg(pids.pid, '#!#') AS pid FROM public.result_pids pids, public.result_datasources rd WHERE rd.id=pids.id AND type='doi' AND rd.datasource=? GROUP BY pids.id) AS pids ON pids.id=r.id LEFT JOIN (SELECT oids.id, string_agg(oids.orid, '#!#') AS orid FROM public.result_oids oids, public.result_datasources rd WHERE rd.id=oids.id AND rd.datasource=? GROUP BY oids.id) AS oids ON oids.id=r.id ORDER BY res.result_id, res.date;");

                st = connection.prepareStatement("SELECT distinct res.result_id, r.title, r.publisher, r.source, rc.type, pids.pid, d.name, res.date, oids.orid, res.downloads, res.views FROM (SELECT us.repository_id, us.result_id, us.date, sum(us.downloads) as downloads, sum(us.views) as views FROM public.usage_stats us WHERE us.date>=? AND us.date<=? AND us.repository_id=? GROUP BY us.repository_id, us.result_id, us.date) AS res JOIN public.result r ON res.result_id=r.id JOIN public.datasource d ON d.id=res.repository_id JOIN public.result_classifications rc ON rc.id=r.id LEFT JOIN (SELECT pids.id, string_agg(pids.pid, '#!#') AS pid FROM public.result_pids pids, result_datasources rd WHERE rd.id=pids.id AND type='doi' AND rd.datasource=? GROUP BY pids.id) AS pids ON pids.id=r.id LEFT JOIN (SELECT oids.id, string_agg(oids.orid, '#!#') AS orid FROM public.result_oids oids, public.result_datasources rd WHERE rd.id=oids.id AND rd.datasource=? GROUP BY oids.id) AS oids ON oids.id=r.id WHERE rc.type=? ORDER BY res.result_id, res.date;");

             */

             */

#log4j.rootLogger = WARN, R

log4j.rootLogger = INFO, R

#log4j.logger.eu.dnetlib = WARN

log4j.logger.eu.dnetlib = INFO