D-Net

package eu.dnetlib.usagestats.controllers;

/**

 * Created by eri_k on 2/21/2016.

 */

import org.springframework.boot.SpringApplication;

import org.springframework.boot.autoconfigure.EnableAutoConfiguration;

import org.springframework.boot.autoconfigure.SpringBootApplication;

import org.springframework.boot.orm.jpa.EntityScan;

import org.springframework.context.annotation.ComponentScan;

import org.springframework.data.jpa.repository.config.EnableJpaRepositories;

@SpringBootApplication

@EnableAutoConfiguration

@ComponentScan(basePackages = {"eu.dnetlib.usagestats.domain","eu.dnetlib.usagestats.repos","eu.dnetlib.usagestats.controllers"})

public class Application {

    public static void main(String[] args) {

        SpringApplication.run(Application.class, args);

    }

   /* Read more

    :http://mrbool.com/rest-server-with-spring-data-spring-boot-and-postgresql/34023#ixzz40oAA4qI3*/

}

package eu.dnetlib.usagestats.controllers;

import eu.dnetlib.usagestats.domain.UsageStats;

import eu.dnetlib.usagestats.repos.DatasourceRepo;

import eu.dnetlib.usagestats.repos.OrganizationRepo;

import eu.dnetlib.usagestats.repos.ProjectRepo;

import eu.dnetlib.usagestats.repos.ResultRepo;

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

import org.springframework.transaction.annotation.Transactional;

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

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

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

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

import java.util.List;

/**

 * Created by eri_k on 3/27/2016.

 */

@RestController

@Transactional

public class UsageStatsController {

    @Autowired

    DatasourceRepo datasourceRepo;

    @Autowired

    ResultRepo resultRepo;

    @Autowired

    OrganizationRepo organizationRepo;

    @Autowired

    ProjectRepo projectRepo;

    //-----------------FOR DATASOURCES--------------------------

    /*@RequestMapping("/home/{error}")*/

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

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

        return datasourceRepo.getClicks(datasourceId);

    }

    @RequestMapping(value = "/stats/datasources/popular")

    public List<UsageStats> getDatasourceByPopularity() throws Exception {

        return datasourceRepo.getMostPopular();

    }

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

    public List<UsageStats> getDatasourcePubsByPopularity(@PathVariable(value = "datasourceId") String datasourceId) throws Exception {

        return datasourceRepo.getMostPopularPubs(datasourceId);

    }

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

    public List<UsageStats> getDatasourceProjectsByPopularity(@PathVariable(value = "datasourceId") String datasourceId) throws Exception {

        return datasourceRepo.getMostPopularProjects(datasourceId);

    }

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

    public List<UsageStats> getDatasourcePopularity(@PathVariable(value = "datasourceId") String datasourceId) throws Exception {

        return datasourceRepo.getPopularityOverTime(datasourceId);

    }

    //-----------------FOR PROJECTS--------------------------

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

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

        return projectRepo.getClicks(projectId);

    }

    @RequestMapping(value = "/stats/projects/popular")

    public List<UsageStats> getProjectByPopularity() throws Exception {

        return projectRepo.getMostPopular();

    }

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

    public List<UsageStats> getProjectPubsByPopularity(@PathVariable(value = "projectId") String projectId) throws Exception {

        return projectRepo.getMostPopularPubs(projectId);

    }

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

    public List<UsageStats> getProjectPopularity(@PathVariable(value = "projectId") String projectId) throws Exception {

        return projectRepo.getPopularityOverTime(projectId);

    }

    //FOR ORGANIZATIONS

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

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

        return organizationRepo.getClicks(organizationId);

    }

    @RequestMapping(value = "/stats/organizations/popular")

    public List<UsageStats> getOrganizationByPopularity() throws Exception {

        return organizationRepo.getMostPopular();

    }

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

    public List<UsageStats> getOrganizationProjectsByPopularity(@PathVariable(value = "organizationId") String organizationId) throws Exception {

        return organizationRepo.getMostPopularProjects(organizationId);

    }

//TOOD url not working???

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

    public List<UsageStats> getOrganizationPopularity(@PathVariable(value = "organizationId") String organizationId) throws Exception {

        return projectRepo.getPopularityOverTime(organizationId);

    }

    // FOR RESULTS

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

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

        return resultRepo.getClicks(resultId);

    }

    @RequestMapping(value = "/stats/results/popular")

    public List<UsageStats> getResultByPopularity() throws Exception {

        return resultRepo.getMostPopular();

    }

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

    public List<UsageStats> getResultPopularity(@PathVariable(value = "resultId") String resultId) throws Exception {

        return resultRepo.getPopularityOverTime(resultId);

    }

}

/**

 * Created by eri_k on 2/20/2016.

 */

package eu.dnetlib.usagestats.domain;

import javax.persistence.Entity;

import javax.persistence.GeneratedValue;

import javax.persistence.GenerationType;

import javax.persistence.Id;

import java.io.Serializable;

import java.util.UUID;

//@Entity(name = "usageStats")

public class UsageStats implements Serializable {

    public UsageStats() {

    }

   /* @Id

    @GeneratedValue(strategy = GenerationType.IDENTITY)*/

    private String id;

    private String name;

    private int value;

    public String getId() {

        return id;

    }

    public void setId(String id) {

        this.id = id;

    }

    public String getName() {

        return name;

    }

    public void setName(String name) {

        this.name = name;

    }

    public int getValue() {

        return value;

    }

    public void setValue(int value) {

        this.value = value;

    }

}

/**

 * Created by eri_k on 2/20/2016.

 */

package eu.dnetlib.usagestats.repos;

import eu.dnetlib.usagestats.domain.UsageStats;

import org.apache.log4j.Logger;

import org.springframework.data.rest.core.annotation.RepositoryRestResource;

import org.springframework.stereotype.Repository;

import java.util.ArrayList;

import java.util.List;

@RepositoryRestResource(collectionResourceRel = "datasourceStats", path = "datasourceStats")

@Repository

public class DatasourceRepo extends BaseRepository {

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

    public UsageStats getClicks(String id) {

        String query = "select d.id, d.name , sum (ds.numberofviews) from datasource d, usagestats.datasourcesstats ds " +

                "where ds.datasourceid=d.id and d.id=? group by d.id, d.name;";

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

        values.add(id);

        List<UsageStats> results = executePreparedQuery(query, values);

        if (!results.isEmpty()) return results.get(0);

        return new UsageStats();

    }

    public List<UsageStats> getMostPopular() {

        String query = "select d.id, d.name , sum (ds.numberofviews) from datasource d, usagestats.datasourcesstats ds where " +

                "ds.datasourceid=d.id group by d.id, d.name order by  sum (ds.numberofviews)  desc limit 10;";

        return executeQuery(query);

    }

    public List<UsageStats> getMostPopularPubs(String datasourceId) {

        String query = "select r.id, r.title , sum(rs.numberofviews) from result r, usagestats.resultsstats rs, result_datasources rd  where r.id=rd.id and rd.datasource=? and  rs.resultid=r.id group by r.id, r. title order by  sum(rs.numberofviews)  desc limit 10;";

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

        values.add(datasourceId);

        return executePreparedQuery(query, values);

    }

    public List<UsageStats> getMostPopularProjects(String datasourceId) {

        String query = "select p.id, p.title, sum(ps.numberofviews)  from project p, result_datasources rd, project_results pr , usagestats.projectsstats ps " +

                " where  rd.datasource=? and  p.id=pr.id and pr.result=rd.id  and ps.projectid=p.id " +

                " group by p.id, p.title order by sum (ps.numberofviews) desc limit 10;";

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

        values.add(datasourceId);

        return executePreparedQuery(query, values);

    }

    public List<UsageStats> getPopularityOverTime(String datasourceId) {

        String query = "select ds.datasourceid, ds.timestamp_month , ds.numberofviews from usagestats.datasourcesstats ds " +

                " where ds.datasourceid=?  order by ds.timestamp_month  desc ;";

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

        values.add(datasourceId);

        return executePreparedQuery(query, values);

    }

}

/**

 * Created by eri_k on 2/20/2016.

 */

package eu.dnetlib.usagestats.repos;

import eu.dnetlib.usagestats.domain.UsageStats;

import org.apache.log4j.Logger;

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

import org.springframework.data.rest.core.annotation.RepositoryRestResource;

import org.springframework.stereotype.Repository;

import javax.sql.DataSource;

import java.sql.*;

import java.util.ArrayList;

import java.util.List;

@RepositoryRestResource(collectionResourceRel = "resultStats", path = "resultStats")

@Repository

public class ResultRepo extends BaseRepository {

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

    public UsageStats getClicks(String id) {

        String query = "select r.id, r.title , sum (rs.numberofviews) from result r, usagestats.resultsstats rs " +

                "where rs.resultid=r.id and r.id=? group by r.id, r. title;";

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

        values.add(id);

        List<UsageStats> results = executePreparedQuery(query, values);

        if (!results.isEmpty()) return results.get(0);

        return new UsageStats();

    }

    public List<UsageStats> getMostPopular() {

        String query = "select r.id, r.title , sum (rs.numberofviews) from result r, usagestats.resultsstats rs where " +

                "rs.resultid=r.id group by r.id, r. title order by  sum (rs.numberofviews)  desc limit 10;";

        return executeQuery(query);

    }

    /*  public List<UsageStats> getMostPopularPubs(String projectId) {

          String query = "select r.id, r.title , sum (rs.numberofviews) from result r, usagestats.resultsstats rs, project_results pr " +

                          "where pr.id=? and r.id=pr.result  and rs.resultid=r.id group by r.id, r. title order by  sum (rs.numberofviews)  desc limit 10;";

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

          values.add(projectId);

          return executePreparedQuery(query, values);

      }

  */

    public List<UsageStats> getPopularityOverTime(String resultId) {

        String query = "select rs.resultid, rs.timestamp_month , rs.numberofviews from  usagestats.resultsstats rs " +

                " where rs.resultid=?  order by rs.timestamp_month  desc ;";

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

        values.add(resultId);

        return executePreparedQuery(query, values);

    }

}

package eu.dnetlib.usagestats.repos;

import eu.dnetlib.usagestats.domain.UsageStats;

import org.apache.log4j.Logger;

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

import javax.sql.DataSource;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.Statement;

import java.util.ArrayList;

import java.util.List;

/**

 * Created by eri_k on 3/28/2016.

 */

public class BaseRepository {

    @Autowired

    DataSource dataSource;

    private Connection connection;

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

    protected List<UsageStats> executeQuery(String query) {

        List<UsageStats> statsList = new ArrayList<UsageStats>();

        try {

            Connection connection = dataSource.getConnection();

            Statement st = connection.createStatement();

            st.execute(query);

            ResultSet rs = st.getResultSet();

            while (rs.next()) {

                UsageStats stats = new UsageStats();

                stats.setId(rs.getString(1));

                stats.setName(rs.getString(2));

                stats.setValue(rs.getInt(3));

                statsList.add(stats);

            }

            rs.close();

            st.close();

            connection.close();

        } catch (

                Exception e

                )

        {

            log.error("Cannot execute query : " + e);

        }

        return statsList;

    }

    protected List<UsageStats> executePreparedQuery(String query, List<String> values) {

        List<UsageStats> statsList = new ArrayList<UsageStats>();

        try {

            Connection connection = dataSource.getConnection();

            PreparedStatement st = connection.prepareStatement(query);

            int i = 1;

            for (String s : values) {

                st.setString(i, s);

                i++;

            }

            ResultSet rs = st.executeQuery();

            while (rs.next()) {

                UsageStats stats = new UsageStats();

                stats.setId(rs.getString(1));

                stats.setName(rs.getString(2));

                stats.setValue(rs.getInt(3));

                statsList.add(stats);

            }

            rs.close();

            st.close();

            connection.close();

        } catch (

                Exception e

                )

        {

            log.error("Cannot execute query : " + e);

        }

        return statsList;

    }

}

/**

 * Created by eri_k on 2/20/2016.

 */

package eu.dnetlib.usagestats.repos;

import eu.dnetlib.usagestats.domain.UsageStats;

import org.apache.log4j.Logger;

import org.springframework.data.rest.core.annotation.RepositoryRestResource;

import org.springframework.stereotype.Repository;

import java.util.ArrayList;

import java.util.List;

@Repository

public class OrganizationRepo extends BaseRepository {

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

    public UsageStats getClicks(String organizationId) {

        String query = "select o.id, o.name , sum (os.numberofviews) from organization o, usagestats.organizationsstats os " +

                "where os.organizationid=o.id and o.id=? group by o.id, o.name;";

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

        values.add(organizationId);

        List<UsageStats> results = executePreparedQuery(query, values);

        if (!results.isEmpty()) return results.get(0);

        return new UsageStats();

    }

    public List<UsageStats> getMostPopular() {

        String query = "select o.id, o.name , sum (os.numberofviews) from organization o, usagestats.organizationsstats os where " +

                "os.organizationid=o.id group by o.id, o.name order by  sum(os.numberofviews)  desc limit 10;";

        return executeQuery(query);

    }

    public List<UsageStats> getMostPopularProjects(String organizationId) {

        String query = "select p.id, p.title, sum(ps.numberofviews)  from project p, project_organizations por, usagestats.projectsstats ps " +

                " where  por.organization=? and  p.id=por.id and ps.projectid=p.id " +

                " group by p.id, p.title order by sum (ps.numberofviews) desc limit 10;";

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

        values.add(organizationId);

        return executePreparedQuery(query, values);

    }

    public List<UsageStats> getPopularityOverTime(String organizationId) {

        String query = "select os.organizationid, os.timestamp_month , os.numberofviews from usagestats.organizationsstats os " +

                " where os.organizationid=?  order by os.timestamp_month  desc ;";

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

        values.add(organizationId);

        return executePreparedQuery(query, values);

    }

}

/**

 * Created by eri_k on 2/20/2016.

 */

package eu.dnetlib.usagestats.repos;

import eu.dnetlib.usagestats.domain.UsageStats;

import org.apache.log4j.Logger;

import org.springframework.data.rest.core.annotation.RepositoryRestResource;

import org.springframework.stereotype.Repository;

import java.util.ArrayList;

import java.util.List;

@RepositoryRestResource(collectionResourceRel = "projectStats", path = "projectStats")

@Repository

public class ProjectRepo extends BaseRepository {

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

    public UsageStats getClicks(String projectId) {

        String query = "select p.id, p.title, sum (ps.numberofviews) from project p, usagestats.projectsstats ps " +

                "where ps.projectid=p.id and p.id=? group by p.id, p.title;";

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

        values.add(projectId);

        List<UsageStats> results = executePreparedQuery(query, values);

        if (!results.isEmpty()) return results.get(0);

        return new UsageStats();

    }

    public List<UsageStats> getMostPopular() {

        String query = "select p.id, p.title , sum (ps.numberofviews) from project p, usagestats.projectsstats ps where " +

                "ps.projectid=p.id group by p.id, p.title order by  sum(ps.numberofviews)  desc limit 10;";

        return executeQuery(query);

    }

    public List<UsageStats> getMostPopularPubs(String projectId) {

        String query = "select r.id, r.title , sum(rs.numberofviews) from result r, usagestats.resultsstats rs, " +

                "project_results pr  where r.id=pr.result and pr.id=? and  rs.resultid=r.id group by r.id, r. title order by  sum(rs.numberofviews)  desc limit 10;";

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

        values.add(projectId);

        return executePreparedQuery(query, values);

    }

    public List<UsageStats> getPopularityOverTime(String projectId) {

        String query = "select ps.projectid, ps.timestamp_month , ps.numberofviews from usagestats.projectsstats ps " +

                " where   ps.projectid=?  order by ps.timestamp_month  desc ;";

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

        values.add(projectId);

        return executePreparedQuery(query, values);

    }

}

#spring.jpa.database=POSTGRESQL

#spring.datasource.platform=postgres

#spring.jpa.show-sql=true

#spring.jpa.hibernate.ddl-auto=validate

spring.database.driverClassName=org.postgresql.Driver

spring.datasource.url=jdbc:postgresql://194.177.192.118:5432/stats

spring.datasource.username=postgres

spring.datasource.password=vereniki

server.port=8080

<?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>0.0.1-SNAPSHOT</version>

    <parent>

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

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

        <version>1.3.2.RELEASE</version>

    </parent>

    <dependencies>

        <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-data-rest</artifactId>

        </dependency>

        <dependency>

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

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

        </dependency>

        <dependency>

            <groupId>postgresql</groupId>

            <artifactId>postgresql</artifactId>

            <version>9.1-901-1.jdbc4</version>

            <scope>runtime</scope>

        </dependency>

        <dependency>

            <groupId>junit</groupId>

            <artifactId>junit</artifactId>

            <version>3.8.1</version>

            <scope>test</scope>

        </dependency>

        <dependency>

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

            <artifactId>json-simple</artifactId>

            <version>1.1.1</version>

        </dependency>

    </dependencies>

    <properties>

        <java.version>1.7</java.version>

    </properties>

    <build>

        <plugins>

            <plugin>

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

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

            </plugin>

        </plugins>

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