D-Net

package eu.dnetlib.goldoa.service.dao;

import eu.dnetlib.goldoa.domain.*;

import org.hibernate.criterion.*;

import org.hibernate.transform.Transformers;

import org.springframework.stereotype.Repository;

import java.util.ArrayList;

import java.util.Calendar;

import java.util.Date;

import java.util.List;

/*

 * Created by antleb on 3/13/15.

*/

@Repository

public class ProjectDAO extends AbstractDao<String,Project>{

	private long expirationOk = 24;

	private final String GET_PROJECTS_FOR_ORGANIZATION =

			"select id, acronym, title, funder, fundingstream, scientificarea, call, \"grant\", startdate, enddate, sc39, url, " +

					"source, array_agg(distinct pc.coordinator) as coordinators, array_agg(distinct po.organization) as organizations\n" +

					"from project  p\n" +

					"left join project_organization po on po.project=p.id\n" +

					"left join project_coordinator pc on pc.project=p.id\n" +

					"where po.organization=? and p.enddate > now() - (? || ' months')::interval and p.enddate < now() \n" +

					"group by id, acronym, title, funder, fundingstream, scientificarea, call, \"grant\", startdate, enddate, sc39, url, source";

	private final String GET_FUTURE_PROJECTS_FOR_ORGANIZATION =

			"select id, acronym, title, funder, fundingstream, scientificarea, call, \"grant\", startdate, enddate, sc39, url, source, array_agg(distinct pc.coordinator) as coordinators, array_agg(distinct po.organization) as organizations\n" +

					"from project  p\n" +

					"left join project_organization po on po.project=p.id\n" +

					"left join project_coordinator pc on pc.project=p.id\n" +

					"where po.organization=? and p.enddate > now() \n" +

					"group by id, acronym, title, funder, fundingstream, scientificarea, call, \"grant\", startdate, enddate, sc39, url, source";

	public List<Project> getProjectsCoordinatedBy(String personId) {

		return new ArrayList<Project>();

	}

	@SuppressWarnings("unchecked")

	public List<Object> search(String term) {

		Disjunction d = Restrictions.disjunction();

		d.add(Restrictions.eq("acronym",term.toLowerCase()).ignoreCase());

		d.add(Restrictions.eq("title",term.toLowerCase()).ignoreCase());

		d.add(Restrictions.eq("grant",term.toLowerCase()).ignoreCase());

        return createEntityCriteria().add(d).list();

	}

	public Project getProject(String projectId) {

	    return (Project) createEntityCriteria().add(Restrictions.eq("id",projectId)).list().get(0);

	}

	@SuppressWarnings("unchecked")

	public List<Project> getEligibleProjectsForOrganization(String organizationId) {

		Date referenceDate = new Date();

		Calendar c = Calendar.getInstance();

		c.setTime(referenceDate);

		c.add(Calendar.MONTH, (int) -expirationOk);

		ProjectionList projList = Projections.projectionList();

		projList.add(Projections.groupProperty("id"),"id");

		projList.add(Projections.groupProperty("acronym"),"acronym");

		projList.add(Projections.groupProperty("title"),"title");

		projList.add(Projections.groupProperty("funder"),"funder");

		projList.add(Projections.groupProperty("fundingstream"),"fundingstream");

		projList.add(Projections.groupProperty("scientificarea"),"scientificarea");

		projList.add(Projections.groupProperty("call"),"call");

		projList.add(Projections.groupProperty("grant"),"grant");

		projList.add(Projections.groupProperty("startdate"),"startdate");

		projList.add(Projections.groupProperty("enddate"),"enddate");

		projList.add(Projections.groupProperty("sc39"),"sc39");

		projList.add(Projections.groupProperty("url"),"url");

		projList.add(Projections.groupProperty("source"),"source");

		return createEntityCriteria().createAlias("organizations","o")

				.add(Restrictions.eq("o.id",organizationId))

				.add(Restrictions.lt("enddate",new Date()))

				.add(Restrictions.gt("enddate",c.getTime()))

				.setProjection(projList)

				.setResultTransformer( Transformers.aliasToBean(Project.class))

				.list();

	}

	@SuppressWarnings("unchecked")

	public List<Project> getFutureEligibleProjectsForOrganization(String organizationId) {

		ProjectionList projList = Projections.projectionList();

		projList.add(Projections.groupProperty("id"),"id");

		projList.add(Projections.groupProperty("acronym"),"acronym");

		projList.add(Projections.groupProperty("title"),"title");

		projList.add(Projections.groupProperty("funder"),"funder");

		projList.add(Projections.groupProperty("fundingstream"),"fundingstream");

		projList.add(Projections.groupProperty("scientificarea"),"scientificarea");

		projList.add(Projections.groupProperty("call"),"call");

		projList.add(Projections.groupProperty("grant"),"grant");

		projList.add(Projections.groupProperty("startdate"),"startdate");

		projList.add(Projections.groupProperty("enddate"),"enddate");

		projList.add(Projections.groupProperty("sc39"),"sc39");

		projList.add(Projections.groupProperty("url"),"url");

		projList.add(Projections.groupProperty("source"),"source");

		return createEntityCriteria().createAlias("organizations", "o")

				.add(Restrictions.eq("o.id", organizationId))

				.add(Restrictions.gt("enddate", new Date()))

				.setProjection(projList)

				.setResultTransformer(Transformers.aliasToBean(Project.class))

				.list();

	}

	public long getExpirationOk() {

		return expirationOk;

	}

	public void setExpirationOk(long expirationOk) {

		this.expirationOk = expirationOk;

	}

}