1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16 package org.kuali.rice.kim.impl.role;
17
18 import java.sql.Connection;
19 import java.sql.PreparedStatement;
20 import java.sql.ResultSet;
21 import java.sql.SQLException;
22 import java.sql.Timestamp;
23 import java.util.ArrayList;
24 import java.util.Collection;
25 import java.util.HashMap;
26 import java.util.List;
27 import java.util.Map;
28
29 import javax.sql.DataSource;
30
31 import org.apache.commons.collections.CollectionUtils;
32 import org.apache.commons.lang.StringUtils;
33 import org.joda.time.DateTime;
34 import org.kuali.rice.core.api.membership.MemberType;
35 import org.kuali.rice.core.api.util.Truth;
36 import org.kuali.rice.kim.api.common.attribute.KimAttribute;
37 import org.kuali.rice.kim.api.role.RoleMember;
38 import org.kuali.rice.kim.api.services.KimApiServiceLocator;
39 import org.kuali.rice.kim.api.type.KimType;
40 import org.kuali.rice.kim.impl.common.attribute.KimAttributeBo;
41 import org.kuali.rice.kim.impl.type.KimTypeBo;
42 import org.springframework.dao.DataAccessException;
43 import org.springframework.jdbc.core.JdbcTemplate;
44 import org.springframework.jdbc.core.PreparedStatementCallback;
45 import org.springframework.jdbc.core.PreparedStatementCreator;
46 import org.springframework.jdbc.datasource.TransactionAwareDataSourceProxy;
47
48 public class RoleDaoJdbc implements RoleDao {
49
50 protected DataSource dataSource;
51
52 @Override
53 public List<RoleMemberBo> getRoleMembersForRoleIds(Collection<String> roleIds, String memberTypeCode,
54 Map<String, String> qualification) {
55 JdbcTemplate template = new JdbcTemplate(dataSource);
56 final List<String> roleIDs = new ArrayList<String>(roleIds);
57 final String memberTypeCd = memberTypeCode;
58 final Map<String, String> qual = qualification;
59 final List<RoleMemberBo> roleMemberBos = new ArrayList<RoleMemberBo>();
60 template.execute(new PreparedStatementCreator() {
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81 @Override
82 public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
83
84
85
86
87
88 StringBuilder sql1 = new StringBuilder("SELECT "
89 + " A0.ROLE_MBR_ID AS ROLE_MBR_ID,A0.ROLE_ID AS ROLE_ID,A0.MBR_ID AS MBR_ID,A0.MBR_TYP_CD AS MBR_TYP_CD,A0.VER_NBR AS ROLE_MBR_VER_NBR,A0.OBJ_ID AS ROLE_MBR_OBJ_ID,A0.ACTV_FRM_DT AS ROLE_MBR_ACTV_FRM_DT ,A0.ACTV_TO_DT AS ROLE_MBR_ACTV_TO_DT, "
90 + " BO.KIM_TYP_ID AS KIM_TYP_ID, BO.KIM_ATTR_DEFN_ID AS KIM_ATTR_DEFN_ID, BO.ATTR_VAL AS ATTR_VAL, BO.ATTR_DATA_ID AS ATTR_DATA_ID, BO.OBJ_ID AS ATTR_DATA_OBJ_ID, BO.VER_NBR AS ATTR_DATA_VER_NBR, "
91 + " C0.KIM_ATTR_DEFN_ID AS KIM_ATTR_DEFN_ID, C0.OBJ_ID AS ATTR_DEFN_OBJ_ID, C0.VER_NBR as ATTR_DEFN_VER_NBR, C0.NM AS ATTR_NAME, C0.LBL as ATTR_DEFN_LBL, C0.ACTV_IND as ATTR_DEFN_ACTV_IND, C0.NMSPC_CD AS ATTR_DEFN_NMSPC_CD, C0.CMPNT_NM AS ATTR_DEFN_CMPNT_NM "
92 + " FROM KRIM_ROLE_MBR_T A0 JOIN KRIM_ROLE_MBR_ATTR_DATA_T BO ON A0.ROLE_MBR_ID = BO.ROLE_MBR_ID "
93 + " JOIN KRIM_ATTR_DEFN_T C0 ON BO.KIM_ATTR_DEFN_ID = C0.KIM_ATTR_DEFN_ID ");
94
95 StringBuilder sql2 = new StringBuilder("SELECT"
96 + " D0.ROLE_MBR_ID AS ROLE_MBR_ID,D0.ROLE_ID AS ROLE_ID,D0.MBR_ID AS MBR_ID,D0.MBR_TYP_CD AS MBR_TYP_CD,D0.VER_NBR AS ROLE_MBR_VER_NBR,D0.OBJ_ID AS ROLE_MBR_OBJ_ID,D0.ACTV_FRM_DT AS ROLE_MBR_ACTV_FRM_DT ,D0.ACTV_TO_DT AS ROLE_MBR_ACTV_TO_DT, "
97 + " '' AS KIM_TYP_ID, '' AS KIM_ATTR_DEFN_ID, '' AS ATTR_VAL, '' AS ATTR_DATA_ID, '' AS ATTR_DATA_OBJ_ID, NULL AS ATTR_DATA_VER_NBR,"
98 + " '' AS KIM_ATTR_DEFN_ID,'' AS ATTR_DEFN_OBJ_ID, NULL as ATTR_DEFN_VER_NBR, '' AS ATTR_NAME, '' as ATTR_DEFN_LBL, '' as ATTR_DEFN_ACTV_IND, '' AS ATTR_DEFN_NMSPC_CD, '' AS ATTR_DEFN_CMPNT_NM "
99 + " FROM KRIM_ROLE_MBR_T D0 "
100 + " WHERE D0.ROLE_MBR_ID NOT IN (SELECT DISTINCT (E0.ROLE_MBR_ID) FROM KRIM_ROLE_MBR_ATTR_DATA_T E0)");
101
102 StringBuilder criteria = new StringBuilder();
103
104 List<String> params1 = new ArrayList<String>();
105 List<String> params2 = new ArrayList<String>();
106
107 if (roleIDs != null && !roleIDs.isEmpty()) {
108 criteria.append("A0.ROLE_ID IN (");
109
110 for (String roleId : roleIDs) {
111 criteria.append("?,");
112 params1.add(roleId);
113 params2.add(roleId);
114 }
115 criteria.deleteCharAt(criteria.length() - 1);
116 criteria.append(")");
117 }
118
119 if (memberTypeCd != null) {
120 if (criteria.length() > 0) {
121 criteria.append(" AND ");
122 }
123
124 criteria.append("A0.MBR_TYP_CD = ?");
125 params1.add(memberTypeCd);
126 params2.add(memberTypeCd);
127 }
128
129
130 if (criteria.length() > 0) {
131 sql1.append(" WHERE ");
132 sql2.append(" AND ");
133 sql1.append(criteria);
134 sql2.append(criteria.toString().replaceAll("A0", "D0"));
135 }
136
137 if (qual != null && CollectionUtils.isNotEmpty(qual.keySet())) {
138
139
140
141 sql2 = new StringBuilder();
142
143 if (criteria.length() > 0) {
144 sql1.append(" AND ");
145 } else {
146 sql1.append(" WHERE ");
147 }
148
149 sql1.append(" EXISTS (SELECT B1.ROLE_MBR_ID FROM KRIM_ROLE_MBR_ATTR_DATA_T B1 WHERE (");
150 for (Map.Entry<String, String> qualifier : qual.entrySet()) {
151 if (StringUtils.isNotEmpty(qualifier.getValue())) {
152 String value = (qualifier.getValue()).replace('*', '%');
153 sql1.append(" (B1.ATTR_VAL LIKE ? AND B1.KIM_ATTR_DEFN_ID = ? ) ");
154 params1.add(value);
155 params1.add(qualifier.getKey());
156 }
157 sql1.append("OR");
158 }
159 sql1.delete(sql1.length() - 2, sql1.length());
160 sql1.append(") AND B1.ROLE_MBR_ID = A0.ROLE_MBR_ID )");
161
162 }
163
164 StringBuilder sql = new StringBuilder(sql1.toString());
165
166 if (sql2.length() > 0) {
167 sql.append(" UNION ALL ");
168 sql.append(sql2.toString());
169 }
170
171 sql.append(" ORDER BY ROLE_MBR_ID ");
172
173 PreparedStatement statement = connection.prepareStatement(sql.toString());
174 int i = 1;
175 for (String param : params1) {
176 statement.setString(i, param);
177 i++;
178 }
179
180 if (sql2.length() > 0) {
181 for (String param : params2) {
182 statement.setString(i, param);
183 i++;
184 }
185 }
186
187 return statement;
188 }
189 }, new PreparedStatementCallback<List<RoleMemberBo>>() {
190 @Override
191 public List<RoleMemberBo> doInPreparedStatement(
192 PreparedStatement statement) throws SQLException, DataAccessException {
193 ResultSet rs = statement.executeQuery();
194 try {
195 RoleMemberBo lastRoleMember = null;
196 while (rs.next()) {
197 boolean processRolemember = true;
198
199 String roleId = rs.getString("ROLE_ID");
200 String id = rs.getString("ROLE_MBR_ID");
201 String memberId = rs.getString("MBR_ID");
202
203 MemberType memberType = MemberType.fromCode(rs.getString("MBR_TYP_CD"));
204 DateTime activeFromDate = rs.getDate("ROLE_MBR_ACTV_FRM_DT") == null ? null: new DateTime(rs.getDate("ROLE_MBR_ACTV_FRM_DT"));
205 DateTime activeToDate = rs.getDate("ROLE_MBR_ACTV_TO_DT") == null ? null: new DateTime(rs.getDate("ROLE_MBR_ACTV_TO_DT"));
206
207
208
209 if (lastRoleMember == null || !id.equals(lastRoleMember.getId())) {
210 RoleMember roleMember = RoleMember.Builder.create(roleId, id, memberId, memberType,
211 activeFromDate, activeToDate, new HashMap<String, String>(), "", "").build();
212 Long roleVersionNbr = rs.getLong("ROLE_MBR_VER_NBR");
213 String roleObjId = rs.getString("ROLE_MBR_OBJ_ID");
214
215 RoleMemberBo roleMemberBo = RoleMemberBo.from(roleMember);
216 roleMemberBo.setVersionNumber(roleVersionNbr);
217 roleMemberBo.setObjectId(roleObjId);
218 List<RoleMemberAttributeDataBo> roleMemAttrBos = new ArrayList<RoleMemberAttributeDataBo>();
219
220 roleMemberBo.setAttributeDetails(roleMemAttrBos);
221 if(roleMemberBo.isActive(new Timestamp(System.currentTimeMillis()))){
222 roleMemberBos.add(roleMemberBo);
223 } else {
224 processRolemember = false;
225 }
226
227 lastRoleMember = roleMemberBo;
228 }
229
230 String kimTypeId = rs.getString("KIM_TYP_ID");
231 if (processRolemember && StringUtils.isNotEmpty(kimTypeId)) {
232 KimType theType = KimApiServiceLocator.getKimTypeInfoService().getKimType(kimTypeId);
233
234 RoleMemberAttributeDataBo roleMemAttrDataBo = new RoleMemberAttributeDataBo();
235
236 KimAttribute.Builder attrBuilder = KimAttribute.Builder.create(
237 rs.getString("ATTR_DEFN_CMPNT_NM")
238 , rs.getString("ATTR_NAME")
239 , rs.getString("ATTR_DEFN_NMSPC_CD"));
240 attrBuilder.setActive(Truth.strToBooleanIgnoreCase(rs.getString("ATTR_DEFN_ACTV_IND")));
241 attrBuilder.setAttributeLabel(rs.getString("ATTR_DEFN_LBL"));
242 attrBuilder.setId(rs.getString("KIM_ATTR_DEFN_ID"));
243 attrBuilder.setObjectId(rs.getString("ATTR_DEFN_OBJ_ID"));
244 attrBuilder.setVersionNumber(rs.getLong("ATTR_DEFN_VER_NBR"));
245
246 roleMemAttrDataBo.setId(rs.getString("ATTR_DATA_ID"));
247 roleMemAttrDataBo.setAssignedToId(id);
248 roleMemAttrDataBo.setKimTypeId(kimTypeId);
249 roleMemAttrDataBo.setKimType(KimTypeBo.from(theType));
250 roleMemAttrDataBo.setKimAttributeId(attrBuilder.getId());
251 roleMemAttrDataBo.setAttributeValue(rs.getString("ATTR_VAL"));
252 roleMemAttrDataBo.setVersionNumber(attrBuilder.getVersionNumber());
253 roleMemAttrDataBo.setObjectId(attrBuilder.getObjectId());
254
255 roleMemAttrDataBo.setKimAttribute(KimAttributeBo.from(attrBuilder.build()));
256 lastRoleMember.getAttributeDetails().add(roleMemAttrDataBo);
257 }
258
259 }
260 } finally {
261 if (rs != null) {
262 rs.close();
263 }
264 }
265 return roleMemberBos;
266 }
267 }
268 );
269 return roleMemberBos;
270 }
271
272 public void setDataSource(DataSource dataSource) {
273 this.dataSource = new TransactionAwareDataSourceProxy(dataSource);
274 }
275
276 }