D-Net

package eu.dnetlib.goldoa.service.stats;

import eu.dnetlib.goldoa.domain.Request;

import eu.dnetlib.goldoa.domain.stats.Browse;

import eu.dnetlib.goldoa.domain.stats.DateSeries;

import eu.dnetlib.goldoa.domain.stats.MapData;

import eu.dnetlib.goldoa.domain.stats.Numbers;

import eu.dnetlib.goldoa.domain.stats.Quadruple;

import eu.dnetlib.goldoa.domain.stats.Series;

import eu.dnetlib.goldoa.domain.stats.Triple;

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

import org.springframework.jdbc.core.JdbcTemplate;

import org.springframework.jdbc.core.RowMapper;

import org.springframework.transaction.annotation.Transactional;

import javax.sql.DataSource;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.text.SimpleDateFormat;

import java.util.ArrayList;

import java.util.Date;

import java.util.HashMap;

import java.util.List;

import java.util.Map;

/**

 * Created by antleb on 11/19/15.

 */

@Transactional(readOnly = true)

public class StatsManagerImpl implements StatsManager {

	public static final String NUMBERS_REQUESTS = "select count(*) from request r where r.status in (" + Request.RequestStatus.APPROVED.getCode() + ", " + Request.RequestStatus.ACCOUNTING_PROCESSING.getCode() + ", " + Request.RequestStatus.ACCOUNTING_PAID.getCode() + ", " + Request.RequestStatus.CONDITIONALLY_APPROVED.getCode() + ", " + Request.RequestStatus.ACCOUNTING_ON_HOLD.getCode() + ")" ;

	public static final String NUMBERS_PAID_REQUESTS = "select count(*) from request r where r.status in (" + Request.RequestStatus.ACCOUNTING_PAID.getCode() + ")" ;

	public static final String NUMBERS_PROJECTS = "select count(distinct project) from request r where r.status in (" + Request.RequestStatus.APPROVED.getCode() + ", " + Request.RequestStatus.ACCOUNTING_PROCESSING.getCode() + ", " + Request.RequestStatus.ACCOUNTING_PAID.getCode() + ", " + Request.RequestStatus.CONDITIONALLY_APPROVED.getCode() + ", " + Request.RequestStatus.ACCOUNTING_ON_HOLD.getCode() + ")";

	public static final String NUMBERS_TOTAL_AMOUNT = "select sum(apc_paid) from request r";

	public static final String NUMBERS_AVERAGE_AMOUNT = "select avg(apc_paid)::INTEGER from request r";

	public static final String NUMBERS_AVERAGE_PER_ARTICLE = "select avg(apc_paid)::INTEGER from request r join publication p on r.publication=p.id where p.type='ARTICLE'";

	public static final String NUMBERS_AVERAGE_PER_MONOGRAPH = "select avg(apc_paid)::INTEGER from request r join publication p on r.publication=p.id where p.type='MONOGRAPH'";

	public static final String NUMBERS_AVERAGE_PER_BOOK_CHAPTER = "select avg(apc_paid)::INTEGER from request r join publication p on r.publication=p.id where p.type='BOOK_CHAPTER'";

	public static final String NUMBERS_PAID_REQUESTS_WITH_DOI = "select count(*) from request r join publication p on r.publication=p.id where p.doi is not null and r.status=" + Request.RequestStatus.ACCOUNTING_PAID;

	public static final String NUMBERS_APPROVED_REQUESTS_WITH_DOI = "select count(*) from request r join publication p on r.publication=p.id where p.doi is not null and r.status in (" + Request.RequestStatus.APPROVED.getCode() + ", " + Request.RequestStatus.ACCOUNTING_PROCESSING.getCode() + ", " + Request.RequestStatus.ACCOUNTING_PAID.getCode() + ", " + Request.RequestStatus.CONDITIONALLY_APPROVED.getCode() + ", " + Request.RequestStatus.ACCOUNTING_ON_HOLD.getCode() + ", " + Request.RequestStatus.CONDITIONALLY_APPROVED.getCode() + ")";

	public static final String SERIES_COUNTRY = "select c.name as category, count(r.id) as count, sum(apc_paid) as apc, avg(apc_paid) as avgapc from request r join organisation o on r.organization=o.id join country c on c.id=o.country %WHERE% group by c.name";

	public static final String SERIES_STATUS = "select r.status as category, count(r.id) as count, sum(apc_paid) as apc, avg(apc_paid) as avgapc from request r %WHERE% group by r.status";

	public static final String SERIES_TYPE = "select p.type as category, count(r.id) as count, sum(apc_paid) as apc, avg(apc_paid) as avgapc from request r join publication p on p.id=r.publication %WHERE% group by p.type";

	public static final String SERIES_ORGANIZATION = "select o.name as category, count(r.id) as count, sum(apc_paid) as apc, avg(apc_paid) as avgapc from request r join organisation o on r.organization=o.id %WHERE% group by o.shortname, o.name";

	public static final String SERIES_PUBLISHER = "select p.name as category, count(r.id) as count, sum(apc_paid) as apc, avg(apc_paid) as avgapc from (select r.id, r.status, r.apc_paid, j.publisher, 'j' from request r join journal j on r.journal=j.id where j.publisher is not null union select r.id, r.status, r.apc_paid, p.id, 'p' from request r join publisher p on r.publisher=p.id) as r join publisher p on r.publisher=p.id %WHERE% group by p.name";

	public static final String BROWSE_SCIENTIFIC_AREA = "select distinct p.scientificarea as id, p.scientificarea as category, count(r.id) as count from request r join project p on r.project=p.id group by p.scientificarea order by category";

	public static final String BROWSE_COUNTRY = "select c.id as id, c.name as category, count(r.id) as count from request r join organisation o on r.organization=o.id join country c on c.id=o.country group by c.name, c.id order by category";

	public static final String BROWSE_ORGANIZATION = "select o.id as id, case when o.shortname is not null then o.shortname else o.name end  as category, count(r.id) as count from request r join organisation o on o.id=r.organization group by o.shortname, o.name, o.id order by category";

	public static final String BROWSE_PUBLISHER = "select p.id as id, p.name as category, count(r.id) as count from (select r.id, r.status, j.publisher, 'j' from request r join journal j on r.journal=j.id where j.publisher is not null union select r.id, r.status, p.id, 'p' from request r join publisher p on r.publisher=p.id) as r join publisher p on r.publisher=p.id group by p.name, p.id order by category";

	public static final String BROWSE_STATUS = "select r.status as id, r.status as category, count(r.id) as count from request r group by status";

	@Autowired

	private DataSource dataSource;

	@Override

	public Numbers getNumbers(Numbers.Category[] categories) {

		JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

		Map<Numbers.Category, Integer> map = new HashMap<Numbers.Category, Integer>();

		for (Numbers.Category category:categories) {

			switch (category) {

				case REQUESTS:

					map.put(Numbers.Category.REQUESTS, jdbcTemplate.queryForObject(NUMBERS_REQUESTS, Integer.class));

					break;

				case PAID_REQUESTS:

					map.put(Numbers.Category.PAID_REQUESTS, jdbcTemplate.queryForObject(NUMBERS_PAID_REQUESTS, Integer.class));

					break;

				case PROJECTS:

					map.put(Numbers.Category.PROJECTS, jdbcTemplate.queryForObject(NUMBERS_PROJECTS, Integer.class));

					break;

				case TOTAL_AMOUNT:

					map.put(Numbers.Category.TOTAL_AMOUNT, jdbcTemplate.queryForObject(NUMBERS_TOTAL_AMOUNT, Integer.class));

					break;

				case AVERAGE_AMOUNT:

					map.put(Numbers.Category.AVERAGE_AMOUNT, jdbcTemplate.queryForObject(NUMBERS_AVERAGE_AMOUNT, Integer.class));

					break;

				case AVERAGE_PER_ARTICLE:

					map.put(Numbers.Category.AVERAGE_PER_ARTICLE, jdbcTemplate.queryForObject(NUMBERS_AVERAGE_PER_ARTICLE, Integer.class));

					break;

				case AVERAGE_PER_MONOGRAPH:

					map.put(Numbers.Category.AVERAGE_PER_MONOGRAPH, jdbcTemplate.queryForObject(NUMBERS_AVERAGE_PER_MONOGRAPH, Integer.class));

					break;

				case AVERAGE_PER_BOOK_CHAPTER:

					map.put(Numbers.Category.AVERAGE_PER_BOOK_CHAPTER, jdbcTemplate.queryForObject(NUMBERS_AVERAGE_PER_BOOK_CHAPTER, Integer.class));

					break;

				case PAID_REQUESTS_WITH_DOI:

					map.put(Numbers.Category.PAID_REQUESTS_WITH_DOI, jdbcTemplate.queryForObject(NUMBERS_PAID_REQUESTS_WITH_DOI, Integer.class));

					break;

				case APPROVED_REQUESTS_WITH_DOI:

					map.put(Numbers.Category.APPROVED_REQUESTS_WITH_DOI, jdbcTemplate.queryForObject(NUMBERS_APPROVED_REQUESTS_WITH_DOI, Integer.class));

					break;

			}

		}

		return new Numbers(map);

	}

	@Override

	public Series getSeries(Series.Category[] categories, Request.RequestStatus[] statuses) {

		JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

		Map<Series.Category, List<Quadruple<String, Integer, Float, Float>>> map = new HashMap<Series.Category, List<Quadruple<String, Integer, Float, Float>>>();

		for (final Series.Category category:categories) {

			String query = null;

			switch (category) {

				case COUNTRY:

					query = SERIES_COUNTRY;

					break;

				case ORGANIZATION:

					query = SERIES_ORGANIZATION;

					break;

				case PUBLISHER:

					query = SERIES_PUBLISHER;

					break;

				case STATUS:

					query = SERIES_STATUS;

					break;

				case PUBLICATION_TYPE:

					query = SERIES_TYPE;

			}

			if (statuses != null) {

				StringBuilder ssb = new StringBuilder();

				for (Request.RequestStatus status:statuses) {

					String clause;

					int code = status.getCode();

					switch (code) {

						case 0:

							clause = " r.status=0";

							break;

						default:

							clause = " (r.status = " + code + ")";

							break;

					}

					ssb.append(ssb.length()==0?clause:" or " + clause);

				}

				query = query.replace("%WHERE%", " where " + ssb.toString());

			} else {

				query = query.replace("%WHERE%", "");

			}

			map.put(category, jdbcTemplate.query(query, new RowMapper<Quadruple<String, Integer, Float, Float>>() {

				@Override

				public Quadruple<String, Integer, Float, Float> mapRow(ResultSet rs, int rowNum) throws SQLException {

					if (category != Series.Category.STATUS)

						return new Quadruple<String, Integer, Float, Float>(rs.getString("category"), rs.getInt("count"), rs.getFloat("apc"), rs.getFloat("avgapc"));

					else

						return new Quadruple<String, Integer, Float, Float>(Request.RequestStatus.forStatus(rs.getInt("category")).getValue(), rs.getInt("count"), rs.getFloat("apc"), rs.getFloat("avgapc"));

				}

			}));

		}

		return new Series(map);

	}

	@Override

	public DateSeries getDateSeries(DateSeries.Over over, boolean cumulative, Date from, Date to, String[] publishers, String[] organizations, String[] countries, String[] scientificAreas) {

		StringBuilder sb = new StringBuilder();

		String dateColumn = null;

		String windowKeyword ;

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

		switch (over) {

			case DAY:

				dateColumn = "date_trunc('day', submissiondate)";

				break;

			case MONTH:

				dateColumn = "date_trunc('month', submissiondate)";

				break;

			case YEAR:

				dateColumn = "date_trunc('year', submissiondate)";

				break;

		}

		if (cumulative)

			windowKeyword = "order";

		else

			windowKeyword = "partition";

		if (from != null) {

			clauses.add(" r.submissiondate >= '" + new SimpleDateFormat("yyyy-MM-dd").format(from) + "' ");

		}

		if (to != null) {

			clauses.add(" r.submissiondate <= '" + new SimpleDateFormat("yyyy-MM-dd").format(to) + "' ");

		}

		if (publishers != null && publishers.length > 0) {

			String clause = "pub.id in (";

			for (int i = 0; i < publishers.length; i++) {

				if (i > 0)

					clause += ", ";

				clause += "'" + publishers[i] + "'";

			}

			clause += ")";

			clauses.add(clause);

		}

		if (organizations != null && organizations.length > 0) {

			String clause = "o.id in (";

			for (int i = 0; i < organizations.length; i++) {

				if (i > 0)

					clause += ", ";

				clause += "'" + organizations[i] + "'";

			}

			clause += ")";

			clauses.add(clause);

		}

		if (countries != null && countries.length > 0) {

			String clause = "c.id in (";

			for (int i = 0; i < countries.length; i++) {

				if (i > 0)

					clause += ", ";

				clause += "'" + countries[i] + "'";

			}

			clause += ")";

			clauses.add(clause);

		}

		if (scientificAreas != null && scientificAreas.length > 0) {

			String clause = "p.scientificarea in (";

			for (int i = 0; i < scientificAreas.length; i++) {

				if (i > 0)

					clause += ", ";

				clause += "'" + scientificAreas[i] + "'";

			}

			clause += ")";

			clauses.add(clause);

		}

		sb.append("select distinct ").append(dateColumn).append(" as date, count(r.id) over (").append(windowKeyword).append(" by ").append(dateColumn).append(") as count, sum(apc_paid) over (").append(windowKeyword).append(" by ").append(dateColumn).append(") as apc ");

		sb.append("from request r left join project p on r.project=p.id left join organisation o on r.organization=o.id left join country c on c.id=o.country left join (select r.id as rid,  p.id, p.name from request r join journal j on r.journal=j.id join publisher p on p.id=j.publisher  union select r.id, p.id, p.name from request r join publisher p on r.publisher=p.id) as pub on pub.rid=r.id");

		if (clauses.size() > 0)

			sb.append(" where ");

		for (int i = 0; i < clauses.size(); i++) {

			if (i > 0)

				sb.append(" and ");

			sb.append(" (").append(clauses.get(i)).append(") ");

		}

		sb.append(" order by ").append(dateColumn);

//		System.out.println(sb.toString());return null;

		return new DateSeries(new JdbcTemplate(dataSource).query(sb.toString(), new RowMapper<Triple<Date, Integer, Float>>() {

			@Override

			public Triple<Date, Integer, Float> mapRow(ResultSet rs, int rowNum) throws SQLException {

				return new Triple<Date, Integer, Float>(rs.getDate("date"), rs.getInt("count"), rs.getFloat("apc"));

			}

		}));

	}

	public static void main(String[] args) {

		System.out.println(new StatsManagerImpl().getDateSeries(DateSeries.Over.DAY, true, null,null,null,null,null,null));

	}

	@Override

	public Browse browse(Browse.Category[] categories) {

		JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

		Map<Browse.Category, List<Triple<String, String, Integer>>> map = new HashMap<Browse.Category, List<Triple<String, String, Integer>>>();

		for (final Browse.Category category:categories) {

			String query = null;

			switch (category) {

				case COUNTRY:

					query = BROWSE_COUNTRY;

					break;

				case ORGANIZATION:

					query = BROWSE_ORGANIZATION;

					break;

				case PUBLISHER:

					query = BROWSE_PUBLISHER;

					break;

				case SCIENTIFIC_AREA:

					query = BROWSE_SCIENTIFIC_AREA;

					break;

				case STATUS:

					query =  BROWSE_STATUS;

					break;

			}

			map.put(category, jdbcTemplate.query(query, new RowMapper<Triple<String, String, Integer>>() {

				@Override

				public Triple<String, String, Integer> mapRow(ResultSet rs, int rowNum) throws SQLException {

					if (category != Browse.Category.STATUS)

						return new Triple<String, String, Integer>(rs.getString("id"), rs.getString("category"), rs.getInt("count"));

					else

						return new Triple<String, String, Integer>(Request.RequestStatus.forStatus(rs.getInt("id")).name(), Request.RequestStatus.forStatus(rs.getInt("category")).getValue(), rs.getInt("count"));

				}

			}));

		}

		return new Browse(map);

	}

	@Override

	public MapData getMapData() {

		return null;

	}

}