|
1
|
/*
|
|
2
|
package eu.dnetlib.goldoa.service.dao;
|
|
3
|
|
|
4
|
import eu.dnetlib.goldoa.domain.Organization;
|
|
5
|
import eu.dnetlib.goldoa.domain.User;
|
|
6
|
import eu.dnetlib.goldoa.domain.Project;
|
|
7
|
import eu.dnetlib.goldoa.domain.ProjectCoordinator;
|
|
8
|
import eu.dnetlib.goldoa.domain.Vocabulary;
|
|
9
|
import org.springframework.beans.factory.annotation.Autowired;
|
|
10
|
import org.springframework.jdbc.core.JdbcTemplate;
|
|
11
|
import org.springframework.jdbc.core.RowMapper;
|
|
12
|
|
|
13
|
import javax.sql.DataSource;
|
|
14
|
import java.sql.ResultSet;
|
|
15
|
import java.sql.SQLException;
|
|
16
|
import java.sql.Types;
|
|
17
|
import java.util.ArrayList;
|
|
18
|
import java.util.List;
|
|
19
|
|
|
20
|
*/
|
|
21
|
/**
|
|
22
|
* Created by antleb on 3/13/15.
|
|
23
|
*//*
|
|
24
|
|
|
25
|
public class ProjectDAO {
|
|
26
|
|
|
27
|
@Autowired
|
|
28
|
private DataSource dataSource;
|
|
29
|
|
|
30
|
private long expirationOk;
|
|
31
|
|
|
32
|
private final String SEARCH_PROJECTS = "select id, acronym, title, \"grant\" from project where lower(acronym) like lower(?) or lower(title) like lower(?) or lower(\"grant\") like lower(?)";
|
|
33
|
private final String GET_PROJECT =
|
|
34
|
"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" +
|
|
35
|
"from project p\n" +
|
|
36
|
"left join project_organization po on po.project=p.id\n" +
|
|
37
|
"left join project_coordinator pc on pc.project=p.id\n" +
|
|
38
|
"where p.id=?\n" +
|
|
39
|
"group by id, acronym, title, funder, fundingstream, scientificarea, call, \"grant\", startdate, enddate, sc39, url, source";
|
|
40
|
private final String GET_PROJECTS_FOR_ORGANIZATION =
|
|
41
|
"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" +
|
|
42
|
"from project p\n" +
|
|
43
|
"left join project_organization po on po.project=p.id\n" +
|
|
44
|
"left join project_coordinator pc on pc.project=p.id\n" +
|
|
45
|
"where po.organization=? and p.enddate > now() - (? || ' months')::interval and p.enddate < now() \n" +
|
|
46
|
"group by id, acronym, title, funder, fundingstream, scientificarea, call, \"grant\", startdate, enddate, sc39, url, source";
|
|
47
|
private final String GET_FUTURE_PROJECTS_FOR_ORGANIZATION =
|
|
48
|
"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" +
|
|
49
|
"from project p\n" +
|
|
50
|
"left join project_organization po on po.project=p.id\n" +
|
|
51
|
"left join project_coordinator pc on pc.project=p.id\n" +
|
|
52
|
"where po.organization=? and p.enddate > now() \n" +
|
|
53
|
"group by id, acronym, title, funder, fundingstream, scientificarea, call, \"grant\", startdate, enddate, sc39, url, source";
|
|
54
|
|
|
55
|
private RowMapper<Project> rowMapper = new RowMapper<Project>() {
|
|
56
|
@Override
|
|
57
|
public Project mapRow(ResultSet rs, int rowNum) throws SQLException {
|
|
58
|
Project project = new Project();
|
|
59
|
|
|
60
|
project.setId(rs.getString("id"));
|
|
61
|
project.setTitle(rs.getString("title"));
|
|
62
|
project.setAcronym(rs.getString("acronym"));
|
|
63
|
project.setFunder(rs.getString("funder"));
|
|
64
|
project.setFundingstream(rs.getString("fundingstream"));
|
|
65
|
project.setScientificarea(rs.getString("scientificarea"));
|
|
66
|
project.setCall(rs.getString("call"));
|
|
67
|
project.setGrant(rs.getString("grant"));
|
|
68
|
project.setStartdate(rs.getTimestamp("startdate"));
|
|
69
|
project.setEnddate(rs.getTimestamp("enddate"));
|
|
70
|
project.setSc39(rs.getBoolean("sc39"));
|
|
71
|
project.setUrl(rs.getString("url"));
|
|
72
|
|
|
73
|
|
|
74
|
project.setProjectCoordinators(new ArrayList<ProjectCoordinator>());
|
|
75
|
for (String coordinatorId : (String[]) rs.getArray("coordinators").getArray())
|
|
76
|
project.getCoordinators().add(new Person(coordinatorId));
|
|
77
|
|
|
78
|
project.setOrganizations(new ArrayList<Organization>());
|
|
79
|
for (String organizationId : (String[]) rs.getArray("organizations").getArray())
|
|
80
|
project.getOrganizations().add(new Organization(organizationId));
|
|
81
|
|
|
82
|
return project;
|
|
83
|
}
|
|
84
|
};
|
|
85
|
|
|
86
|
public List<Project> getProjectsCoordinatedBy(String personId) {
|
|
87
|
return new ArrayList<Project>();
|
|
88
|
}
|
|
89
|
|
|
90
|
public List<Vocabulary> search(String term) {
|
|
91
|
return new JdbcTemplate(dataSource).query(SEARCH_PROJECTS, new Object[]{"%" + term + "%", "%" + term + "%", "%" + term + "%"}, new int[]{Types.VARCHAR, Types.VARCHAR, Types.VARCHAR}, new RowMapper<Vocabulary>() {
|
|
92
|
@Override
|
|
93
|
public Vocabulary mapRow(ResultSet rs, int rowNum) throws SQLException {
|
|
94
|
return new Vocabulary(rs.getString("id"), rs.getString("acronym") + " - " + rs.getString("grant") + " (" + rs.getString("title") + ")");
|
|
95
|
}
|
|
96
|
});
|
|
97
|
}
|
|
98
|
|
|
99
|
public Project getProject(String projectId) {
|
|
100
|
return new JdbcTemplate(dataSource).queryForObject(GET_PROJECT, new Object[]{projectId}, new int[]{Types.VARCHAR}, rowMapper);
|
|
101
|
}
|
|
102
|
|
|
103
|
public DataSource getDataSource() {
|
|
104
|
return dataSource;
|
|
105
|
}
|
|
106
|
|
|
107
|
public void setDataSource(DataSource dataSource) {
|
|
108
|
this.dataSource = dataSource;
|
|
109
|
}
|
|
110
|
|
|
111
|
public List<Project> getEligibleProjectsForOrganization(String organizationId) {
|
|
112
|
return new JdbcTemplate(dataSource).query(GET_PROJECTS_FOR_ORGANIZATION, new Object[]{organizationId, this.expirationOk}, new int[]{Types.VARCHAR, Types.INTEGER}, rowMapper);
|
|
113
|
}
|
|
114
|
|
|
115
|
public List<Project> getFutureEligibleProjectsForOrganization(String organizationId) {
|
|
116
|
return new JdbcTemplate(dataSource).query(GET_FUTURE_PROJECTS_FOR_ORGANIZATION, new String[]{organizationId}, new int[]{Types.VARCHAR}, rowMapper);
|
|
117
|
}
|
|
118
|
|
|
119
|
public long getExpirationOk() {
|
|
120
|
return expirationOk;
|
|
121
|
}
|
|
122
|
|
|
123
|
public void setExpirationOk(long expirationOk) {
|
|
124
|
this.expirationOk = expirationOk;
|
|
125
|
}
|
|
126
|
}
|
|
127
|
*/
|