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 org.springframework.transaction.annotation.Propagation;

import org.springframework.transaction.annotation.Transactional;

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 final String SEARCH_ORGANIZATIONS = "select id, name from organisation where lower(name) like lower(?)";

	private final String GET_ORGANIZATION = "select 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 id, name, shortname";

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

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

					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 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 insertOrganizationBudget(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 DataSource getDataSource() {

		return dataSource;

	}

	public void setDataSource(DataSource dataSource) {

		this.dataSource = dataSource;

	}

}