D-Net

package eu.dnetlib.goldoa.service.dao;

import eu.dnetlib.goldoa.domain.*;

import org.hibernate.Criteria;

import org.hibernate.criterion.Restrictions;

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

import org.springframework.jdbc.core.BatchPreparedStatementSetter;

import org.springframework.jdbc.core.JdbcTemplate;

import org.springframework.jdbc.core.PreparedStatementSetter;

import org.springframework.jdbc.core.RowMapper;

import org.springframework.stereotype.Repository;

import javax.sql.DataSource;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Types;

import java.util.ArrayList;

import java.util.Arrays;

import java.util.List;

/**

 * Created by antleb on 3/13/15.

 */

@Repository

public class OrganizationDAO extends AbstractDao<String, Organization>{

	private static final String SEARCH_ORGANIZATIONS = "select id, name from organisation where lower(name) like lower(?)";

	private static final String GET_ORGANIZATION = "select o.id, name, shortname, array_agg(distinct budget) as budgets from organisation o left join organization_budget ob on ob.organization=o.id where o.id = ? group by o.id, name, shortname";

	private static final String GET_AFFILIATED_WITH_USER = "select o.id, name, shortname, array_agg(distinct budget) as budgets \n" +

			"from organisation o \n" +

			"left join organization_budget ob on ob.organization=o.id \n" +

			"join affiliation a on a.organization=o.id\n" +

			"where a.person=?\n" +

			"group by o.id, name, shortname";

	private final String INSERT_ORGANIZATION = "insert into organisation (name, shortname, source, id) values (?,?,?,?)";

	private final String UPDATE_ORGANIZATION = "update organisation set name=?, shortname=?, source=? where id=?";

	private final String INSERT_ORGANIZATION_BUDGET = "insert into organization_budget (organization, budget) values (?, ?)";

	@SuppressWarnings("unchecked")

	public Organization getOrganization(String id) {

		List<Organization> rs = (List<Organization>) createEntityCriteria().add(Restrictions.ilike("id", id)).list();

		if(rs.size() == 0)

			return null;

		return rs.get(0);

	}

	@SuppressWarnings("unchecked")

	public List<Object> search(String term) {

		return createEntityCriteria().add(Restrictions.

				like("name","%" + term.toLowerCase() + "%").ignoreCase()).list();

	}

	public Organization saveOrganization(Organization organization) {

	    persist(organization);

		return organization;

	}

	public void insertOrganization(final Organization organization) {

		saveOrganization(organization);

	}

	public List<Organization> getAffiliatedWithUser(String email) {

	    Criteria cr = getSession().createCriteria(User.class);

        User user = (User) cr.add(Restrictions.eq("email",email)).list().get(0);

        List<Affiliation> afs = user.getAffiliations();

        List<Organization> orgs = new ArrayList<>();

        for(Affiliation af : afs)

            orgs.add(af.getOrganization());

        return orgs;

	}

	public void deleteOrganizationBudgets(Organization organization) {

		/*organization.setBudgets(null);

		saveOrganization(organization);*/

	}

}