D-Net

package eu.dnetlib.data.claims.handler;

import com.google.gson.*;

import eu.dnetlib.data.claims.entity.Metrics;

import eu.dnetlib.data.claims.sql.SQLStoreException;

import eu.dnetlib.data.claims.sql.SqlDAO;

import eu.dnetlib.data.claims.utils.QueryGenerator;

import org.apache.log4j.Logger;

import org.postgresql.util.PGobject;

import org.springframework.scheduling.annotation.EnableScheduling;

import org.springframework.scheduling.annotation.Scheduled;

import org.springframework.stereotype.Service;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.util.*;

@EnableScheduling

@Service

public class MetricsHandler {

    SqlDAO sqlDAO = null;

    QueryGenerator queryGenerator = null;

    private static final Logger logger = Logger.getLogger(MetricsHandler.class);

    public int countEUProjects() throws SQLStoreException, SQLException {

        logger.info("Counting EU projects...");

        ArrayList<Object> params = new ArrayList<>();

        String query = queryGenerator.generateSelectNumOfEUProjectsQuery();

        ResultSet rs = sqlDAO.executePreparedQuery(query, params);

        int res = fetchCountByResultSet(rs);

        return res;

    }

    public int countCountriesFromClaimedBy() throws SQLStoreException, SQLException {

        logger.info("Counting countries for people who claimed...");

        ArrayList<Object> params = new ArrayList<>();

        String query = queryGenerator.generateSelectNumOfCountriesQuery();

        ResultSet rs = sqlDAO.executePreparedQuery(query, params);

        int res = fetchCountByResultSet(rs);

        return res;

    }

    public int countUniqueResearchResults() throws SQLStoreException, SQLException {

        logger.info("Counting unique research results claimed...");

        ArrayList<Object> params = new ArrayList<>();

        String query = queryGenerator.generateSelectNumOfUniqueResearchResultsQuery();

        ResultSet rs = sqlDAO.executePreparedQuery(query, params);

        int res = fetchCountByResultSet(rs);

        return res;

    }

    // every day at midnight

    @Scheduled(cron = "0 0 0 * * ?")

    // every 5 mins for testing

//    @Scheduled(cron = "0 0/5 * * * *")

    public void calculateMetrics() throws SQLStoreException, Exception {

        logger.info("Calculating metrics...");

        String id = null;

        String query = queryGenerator.generateSelectAndBuildMetricsQuery();

        ResultSet rs1 = sqlDAO.executePreparedQuery(query);

        Metrics calculatedMetrics = fetchMetricsByResultSet(rs1, false);

        ArrayList<Object> params = new ArrayList<>();

        query = queryGenerator.generateUpdateMetricsQuery(calculatedMetrics, params);

        sqlDAO.executeUpdateQuery(query, params);

    }

    public Metrics getMetrics() throws SQLStoreException, SQLException {

        logger.info("Fetching metrics...");

        String query = queryGenerator.generateSelectMetricsQuery();

        ResultSet rs = sqlDAO.executePreparedQuery(query);

        Metrics res = fetchMetricsByResultSet(rs, true);

        return res;

    }

    private Metrics fetchMetricsByResultSet(ResultSet rs, boolean fromMetricsTable) throws SQLException {

        logger.debug("fetchMetricsByResultSet (from table="+fromMetricsTable+")");

        Metrics metrics = null;

        boolean hasNext = false;

        List<JsonObject> metrics_per_dashboard = new ArrayList<>();

        if(fromMetricsTable) {

            if(rs.next()) {

                metrics = new Metrics();

                PGobject pg = (PGobject) rs.getObject("metrics_per_dashboard");

                String jsonText = pg.getValue();

                Gson gson = new Gson();

                JsonArray myList2 = gson.fromJson(jsonText, JsonArray.class);

                List<JsonObject> listObj = new ArrayList<>();

                for(JsonElement jsonElement : myList2) {

                    listObj.add(jsonElement.getAsJsonObject());

                }

                metrics.setMetrics_per_dashboard(listObj);

                metrics.setDate(rs.getTimestamp("date"));

            }

        } else {

            while (rs.next()) {

                String[] dashboard_elements = ((String) rs.getString("claimed_in_dashboard")).split("_", 2);

                JsonObject dashboardMetrics = new JsonObject();

                dashboardMetrics.add("environment", new JsonPrimitive(dashboard_elements[0]));

                dashboardMetrics.add("dashboard", new JsonPrimitive(dashboard_elements[1]));

                dashboardMetrics.add("claims", new JsonPrimitive(rs.getInt("total_claims")));

                dashboardMetrics.add("users", new JsonPrimitive(rs.getInt("total_users")));

                dashboardMetrics.add("projects", new JsonPrimitive(rs.getInt("projects")));

                dashboardMetrics.add("eu_projects", new JsonPrimitive(rs.getInt("eu_projects")));

                dashboardMetrics.add("countries", new JsonPrimitive(rs.getInt("countries")));

                dashboardMetrics.add("research_results", new JsonPrimitive(rs.getInt("research_results")));

                metrics_per_dashboard.add(dashboardMetrics);

                hasNext = true;

            }

            if (hasNext) {

                metrics = new Metrics();

                metrics.setMetrics_per_dashboard(metrics_per_dashboard);

                metrics.setDate(new Date());

                metrics.setId("current");

            }

        }

        return metrics;

    }

    private int fetchCountByResultSet(ResultSet rs) throws SQLException {

        int count = 0;

        if(rs.next()) {

            count = rs.getInt("count");

        }

        return count;

    }

    public SqlDAO getSqlDAO() {

        return sqlDAO;

    }

    public void setSqlDAO(SqlDAO sqlDAO) {

        this.sqlDAO = sqlDAO;

    }

    public QueryGenerator getQueryGenerator() {

        return queryGenerator;

    }

    public void setQueryGenerator(QueryGenerator queryGenerator) {

        this.queryGenerator = queryGenerator;

    }

}