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 int conditionCount = 0;
151 for (Map.Entry<String, String> qualifier : qual.entrySet()) {
152 if (StringUtils.isNotEmpty(qualifier.getValue())) {
153
154 conditionCount++;
155
156
157 if (conditionCount == 1) {
158 sql1.append("(");
159 }
160
161
162 String value = (qualifier.getValue()).replace('*', '%');
163 sql1.append(" (B1.ATTR_VAL LIKE ? AND B1.KIM_ATTR_DEFN_ID = ?) ");
164 params1.add(value);
165 params1.add(qualifier.getKey());
166 }
167
168 sql1.append("OR");
169 }
170
171 sql1.delete(sql1.length() - 2, sql1.length());
172
173 if (conditionCount != 0) {
174 sql1.append(") AND");
175 }
176 sql1.append(" B1.ROLE_MBR_ID = A0.ROLE_MBR_ID)");
177 }
178
179 StringBuilder sql = new StringBuilder(sql1.toString());
180
181 if (sql2.length() > 0) {
182 sql.append(" UNION ALL ");
183 sql.append(sql2.toString());
184 }
185
186 sql.append(" ORDER BY ROLE_MBR_ID ");
187
188 PreparedStatement statement = connection.prepareStatement(sql.toString());
189 int i = 1;
190 for (String param : params1) {
191 statement.setString(i, param);
192 i++;
193 }
194
195 if (sql2.length() > 0) {
196 for (String param : params2) {
197 statement.setString(i, param);
198 i++;
199 }
200 }
201
202 return statement;
203 }
204 }, new PreparedStatementCallback<List<RoleMemberBo>>() {
205 @Override
206 public List<RoleMemberBo> doInPreparedStatement(
207 PreparedStatement statement) throws SQLException, DataAccessException {
208 ResultSet rs = statement.executeQuery();
209 try {
210 RoleMemberBo lastRoleMember = null;
211 while (rs.next()) {
212 boolean processRolemember = true;
213
214 String roleId = rs.getString("ROLE_ID");
215 String id = rs.getString("ROLE_MBR_ID");
216 String memberId = rs.getString("MBR_ID");
217
218 MemberType memberType = MemberType.fromCode(rs.getString("MBR_TYP_CD"));
219 DateTime activeFromDate = rs.getDate("ROLE_MBR_ACTV_FRM_DT") == null ? null: new DateTime(rs.getDate("ROLE_MBR_ACTV_FRM_DT"));
220 DateTime activeToDate = rs.getDate("ROLE_MBR_ACTV_TO_DT") == null ? null: new DateTime(rs.getDate("ROLE_MBR_ACTV_TO_DT"));
221
222
223
224 if (lastRoleMember == null || !id.equals(lastRoleMember.getId())) {
225 RoleMember roleMember = RoleMember.Builder.create(roleId, id, memberId, memberType,
226 activeFromDate, activeToDate, new HashMap<String, String>(), "", "").build();
227 Long roleVersionNbr = rs.getLong("ROLE_MBR_VER_NBR");
228 String roleObjId = rs.getString("ROLE_MBR_OBJ_ID");
229
230 RoleMemberBo roleMemberBo = RoleMemberBo.from(roleMember);
231 roleMemberBo.setVersionNumber(roleVersionNbr);
232 roleMemberBo.setObjectId(roleObjId);
233 List<RoleMemberAttributeDataBo> roleMemAttrBos = new ArrayList<RoleMemberAttributeDataBo>();
234
235 roleMemberBo.setAttributeDetails(roleMemAttrBos);
236 if(roleMemberBo.isActive(new Timestamp(System.currentTimeMillis()))){
237 roleMemberBos.add(roleMemberBo);
238 } else {
239 processRolemember = false;
240 }
241
242 lastRoleMember = roleMemberBo;
243 }
244
245 String kimTypeId = rs.getString("KIM_TYP_ID");
246 if (processRolemember && StringUtils.isNotEmpty(kimTypeId)) {
247 KimType theType = KimApiServiceLocator.getKimTypeInfoService().getKimType(kimTypeId);
248
249 RoleMemberAttributeDataBo roleMemAttrDataBo = new RoleMemberAttributeDataBo();
250
251 KimAttribute.Builder attrBuilder = KimAttribute.Builder.create(
252 rs.getString("ATTR_DEFN_CMPNT_NM")
253 , rs.getString("ATTR_NAME")
254 , rs.getString("ATTR_DEFN_NMSPC_CD"));
255 attrBuilder.setActive(Truth.strToBooleanIgnoreCase(rs.getString("ATTR_DEFN_ACTV_IND")));
256 attrBuilder.setAttributeLabel(rs.getString("ATTR_DEFN_LBL"));
257 attrBuilder.setId(rs.getString("KIM_ATTR_DEFN_ID"));
258 attrBuilder.setObjectId(rs.getString("ATTR_DEFN_OBJ_ID"));
259 attrBuilder.setVersionNumber(rs.getLong("ATTR_DEFN_VER_NBR"));
260
261 roleMemAttrDataBo.setId(rs.getString("ATTR_DATA_ID"));
262 roleMemAttrDataBo.setAssignedToId(id);
263 roleMemAttrDataBo.setKimTypeId(kimTypeId);
264 roleMemAttrDataBo.setKimType(KimTypeBo.from(theType));
265 roleMemAttrDataBo.setKimAttributeId(attrBuilder.getId());
266 roleMemAttrDataBo.setAttributeValue(rs.getString("ATTR_VAL"));
267 roleMemAttrDataBo.setVersionNumber(attrBuilder.getVersionNumber());
268 roleMemAttrDataBo.setObjectId(attrBuilder.getObjectId());
269
270 roleMemAttrDataBo.setKimAttribute(KimAttributeBo.from(attrBuilder.build()));
271 lastRoleMember.getAttributeDetails().add(roleMemAttrDataBo);
272 }
273
274 }
275 } finally {
276 if (rs != null) {
277 rs.close();
278 }
279 }
280 return roleMemberBos;
281 }
282 }
283 );
284 return roleMemberBos;
285 }
286
287 public void setDataSource(DataSource dataSource) {
288 this.dataSource = new TransactionAwareDataSourceProxy(dataSource);
289 }
290
291 }