D-Net

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

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

import java.text.SimpleDateFormat;

import java.util.ArrayList;

import java.util.Date;

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

	public static final String BROWSE_SCIENTIFIC_AREA = "select p.id 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";

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

	public static final String BROWSE_ORGANIZATION = "select o.id as id, o.shortname as category, count(r.id) as count from request r join organisation o on o.id=r.organization group by o.shortname";

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

	public DateSeries getDateSeries(DateSeries.Over over, boolean cummulative, 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 (cummulative)

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

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

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

			@Override

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

				return new Tuple<Date, Integer>(rs.getDate("date"), rs.getInt("count"));

			}

		}));

	public static void main(String[] args) {

		new StatsManagerImpl().getDateSeries(DateSeries.Over.DAY, true, new Date(), new Date(), new String[] {"123", "123"} , new String [] {"456"}, new String[] {"789"}, new String[] {"000"});

	}

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

			}

			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 {

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

				}

			}));

		}

		return new Browse(map);

	}

	@Override