D-Net

package eu.dnetlib.usagestats.services;

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

import eu.dnetlib.usagestats.portal.TotalStatsReposViewsDownloads;

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

    }

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

    }

}