1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
|
package org.openslx.bwlp.sat.database.mappers;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import org.apache.log4j.Logger;
import org.openslx.bwlp.sat.database.Database;
import org.openslx.bwlp.sat.database.MysqlConnection;
import org.openslx.bwlp.sat.database.MysqlStatement;
import org.openslx.bwlp.sat.web.XmlFilterEntry;
import org.openslx.bwlp.thrift.iface.LdapFilter;
import org.openslx.util.Util;
public class DbLectureFilter {
private static final Logger LOGGER = Logger.getLogger(DbLectureFilter.class);
private static MysqlStatement getLdapFilterStatement(MysqlConnection connection, String lectureId) throws SQLException {
MysqlStatement stmt = connection.prepareStatement(
"SELECT p.filterid, p.filtername,"
+ " IFNULL(p.filterkey, f.filterkey) AS filterkey, IFNULL(p.filtervalue, f.filtervalue) AS filtervalue"
+ " FROM lecturefilter f"
+ " LEFT JOIN presetlecturefilter p ON (f.filterpresetid = p.filterid)"
+ " WHERE f.lectureid = :lectureid AND (f.filtertype = 'LDAP' OR p.filtertype = 'LDAP')");
stmt.setString("lectureid", lectureId);
return stmt;
}
public static void getSplitForLectureLdap(MysqlConnection connection, String lectureId,
List<LdapFilter> custom, List<Integer> predef) throws SQLException {
MysqlStatement stmt = getLdapFilterStatement(connection, lectureId);
ResultSet rs = stmt.executeQuery();
while (rs.next()) {
int id = rs.getInt("filterid");
if (id != 0) {
predef.add(id);
} else {
LdapFilter filter = new LdapFilter(rs.getString("filterkey"), rs.getString("filtervalue"));
filter.setFilterId(id);
filter.setTitle(rs.getString("filtername"));
custom.add(filter);
}
}
}
public static List<LdapFilter> getPredefinedLdap()
throws SQLException {
try (MysqlConnection connection = Database.getConnection()) {
List<LdapFilter> list = new ArrayList<>();
MysqlStatement stmt = connection.prepareStatement("SELECT filterid, filtername, filterkey, filtervalue"
+ " FROM presetlecturefilter WHERE filtertype = 'LDAP'");
ResultSet rs = stmt.executeQuery();
while (rs.next()) {
LdapFilter filter = new LdapFilter(rs.getString("filterkey"), rs.getString("filtervalue"));
filter.setFilterId(rs.getInt("filterid"));
filter.setTitle(rs.getString("filtername"));
list.add(filter);
}
return list;
} catch (SQLException e) {
LOGGER.error("Query failed in getPredefinedLdapFilters()", e);
throw e;
}
}
public static final List<XmlFilterEntry> getFiltersXml(MysqlConnection connection, String lectureId)
throws SQLException {
List<XmlFilterEntry> list = null;
MysqlStatement stmt = getLdapFilterStatement(connection, lectureId);
ResultSet rs = stmt.executeQuery();
while (rs.next()) {
String key = rs.getString("filterkey");
String value = rs.getString("filtervalue");
if (list == null) {
list = new ArrayList<>();
}
list.add(new XmlFilterEntry("LDAP", key, value));
}
return list;
}
public static void writeForLectureLdap(MysqlConnection connection, String lectureId, List<LdapFilter> list)
throws SQLException {
if (lectureId == null || lectureId.isEmpty()) {
return;
}
MysqlStatement delStmt = connection.prepareStatement("DELETE FROM lecturefilter WHERE lectureid = :lectureid");
delStmt.setString("lectureid", lectureId);
delStmt.executeUpdate();
if (list == null || list.isEmpty()) {
return;
}
MysqlStatement addCustomStmt = connection.prepareStatement(
"INSERT INTO lecturefilter (lectureid, filtertype, filterkey, filtervalue)"
+ " VALUES (:lectureid, 'LDAP', :key, :value)");
MysqlStatement addPredefStmt = connection.prepareStatement(
"INSERT INTO lecturefilter (lectureid, filterpresetid)"
+ " VALUES (:lectureid, :filterid)");
addCustomStmt.setString("lectureid", lectureId);
addPredefStmt.setString("lectureid", lectureId);
for (LdapFilter filter : list) {
if (filter.filterId == 0) {
// Custom
if (Util.isEmptyString(filter.attribute) || filter.value == null)
continue;
addCustomStmt.setString("key", filter.attribute);
addCustomStmt.setString("value", filter.value);
addCustomStmt.executeUpdate();
} else {
// Predef reference
addPredefStmt.setInt("filterid", filter.filterId);
addPredefStmt.executeUpdate();
}
}
}
}
|