View Javadoc

1   /**
2    * Copyright 2004-2012 The Kuali Foundation
3    *
4    * Licensed under the Educational Community License, Version 2.0 (the "License");
5    * you may not use this file except in compliance with the License.
6    * You may obtain a copy of the License at
7    *
8    * http://www.opensource.org/licenses/ecl2.php
9    *
10   * Unless required by applicable law or agreed to in writing, software
11   * distributed under the License is distributed on an "AS IS" BASIS,
12   * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
13   * See the License for the specific language governing permissions and
14   * limitations under the License.
15   */
16  package org.kuali.hr.time.roles.dao;
17  
18  import java.sql.Date;
19  import java.util.ArrayList;
20  import java.util.Collection;
21  import java.util.List;
22  
23  import org.apache.commons.collections.CollectionUtils;
24  import org.apache.commons.lang.StringUtils;
25  import org.apache.ojb.broker.query.Criteria;
26  import org.apache.ojb.broker.query.Query;
27  import org.apache.ojb.broker.query.QueryFactory;
28  import org.apache.ojb.broker.query.ReportQueryByCriteria;
29  import org.kuali.hr.job.Job;
30  import org.kuali.hr.time.roles.TkRole;
31  import org.kuali.hr.time.service.base.TkServiceLocator;
32  import org.kuali.hr.time.util.TKUtils;
33  import org.kuali.hr.time.util.TkConstants;
34  import org.kuali.hr.time.workarea.WorkArea;
35  import org.kuali.rice.core.framework.persistence.ojb.dao.PlatformAwareDaoBaseOjb;
36  import org.kuali.rice.krad.service.KRADServiceLocator;
37  
38  public class TkRoleDaoSpringOjbImpl extends PlatformAwareDaoBaseOjb implements TkRoleDao {
39  
40      public List<TkRole> findAllRoles(String principalId, Date asOfDate) {
41          return findRoles(principalId, asOfDate, null, null, null, null);
42      }
43  
44      @SuppressWarnings("unchecked")
45      @Override
46      public List<TkRole> findPositionRoles(String positionNumber, Date asOfDate, String roleName, Long workArea, String department, String chart) {
47          List<TkRole> roles = new ArrayList<TkRole>();
48  
49          Criteria root = new Criteria();
50          Criteria effdt = new Criteria();
51          Criteria timestamp = new Criteria();
52          ReportQueryByCriteria effdtSubQuery;
53          ReportQueryByCriteria timestampSubQuery;
54  
55          effdt.addEqualToField("roleName", Criteria.PARENT_QUERY_PREFIX + "roleName");
56          effdt.addEqualToField("positionNumber", Criteria.PARENT_QUERY_PREFIX + "positionNumber");
57          effdt.addLessOrEqualThan("effectiveDate", asOfDate);
58  
59          // EFFECTIVE DATE --
60  
61          // Adding criteria to nest an AND that has multiple ORs to select
62          // the correct ID / date combination.
63          Criteria orWrapperEd = new Criteria();
64          Criteria nstWaEd = new Criteria();
65          Criteria nstDptEd = new Criteria();
66          Criteria nstChrEd = new Criteria();
67  
68          // Inner AND to allow for all null chart/dept/work area
69          Criteria nullAndWrapper = new Criteria();
70          nullAndWrapper.addIsNull("workArea");
71          nullAndWrapper.addIsNull("department");
72          nullAndWrapper.addIsNull("chart");
73  
74          nstWaEd.addEqualToField("workArea", Criteria.PARENT_QUERY_PREFIX + "workArea"); // OR
75          nstDptEd.addEqualToField("department", Criteria.PARENT_QUERY_PREFIX + "department"); // OR
76          nstChrEd.addEqualToField("chart", Criteria.PARENT_QUERY_PREFIX + "chart"); // OR
77          orWrapperEd.addOrCriteria(nstWaEd);
78          orWrapperEd.addOrCriteria(nstDptEd);
79          orWrapperEd.addOrCriteria(nstChrEd);
80  
81          // Inner AND to allow for all null chart/dept/work area
82          orWrapperEd.addOrCriteria(nullAndWrapper);
83  
84          // Add the inner OR criteria to effective date
85          effdt.addAndCriteria(orWrapperEd);
86  
87          effdtSubQuery = QueryFactory.newReportQuery(TkRole.class, effdt);
88          effdtSubQuery.setAttributes(new String[]{"max(effdt)"});
89  
90  
91          // TIMESTAMP --
92  
93          //Configure the actual "criteria" in the where clause
94          timestamp.addEqualToField("roleName", Criteria.PARENT_QUERY_PREFIX + "roleName");
95          timestamp.addEqualToField("positionNumber", Criteria.PARENT_QUERY_PREFIX + "positionNumber");
96          timestamp.addEqualToField("effectiveDate", Criteria.PARENT_QUERY_PREFIX + "effectiveDate");
97  
98          // Adding criteria to nest an AND that has multiple ORs to select
99          // the correct ID / date combination.
100         orWrapperEd = new Criteria();
101         nstWaEd = new Criteria();
102         nstDptEd = new Criteria();
103         nstChrEd = new Criteria();
104 
105         // Inner AND to allow for all null chart/dept/work area
106         nullAndWrapper = new Criteria();
107         nullAndWrapper.addIsNull("workArea");
108         nullAndWrapper.addIsNull("department");
109         nullAndWrapper.addIsNull("chart");
110 
111         nstWaEd.addEqualToField("workArea", Criteria.PARENT_QUERY_PREFIX + "workArea"); // OR
112         nstDptEd.addEqualToField("department", Criteria.PARENT_QUERY_PREFIX + "department"); // OR
113         nstChrEd.addEqualToField("chart", Criteria.PARENT_QUERY_PREFIX + "chart"); // OR
114         orWrapperEd.addOrCriteria(nstWaEd);
115         orWrapperEd.addOrCriteria(nstDptEd);
116         orWrapperEd.addOrCriteria(nstChrEd);
117 
118         // Inner AND to allow for all null chart/dept/work area
119         orWrapperEd.addOrCriteria(nullAndWrapper);
120 
121         // Add the inner OR criteria to effective date
122         timestamp.addAndCriteria(orWrapperEd);
123 
124         timestampSubQuery = QueryFactory.newReportQuery(TkRole.class, timestamp);
125         timestampSubQuery.setAttributes(new String[]{"max(timestamp)"});
126 
127 
128         // Filter by Max(EffDt) / Max(Timestamp)
129         root.addEqualTo("effectiveDate", effdtSubQuery);
130         root.addEqualTo("timestamp", timestampSubQuery);
131 
132         // Optional ROOT criteria added :
133         if (workArea != null)
134             root.addEqualTo("workArea", workArea);
135         if (StringUtils.isNotEmpty(department))
136             root.addEqualTo("department", department);
137         if (chart != null)
138             root.addEqualTo("chart", chart);
139         if (roleName != null)
140             root.addEqualTo("roleName", roleName);
141         if (positionNumber != null)
142             root.addEqualTo("positionNumber", positionNumber);
143 
144         // Filter for ACTIVE = 'Y'
145         Criteria activeFilter = new Criteria();
146         activeFilter.addEqualTo("active", true);
147         root.addAndCriteria(activeFilter);
148 
149         Query query = QueryFactory.newQuery(TkRole.class, root);
150         Collection c = this.getPersistenceBrokerTemplate().getCollectionByQuery(query);
151 
152         if (c != null) {
153             roles.addAll(c);
154         }
155 
156         return roles;
157     }
158 
159     @SuppressWarnings("unchecked")
160     @Override
161     public List<TkRole> findRoles(String principalId, Date asOfDate, String roleName, Long workArea, String department, String chart) {
162         List<TkRole> roles = new ArrayList<TkRole>();
163 
164         Criteria root = new Criteria();
165         Criteria effdt = new Criteria();
166         Criteria timestamp = new Criteria();
167         Criteria departmentCriteria = new Criteria();
168         Criteria workAreaCriteria = new Criteria();
169         ReportQueryByCriteria effdtSubQuery;
170         ReportQueryByCriteria timestampSubQuery;
171 
172         // EFFECTIVE DATE / TIMESTAMP
173         effdt.addEqualToField("roleName", Criteria.PARENT_QUERY_PREFIX + "roleName");
174         effdt.addEqualToField("principalId", Criteria.PARENT_QUERY_PREFIX + "principalId");
175         effdt.addLessOrEqualThan("effectiveDate", asOfDate);
176         
177         if (workArea != null || StringUtils.isNotEmpty(department) || StringUtils.isNotEmpty(chart)) {
178             if (workArea != null)
179                 effdt.addEqualToField("workArea", Criteria.PARENT_QUERY_PREFIX + "workArea");
180             if (department != null)
181                 effdt.addEqualToField("department", Criteria.PARENT_QUERY_PREFIX + "department");
182             if (chart != null)
183                 effdt.addEqualToField("chart", Criteria.PARENT_QUERY_PREFIX + "chart");
184         }
185 
186         timestamp.addEqualToField("roleName", Criteria.PARENT_QUERY_PREFIX + "roleName");
187         timestamp.addEqualToField("principalId", Criteria.PARENT_QUERY_PREFIX + "principalId");
188         timestamp.addEqualToField("effectiveDate", Criteria.PARENT_QUERY_PREFIX + "effectiveDate");
189 
190         if (workArea != null || StringUtils.isNotEmpty(department) || StringUtils.isNotEmpty(chart)) {
191             if (workArea != null)
192                 timestamp.addEqualToField("workArea", Criteria.PARENT_QUERY_PREFIX + "workArea");
193             if (department != null)
194                 timestamp.addEqualToField("department", Criteria.PARENT_QUERY_PREFIX + "department");
195             if (chart != null)
196                 timestamp.addEqualToField("chart", Criteria.PARENT_QUERY_PREFIX + "chart");
197         }
198 
199         timestampSubQuery = QueryFactory.newReportQuery(TkRole.class, timestamp);
200         timestampSubQuery.setAttributes(new String[]{"max(timestamp)"});
201 
202         effdt.addEqualTo("timestamp", timestampSubQuery);
203 
204         effdtSubQuery = QueryFactory.newReportQuery(TkRole.class, effdt);
205         effdtSubQuery.setAttributes(new String[]{"max(effdt)"});
206 
207         root.addEqualTo("effectiveDate", effdtSubQuery);
208 
209         if (workArea != null) {
210             root.addEqualTo("workArea", workArea);
211         }
212 
213         if (StringUtils.isNotEmpty(department)) {
214             departmentCriteria.addEqualTo("department", department);
215             Collection<WorkArea> collectionWorkAreas = TkServiceLocator.getWorkAreaService().getWorkAreas(department, asOfDate);
216             if (CollectionUtils.isNotEmpty(collectionWorkAreas)) {
217                 List<Long> longWorkAreas = new ArrayList<Long>();
218                 for(WorkArea cwa : collectionWorkAreas){
219                     longWorkAreas.add(cwa.getWorkArea());
220                 }
221                 workAreaCriteria.addIn("workArea", longWorkAreas);
222                 departmentCriteria.addOrCriteria(workAreaCriteria);
223             }
224             root.addAndCriteria(departmentCriteria);
225         }
226         if (StringUtils.isNotEmpty(chart)) {
227             root.addEqualTo("chart", chart);
228         }
229         if (StringUtils.isNotEmpty(roleName)) {
230             root.addEqualTo("roleName", roleName);
231         }
232         if (StringUtils.isNotEmpty(principalId)) {
233             root.addEqualTo("principalId", principalId);
234         }
235 
236         // Filter for ACTIVE = 'Y'
237         root.addEqualTo("active", true);
238 
239         Query query = QueryFactory.newQuery(TkRole.class, root);
240         // limit the number of the resultset
241         // TODO: hard coding the limits?  probably not the most user friendly of ways to do this
242         query.setStartAtIndex(0);
243         query.setEndAtIndex(299);
244         Collection c = this.getPersistenceBrokerTemplate().getCollectionByQuery(query);
245 
246         if (c != null) {
247             roles.addAll(c);
248         }
249 
250         if (StringUtils.isNotBlank(principalId)) {
251             //Fetch all the jobs and grab any position roles for this persons jobs
252             List<Job> lstActiveJobs = TkServiceLocator.getJobService().getJobs(principalId, asOfDate);
253             for (Job job : lstActiveJobs) {
254                 if (job.getPositionNumber() != null) {
255                     List<TkRole> lstRoles = findPositionRoles(job.getPositionNumber(),
256                             asOfDate, roleName, workArea, department, chart);
257                     roles.addAll(lstRoles);
258                 }
259             }
260         } else if (workArea != null) {
261             List<TkRole> lstPosRoles = getPositionRolesForWorkArea(workArea, asOfDate);
262             for (TkRole tkRole : lstPosRoles) {
263                 roles.add(tkRole);
264             }
265         }
266         return roles;
267     }
268 
269     @SuppressWarnings("unchecked")
270     @Override
271     public List<TkRole> findInActiveRoles(String principalId, Date asOfDate, String roleName, Long workArea, String department, String chart) {
272         List<TkRole> roles = new ArrayList<TkRole>();
273 
274         Criteria root = new Criteria();
275         Criteria effdt = new Criteria();
276         Criteria timestamp = new Criteria();
277         ReportQueryByCriteria effdtSubQuery;
278         ReportQueryByCriteria timestampSubQuery;
279 
280         effdt.addEqualToField("roleName", Criteria.PARENT_QUERY_PREFIX + "roleName");
281         effdt.addEqualToField("principalId", Criteria.PARENT_QUERY_PREFIX + "principalId");
282         effdt.addLessOrEqualThan("effectiveDate", asOfDate);
283 
284         // EFFECTIVE DATE --
285 
286         // Adding criteria to nest an AND that has multiple ORs to select
287         // the correct ID / date combination.
288         Criteria orWrapperEd = new Criteria();
289         Criteria nstWaEd = new Criteria();
290         Criteria nstDptEd = new Criteria();
291         Criteria nstChrEd = new Criteria();
292 
293         // Inner AND to allow for all null chart/dept/work area
294         Criteria nullAndWrapper = new Criteria();
295         nullAndWrapper.addIsNull("workArea");
296         nullAndWrapper.addIsNull("department");
297         nullAndWrapper.addIsNull("chart");
298 
299         nstWaEd.addEqualToField("workArea", Criteria.PARENT_QUERY_PREFIX + "workArea"); // OR
300         nstDptEd.addEqualToField("department", Criteria.PARENT_QUERY_PREFIX + "department"); // OR
301         nstChrEd.addEqualToField("chart", Criteria.PARENT_QUERY_PREFIX + "chart"); // OR
302         orWrapperEd.addOrCriteria(nstWaEd);
303         orWrapperEd.addOrCriteria(nstDptEd);
304         orWrapperEd.addOrCriteria(nstChrEd);
305 
306         // Inner AND to allow for all null chart/dept/work area
307         orWrapperEd.addOrCriteria(nullAndWrapper);
308 
309         // Add the inner OR criteria to effective date
310         effdt.addAndCriteria(orWrapperEd);
311 
312         effdtSubQuery = QueryFactory.newReportQuery(TkRole.class, effdt);
313         effdtSubQuery.setAttributes(new String[]{"max(effdt)"});
314 
315 
316         // TIMESTAMP --
317 
318         //Configure the actual "criteria" in the where clause
319         timestamp.addEqualToField("roleName", Criteria.PARENT_QUERY_PREFIX + "roleName");
320         timestamp.addEqualToField("principalId", Criteria.PARENT_QUERY_PREFIX + "principalId");
321         timestamp.addEqualToField("effectiveDate", Criteria.PARENT_QUERY_PREFIX + "effectiveDate");
322 
323         // Adding criteria to nest an AND that has multiple ORs to select
324         // the correct ID / date combination.
325         orWrapperEd = new Criteria();
326         nstWaEd = new Criteria();
327         nstDptEd = new Criteria();
328         nstChrEd = new Criteria();
329 
330         // Inner AND to allow for all null chart/dept/work area
331         nullAndWrapper = new Criteria();
332         nullAndWrapper.addIsNull("workArea");
333         nullAndWrapper.addIsNull("department");
334         nullAndWrapper.addIsNull("chart");
335 
336         nstWaEd.addEqualToField("workArea", Criteria.PARENT_QUERY_PREFIX + "workArea"); // OR
337         nstDptEd.addEqualToField("department", Criteria.PARENT_QUERY_PREFIX + "department"); // OR
338         nstChrEd.addEqualToField("chart", Criteria.PARENT_QUERY_PREFIX + "chart"); // OR
339         orWrapperEd.addOrCriteria(nstWaEd);
340         orWrapperEd.addOrCriteria(nstDptEd);
341         orWrapperEd.addOrCriteria(nstChrEd);
342 
343         // Inner AND to allow for all null chart/dept/work area
344         orWrapperEd.addOrCriteria(nullAndWrapper);
345 
346         // Add the inner OR criteria to effective date
347         timestamp.addAndCriteria(orWrapperEd);
348 
349         timestampSubQuery = QueryFactory.newReportQuery(TkRole.class, timestamp);
350         timestampSubQuery.setAttributes(new String[]{"max(timestamp)"});
351 
352 
353         // Filter by Max(EffDt) / Max(Timestamp)
354         root.addEqualTo("effectiveDate", effdtSubQuery);
355         root.addEqualTo("timestamp", timestampSubQuery);
356 
357         // Optional ROOT criteria added :
358         if (workArea != null)
359             root.addEqualTo("workArea", workArea);
360         if (department != null)
361             root.addEqualTo("department", department);
362         if (chart != null)
363             root.addEqualTo("chart", chart);
364         if (roleName != null)
365             root.addEqualTo("roleName", roleName);
366         if (principalId != null)
367             root.addEqualTo("principalId", principalId);
368 
369         // Filter for ACTIVE = 'N'
370         Criteria activeFilter = new Criteria();
371         activeFilter.addEqualTo("active", false);
372         root.addAndCriteria(activeFilter);
373 
374         Query query = QueryFactory.newQuery(TkRole.class, root);
375         Collection c = this.getPersistenceBrokerTemplate().getCollectionByQuery(query);
376 
377         if (c != null) {
378             roles.addAll(c);
379         }
380 
381         return roles;
382     }
383 
384     @Override
385     public void saveOrUpdateRole(TkRole role) {
386         KRADServiceLocator.getBusinessObjectService().save(role);
387     }
388 
389     @Override
390     public void saveOrUpdateRoles(List<TkRole> roles) {
391         if (roles != null) {
392             for (TkRole role : roles) {
393                 saveOrUpdateRole(role);
394             }
395         }
396     }
397 
398     @Override
399     public TkRole getRole(String tkRoleId) {
400         Criteria currentRecordCriteria = new Criteria();
401         currentRecordCriteria.addEqualTo("hrRolesId", tkRoleId);
402 
403         return (TkRole) this.getPersistenceBrokerTemplate().getObjectByQuery(QueryFactory.newQuery(TkRole.class, currentRecordCriteria));
404     }
405 
406     @Override
407     public TkRole getRolesByPosition(String positionNumber) {
408         Criteria currentRecordCriteria = new Criteria();
409         Criteria effdt = new Criteria();
410         currentRecordCriteria.addEqualTo("positionNumber", positionNumber);
411 
412         effdt.addEqualToField("positionNumber", Criteria.PARENT_QUERY_PREFIX + "positionNumber");
413         effdt.addLessOrEqualThan("effectiveDate", TKUtils.getCurrentDate());
414         ReportQueryByCriteria effdtSubQuery = QueryFactory.newReportQuery(TkRole.class, effdt);
415         effdtSubQuery.setAttributes(new String[]{"max(effdt)"});
416         currentRecordCriteria.addEqualTo("effectiveDate", effdtSubQuery);
417 
418         // Filter for ACTIVE = 'Y'
419         Criteria activeFilter = new Criteria();
420         activeFilter.addEqualTo("active", true);
421         currentRecordCriteria.addAndCriteria(activeFilter);
422 
423 
424         TkRole tkRole = (TkRole) this.getPersistenceBrokerTemplate().getObjectByQuery(QueryFactory.newQuery(TkRole.class, currentRecordCriteria));
425         return tkRole;
426     }
427 
428     @Override
429     public TkRole getInactiveRolesByPosition(String positionNumber) {
430         Criteria currentRecordCriteria = new Criteria();
431         Criteria effdt = new Criteria();
432         currentRecordCriteria.addEqualTo("positionNumber", positionNumber);
433 
434         effdt.addEqualToField("positionNumber", Criteria.PARENT_QUERY_PREFIX + "positionNumber");
435         effdt.addLessOrEqualThan("effectiveDate", TKUtils.getCurrentDate());
436         ReportQueryByCriteria effdtSubQuery = QueryFactory.newReportQuery(TkRole.class, effdt);
437         effdtSubQuery.setAttributes(new String[]{"max(effdt)"});
438         currentRecordCriteria.addEqualTo("effectiveDate", effdtSubQuery);
439 
440         // Filter for ACTIVE = 'N'
441         Criteria activeFilter = new Criteria();
442         activeFilter.addEqualTo("active", false);
443         currentRecordCriteria.addAndCriteria(activeFilter);
444 
445 
446         TkRole tkRole = (TkRole) this.getPersistenceBrokerTemplate().getObjectByQuery(QueryFactory.newQuery(TkRole.class, currentRecordCriteria));
447         return tkRole;
448     }
449 
450 
451     @Override
452     public List<TkRole> getPositionRolesForWorkArea(Long workArea, Date asOfDate) {
453         List<TkRole> roles = new ArrayList<TkRole>();
454 
455         Criteria root = new Criteria();
456         Criteria effdt = new Criteria();
457         Criteria timestamp = new Criteria();
458         ReportQueryByCriteria effdtSubQuery;
459         ReportQueryByCriteria timestampSubQuery;
460 
461         effdt.addEqualToField("roleName", Criteria.PARENT_QUERY_PREFIX + "roleName");
462         effdt.addEqualToField("positionNumber", Criteria.PARENT_QUERY_PREFIX + "positionNumber");
463         effdt.addLessOrEqualThan("effectiveDate", asOfDate);
464 
465         // EFFECTIVE DATE --
466 
467         // Adding criteria to nest an AND that has multiple ORs to select
468         // the correct ID / date combination.
469         Criteria orWrapperEd = new Criteria();
470         Criteria nstWaEd = new Criteria();
471         Criteria nstDptEd = new Criteria();
472         Criteria nstChrEd = new Criteria();
473 
474         // Inner AND to allow for all null chart/dept/work area
475         Criteria nullAndWrapper = new Criteria();
476         nullAndWrapper.addIsNull("department");
477         nullAndWrapper.addIsNull("chart");
478 
479         nstWaEd.addEqualToField("workArea", Criteria.PARENT_QUERY_PREFIX + "workArea"); // OR
480         nstDptEd.addEqualToField("department", Criteria.PARENT_QUERY_PREFIX + "department"); // OR
481         nstChrEd.addEqualToField("chart", Criteria.PARENT_QUERY_PREFIX + "chart"); // OR
482         orWrapperEd.addOrCriteria(nstWaEd);
483         orWrapperEd.addOrCriteria(nstDptEd);
484         orWrapperEd.addOrCriteria(nstChrEd);
485 
486         // Inner AND to allow for all null chart/dept/work area
487         orWrapperEd.addOrCriteria(nullAndWrapper);
488 
489         // Add the inner OR criteria to effective date
490         effdt.addAndCriteria(orWrapperEd);
491 
492         effdtSubQuery = QueryFactory.newReportQuery(TkRole.class, effdt);
493         effdtSubQuery.setAttributes(new String[]{"max(effdt)"});
494 
495 
496         // TIMESTAMP --
497 
498         //Configure the actual "criteria" in the where clause
499         timestamp.addEqualToField("roleName", Criteria.PARENT_QUERY_PREFIX + "roleName");
500         //timestamp.addEqualToField("principalId", Criteria.PARENT_QUERY_PREFIX + "principalId");
501         timestamp.addEqualToField("effectiveDate", Criteria.PARENT_QUERY_PREFIX + "effectiveDate");
502 
503         // Adding criteria to nest an AND that has multiple ORs to select
504         // the correct ID / date combination.
505         orWrapperEd = new Criteria();
506         nstWaEd = new Criteria();
507         nstDptEd = new Criteria();
508         nstChrEd = new Criteria();
509 
510         // Inner AND to allow for all null chart/dept/work area
511         nullAndWrapper = new Criteria();
512         nullAndWrapper.addIsNull("department");
513         nullAndWrapper.addIsNull("chart");
514 
515         nstWaEd.addEqualToField("workArea", Criteria.PARENT_QUERY_PREFIX + "workArea"); // OR
516         nstDptEd.addEqualToField("department", Criteria.PARENT_QUERY_PREFIX + "department"); // OR
517         nstChrEd.addEqualToField("chart", Criteria.PARENT_QUERY_PREFIX + "chart"); // OR
518         orWrapperEd.addOrCriteria(nstWaEd);
519         orWrapperEd.addOrCriteria(nstDptEd);
520         orWrapperEd.addOrCriteria(nstChrEd);
521 
522         // Inner AND to allow for all null chart/dept/work area
523         orWrapperEd.addOrCriteria(nullAndWrapper);
524 
525         // Add the inner OR criteria to effective date
526         timestamp.addAndCriteria(orWrapperEd);
527 
528         timestampSubQuery = QueryFactory.newReportQuery(TkRole.class, timestamp);
529         timestampSubQuery.setAttributes(new String[]{"max(timestamp)"});
530 
531 
532         // Filter by Max(EffDt) / Max(Timestamp)
533         root.addEqualTo("effectiveDate", effdtSubQuery);
534         root.addEqualTo("timestamp", timestampSubQuery);
535 
536         // Optional ROOT criteria added :
537         if (workArea != null)
538             root.addEqualTo("workArea", workArea);
539         root.addEqualTo("roleName", TkConstants.ROLE_TK_APPROVER);
540 
541         // Filter for ACTIVE = 'Y'
542         Criteria activeFilter = new Criteria();
543         activeFilter.addEqualTo("active", true);
544         root.addAndCriteria(activeFilter);
545 
546         Query query = QueryFactory.newQuery(TkRole.class, root);
547         Collection c = this.getPersistenceBrokerTemplate().getCollectionByQuery(query);
548 
549         if (c != null) {
550             roles.addAll(c);
551         }
552 
553         return roles;
554     }
555 }