Revision 36853
Added by Antonis Lempesis over 9 years ago
| modules/uoa-goldoa-service/trunk/src/main/java/eu/dnetlib/goldoa/service/ProjectManagerImpl.java | ||
|---|---|---|
| 1 | 1 |
package eu.dnetlib.goldoa.service; |
| 2 | 2 |
|
| 3 |
import eu.dnetlib.goldoa.domain.Organization; |
|
| 4 |
import eu.dnetlib.goldoa.domain.Project; |
|
| 5 |
import eu.dnetlib.goldoa.domain.ProjectInfo; |
|
| 6 |
import eu.dnetlib.goldoa.domain.Vocabulary; |
|
| 3 |
import eu.dnetlib.goldoa.domain.*; |
|
| 7 | 4 |
import eu.dnetlib.goldoa.service.dao.OrganizationDAO; |
| 8 | 5 |
import eu.dnetlib.goldoa.service.dao.PersonDAO; |
| 9 | 6 |
import eu.dnetlib.goldoa.service.dao.ProjectDAO; |
| ... | ... | |
| 72 | 69 |
|
| 73 | 70 |
private void loadRelationships(Project project) {
|
| 74 | 71 |
|
| 75 |
try {
|
|
| 76 |
project.setCoordinator(personDAO.getPersonById(project.getCoordinator().getId())); |
|
| 77 |
} catch (EmptyResultDataAccessException e) {
|
|
| 78 |
; |
|
| 72 |
List<Person> coordinators = new ArrayList<Person>(); |
|
| 73 |
|
|
| 74 |
for (Person p:project.getCoordinators()) {
|
|
| 75 |
try {
|
|
| 76 |
coordinators.add(personDAO.getPersonById(p.getId())); |
|
| 77 |
} catch (EmptyResultDataAccessException e) {
|
|
| 78 |
; |
|
| 79 |
} |
|
| 79 | 80 |
} |
| 81 |
project.setCoordinators(coordinators); |
|
| 80 | 82 |
|
| 81 | 83 |
List<Organization> orgs = new ArrayList<Organization>(); |
| 82 | 84 |
|
| modules/uoa-goldoa-service/trunk/src/main/java/eu/dnetlib/goldoa/service/dao/PersonDAO.java | ||
|---|---|---|
| 29 | 29 |
private DataSource dataSource; |
| 30 | 30 |
|
| 31 | 31 |
private static final String GET_BY_ID = |
| 32 |
"select p.id, email, firstname, lastname, initials, password, telephone, active, orcidid, p.source, p.publisher, array_agg(distinct pro.id) as projects, array_agg(distinct a.organization||'||'||case when a.department is null then '' else a.department end||'||'||case when a.startdate is null then 0 else extract(epoch from a.startdate) end||'||'||case when a.enddate is null then 0 else extract(epoch from a.enddate) end) as affiliations, array_agg(distinct pr.role||'||'||pr.approved) as roles\n" +
|
|
| 32 |
"select p.id, email, firstname, lastname, initials, password, telephone, active, orcidid, p.source, p.publisher, array_agg(distinct pro.project) as projects, array_agg(distinct a.organization||'||'||case when a.department is null then '' else a.department end||'||'||case when a.startdate is null then 0 else extract(epoch from a.startdate) end||'||'||case when a.enddate is null then 0 else extract(epoch from a.enddate) end) as affiliations, array_agg(distinct pr.role||'||'||pr.approved) as roles\n" +
|
|
| 33 | 33 |
"from person p\n" + |
| 34 | 34 |
"left join person_role pr on p.id = pr.person\n" + |
| 35 | 35 |
"left join role r on r.id = pr.role\n" + |
| 36 | 36 |
"left join affiliation a on a.person = p.id\n" + |
| 37 | 37 |
"left join organisation o on o.id = a.organization\n" + |
| 38 |
"left join project pro on pro.coordinator=p.id\n" + |
|
| 38 |
"left join project_coordinator pro on pro.coordinator=p.id\n" +
|
|
| 39 | 39 |
"where p.id = ?\n" + |
| 40 | 40 |
"group by p.id, p.email, firstname, lastname, initials, password, telephone, active, orcidid, p.source\n"; |
| 41 | 41 |
|
| 42 | 42 |
private static final String GET_BY_EMAIL = |
| 43 |
"select p.id, email, firstname, lastname, initials, password, telephone, active, orcidid, p.source, p.publisher, array_agg(distinct pro.id) as projects, array_agg(distinct a.organization||'||'||case when a.department is null then '' else a.department end||'||'||case when a.startdate is null then 0 else extract(epoch from a.startdate) end||'||'||case when a.enddate is null then 0 else extract(epoch from a.enddate) end) as affiliations, array_agg(distinct pr.role||'||'||pr.approved) as roles\n" +
|
|
| 43 |
"select p.id, email, firstname, lastname, initials, password, telephone, active, orcidid, p.source, p.publisher, array_agg(distinct pro.project) as projects, array_agg(distinct a.organization||'||'||case when a.department is null then '' else a.department end||'||'||case when a.startdate is null then 0 else extract(epoch from a.startdate) end||'||'||case when a.enddate is null then 0 else extract(epoch from a.enddate) end) as affiliations, array_agg(distinct pr.role||'||'||pr.approved) as roles\n" +
|
|
| 44 | 44 |
"from person p\n" + |
| 45 | 45 |
"left join person_role pr on p.id = pr.person\n" + |
| 46 | 46 |
"left join role r on r.id = pr.role\n" + |
| 47 | 47 |
"left join affiliation a on a.person = p.id\n" + |
| 48 | 48 |
"left join organisation o on o.id = a.organization\n" + |
| 49 |
"left join project pro on pro.coordinator=p.id\n" + |
|
| 49 |
"left join project_coordinator pro on pro.coordinator=p.id\n" +
|
|
| 50 | 50 |
"where email = ?\n" + |
| 51 | 51 |
"group by p.id, p.email, firstname, lastname, initials, password, telephone, active, orcidid, p.source\n"; |
| 52 | 52 |
|
| 53 | 53 |
private static final String GET_USERS = |
| 54 |
"select p.id, email, firstname, lastname, initials, password, telephone, active, orcidid, p.source, p.publisher, array_agg(distinct pro.id) as projects, array_agg(distinct a.organization||'||'||case when a.department is null then '' else a.department end||'||'||case when a.startdate is null then 0 else extract(epoch from a.startdate) end||'||'||case when a.enddate is null then 0 else extract(epoch from a.enddate) end) as affiliations, array_agg(distinct pr.role||'||'||pr.approved) as roles\n" +
|
|
| 54 |
"select p.id, email, firstname, lastname, initials, password, telephone, active, orcidid, p.source, p.publisher, array_agg(distinct pro.project) as projects, array_agg(distinct a.organization||'||'||case when a.department is null then '' else a.department end||'||'||case when a.startdate is null then 0 else extract(epoch from a.startdate) end||'||'||case when a.enddate is null then 0 else extract(epoch from a.enddate) end) as affiliations, array_agg(distinct pr.role||'||'||pr.approved) as roles\n" +
|
|
| 55 | 55 |
"from person p\n" + |
| 56 | 56 |
"left join person_role pr on p.id = pr.person\n" + |
| 57 | 57 |
"left join role r on r.id = pr.role\n" + |
| 58 | 58 |
"left join affiliation a on a.person = p.id\n" + |
| 59 | 59 |
"left join organisation o on o.id = a.organization\n" + |
| 60 |
"left join project pro on pro.coordinator=p.id\n" + |
|
| 60 |
"left join project_coordinator pro on pro.coordinator=p.id\n" +
|
|
| 61 | 61 |
"where password is not null \n" + |
| 62 | 62 |
"group by p.id, p.email, firstname, lastname, initials, password, telephone, active, orcidid, p.source\n"; |
| 63 | 63 |
|
| ... | ... | |
| 65 | 65 |
|
| 66 | 66 |
private static final String UPDATE_PERSON = "update person set email=?, firstname=?, lastname=?, initials=?, password= (case when password=? then password else md5(?) end) , telephone=?, orcidid=?, source=?, publisher=? where id=?"; |
| 67 | 67 |
|
| 68 |
private static final String UPDATE_PROJECT_COORDINATOR = "update project set coordinator = ? where array[id]::text[] <@ ?::text[]";
|
|
| 68 |
private static final String INSERT_COORDINATORS = "insert into project_coordinator (project, coordinator) values (?, ?)";
|
|
| 69 | 69 |
|
| 70 | 70 |
private static final String INSERT_PERSON_ROLE = "insert into person_role (person, role, approved) values (?, ? ,?)"; |
| 71 | 71 |
|
| ... | ... | |
| 248 | 248 |
|
| 249 | 249 |
//TODO MOVE TO PROJECT DAO |
| 250 | 250 |
public void removeProjectCoordinators(final String personId) {
|
| 251 |
new JdbcTemplate(dataSource).update("update project set coordinator=null where coordinator=?", new PreparedStatementSetter() {
|
|
| 251 |
new JdbcTemplate(dataSource).update("delete from project_coordinator where coordinator=?", new PreparedStatementSetter() {
|
|
| 252 | 252 |
@Override |
| 253 | 253 |
public void setValues(PreparedStatement preparedStatement) throws SQLException {
|
| 254 | 254 |
preparedStatement.setString(1, personId); |
| ... | ... | |
| 257 | 257 |
} |
| 258 | 258 |
|
| 259 | 259 |
public void saveProjectCoordinators(final String personId, final List<String> projectIds) {
|
| 260 |
new JdbcTemplate(dataSource).update(new PreparedStatementCreator() {
|
|
| 261 |
@Override |
|
| 262 |
public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
|
|
| 263 |
PreparedStatement ps = con.prepareStatement(UPDATE_PROJECT_COORDINATOR); |
|
| 260 |
new JdbcTemplate(dataSource).batchUpdate(INSERT_COORDINATORS, new BatchPreparedStatementSetter() {
|
|
| 261 |
@Override |
|
| 262 |
public void setValues(PreparedStatement ps, int i) throws SQLException {
|
|
| 263 |
ps.setString(1, projectIds.get(i)); |
|
| 264 |
ps.setString(2, personId); |
|
| 265 |
} |
|
| 264 | 266 |
|
| 265 |
ps.setString(1, personId); |
|
| 266 |
ps.setArray(2, con.createArrayOf("text", projectIds.toArray()));
|
|
| 267 |
|
|
| 268 |
return ps; |
|
| 269 |
} |
|
| 270 |
}); |
|
| 267 |
@Override |
|
| 268 |
public int getBatchSize() {
|
|
| 269 |
return projectIds.size(); |
|
| 270 |
} |
|
| 271 |
}); |
|
| 271 | 272 |
} |
| 272 | 273 |
|
| 273 | 274 |
public void savePersonRoles(final String personId, final List<PersonRole> roles) {
|
| modules/uoa-goldoa-service/trunk/src/main/java/eu/dnetlib/goldoa/service/dao/ProjectDAO.java | ||
|---|---|---|
| 29 | 29 |
|
| 30 | 30 |
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(?)"; |
| 31 | 31 |
private final String GET_PROJECT = |
| 32 |
"select id, acronym, title, funder, fundingstream, scientificarea, call, \"grant\", startdate, enddate, sc39, url, source, coordinator, array_agg(po.organization) as organizations\n" +
|
|
| 32 |
"select id, acronym, title, funder, fundingstream, scientificarea, call, \"grant\", startdate, enddate, sc39, url, source, array_agg(pc.coordinator) as coordinators, array_agg(po.organization) as organizations\n" +
|
|
| 33 | 33 |
"from project p\n" + |
| 34 | 34 |
"left join project_organization po on po.project=p.id\n" + |
| 35 |
"left join project_coordinator pc on pc.project=p.id\n" + |
|
| 35 | 36 |
"where p.id=?\n" + |
| 36 |
"group by id, acronym, title, funder, fundingstream, scientificarea, call, \"grant\", startdate, enddate, sc39, url, source, coordinator ";
|
|
| 37 |
"group by id, acronym, title, funder, fundingstream, scientificarea, call, \"grant\", startdate, enddate, sc39, url, source"; |
|
| 37 | 38 |
private final String GET_PROJECTS_FOR_ORGANIZATION = |
| 38 |
"select id, acronym, title, funder, fundingstream, scientificarea, call, \"grant\", startdate, enddate, sc39, url, source, coordinator, array_agg(po.organization) as organizations\n" +
|
|
| 39 |
"select id, acronym, title, funder, fundingstream, scientificarea, call, \"grant\", startdate, enddate, sc39, url, source, array_agg(pc.coordinator) as coordinators, array_agg(po.organization) as organizations\n" +
|
|
| 39 | 40 |
"from project p\n" + |
| 40 | 41 |
"left join project_organization po on po.project=p.id\n" + |
| 42 |
"left join project_coordinator pc on pc.project=p.id\n" + |
|
| 41 | 43 |
"where po.organization=? and p.enddate > now() - (? || ' months')::interval and p.enddate < now() \n" + |
| 42 |
"group by id, acronym, title, funder, fundingstream, scientificarea, call, \"grant\", startdate, enddate, sc39, url, source, coordinator ";
|
|
| 44 |
"group by id, acronym, title, funder, fundingstream, scientificarea, call, \"grant\", startdate, enddate, sc39, url, source"; |
|
| 43 | 45 |
private final String GET_FUTURE_PROJECTS_FOR_ORGANIZATION= |
| 44 |
"select id, acronym, title, funder, fundingstream, scientificarea, call, \"grant\", startdate, enddate, sc39, url, source, coordinator, array_agg(po.organization) as organizations\n" +
|
|
| 46 |
"select id, acronym, title, funder, fundingstream, scientificarea, call, \"grant\", startdate, enddate, sc39, url, source, array_agg(pc.coordinator) as coordinators, array_agg(po.organization) as organizations\n" +
|
|
| 45 | 47 |
"from project p\n" + |
| 46 | 48 |
"left join project_organization po on po.project=p.id\n" + |
| 49 |
"left join project_coordinator pc on pc.project=p.id\n" + |
|
| 47 | 50 |
"where po.organization=? and p.enddate > now() \n" + |
| 48 |
"group by id, acronym, title, funder, fundingstream, scientificarea, call, \"grant\", startdate, enddate, sc39, url, source, coordinator ";
|
|
| 51 |
"group by id, acronym, title, funder, fundingstream, scientificarea, call, \"grant\", startdate, enddate, sc39, url, source"; |
|
| 49 | 52 |
|
| 50 | 53 |
private RowMapper<Project> rowMapper = new RowMapper<Project>() {
|
| 51 | 54 |
@Override |
| ... | ... | |
| 66 | 69 |
project.setUrl(rs.getString("url"));
|
| 67 | 70 |
|
| 68 | 71 |
|
| 69 |
project.setCoordinator(new Person(rs.getString("coordinator")));
|
|
| 72 |
project.setCoordinators(new ArrayList<Person>()); |
|
| 73 |
for (String coordinatorId : (String[]) rs.getArray("coordinators").getArray())
|
|
| 74 |
project.getCoordinators().add(new Person(coordinatorId)); |
|
| 70 | 75 |
|
| 71 | 76 |
project.setOrganizations(new ArrayList<Organization>()); |
| 72 | 77 |
for (String organizationId : (String[]) rs.getArray("organizations").getArray())
|
| modules/uoa-goldoa-service/trunk/src/main/java/eu/dnetlib/goldoa/domain/Project.java | ||
|---|---|---|
| 22 | 22 |
private boolean sc39; |
| 23 | 23 |
private List<Organization> organizations; |
| 24 | 24 |
private String url; |
| 25 |
private Person coordinator;
|
|
| 25 |
private List<Person> coordinators;
|
|
| 26 | 26 |
|
| 27 |
public Project(String id, String acronym, String title, String funder, String fundingString, String scientificArea, String call, String grant, Date startDate, Date endDate, boolean sc39, List<Organization> organizations, String url, Person coordinator) {
|
|
| 28 |
this.id = id; |
|
| 29 |
this.acronym = acronym; |
|
| 30 |
this.title = title; |
|
| 31 |
this.funder = funder; |
|
| 32 |
this.fundingString = fundingString; |
|
| 33 |
this.scientificArea = scientificArea; |
|
| 34 |
this.call = call; |
|
| 35 |
this.grant = grant; |
|
| 36 |
this.startDate = startDate; |
|
| 37 |
this.endDate = endDate; |
|
| 38 |
this.sc39 = sc39; |
|
| 39 |
this.organizations = organizations; |
|
| 40 |
this.url = url; |
|
| 41 |
this.coordinator = coordinator; |
|
| 42 |
} |
|
| 43 |
|
|
| 44 | 27 |
public Project() {
|
| 45 | 28 |
} |
| 46 | 29 |
|
| ... | ... | |
| 152 | 135 |
this.organizations = organizations; |
| 153 | 136 |
} |
| 154 | 137 |
|
| 155 |
public Person getCoordinator() {
|
|
| 156 |
return coordinator;
|
|
| 157 |
}
|
|
| 138 |
public List<Person> getCoordinators() {
|
|
| 139 |
return coordinators;
|
|
| 140 |
}
|
|
| 158 | 141 |
|
| 159 |
public void setCoordinator(Person coordinator) {
|
|
| 160 |
this.coordinator = coordinator;
|
|
| 161 |
}
|
|
| 142 |
public void setCoordinators(List<Person> coordinators) {
|
|
| 143 |
this.coordinators = coordinators;
|
|
| 144 |
}
|
|
| 162 | 145 |
} |
Also available in: Unified diff
supporting multiple coordinators per project