|
1
|
package eu.dnetlib.data.db;
|
|
2
|
|
|
3
|
import java.util.HashMap;
|
|
4
|
import java.util.HashSet;
|
|
5
|
import java.util.List;
|
|
6
|
import java.util.Map;
|
|
7
|
import java.util.Set;
|
|
8
|
import java.util.TreeSet;
|
|
9
|
import java.util.stream.Collectors;
|
|
10
|
|
|
11
|
import org.springframework.beans.factory.annotation.Required;
|
|
12
|
import org.springframework.jdbc.core.JdbcTemplate;
|
|
13
|
import org.springframework.jdbc.core.RowMapper;
|
|
14
|
|
|
15
|
public class AffiliationsDao {
|
|
16
|
|
|
17
|
private JdbcTemplate jdbcTemplate;
|
|
18
|
|
|
19
|
public List<Affiliation> listAffiliations() {
|
|
20
|
return jdbcTemplate.query("select * from affiliations", (RowMapper<Affiliation>) (rs, rowNum) -> {
|
|
21
|
return new Affiliation(rs.getString("pid"), rs.getString("gid"), rs.getInt("year"));
|
|
22
|
});
|
|
23
|
}
|
|
24
|
|
|
25
|
public List<Person> listPersonsWithAffiliations() {
|
|
26
|
|
|
27
|
final Map<String, Person> temp = new HashMap<>();
|
|
28
|
|
|
29
|
for (final Map<String, Object> map : jdbcTemplate.queryForList("select * from affiliations_view")) {
|
|
30
|
final String pid = (String) map.get("pid");
|
|
31
|
final Integer year = (Integer) map.get("year");
|
|
32
|
final Group group = new Group((String) map.get("gid"), (String) map.get("gname"), (String) map.get("gtype"));
|
|
33
|
|
|
34
|
temp.putIfAbsent(pid, new Person(pid, (String) map.get("pname")));
|
|
35
|
|
|
36
|
if (year != null) {
|
|
37
|
temp.get(pid).getAffiliations().putIfAbsent(year, new TreeSet<>((g1, g2) -> {
|
|
38
|
final int n1 = calculateIntegerForGroup(g1.getType());
|
|
39
|
final int n2 = calculateIntegerForGroup(g2.getType());
|
|
40
|
return (n1 == n2) ? g1.getId().compareTo(g2.getId()) : Integer.compare(n1, n2);
|
|
41
|
}));
|
|
42
|
temp.get(pid).getAffiliations().get(year).add(group);
|
|
43
|
}
|
|
44
|
}
|
|
45
|
|
|
46
|
return temp.values()
|
|
47
|
.stream()
|
|
48
|
.sorted((p1, p2) -> p1.getName().compareTo(p2.getName()))
|
|
49
|
.collect(Collectors.toList());
|
|
50
|
|
|
51
|
}
|
|
52
|
|
|
53
|
private int calculateIntegerForGroup(final String type) {
|
|
54
|
if (type.equalsIgnoreCase("Laboratorio")) {
|
|
55
|
return 0;
|
|
56
|
} else if (type.equalsIgnoreCase("Servizio")) {
|
|
57
|
return 10;
|
|
58
|
} else if (type.equalsIgnoreCase("Centro")) {
|
|
59
|
return 20;
|
|
60
|
} else {
|
|
61
|
return 100;
|
|
62
|
}
|
|
63
|
}
|
|
64
|
|
|
65
|
public List<Person> listPersons() {
|
|
66
|
return jdbcTemplate.query("select * from persons", (RowMapper<Person>) (rs, rowNum) -> {
|
|
67
|
return new Person(rs.getString("id"), rs.getString("fullname"));
|
|
68
|
});
|
|
69
|
}
|
|
70
|
|
|
71
|
public List<Group> listGroups() {
|
|
72
|
return jdbcTemplate.query("select * from groups where id != 'UNKNOWN'", (RowMapper<Group>) (rs, rowNum) -> {
|
|
73
|
return new Group(rs.getString("id"), rs.getString("name"), rs.getString("type"));
|
|
74
|
});
|
|
75
|
}
|
|
76
|
|
|
77
|
public Set<String> getPersonIds() {
|
|
78
|
return new HashSet<>(jdbcTemplate.query("select id from persons", (RowMapper<String>) (rs, rowNum) -> rs.getString("id")));
|
|
79
|
}
|
|
80
|
|
|
81
|
public Set<String> getGroupIds() {
|
|
82
|
return new HashSet<>(jdbcTemplate.query("select id from groups", (RowMapper<String>) (rs, rowNum) -> rs.getString("id")));
|
|
83
|
}
|
|
84
|
|
|
85
|
public void updatePerson(final String code, final String name) {
|
|
86
|
jdbcTemplate.update("UPDATE persons SET fullname = ? WHERE id = ?", name, code);
|
|
87
|
|
|
88
|
}
|
|
89
|
|
|
90
|
public void registerPerson(final String code, final String name) {
|
|
91
|
jdbcTemplate.update("INSERT into persons(id, fullname) VALUES (?, ?)", code, name);
|
|
92
|
}
|
|
93
|
|
|
94
|
public void updateGroup(final String id, final String name, final String type) {
|
|
95
|
jdbcTemplate.update("UPDATE groups SET (name, type) = (?, ?) WHERE id = ?", name, type, id);
|
|
96
|
}
|
|
97
|
|
|
98
|
public void registerGroup(final String id, final String name, final String type) {
|
|
99
|
jdbcTemplate.update("INSERT into groups(id, name, type) VALUES (?, ?, ?)", id, name, type);
|
|
100
|
}
|
|
101
|
|
|
102
|
public void registerAffiliation(final String pid, final String gid, final int year) {
|
|
103
|
jdbcTemplate.update("INSERT into affiliations(pid, gid, year) VALUES (?, ?, ?)", pid, gid, year);
|
|
104
|
}
|
|
105
|
|
|
106
|
public void updateUnknownAffiliation(final String pid, final String gid, final int year) {
|
|
107
|
jdbcTemplate.update("UPDATE affiliations SET gid=? WHERE gid='UNKNOWN' AND pid=? AND year=?", gid, pid, year);
|
|
108
|
}
|
|
109
|
|
|
110
|
public JdbcTemplate getJdbcTemplate() {
|
|
111
|
return jdbcTemplate;
|
|
112
|
}
|
|
113
|
|
|
114
|
@Required
|
|
115
|
public void setJdbcTemplate(final JdbcTemplate jdbcTemplate) {
|
|
116
|
this.jdbcTemplate = jdbcTemplate;
|
|
117
|
}
|
|
118
|
|
|
119
|
}
|