D-Net

<?xml version="1.0" encoding="UTF-8"?>

<project xmlns="http://maven.apache.org/POM/4.0.0"

         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">

    <modelVersion>4.0.0</modelVersion>

    <groupId>eu.dnetlib</groupId>

    <artifactId>dnet-openaire-usage-stats-api</artifactId>

    <version>3.0.0-SNAPSHOT</version>

    <packaging>war</packaging>

    <parent>

        <groupId>org.springframework.boot</groupId>

        <artifactId>spring-boot-starter-parent</artifactId>

        <version>1.5.15.RELEASE</version>

    </parent>

    <dependencies>

        <dependency>

            <groupId>eu.dnetlib</groupId>

            <artifactId>dnet-openaire-usage-stats-sushilite</artifactId>

            <version>1.0.0-SNAPSHOT</version>

        </dependency>

        <dependency>

            <groupId>org.springframework.boot</groupId>

            <artifactId>spring-boot-starter-web</artifactId>

            <exclusions>

                <exclusion>

                    <groupId>org.springframework.boot</groupId>

                    <artifactId>spring-boot-starter-logging</artifactId>

                </exclusion>

            </exclusions>

        </dependency>

        <dependency>

            <groupId>org.springframework.boot</groupId>

            <artifactId>spring-boot-starter-data-jpa</artifactId>

        </dependency>

        <dependency>

            <groupId>org.springframework.boot</groupId>

            <artifactId>spring-boot-starter-data-redis</artifactId>

        </dependency>

        <dependency>

            <groupId>org.springframework.boot</groupId>

            <artifactId>spring-boot-starter-test</artifactId>

            <scope>test</scope>

        </dependency>

        <!--

        <dependency>

            <groupId>org.springframework.boot</groupId>

            <artifactId>spring-boot-starter-log4j2</artifactId>

        </dependency>

        -->

        <dependency>

            <groupId>log4j</groupId>

            <artifactId>log4j</artifactId>

            <version>1.2.17</version>

        </dependency>

        <dependency>

            <groupId>org.postgresql</groupId>

            <artifactId>postgresql</artifactId>

            <scope>runtime</scope>

        </dependency>

        <dependency>

            <groupId>junit</groupId>

            <artifactId>junit</artifactId>

            <scope>test</scope>

        </dependency>

        <dependency>

            <groupId>com.googlecode.json-simple</groupId>

            <artifactId>json-simple</artifactId>

            <version>1.1.1</version>

        </dependency>

        <dependency>

            <groupId>commons-dbutils</groupId>

            <artifactId>commons-dbutils</artifactId>

            <version>1.7</version>

        </dependency>

    </dependencies>

    <properties>

        <java.version>1.8</java.version>

        <tomcat.version>7.0.52</tomcat.version>

    </properties>

    <build>

        <plugins>

            <plugin>

                <groupId>org.springframework.boot</groupId>

                <artifactId>spring-boot-maven-plugin</artifactId>

            </plugin>

            <plugin>

                <artifactId>maven-war-plugin</artifactId>

                <configuration>

                    <failOnMissingWebXml>false</failOnMissingWebXml>

                </configuration>

            </plugin>

        </plugins>

        <finalName>usagestats</finalName>

    </build>

    <repositories>

        <repository>

            <id>spring-releases</id>

            <url>https://repo.spring.io/libs-release</url>

        </repository>

    </repositories>

    <pluginRepositories>

        <pluginRepository>

            <id>spring-releases</id>

            <url>https://repo.spring.io/libs-release</url>

        </pluginRepository>

    </pluginRepositories>

</project>

package eu.dnetlib.usagestats.portal;

import com.fasterxml.jackson.annotation.JsonProperty;

import java.io.Serializable;

/**

 * Created by tsampikos on 8/11/2016.

 */

public class RepositoryStats implements Serializable {

    private final static long serialVersionUID = 1;

    private String datasource_name = "";

    private String datasource_id = "";

    private String value = "";

    private String openaire = "";

    public RepositoryStats() {

    }

    public RepositoryStats(String datasource_name, String datasource_id, String value, String openaire) {

        this.datasource_name = datasource_name;

        this.datasource_id = datasource_id;

        this.value = value;

        this.openaire = openaire;

    }

    @JsonProperty("datasource_name")

    public String getDatasource_name() {

        return datasource_name;

    }

    @JsonProperty("datasource_id")

    public String getDatasource_id() {

        return datasource_id;

    }

    @JsonProperty("value")

    public String getValue() {

        return value;

    }

    @JsonProperty("openaire")

    public String getOpenaire() {

        return openaire;

    }

    /*

    public String toString(){

        return datasource_name + " " + datasource_id + " " + value + " " + openaire;

    }

    */

}

package eu.dnetlib.usagestats.portal;

import com.fasterxml.jackson.annotation.JsonProperty;

import java.util.List;

public class TotalStats {

    private int repositories;

    private int items;

    private int downloads;

    private int views;

    private List<YearlyStats> yearlyStats;

    @JsonProperty("yearly_stats")

    public List<YearlyStats> getYearlyStats() {

        return yearlyStats;

    }

    public void setYearlyStats(List<YearlyStats> yearlyStats) {

        this.yearlyStats = yearlyStats;

    }

    public TotalStats() {}

    public int getRepositories() {

        return repositories;

    }

    public void setRepositories(int repositories) {

        this.repositories = repositories;

    }

    public int getItems() {

        return items;

    }

    public void setItems(int items) {

        this.items = items;

    }

    public int getDownloads() {

        return downloads;

    }

    public void setDownloads(int downloads) {

        this.downloads = downloads;

    }

    public int getViews() {

        return views;

    }

    public void setViews(int views) {

        this.views = views;

    }

}

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 UsageStats implements Serializable {

    private final static long serialVersionUID = 1;

    private final List<RepositoryStats> downloads = new ArrayList<>();

    private final List<RepositoryStats> views = new ArrayList<>();

    private String total_downloads = "0";

    private String total_views = "0";

    private String pageviews = "0";

    private String total_openaire_views = "0";

    private String total_openaire_downloads = "0";

    public UsageStats() {

    }

    @JsonProperty("downloads")

    public List<RepositoryStats> getDownloads() {

        return downloads;

    }

    @JsonProperty("views")

    public List<RepositoryStats> getViews() {

        return views;

    }

    public void addViews(RepositoryStats view) {

        views.add(view);

    }

    public void addDownloads(RepositoryStats download) {

        downloads.add(download);

    }

    @JsonProperty("total_downloads")

    public String getTotal_downloads() {

        return total_downloads;

    }

    public void setTotal_downloads(String total_downloads) {

        this.total_downloads = total_downloads;

    }

    @JsonProperty("total_views")

    public String getTotal_views() {

        return total_views;

    }

    public void setTotal_views(String total_views) {

        this.total_views = total_views;

    }

    @JsonProperty("pageviews")

    public String getPageViews() {

        return pageviews;

    }

    public void setPageViews(String pageviews) {

        this.pageviews = pageviews;

    }

    @JsonProperty("total_openaire_views")

    public String getTotal_openaire_views() {

        return total_openaire_views;

    }

    public void setTotal_openaire_views(String total_openaire_views) {

        this.total_openaire_views = total_openaire_views;

    }

    @JsonProperty("total_openaire_downloads")

    public String getTotal_openaire_downloads() {

        return total_openaire_downloads;

    }

    public void setTotal_openaire_downloads(String total_openaire_downloads) {

        this.total_openaire_downloads = total_openaire_downloads;

    }

    /*

    public String toString(){

        String string;

        string = total_downloads + " ";

        string += total_views + " ";

        string += pageviews + " ";

        string += total_openaire + " ";

        for(RepositoryStats repositoryStats : downloads){

            string += repositoryStats.toString() + " ";

        }

        for(RepositoryStats repositoryStats : views){

            string += repositoryStats.toString() + " ";

        }

        return string;

    }

    */

}

package eu.dnetlib.usagestats.portal;

import com.fasterxml.jackson.annotation.JsonProperty;

import java.util.List;

public class YearlyStats {

    private int year;

    private int repositories;

    private int items;

    private int downloads;

    private int views;

    private List<MonthlyStats> monthlyStats;

    @JsonProperty("monthly_stats")

    public List<MonthlyStats> getMonthlyStats() {

        return monthlyStats;

    }

    public void setMonthlyStats(List<MonthlyStats> monthlyStats) {

        this.monthlyStats = monthlyStats;

    }

    public YearlyStats() {}

    public int getYear() {

        return year;

    }

    public void setYear(int year) {

        this.year = year;

    }

    public int getRepositories() {

        return repositories;

    }

    public void setRepositories(int repositories) {

        this.repositories = repositories;

    }

    public int getItems() {

        return items;

    }

    public void setItems(int items) {

        this.items = items;

    }

    public int getDownloads() {

        return downloads;

    }

    public void setDownloads(int downloads) {

        this.downloads = downloads;

    }

    public int getViews() {

        return views;

    }

    public void setViews(int views) {

        this.views = views;

    }

}

package eu.dnetlib.usagestats.portal;

public class MonthlyStats {

    private int month;

    private int repositories;

    private int items;

    private int downloads;

    private int views;

    public int getMonth() {

        return month;

    }

    public void setMonth(int month) {

        this.month = month;

    }

    public int getRepositories() {

        return repositories;

    }

    public void setRepositories(int repositories) {

        this.repositories = repositories;

    }

    public int getItems() {

        return items;

    }

    public void setItems(int items) {

        this.items = items;

    }

    public int getDownloads() {

        return downloads;

    }

    public void setDownloads(int downloads) {

        this.downloads = downloads;

    }

    public int getViews() {

        return views;

    }

    public void setViews(int views) {

        this.views = views;

    }

}

package eu.dnetlib.usagestats.services;

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

public interface SushiLiteService {

    ReportResponseWrapper buildReport(String reportName, String release, String requestorId, String beginDate,

                                             String endDate, String repositoryIdentifier, String itemIdentifier,

                                             String itemDataType, String hasDoi, String granularity, String callback);

    String displayReport(String reportName, String release, String requestorId, String beginDate,

                                      String endDate, String repositoryIdentifier, String itemIdentifier,

                                      String itemDataType, String hasDoi, String granularity, String callback, String pretty);

}

package eu.dnetlib.usagestats.services;

import com.fasterxml.jackson.databind.ObjectMapper;

import eu.dnetlib.usagestats.repositories.UsageStatsRepository;

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

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

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

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

import org.springframework.stereotype.Service;

import java.text.SimpleDateFormat;

import java.util.ArrayList;

import java.util.Calendar;

import java.util.Date;

import java.util.List;

import org.apache.log4j.Logger;

@Service

public class SushiLiteServiceImpl implements SushiLiteService {

    private final UsageStatsRepository usageStatsRepository;

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

    public SushiLiteServiceImpl(UsageStatsRepository usageStatsRepository) {

        this.usageStatsRepository = usageStatsRepository;

    }

    @Override

    public ReportResponseWrapper buildReport(String reportName, String release, String requestorId, String beginDate,

                                             String endDate, String repositoryIdentifier, String itemIdentifier,

                                             String itemDataType, String hasDoi, String granularity, String callback) {

        List<ReportItem> reportItems = new ArrayList<>();

        List<ReportException> reportExceptions = new ArrayList<>();

        if (!granularity.equalsIgnoreCase("totals") && !granularity.equalsIgnoreCase("monthly")) {

            reportExceptions.add(new ReportException("3062", "Warning", "Invalid ReportAttribute Value", "Granularity: \'" + granularity + "\' unknown. Defaulting to Monthly"));

            granularity = "Monthly";

        }

        Date beginDateParsed;

        if (!beginDate.equals("")) {

            beginDateParsed = tryParse(beginDate);

            if (beginDateParsed != null && (granularity.toLowerCase().equals("monthly") || beginDate.length() == 7)) {

                Calendar temp = Calendar.getInstance();

                temp.setTime(beginDateParsed);

                temp.set(Calendar.DAY_OF_MONTH, temp.getActualMinimum(Calendar.DAY_OF_MONTH));

                beginDateParsed = temp.getTime();

            }

        } else {

            Calendar temp = Calendar.getInstance();

            temp.add(Calendar.MONTH, -1);

            temp.set(Calendar.DAY_OF_MONTH, temp.getActualMinimum(Calendar.DAY_OF_MONTH));

            beginDateParsed = temp.getTime();

            reportExceptions.add(new ReportException("3021", "Warning", "Unspecified Date Arguments", "Begin Date set to default: " + new SimpleDateFormat("yyyy-MM-dd").format(beginDateParsed)));

        }

        Date endDateParsed;

        if (!endDate.equals("")) {

            endDateParsed = tryParse(endDate);

            if (endDateParsed != null && (granularity.toLowerCase().equals("monthly") || endDate.length() == 7)) {

                Calendar temp = Calendar.getInstance();

                temp.setTime(endDateParsed);

                temp.set(Calendar.DAY_OF_MONTH, temp.getActualMaximum(Calendar.DAY_OF_MONTH));

                endDateParsed = temp.getTime();

            }

        } else {

            Calendar temp = Calendar.getInstance();

            temp.add(Calendar.MONTH, -1);

            temp.set(Calendar.DAY_OF_MONTH, temp.getActualMaximum(Calendar.DAY_OF_MONTH));

            endDateParsed = temp.getTime();

            reportExceptions.add(new ReportException("3021", "Warning", "Unspecified Date Arguments", "End Date set to default: " + new SimpleDateFormat("yyyy-MM-dd").format(endDateParsed)));

        }

        //log.error("dates: " + beginDateParsed.toString() + " - " + endDateParsed.toString());

        if (beginDateParsed == null) {

            reportExceptions.add(new ReportException("3020", "Error", "Invalid Date Arguments", "Begin Date: " + beginDate + " is not a valid date"));

        }

        if (endDateParsed == null) {

            reportExceptions.add(new ReportException("3020", "Error", "Invalid Date Arguments", "End Date: " + endDate + " is not a valid date"));

        }

        if (beginDateParsed != null && endDateParsed != null && !beginDateParsed.before(endDateParsed)) {

            reportExceptions.add(new ReportException("3020", "Error", "Invalid Date Arguments", "BeginDate \'" + new SimpleDateFormat("yyyy-MM-dd").format(beginDateParsed) + "\' is greater than EndDate \'" + new SimpleDateFormat("yyyy-MM-dd").format(endDateParsed) + "\'"));

        }

        String repoid = "";

        if (!repositoryIdentifier.equals("")) {

            repoid = usageStatsRepository.executeRepoId(repositoryIdentifier, reportName.toLowerCase());

            if (repoid.equals("-1")) {

                reportExceptions.add(new ReportException("3060", "Error", "Invalid Filter Value", "RepositoryIdentifier: " + repositoryIdentifier + " is not valid"));

            }

        }

        String itemid = "";

        if (!itemIdentifier.equals("")) {

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

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

                case "oid":

                    itemid = itemIdentifier;

                    break;

                case "doi":

                    itemid = itemIdentifier;

                    break;

                case "openaire":

                    itemid = itemIdentifier;

                    break;

                default:

                    reportExceptions.add(new ReportException("3060", "Error", "Invalid Filter Value", "ItemIdentifier: " + itemIdentifier + " is not valid"));

                    itemid = "-1";

            }

        }

        if (itemid.equals("") && repoid.equals("") && !reportName.equalsIgnoreCase("rr1") && !reportName.equalsIgnoreCase("jr1")) {

            reportExceptions.add(new ReportException("3070", "Error", "Required Filter Missing", "ItemIdentifier or RepositoryIdentifier must be supplied"));

        }

        if (reportName.equalsIgnoreCase("ar1")) {

            if (!itemid.equals("-1") && !repoid.equals("-1") && beginDateParsed != null && endDateParsed != null && beginDateParsed.before(endDateParsed)) {

                if (!itemid.equals("")) {

                    if (itemDataType.equalsIgnoreCase("") || itemDataType.equalsIgnoreCase("article")) {

                        usageStatsRepository.executeItem(reportItems, itemIdentifier, repoid, "Article", beginDateParsed, endDateParsed, granularity);

                        if (reportItems.isEmpty()) {

                            reportExceptions.add(new ReportException("3030", "Error", "No Usage Available for Requested Dates", "Service did not find any data"));

                        }

                    } else {

                        reportExceptions.add(new ReportException("3030", "Error", "No Usage Available for Requested Dates", "Service did not find any data"));

                    }

                } else if (!repoid.equals("")) {

                    usageStatsRepository.executeBatchItems(reportItems, repoid, "Article", beginDateParsed, endDateParsed, granularity);

                    if (reportItems.isEmpty()) {

                        reportExceptions.add(new ReportException("3030", "Error", "No Usage Available for Requested Dates", "Service did not find any data"));

                    }

                }

            }

        } else if (reportName.equalsIgnoreCase("br1")) {

            if (!itemid.equals("-1") && !repoid.equals("-1") && beginDateParsed != null && endDateParsed != null && beginDateParsed.before(endDateParsed)) {

                if (!itemid.equals("")) {

                    if (itemDataType.equalsIgnoreCase("") || itemDataType.equalsIgnoreCase("book")) {

                        usageStatsRepository.executeItem(reportItems, itemIdentifier, repoid, "Book", beginDateParsed, endDateParsed, granularity);

                        if (reportItems.isEmpty()) {

                            reportExceptions.add(new ReportException("3030", "Error", "No Usage Available for Requested Dates", "Service did not find any data"));

                        }

                    } else {

                        reportExceptions.add(new ReportException("3030", "Error", "No Usage Available for Requested Dates", "Service did not find any data"));

                    }

                } else if (!repoid.equals("")) {

                    usageStatsRepository.executeBatchItems(reportItems, repoid, "Book", beginDateParsed, endDateParsed, granularity);

                    if (reportItems.isEmpty()) {

                        reportExceptions.add(new ReportException("3030", "Error", "No Usage Available for Requested Dates", "Service did not find any data"));

                    }

                }

            }

        } else if (reportName.equalsIgnoreCase("br2")) {

            if (!itemid.equals("-1") && !repoid.equals("-1") && beginDateParsed != null && endDateParsed != null && beginDateParsed.before(endDateParsed)) {

                if (!itemid.equals("")) {

                    if (itemDataType.equalsIgnoreCase("") || itemDataType.equalsIgnoreCase("part of book or chapter of book")) {

                        usageStatsRepository.executeItem(reportItems, itemIdentifier, repoid, "Part of book or chapter of book", beginDateParsed, endDateParsed, granularity);

                        if (reportItems.isEmpty()) {

                            reportExceptions.add(new ReportException("3030", "Error", "No Usage Available for Requested Dates", "Service did not find any data"));

                        }

                    } else {

                        reportExceptions.add(new ReportException("3030", "Error", "No Usage Available for Requested Dates", "Service did not find any data"));

                    }

                } else if (!repoid.equals("")) {

                    usageStatsRepository.executeBatchItems(reportItems, repoid, "Part of book or chapter of book", beginDateParsed, endDateParsed, granularity);

                    if (reportItems.isEmpty()) {

                        reportExceptions.add(new ReportException("3030", "Error", "No Usage Available for Requested Dates", "Service did not find any data"));

                    }

                }

            }

        } else if (reportName.equalsIgnoreCase("ir1")) {

            if (!itemid.equals("-1") && !repoid.equals("-1") && beginDateParsed != null && endDateParsed != null && beginDateParsed.before(endDateParsed)) {

                if (!itemid.equals("")) {

                    usageStatsRepository.executeItem(reportItems, itemIdentifier, repoid, itemDataType, beginDateParsed, endDateParsed, granularity);

                } else if (!repoid.equals("")) {

                    usageStatsRepository.executeBatchItems(reportItems, repoid, itemDataType, beginDateParsed, endDateParsed, granularity);

                }

                if (reportItems.isEmpty()) {

                    reportExceptions.add(new ReportException("3030", "Error", "No Usage Available for Requested Dates", "Service did not find any data"));

                }

            }

        } else if (reportName.equalsIgnoreCase("rr1")) {

            if (!repoid.equals("-1") && beginDateParsed != null && endDateParsed != null && beginDateParsed.before(endDateParsed)) {

                usageStatsRepository.executeRepo(reportItems, repoid, itemDataType, beginDateParsed, endDateParsed, granularity);

            }

            if (reportItems.isEmpty()) {

                reportExceptions.add(new ReportException("3030", "Error", "No Usage Available for Requested Dates", "Service did not find any data"));

            }

        } else if (reportName.equalsIgnoreCase("jr1")) {

            if (!repoid.equals("-1") && beginDateParsed != null && endDateParsed != null && beginDateParsed.before(endDateParsed)) {

                usageStatsRepository.executeJournal(reportItems, repoid, itemDataType, beginDateParsed, endDateParsed, granularity);

            }

            if (reportItems.isEmpty()) {

                reportExceptions.add(new ReportException("3030", "Error", "No Usage Available for Requested Dates", "Service did not find any data"));

            }

        }else if (reportName.equals("")) {

            reportExceptions.add(new ReportException("3050", "Error", "Report argument is missing", "You must supply a Report argument"));

        } else {

            reportExceptions.add(new ReportException("3000", "Error", "Report " + reportName + " not supported", "Supported reports: AR1, IR1, RR1, BR1, BR2"));

        }

        ReportResponse reportResponse = new ReportResponse(reportName, release, requestorId, beginDate, endDate,

                repositoryIdentifier, itemIdentifier, itemDataType, hasDoi, granularity, callback, reportItems, reportExceptions);

        return new ReportResponseWrapper(reportResponse);

    }

    @Override

    public String displayReport(String reportName, String release, String requestorId, String beginDate, String endDate, String repositoryIdentifier, String itemIdentifier, String itemDataType, String hasDoi, String granularity, String callback, String pretty) {

        ObjectMapper objectMapper = new ObjectMapper();

        try {

            if (pretty.equalsIgnoreCase("pretty")) {

                return "<pre>" + objectMapper.writerWithDefaultPrettyPrinter().writeValueAsString(buildReport(reportName, release, requestorId, beginDate, endDate, repositoryIdentifier, itemIdentifier, itemDataType, hasDoi, granularity, callback)).replaceAll("/", "\\\\/") + "</pre>";

            }

            return objectMapper.writeValueAsString(buildReport(reportName, release, requestorId, beginDate, endDate, repositoryIdentifier, itemIdentifier, itemDataType, hasDoi, granularity, callback)).replaceAll("/", "\\\\/");

        } catch (Exception e) {

            e.printStackTrace();

            return null;

        }

        //return report.getReport(reportP, release, requestorId, beginDate, endDate, repositoryIdentifier, itemIdentifier, itemDataType, hasDoi, granularity, callback, pretty);

    }

    private Date tryParse(String dateString) {

        try {

            if (dateString.length() == 7) {

                return new SimpleDateFormat("yyyy-MM").parse(dateString);

            } else if (dateString.length() == 10) {

                return new SimpleDateFormat("yyyy-MM-dd").parse(dateString);

            }

        } catch (Exception e) {

            log.error("ParseError: ", e);

        }

        return null;

    }

}

package eu.dnetlib.usagestats.services;

import eu.dnetlib.usagestats.portal.TotalStats;

import eu.dnetlib.usagestats.portal.UsageStats;

public interface UsageStatsService {

    UsageStats getDatasourceClicks(String id);

    UsageStats getProjectClicks(String id);

    UsageStats getResultClicks(String id);

    //UsageStats getOrganizationClicks(String id);

    TotalStats getTotalStats();

}

package eu.dnetlib.usagestats.services;

import eu.dnetlib.usagestats.portal.TotalStats;

import eu.dnetlib.usagestats.portal.UsageStats;

import eu.dnetlib.usagestats.repositories.UsageStatsRepository;

import org.springframework.stereotype.Service;

import java.util.ArrayList;

import java.util.List;

@Service

public class UsageStatsServiceImpl implements UsageStatsService{

    private final UsageStatsRepository usageStatsRepository;

    public UsageStatsServiceImpl(UsageStatsRepository usageStatsRepository) {

        this.usageStatsRepository = usageStatsRepository;

    }

    @Override

    public UsageStats getDatasourceClicks(String id) {

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

        List<String> values = new ArrayList<>();

        values.add(id);

        values.add(id);

        values.add(id);

        return usageStatsRepository.executeUsageStats(query, values, "datasource");

    }

    /*

    @Override

    public UsageStats getOrganizationClicks(String organizationId) {

        String query = "select sum(number_of_views) from organization_stats where id=?";

        List<String> values = new ArrayList<>();

        values.add(organizationId);

        return usageStatsRepository.executeUsageStats(query, values, "organization");

    }

    */

    @Override

    public UsageStats getProjectClicks(String projectId) {

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

        List<String> values = new ArrayList<>();

        values.add(projectId);

        values.add(projectId);

        values.add(projectId);

        return usageStatsRepository.executeUsageStats(query, values, "project");

    }

    @Override

    public UsageStats getResultClicks(String 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=?;";

        List<String> values = new ArrayList<>();

        values.add(id);

        values.add(id);

        values.add(id);

        return usageStatsRepository.executeUsageStats(query, values, "result");

    }

    @Override

    public TotalStats getTotalStats() {

        return usageStatsRepository.executeTotalStats();

    }

}

package eu.dnetlib.usagestats.config;

import org.apache.log4j.Logger;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.beans.factory.annotation.Value;

import org.springframework.context.annotation.Bean;

import org.springframework.context.annotation.Configuration;

import org.springframework.data.redis.connection.jedis.JedisConnectionFactory;

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

import org.springframework.data.redis.serializer.StringRedisSerializer;

/**

 * Created by tsampikos on 20/4/2017.

 */

@Configuration

public class SpringRedisConfiguration {

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

    @Value("${usagestats.redis.hostname}")

    private String hostname;

    @Value("${usagestats.redis.port}")

    private int port;

    @Bean

    public JedisConnectionFactory connectionFactory() {

        JedisConnectionFactory connectionFactory = new JedisConnectionFactory();

        connectionFactory.setHostName(hostname);

        connectionFactory.setPort(port);

        connectionFactory.setUsePool(false);

        log.info("Opening redis connection to : " + connectionFactory.getHostName() + ":" + connectionFactory.getPort());

        return connectionFactory;

    }

    @Bean

    @Autowired

    public RedisTemplate<String, String> redisTemplate() {

        RedisTemplate<String, String> redisTemplate = new RedisTemplate<>();

        redisTemplate.setConnectionFactory(connectionFactory());

        redisTemplate.setKeySerializer(new StringRedisSerializer());

        redisTemplate.setValueSerializer(new StringRedisSerializer());

        redisTemplate.setHashKeySerializer(new StringRedisSerializer());

        redisTemplate.setHashValueSerializer(new StringRedisSerializer());

        return redisTemplate;

    }

}

package eu.dnetlib.usagestats.config;

//import org.springframework.boot.jdbc.DataSourceBuilder;

import org.springframework.boot.autoconfigure.jdbc.DataSourceBuilder;

import org.springframework.boot.context.properties.ConfigurationProperties;

import org.springframework.context.annotation.Bean;

import org.springframework.context.annotation.Configuration;

import org.springframework.context.annotation.Primary;

import javax.sql.DataSource;

/**

 * Created by tsampikos on 8/3/2017.

 */

@Configuration

public class DataSourceConfiguration {

    @ConfigurationProperties(prefix = "usagestats")

    @Bean

    @Primary

    public DataSource getDataSource() {

        return DataSourceBuilder

                .create()

                .build();

    }

}

package eu.dnetlib.usagestats.config;

import org.springframework.context.annotation.Configuration;

import org.springframework.web.servlet.config.annotation.ViewControllerRegistry;

//import org.springframework.web.servlet.config.annotation.WebMvcConfigurer;

import org.springframework.web.servlet.config.annotation.WebMvcConfigurerAdapter;

/**

 * Created by tsampikos on 12/4/2017.

 */

@Configuration

//public class WebMvcConfiguration implements WebMvcConfigurer {

public class WebMvcConfiguration extends WebMvcConfigurerAdapter {

    @Override

    public void addViewControllers(ViewControllerRegistry registry) {

        registry.addViewController("/sushilite").setViewName("redirect:/sushilite/");

        registry.addViewController("/sushilite/").setViewName("forward:/sushilite/index.html");

        registry.addViewController("/sushilite/AR1").setViewName("redirect:/sushilite/AR1/");

        registry.addViewController("/sushilite/AR1/").setViewName("forward:/sushilite/AR1/index.html");

        registry.addViewController("/sushilite/IR1").setViewName("redirect:/sushilite/IR1/");

        registry.addViewController("/sushilite/IR1/").setViewName("forward:/sushilite/IR1/index.html");

        registry.addViewController("/sushilite/RR1").setViewName("redirect:/sushilite/RR1/");

        registry.addViewController("/sushilite/RR1/").setViewName("forward:/sushilite/RR1/index.html");

        registry.addViewController("/sushilite/JR1").setViewName("redirect:/sushilite/JR1/");

        registry.addViewController("/sushilite/JR1/").setViewName("forward:/sushilite/JR1/index.html");

        registry.addViewController("/sushilite/BR1").setViewName("redirect:/sushilite/BR1/");

        registry.addViewController("/sushilite/BR1/").setViewName("forward:/sushilite/BR1/index.html");

        registry.addViewController("/sushilite/BR2").setViewName("redirect:/sushilite/BR2/");

        registry.addViewController("/sushilite/BR2/").setViewName("forward:/sushilite/BR2/index.html");

        super.addViewControllers(registry);

    }

}

package eu.dnetlib.usagestats.controllers;

import eu.dnetlib.usagestats.portal.TotalStats;

import eu.dnetlib.usagestats.portal.UsageStats;

import eu.dnetlib.usagestats.services.UsageStatsService;

import org.apache.log4j.Logger;

import org.springframework.web.bind.annotation.CrossOrigin;

import org.springframework.web.bind.annotation.PathVariable;

import org.springframework.web.bind.annotation.RequestMapping;

import org.springframework.web.bind.annotation.RequestMethod;

import org.springframework.web.bind.annotation.RestController;

@RestController

@CrossOrigin(methods = RequestMethod.GET, origins = "*")

public class UsageStatsController {

    private final UsageStatsService usageStatsService;

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

    public UsageStatsController(UsageStatsService usageStatsService) {

        this.usageStatsService = usageStatsService;

    }

    @RequestMapping(value = "/datasources/{datasourceId}/clicks")

    public UsageStats getDatasourceClicks(@PathVariable(value = "datasourceId") String datasourceId) {

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

        return usageStatsService.getDatasourceClicks(datasourceId);

    }

    @RequestMapping(value = "/projects/{projectId}/clicks")

    public UsageStats getProjectClicks(@PathVariable(value = "projectId") String projectId) {

        log.info("stats request for project: " + projectId);

        return usageStatsService.getProjectClicks(projectId);

    }

    /*

    @RequestMapping(value = "/organizations/{organizationId}/clicks")

    public UsageStats getOrganizationClicks(@PathVariable(value = "organizationId") String organizationId) {

        log.info("stats request for organization: " + organizationId);

        return usageStatsService.getOrganizationClicks(organizationId);

    }

    */

    @RequestMapping(value = "/results/{resultId}/clicks")

    public UsageStats getResultClicks(@PathVariable(value = "resultId") String resultId) {

        log.info("stats request for result: " + resultId);

        return usageStatsService.getResultClicks(resultId);

    }

    @RequestMapping(value = "/totals")

    public TotalStats getTotalStats() {

        log.info("total stats request");

        return usageStatsService.getTotalStats();

    }

}

package eu.dnetlib.usagestats.controllers;

import eu.dnetlib.usagestats.services.SushiLiteService;

import org.apache.log4j.Logger;

import org.springframework.web.bind.annotation.RequestMapping;

import org.springframework.web.bind.annotation.RequestMethod;

import org.springframework.web.bind.annotation.RequestParam;

import org.springframework.web.bind.annotation.RestController;

/**

 * Created by tsampikos on 10/4/2017.

 */

@RestController

class SushiLiteController {

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

    private final SushiLiteService sushiLiteService;

    public SushiLiteController(SushiLiteService sushiLiteService) {

        this.sushiLiteService = sushiLiteService;

    }

    @RequestMapping(value = "/sushilite/GetReport/", method = RequestMethod.GET)

    public String getReport(@RequestParam(value = "Report", defaultValue = "") String reportP, @RequestParam(value = "Release", defaultValue = "4") String release, @RequestParam(value = "RequestorID", defaultValue = "anonymous") String requestorId,

                            @RequestParam(value = "BeginDate", defaultValue = "") String beginDate, @RequestParam(value = "EndDate", defaultValue = "") String endDate, @RequestParam(value = "RepositoryIdentifier", defaultValue = "") String repositoryIdentifier,

                            @RequestParam(value = "ItemIdentifier", defaultValue = "") String itemIdentifier, @RequestParam(value = "ItemDataType", defaultValue = "") String itemDataType,

                            @RequestParam(value = "hasDOI", defaultValue = "") String hasDoi, @RequestParam(value = "Granularity", defaultValue = "Monthly") String granularity, @RequestParam(value = "Callback", defaultValue = "") String callback,

                            @RequestParam(value = "Pretty", defaultValue = "") String pretty) {

        log.info("Sushi Report request: " + reportP + " from " + requestorId);

        log.info("repository identifier: " + repositoryIdentifier + " - item identifier: " + itemIdentifier);

        return sushiLiteService.displayReport(reportP, release, requestorId, beginDate, endDate, repositoryIdentifier, itemIdentifier, itemDataType, hasDoi, granularity, callback, pretty);

    }

}

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;

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

            st = connection.prepareStatement(query);

            int i = 1;

            for (String s : values) {

                st.setString(i, s);

                i++;

            }

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

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

            if (redis_result != null) {

                return fromJson(redis_result);

            }

            rs = st.executeQuery();

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

                while (rs.next()) {

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

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

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

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

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

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

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

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

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

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

                    }

                }

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

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

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

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

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

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

                while (rs.next()) {

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

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

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

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

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

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

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

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

                    }

                    /*

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

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

                    }

                    */

                }

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

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

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

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

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

            }

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

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

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

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

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

        } catch (Exception e) {

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

        } finally {

            DbUtils.closeQuietly(rs);

            DbUtils.closeQuietly(st);

            DbUtils.closeQuietly(connection);

        }

        return usageStats;

    }

    public TotalStats executeTotalStats() {

        TotalStats totalStats = null;

        try {

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

            if (redis_result != null) {

                totalStats =  fromJsonTotalStats(redis_result);

            } else {

                return updateTotalStats();

            }

        } catch (Exception e) {

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

        }

        return totalStats;

    }

    public TotalStats updateTotalStats() {

        TotalStats totalStats = new TotalStats();

        Connection connection = null;

        PreparedStatement st = null;

        ResultSet rs = null;

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

        try {

            connection = usageStatsDB.getConnection();

            //st = connection.prepareStatement("SELECT count(distinct d.repository_id) AS repository, count(distinct d.result_id) AS items, sum(d.count) AS downloads, sum(v.count) AS views from public.downloads_stats d FULL OUTER JOIN public.views_stats v ON d.source=v.source AND d.repository_id=v.repository_id AND d.result_id=v.result_id AND d.date=v.date;");

            st = connection.prepareStatement("SELECT count(distinct repository_id) AS repository, count(distinct result_id) AS items, sum(downloads) AS downloads, sum(views) AS views from usage_stats");

            rs = st.executeQuery();

            rs.next();

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

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

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

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

            rs.close();

            st.close();

            //st = connection.prepareStatement("select coalesce(d.date,v.date) as month, count(distinct d.repository_id) as repository, count(distinct d.result_id) as items, sum(d.count) as downloads, sum(v.count) as views from public.downloads_stats d FULL OUTER JOIN public.views_stats v ON d.source=v.source AND d.repository_id=v.repository_id AND d.result_id=v.result_id AND d.date=v.date group by month order by month;");

            st = connection.prepareStatement("SELECT date, count(distinct repository_id) AS repository, count(distinct result_id) AS items, sum(downloads) AS downloads, sum(views) AS views FROM public.usage_stats GROUP BY date ORDER BY date;");

            rs = st.executeQuery();

            while (rs.next()) {

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

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

                MonthlyStats monthlyStats = new MonthlyStats();

                monthlyStats.setMonth(month);

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

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

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

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

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

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

                } else {

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

                    newList.add(monthlyStats);

                    monthlyStatsMap.put(year, newList);

                }

            }

            rs.close();

            st.close();

            //st = connection.prepareStatement("SELECT COALESCE(SUBSTRING(d.date FROM 1 FOR 4), SUBSTRING(v.date FROM 1 FOR 4)) AS year, COUNT(DISTINCT d.repository_id) AS repository, COUNT(DISTINCT d.result_id) AS items, SUM(d.count) AS downloads, SUM(v.count) AS views FROM public.downloads_stats d FULL OUTER JOIN public.views_stats v ON d.source=v.source AND d.repository_id=v.repository_id AND d.result_id=v.result_id AND d.date=v.date GROUP BY year ORDER BY year;");

            st = connection.prepareStatement("SELECT SUBSTRING(date FROM 1 FOR 4) AS year, COUNT(DISTINCT repository_id) AS repository, COUNT(DISTINCT result_id) AS items, SUM(downloads) AS downloads, SUM(views) AS views FROM public.usage_stats GROUP BY year ORDER BY year;");

            rs = st.executeQuery();

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

            while (rs.next()) {

                YearlyStats yearlyStats = new YearlyStats();

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

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

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

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

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

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

                yearlyStatsList.add(yearlyStats);

            }

            totalStats.setYearlyStats(yearlyStatsList);

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

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

        } catch (Exception e) {

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

        } finally {

            DbUtils.closeQuietly(rs);

            DbUtils.closeQuietly(st);

            DbUtils.closeQuietly(connection);

        }

        return totalStats;

    }

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

        ObjectMapper objectMapper = new ObjectMapper();

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

    }

    public String executeRepoId(String repositoryIdentifier, String report) {

        PreparedStatement st = null;

        Connection connection = null;

        ResultSet rs = null;

        try {

            connection = usageStatsDB.getConnection();

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

            String openaire_id = "-1";

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

                case "openaire":

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

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

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

                    } else {

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

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

                    }

                    rs = st.executeQuery();

                    while (rs.next()) {

                        openaire_id = rs.getString(1);

                    }

                    return openaire_id;

                case "opendoar":

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

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

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

                    } else {

                        st = connection.prepareStatement("select distinct d.id from public.datasource d, public.datasource_oids di where di.orid=? and d.id=di.id and (type='Journal' OR type='Journal Aggregator/Publisher')");

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

                    }

                    rs = st.executeQuery();

                    while (rs.next()) {

                        openaire_id = rs.getString(1);

                    }

                    return openaire_id;

                case "issn":

                    st = connection.prepareStatement("select distinct d.id from public.datasource d, public.datasource_oids di, public.datasource_results dr where d.id=dr.id and di.orid like ? and d.id=di.id and (type='Journal' OR type='Journal Aggregator/Publisher')");

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

                    rs = st.executeQuery();

                    while (rs.next()) {

                        openaire_id = rs.getString(1);

                    }

                    return openaire_id;

                default:

                    return "-1";

            }

        } catch (Exception e) {

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

        } finally {

            DbUtils.closeQuietly(rs);

            DbUtils.closeQuietly(st);

            DbUtils.closeQuietly(connection);

        }

        return "-1";

    }

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

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

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

            case "oid":

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

                break;

            case "doi":

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

                break;

            case "openaire":

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

                break;

            default:

        }

    }

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

        Connection connection = null;

        PreparedStatement st = null;

        ResultSet rs = null;

        try {

            connection = usageStatsDB.getConnection();

            //st = connection.prepareStatement("SELECT DISTINCT roid.id FROM public.result_oids roid, public.downloads_stats s WHERE s.result_id=roid.id AND roid.orid=? UNION SELECT DISTINCT roid.id FROM public.result_oids roid, public.views_stats s WHERE s.result_id=roid.id AND roid.orid=?");

            st = connection.prepareStatement("SELECT DISTINCT roid.id FROM public.result_oids roid, public.usage_stats us WHERE us.result_id=roid.id AND roid.orid=?");

            st.setString(1, oid);

            st.setString(2, oid);

            rs = st.executeQuery();

            while (rs.next()) {

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

            }

            connection.close();

        } catch (Exception e) {

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

        } finally {

            DbUtils.closeQuietly(rs);

            DbUtils.closeQuietly(st);

            DbUtils.closeQuietly(connection);

        }

    }

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

        Connection connection = null;

        PreparedStatement st = null;

        ResultSet rs = null;

        try {

            connection = usageStatsDB.getConnection();

            //st = connection.prepareStatement("SELECT DISTINCT poid.id FROM public.result_pids poid, public.downloads_stats s WHERE s.result_id=poid.id AND poid.type='doi' AND poid.pid=? UNION SELECT DISTINCT poid.id FROM public.result_pids poid, public.views_stats s WHERE s.result_id=poid.id AND poid.type='doi' AND poid.pid=?");

            st = connection.prepareStatement("SELECT DISTINCT poid.id FROM public.result_pids poid, public.usage_stats us WHERE us.result_id=poid.id AND poid.type='doi' AND poid.pid=?");

            st.setString(1, doi);

            st.setString(2, doi);

            rs = st.executeQuery();

            while (rs.next()) {

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

            }

        } catch (Exception e) {

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

        } finally {

            DbUtils.closeQuietly(rs);

            DbUtils.closeQuietly(st);

            DbUtils.closeQuietly(connection);

        }

    }

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

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

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

        String beginDateStr = postgresFormat.format(beginDate);

        String endDateStr = postgresFormat.format(endDate);

        Connection connection = null;

        PreparedStatement st = null;

        ResultSet rs = null;

        /*

        Calendar startCalendar = Calendar.getInstance();

        startCalendar.setTime(beginDate);

        Calendar endCalendar = Calendar.getInstance();

        endCalendar.setTime(endDate);

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

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

        */

        try {

            connection = usageStatsDB.getConnection();

            if (repositoryIdentifier.equals("")) {

                if (itemDataType.equals("")) {

                    //st = connection.prepareStatement("SELECT res.repository_id, r.title, r.publisher, r.source, rc.type, pids.pid, d.name, res.ddate, oids.orid, res.downloads, res.views FROM (SELECT coalesce(ds.repository_id, vs.repository_id) AS repository_id, coalesce(ds.result_id, vs.result_id) AS result_id, coalesce(ds.date, vs.date) AS ddate, coalesce(ds.sum, 0) AS downloads, coalesce(vs.sum,0) AS views FROM (SELECT s.repository_id, s.result_id, s.date, sum(s.count) FROM public.downloads_stats s WHERE s.date>=? AND s.date<=? AND s.result_id=? GROUP BY s.repository_id, s.result_id, s.date) AS ds FULL OUTER JOIN (SELECT s.repository_id, s.result_id, s.date, sum(s.count) FROM public.views_stats s WHERE s.date>=? AND s.date<=? AND s.result_id=? GROUP BY s.repository_id, s.result_id, s.date) AS vs ON ds.result_id=vs.result_id AND ds.date=vs.date) AS res JOIN public.result r ON res.result_id=r.id JOIN public.datasource d ON d.id=res.repository_id JOIN public.result_classifications rc ON rc.id=r.id LEFT JOIN (SELECT pids.id, string_agg(pids.pid, '#!#') AS pid FROM public.result_pids pids WHERE pids.id=? AND type='doi' GROUP BY pids.id) AS pids ON pids.id=r.id LEFT JOIN (SELECT oids.id, string_agg(oids.orid, '#!#') AS orid FROM public.result_oids oids WHERE oids.id=? GROUP BY oids.id) AS oids ON oids.id=r.id ORDER BY res.repository_id, res.ddate;");

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

                    st.setString(1, beginDateStr);

                    st.setString(2, endDateStr);

                    st.setString(3, openaire);