D-Net

/**

 * Created by Glykeria Katsari 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 limit) {

        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 " + limit + " ;";

        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 to_date(os.timestamp_month,'MM/YYYY') ;";

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

        values.add(organizationId);

        return executePreparedQuery(query, values);

    }

}