D-Net

package eu.dnetlib.goldoa.service.dao;

import eu.dnetlib.goldoa.domain.Budget;

import eu.dnetlib.goldoa.domain.Organization;

import eu.dnetlib.goldoa.domain.Vocabulary;

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 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.

 */

public class OrganizationDAO {

	@Autowired

	private DataSource dataSource;

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

	public Organization getOrganization(String id) {

		return new JdbcTemplate(dataSource).queryForObject(GET_ORGANIZATION, new Object[]{id}, new int[]{Types.VARCHAR}, new RowMapper<Organization>() {

			@Override

			public Organization mapRow(ResultSet rs, int rowNum) throws SQLException {

				Organization org = new Organization();

				org.setId(rs.getString("id"));

				org.setName(rs.getString("name"));

				org.setShortName(rs.getString("shortname"));

				org.setBudgets(new ArrayList<Budget>());

				for (String budget : Arrays.asList((String[]) rs.getArray("budgets").getArray()))

					if (budget != null)

						org.getBudgets().add(new Budget(budget));

				return org;

			}

		});

	}

	public List<Vocabulary> search(String term) {

		return new JdbcTemplate(dataSource).query(SEARCH_ORGANIZATIONS, new Object[]{"%" + term + "%"}, new int[]{Types.VARCHAR}, new RowMapper<Vocabulary>() {

			@Override

			public Vocabulary mapRow(ResultSet rs, int rowNum) throws SQLException {

				return new Vocabulary(rs.getString("id"), rs.getString("name"));

			}

		});

	}

	public Organization saveOrganization(Organization organization) {

		JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

		Object[] args = {organization.getName(), organization.getShortName(), organization.getSource(), organization.getId()};

		int[] types = {Types.VARCHAR, Types.VARCHAR, Types.VARCHAR, Types.VARCHAR};

		if (jdbcTemplate.update(UPDATE_ORGANIZATION, args, types) == 0)

			jdbcTemplate.update(INSERT_ORGANIZATION, args, types);

		return organization;

	}

	public void insertOrganization(final Organization organization) {

		new JdbcTemplate(dataSource).update(INSERT_ORGANIZATION, new PreparedStatementSetter() {

			@Override

			public void setValues(PreparedStatement ps) throws SQLException {

				ps.setString(1, organization.getId());

				ps.setString(2, organization.getName());

				ps.setString(3, organization.getShortName());

			}

		});

	}

	public void insertOrganizationBudgets(final Organization organization) {

		new JdbcTemplate(dataSource).batchUpdate(INSERT_ORGANIZATION_BUDGET, new BatchPreparedStatementSetter() {

			@Override

			public void setValues(PreparedStatement ps, int i) throws SQLException {

				ps.setString(1, organization.getId());

				ps.setString(2, organization.getBudgets().get(i).getId());

			}

			@Override

			public int getBatchSize() {

				return organization.getBudgets() != null ? organization.getBudgets().size() : 0;

			}

		});

	}

	public List<Organization> getAffiliatedWithUser(String userId) {

		return new JdbcTemplate(dataSource).query(GET_AFFILIATED_WITH_USER, new Object[]{userId}, new int[]{Types.VARCHAR}, new RowMapper<Organization>() {

			@Override

			public Organization mapRow(ResultSet rs, int rowNum) throws SQLException {

				Organization org = new Organization();

				org.setId(rs.getString("id"));

				org.setName(rs.getString("name"));

				org.setShortName(rs.getString("shortname"));

				org.setBudgets(new ArrayList<Budget>());

				for (String budget : Arrays.asList((String[]) rs.getArray("budgets").getArray()))

					if (budget != null)

						org.getBudgets().add(new Budget(budget));

				return org;

			}

		});

	}

	public void deleteOrganizationBudgets(Organization organization) {

		new JdbcTemplate(dataSource).update("delete from organization_budget where organization = ?", new String[]{organization.getId()}, new int[]{Types.VARCHAR});

	}

}