D-Net

package eu.dnetlib.goldoa.service.stats;

import eu.dnetlib.goldoa.domain.Request;

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

import eu.dnetlib.goldoa.service.RequestManager;

import org.apache.log4j.Logger;

import org.hibernate.SessionFactory;

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

import org.springframework.stereotype.Service;

import org.springframework.transaction.annotation.Transactional;

import java.text.ParseException;

import java.text.SimpleDateFormat;

import java.util.*;

/*

 * Created by antleb on 11/19/15.

*/

@Transactional(readOnly = true)

@Service("statsManager")

public class StatsManagerImpl implements StatsManager {

	private static Logger logger = Logger.getLogger(StatsManagerImpl.class);

	public static final String SERIES_COUNTRY = "select c.name as category, count(r.id) as count, sum(apcpaid) as apc, avg(apcpaid) as avgapc " +

			"from request r join organization 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(apcpaid) as apc, avg(apcpaid) 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(apcpaid) as apc, avg(apcpaid) 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(apcpaid) as apc, avg(apcpaid) as avgapc " +

			"from request r join organization 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(apcpaid) as apc, avg(apcpaid) as avgapc " +

			"from (select r.id, r.status, r.apcpaid, 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.apcpaid, 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 organization 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 organization 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 RequestManager requestManager;

	@Autowired

	private SessionFactory sessionFactory;

	@Override

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

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

		for (Numbers.Category category:categories) {

			switch (category) {

				case REQUESTS:

					map.put(Numbers.Category.REQUESTS, requestManager.getActiveNumber());

					break;

				case PAID_REQUESTS:

					map.put(Numbers.Category.PAID_REQUESTS,requestManager.getPaidNumber() );

					break;

				case PROJECTS:

					map.put(Numbers.Category.PROJECTS,requestManager.getNumberOfProjects());

					break;

				case TOTAL_AMOUNT:

					map.put(Numbers.Category.TOTAL_AMOUNT,requestManager.getTotalAmount() );

					break;

				case AVERAGE_AMOUNT:

					map.put(Numbers.Category.AVERAGE_AMOUNT,requestManager.getAverageAmount() );

					break;

				case AVERAGE_PER_ARTICLE:

					map.put(Numbers.Category.AVERAGE_PER_ARTICLE,requestManager.getAverageAmountPerArticle() );

					break;

				case AVERAGE_PER_MONOGRAPH:

					map.put(Numbers.Category.AVERAGE_PER_MONOGRAPH,requestManager.getAverageAmountPerMonograph());

					break;

				case AVERAGE_PER_BOOK_CHAPTER:

					map.put(Numbers.Category.AVERAGE_PER_BOOK_CHAPTER,requestManager.getAverageAmountPerBookChapter()  );

					break;

				case PAID_REQUESTS_WITH_DOI:

					map.put(Numbers.Category.PAID_REQUESTS_WITH_DOI,requestManager.getNumberOfPaidRequestsWithDOI());

					break;

				case APPROVED_REQUESTS_WITH_DOI:

					map.put(Numbers.Category.APPROVED_REQUESTS_WITH_DOI,requestManager.getNumberOfApprovedRequestsWithDOI());

					break;

				case PAID_ARTICLE_REQUESTS:

					map.put(Numbers.Category.PAID_ARTICLE_REQUESTS,requestManager.getNumberOfPaidPublicationPerArticle());

					break;

				case PAID_MONOGRAPH_REQUESTS:

					map.put(Numbers.Category.PAID_MONOGRAPH_REQUESTS, requestManager.getNumberOfPaidPublicationPerMonograph());

					break;

				case PAID_BOOK_CHAPTER_REQUESTS:

					map.put(Numbers.Category.PAID_BOOK_CHAPTER_REQUESTS, requestManager.getNumberOfPaidPublicationPerBookChapter());

					break;

			}

		}

		return new Numbers(map);

	}

	@Override

	@SuppressWarnings("unchecked")

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

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

			}

			List<Object[]> resultSet = sessionFactory.getCurrentSession().createSQLQuery(query).list();

			Quadruple<String, Integer, Float, Float> q = null;

			List<Quadruple<String, Integer, Float, Float>> l = new ArrayList<>();

			for(Object[] rs : resultSet){

				if(rs[0] == null) rs[0] = "";

				if(rs[2] == null) rs[2] = "0.0";

				if(rs[3] == null) rs[3] = "0.0";

				if(category != Series.Category.STATUS)

					q = new Quadruple<>(rs[0].toString(),

							Integer.parseInt(rs[1].toString()), Float.parseFloat(rs[2].toString()), Float.parseFloat(rs[3].toString()));

				else

					q = new Quadruple<>(Request.RequestStatus.forStatus((Integer) rs[0]).getValue(),

							Integer.parseInt(rs[1].toString()), Float.parseFloat(rs[2].toString()), Float.parseFloat(rs[3].toString()));

				l.add(q);

			}

			map.put(category,l);

		}

		return new Series(map);

	}

	@Override

	@SuppressWarnings("unchecked")

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

		List<Object[]> resultSet = sessionFactory.getCurrentSession().createSQLQuery(sb.toString()).list();

		List<Triple<Date, Integer, Float>> l = new ArrayList<>();

		SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.s");

		for(Object[] rs : resultSet){

			Date date = null;

			try {

				if(rs[0] == null)

					date = null;

				else

					date = formatter.parse(rs[0].toString());

				if(rs[2] == null)

					rs[2] = "0.0";

				Triple<Date, Integer, Float> t = new Triple<Date,Integer, Float>(date,

						Integer.parseInt(rs[1].toString()),Float.parseFloat(rs[2].toString()));

				l.add(t);

			} catch (ParseException e) {

				e.printStackTrace();

			}

		}

		return new DateSeries(l);

	}

	public static void main(String[] args) {

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

	}

	@Override

	@SuppressWarnings("unchecked")

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

		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;

			}

			List<Object[]> resultSet = sessionFactory.getCurrentSession().createSQLQuery(query).list();

			Triple<String, String, Integer> t;

			List<Triple<String, String, Integer>> l = new ArrayList<>();

			for(Object[] rs : resultSet){

				if(rs[0] == null ) rs[0] = "";

				if(rs[1] == null ) rs[1] = "";

				if(rs[2] == null ) rs[2] = 0;

				if(category != Browse.Category.STATUS)

					t = new Triple<>(rs[0].toString(),rs[1].toString(), Integer.parseInt(rs[2].toString()));

				else

					t = new Triple<>(Request.RequestStatus.forStatus(Integer.parseInt(rs[0].toString())).getValue(),

							Request.RequestStatus.forStatus(Integer.parseInt(rs[0].toString())).getValue(),

							Integer.parseInt(rs[2].toString()));

				l.add(t);

			}

			map.put(category,l);

		}

		return new Browse(map);

	}

	@Override

	public MapData getMapData() {

		return null;

	}

}