View Javadoc

1   /**
2    * Copyright 2004-2013 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.assignment.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.log4j.Logger;
26  import org.apache.ojb.broker.query.Criteria;
27  import org.apache.ojb.broker.query.Query;
28  import org.apache.ojb.broker.query.QueryFactory;
29  import org.apache.ojb.broker.query.ReportQueryByCriteria;
30  import org.kuali.hr.time.assignment.Assignment;
31  import org.kuali.hr.time.service.base.TkServiceLocator;
32  import org.kuali.hr.time.util.TKContext;
33  import org.kuali.hr.time.util.TKUtils;
34  import org.kuali.hr.time.workarea.WorkArea;
35  import org.kuali.rice.core.framework.persistence.ojb.dao.PlatformAwareDaoBaseOjb;
36  
37  public class AssignmentDaoSpringOjbImpl extends PlatformAwareDaoBaseOjb implements AssignmentDao {
38  
39      private static final Logger LOG = Logger.getLogger(AssignmentDaoSpringOjbImpl.class);
40  
41      @Override
42      public void saveOrUpdate(Assignment assignment) {
43          this.getPersistenceBrokerTemplate().store(assignment);
44      }
45  
46      @Override
47      public void saveOrUpdate(List<Assignment> assignments) {
48          if (assignments != null) {
49              for (Assignment assign : assignments) {
50                  this.getPersistenceBrokerTemplate().store(assign);
51              }
52          }
53      }
54  
55      @Override
56      public void delete(Assignment assignment) {
57          if (assignment != null) {
58              LOG.debug("Deleting assignment:" + assignment.getTkAssignmentId());
59              this.getPersistenceBrokerTemplate().delete(assignment);
60          } else {
61              LOG.warn("Attempt to delete null assignment.");
62          }
63      }
64  
65      public Assignment getAssignment(String principalId, Long jobNumber, Long workArea, Long task, Date asOfDate) {
66          Criteria root = new Criteria();
67          Criteria effdt = new Criteria();
68          Criteria timestamp = new Criteria();
69  
70          effdt.addEqualToField("jobNumber", Criteria.PARENT_QUERY_PREFIX + "jobNumber");
71          effdt.addEqualToField("workArea", Criteria.PARENT_QUERY_PREFIX + "workArea");
72          effdt.addEqualToField("task", Criteria.PARENT_QUERY_PREFIX + "task");
73          effdt.addLessOrEqualThan("effectiveDate", asOfDate);
74          effdt.addEqualToField("principalId", Criteria.PARENT_QUERY_PREFIX + "principalId");
75          ReportQueryByCriteria effdtSubQuery = QueryFactory.newReportQuery(Assignment.class, effdt);
76          effdtSubQuery.setAttributes(new String[]{"max(effdt)"});
77  
78          // OJB's awesome sub query setup part 2
79          timestamp.addEqualToField("jobNumber", Criteria.PARENT_QUERY_PREFIX + "jobNumber");
80          timestamp.addEqualToField("workArea", Criteria.PARENT_QUERY_PREFIX + "workArea");
81          timestamp.addEqualToField("task", Criteria.PARENT_QUERY_PREFIX + "task");
82          timestamp.addEqualToField("effectiveDate", Criteria.PARENT_QUERY_PREFIX + "effectiveDate");
83          //timestamp.addEqualTo("active", true);
84          timestamp.addEqualToField("principalId", Criteria.PARENT_QUERY_PREFIX + "principalId");
85          ReportQueryByCriteria timestampSubQuery = QueryFactory.newReportQuery(Assignment.class, timestamp);
86          timestampSubQuery.setAttributes(new String[]{"max(timestamp)"});
87  
88          root.addEqualTo("principalId", principalId);
89          root.addEqualTo("jobNumber", jobNumber);
90          root.addEqualTo("workArea", workArea);
91          root.addEqualTo("task", task);
92          root.addEqualTo("effectiveDate", effdtSubQuery);
93          root.addEqualTo("timestamp", timestampSubQuery);
94          //root.addEqualTo("active", true);
95  
96          Criteria activeFilter = new Criteria(); // Inner Join For Activity
97          activeFilter.addEqualTo("active", true);
98          root.addAndCriteria(activeFilter);
99  
100         Query query = QueryFactory.newQuery(Assignment.class, root);
101         Object o = this.getPersistenceBrokerTemplate().getObjectByQuery(query);
102 
103         return (Assignment) o;
104     }
105 
106 
107     @Override
108     public Assignment getAssignment(Long job, Long workArea, Long task, Date asOfDate) {
109         Criteria root = new Criteria();
110         Criteria effdt = new Criteria();
111         Criteria timestamp = new Criteria();
112 
113         effdt.addEqualToField("jobNumber", Criteria.PARENT_QUERY_PREFIX + "jobNumber");
114         effdt.addEqualToField("workArea", Criteria.PARENT_QUERY_PREFIX + "workArea");
115         effdt.addEqualToField("task", Criteria.PARENT_QUERY_PREFIX + "task");
116         effdt.addLessOrEqualThan("effectiveDate", asOfDate);
117         effdt.addEqualToField("principalId", Criteria.PARENT_QUERY_PREFIX + "principalId");
118         ReportQueryByCriteria effdtSubQuery = QueryFactory.newReportQuery(Assignment.class, effdt);
119         effdtSubQuery.setAttributes(new String[]{"max(effdt)"});
120 
121         // OJB's awesome sub query setup part 2
122         timestamp.addEqualToField("jobNumber", Criteria.PARENT_QUERY_PREFIX + "jobNumber");
123         timestamp.addEqualToField("workArea", Criteria.PARENT_QUERY_PREFIX + "workArea");
124         timestamp.addEqualToField("task", Criteria.PARENT_QUERY_PREFIX + "task");
125         timestamp.addEqualToField("effectiveDate", Criteria.PARENT_QUERY_PREFIX + "effectiveDate");
126         //timestamp.addEqualTo("active", true);
127         timestamp.addEqualToField("principalId", Criteria.PARENT_QUERY_PREFIX + "principalId");
128         ReportQueryByCriteria timestampSubQuery = QueryFactory.newReportQuery(Assignment.class, timestamp);
129         timestampSubQuery.setAttributes(new String[]{"max(timestamp)"});
130 
131         root.addEqualTo("jobNumber", job);
132         root.addEqualTo("workArea", workArea);
133         root.addEqualTo("task", task);
134         root.addEqualTo("effectiveDate", effdtSubQuery);
135         root.addEqualTo("timestamp", timestampSubQuery);
136         root.addEqualTo("principalId", TKContext.getTargetPrincipalId());
137         //root.addEqualTo("active", true);
138 
139         Criteria activeFilter = new Criteria(); // Inner Join For Activity
140         activeFilter.addEqualTo("active", true);
141         root.addAndCriteria(activeFilter);
142 
143         Query query = QueryFactory.newQuery(Assignment.class, root);
144         Object o = this.getPersistenceBrokerTemplate().getObjectByQuery(query);
145 
146         return (Assignment) o;
147     }
148 
149     @SuppressWarnings({"unchecked", "rawtypes"})
150     @Override
151     public List<Assignment> findAssignments(String principalId, Date asOfDate) {
152         List<Assignment> assignments = new ArrayList<Assignment>();
153         Criteria root = new Criteria();
154         Criteria effdt = new Criteria();
155         Criteria timestamp = new Criteria();
156 
157         effdt.addEqualToField("jobNumber", Criteria.PARENT_QUERY_PREFIX + "jobNumber");
158         effdt.addEqualToField("workArea", Criteria.PARENT_QUERY_PREFIX + "workArea");
159         effdt.addEqualToField("task", Criteria.PARENT_QUERY_PREFIX + "task");
160         effdt.addLessOrEqualThan("effectiveDate", asOfDate);
161         //effdt.addEqualTo("active", true);
162         effdt.addEqualTo("principalId", principalId);
163         ReportQueryByCriteria effdtSubQuery = QueryFactory.newReportQuery(Assignment.class, effdt);
164         effdtSubQuery.setAttributes(new String[]{"max(effdt)"});
165 
166         // OJB's awesome sub query setup part 2
167         timestamp.addEqualToField("jobNumber", Criteria.PARENT_QUERY_PREFIX + "jobNumber");
168         timestamp.addEqualToField("workArea", Criteria.PARENT_QUERY_PREFIX + "workArea");
169         timestamp.addEqualToField("task", Criteria.PARENT_QUERY_PREFIX + "task");
170         timestamp.addEqualToField("effectiveDate", Criteria.PARENT_QUERY_PREFIX + "effectiveDate");
171         //timestamp.addEqualTo("active", true);
172         timestamp.addEqualTo("principalId", principalId);
173         ReportQueryByCriteria timestampSubQuery = QueryFactory.newReportQuery(Assignment.class, timestamp);
174         timestampSubQuery.setAttributes(new String[]{"max(timestamp)"});
175 
176         root.addEqualTo("principalId", principalId);
177         root.addEqualTo("effectiveDate", effdtSubQuery);
178         root.addEqualTo("timestamp", timestampSubQuery);
179         //root.addEqualTo("active", true);
180 
181         Criteria activeFilter = new Criteria(); // Inner Join For Activity
182         activeFilter.addEqualTo("active", true);
183         root.addAndCriteria(activeFilter);
184 
185         Query query = QueryFactory.newQuery(Assignment.class, root);
186         Collection c = this.getPersistenceBrokerTemplate().getCollectionByQuery(query);
187 
188         if (c != null) {
189             assignments.addAll(c);
190         }
191 
192         return assignments;
193     }
194 
195     @SuppressWarnings({"unchecked", "rawtypes"})
196     @Override
197     public List<Assignment> findAssignmentsWithinPeriod(String principalId, Date startDate, Date endDate) {
198         List<Assignment> assignments = new ArrayList<Assignment>();
199         Criteria root = new Criteria();
200 
201         root.addGreaterOrEqualThan("effectiveDate", startDate);
202         root.addLessOrEqualThan("effectiveDate", endDate);
203         root.addEqualTo("principalId", principalId);
204         root.addEqualTo("active", true);
205 
206         Query query = QueryFactory.newQuery(Assignment.class, root);
207         Collection c = this.getPersistenceBrokerTemplate().getCollectionByQuery(query);
208 
209         if (c != null) {
210             assignments.addAll(c);
211         }
212 
213         return assignments;
214     }
215 
216     @SuppressWarnings({"rawtypes", "unchecked"})
217     public List<Assignment> getActiveAssignmentsInWorkArea(Long workArea, Date asOfDate) {
218         List<Assignment> assignments = new ArrayList<Assignment>();
219         Criteria root = new Criteria();
220         Criteria effdt = new Criteria();
221         Criteria timestamp = new Criteria();
222 
223         // OJB's awesome sub query setup part 1
224         effdt.addLessOrEqualThan("effectiveDate", asOfDate);
225         effdt.addEqualTo("active", true);
226         effdt.addEqualTo("workArea", workArea);
227         effdt.addEqualToField("task", Criteria.PARENT_QUERY_PREFIX + "task");
228         effdt.addEqualToField("principalId", Criteria.PARENT_QUERY_PREFIX + "principalId");
229         ReportQueryByCriteria effdtSubQuery = QueryFactory.newReportQuery(Assignment.class, effdt);
230         effdtSubQuery.setAttributes(new String[]{"max(effdt)"});
231 
232         // OJB's awesome sub query setup part 2
233         timestamp.addEqualToField("effectiveDate", Criteria.PARENT_QUERY_PREFIX + "effectiveDate");
234         timestamp.addEqualTo("active", true);
235         timestamp.addEqualTo("workArea", workArea);
236         timestamp.addEqualToField("task", Criteria.PARENT_QUERY_PREFIX + "task");
237         timestamp.addEqualToField("principalId", Criteria.PARENT_QUERY_PREFIX + "principalId");
238         ReportQueryByCriteria timestampSubQuery = QueryFactory.newReportQuery(Assignment.class, timestamp);
239         timestampSubQuery.setAttributes(new String[]{"max(timestamp)"});
240 
241         root.addEqualTo("workArea", workArea);
242         root.addEqualTo("effectiveDate", effdtSubQuery);
243         root.addEqualTo("timestamp", timestampSubQuery);
244         root.addEqualTo("active", true);
245 
246         Criteria activeFilter = new Criteria(); // Inner Join For Activity
247         activeFilter.addEqualTo("active", true);
248         root.addAndCriteria(activeFilter);
249 
250         Query query = QueryFactory.newQuery(Assignment.class, root);
251         Collection c = this.getPersistenceBrokerTemplate().getCollectionByQuery(query);
252 
253         if (c != null) {
254             assignments.addAll(c);
255         }
256 
257         return assignments;
258     }
259 
260     public List<Assignment> getActiveAssignments(Date asOfDate) {
261         List<Assignment> assignments = new ArrayList<Assignment>();
262         Criteria root = new Criteria();
263         Criteria effdt = new Criteria();
264         Criteria timestamp = new Criteria();
265 
266         // OJB's awesome sub query setup part 1
267         effdt.addLessOrEqualThan("effectiveDate", asOfDate);
268         effdt.addEqualTo("active", true);
269         ReportQueryByCriteria effdtSubQuery = QueryFactory.newReportQuery(Assignment.class, effdt);
270         effdtSubQuery.setAttributes(new String[]{"max(effdt)"});
271 
272         // OJB's awesome sub query setup part 2
273         timestamp.addEqualToField("effectiveDate", Criteria.PARENT_QUERY_PREFIX + "effectiveDate");
274         timestamp.addEqualTo("active", true);
275         ReportQueryByCriteria timestampSubQuery = QueryFactory.newReportQuery(Assignment.class, timestamp);
276         timestampSubQuery.setAttributes(new String[]{"max(timestamp)"});
277 
278         root.addEqualTo("effectiveDate", effdtSubQuery);
279         root.addEqualTo("timestamp", timestampSubQuery);
280         root.addEqualTo("active", true);
281 
282         Criteria activeFilter = new Criteria(); // Inner Join For Activity
283         activeFilter.addEqualTo("active", true);
284         root.addAndCriteria(activeFilter);
285 
286         Query query = QueryFactory.newQuery(Assignment.class, root);
287         Collection c = this.getPersistenceBrokerTemplate().getCollectionByQuery(query);
288 
289         if (c != null) {
290             assignments.addAll(c);
291         }
292 
293         return assignments;
294     }
295 
296     public Assignment getAssignment(String tkAssignmentId) {
297         Criteria crit = new Criteria();
298         crit.addEqualTo("tkAssignmentId", tkAssignmentId);
299         Query query = QueryFactory.newQuery(Assignment.class, crit);
300         return (Assignment) this.getPersistenceBrokerTemplate().getObjectByQuery(query);
301     }
302 
303     // KPME-1129 Kagata
304     @SuppressWarnings({"rawtypes", "unchecked"})
305     public List<Assignment> getActiveAssignmentsForJob(String principalId, Long jobNumber, Date asOfDate) {
306         List<Assignment> assignments = new ArrayList<Assignment>();
307         Criteria root = new Criteria();
308         Criteria effdt = new Criteria();
309         Criteria timestamp = new Criteria();
310 
311         // subquery for effective date
312         effdt.addLessOrEqualThan("effectiveDate", asOfDate);
313         effdt.addEqualTo("principalId", principalId);
314         effdt.addEqualTo("jobNumber", jobNumber);
315         effdt.addEqualToField("workArea", Criteria.PARENT_QUERY_PREFIX + "workArea");
316         effdt.addEqualToField("task", Criteria.PARENT_QUERY_PREFIX + "task");
317         ReportQueryByCriteria effdtSubQuery = QueryFactory.newReportQuery(Assignment.class, effdt);
318         effdtSubQuery.setAttributes(new String[]{"max(effdt)"});
319 
320         // subquery for timestamp
321         timestamp.addEqualToField("effectiveDate", Criteria.PARENT_QUERY_PREFIX + "effectiveDate");
322         timestamp.addEqualTo("principalId", principalId);
323         timestamp.addEqualTo("jobNumber", jobNumber);
324         timestamp.addEqualToField("workArea", Criteria.PARENT_QUERY_PREFIX + "workArea");
325         timestamp.addEqualToField("task", Criteria.PARENT_QUERY_PREFIX + "task");
326         ReportQueryByCriteria timestampSubQuery = QueryFactory.newReportQuery(Assignment.class, timestamp);
327         timestampSubQuery.setAttributes(new String[]{"max(timestamp)"});
328 
329         root.addEqualTo("principalId", principalId);
330         root.addEqualTo("jobNumber", jobNumber);
331         root.addEqualTo("effectiveDate", effdtSubQuery);
332         root.addEqualTo("timestamp", timestampSubQuery);
333         root.addEqualTo("active", true);
334 
335         Criteria activeFilter = new Criteria(); // Inner Join For Activity
336         activeFilter.addEqualTo("active", true);
337         root.addAndCriteria(activeFilter);
338 
339         Query query = QueryFactory.newQuery(Assignment.class, root);
340         Collection c = this.getPersistenceBrokerTemplate().getCollectionByQuery(query);
341 
342         if (c != null) {
343             assignments.addAll(c);
344         }
345 
346         return assignments;
347     }
348 
349 	@Override
350     @SuppressWarnings("unchecked")
351     public List<Assignment> searchAssignments(Date fromEffdt, Date toEffdt, String principalId, String jobNumber, String dept, String workArea, 
352     										  String active, String showHistory) {
353 
354         List<Assignment> results = new ArrayList<Assignment>();
355         
356         Criteria root = new Criteria();
357 
358         Criteria effectiveDateFilter = new Criteria();
359         if (fromEffdt != null) {
360             effectiveDateFilter.addGreaterOrEqualThan("effectiveDate", fromEffdt);
361         }
362         if (toEffdt != null) {
363             effectiveDateFilter.addLessOrEqualThan("effectiveDate", toEffdt);
364         }
365         if (fromEffdt == null && toEffdt == null) {
366             effectiveDateFilter.addLessOrEqualThan("effectiveDate", TKUtils.getCurrentDate());
367         }
368         root.addAndCriteria(effectiveDateFilter);
369         
370         if (StringUtils.isNotBlank(principalId)) {
371             root.addLike("principalId", principalId);
372         }
373 
374         if (StringUtils.isNotBlank(jobNumber)) {
375             root.addLike("jobNumber", jobNumber);
376         }
377 
378         if (StringUtils.isNotBlank(dept)) {
379             Criteria workAreaCriteria = new Criteria();
380             Date asOfDate = toEffdt != null ? toEffdt : TKUtils.getCurrentDate();
381             Collection<WorkArea> workAreasForDept = TkServiceLocator.getWorkAreaService().getWorkAreas(dept,asOfDate);
382             if (CollectionUtils.isNotEmpty(workAreasForDept)) {
383                 List<Long> longWorkAreas = new ArrayList<Long>();
384                 for(WorkArea cwa : workAreasForDept){
385                     longWorkAreas.add(cwa.getWorkArea());
386                 }
387                 workAreaCriteria.addIn("workArea", longWorkAreas);
388             }
389             root.addAndCriteria(workAreaCriteria);
390         }
391 
392         if (StringUtils.isNotBlank(workArea)) {
393             root.addLike("workArea", workArea);
394         }
395         
396         if (StringUtils.isNotBlank(active)) {
397         	Criteria activeFilter = new Criteria();
398             if (StringUtils.equals(active, "Y")) {
399                 activeFilter.addEqualTo("active", true);
400             } else if (StringUtils.equals(active, "N")) {
401                 activeFilter.addEqualTo("active", false);
402             }
403             root.addAndCriteria(activeFilter);
404         }
405 
406         if (StringUtils.equals(showHistory, "N")) {
407             Criteria effdt = new Criteria();
408             effdt.addEqualToField("principalId", Criteria.PARENT_QUERY_PREFIX + "principalId");
409             effdt.addEqualToField("jobNumber", Criteria.PARENT_QUERY_PREFIX + "jobNumber");
410             effdt.addEqualToField("workArea", Criteria.PARENT_QUERY_PREFIX + "workArea");
411             effdt.addEqualToField("task", Criteria.PARENT_QUERY_PREFIX + "task");
412             effdt.addAndCriteria(effectiveDateFilter);
413             ReportQueryByCriteria effdtSubQuery = QueryFactory.newReportQuery(Assignment.class, effdt);
414             effdtSubQuery.setAttributes(new String[]{"max(effectiveDate)"});
415             root.addEqualTo("effectiveDate", effdtSubQuery);
416             
417             Criteria timestamp = new Criteria();
418             timestamp.addEqualToField("principalId", Criteria.PARENT_QUERY_PREFIX + "principalId");
419             timestamp.addEqualToField("jobNumber", Criteria.PARENT_QUERY_PREFIX + "jobNumber");
420             timestamp.addEqualToField("workArea", Criteria.PARENT_QUERY_PREFIX + "workArea");
421             timestamp.addEqualToField("task", Criteria.PARENT_QUERY_PREFIX + "task");
422             timestamp.addAndCriteria(effectiveDateFilter);
423             ReportQueryByCriteria timestampSubQuery = QueryFactory.newReportQuery(Assignment.class, timestamp);
424             timestampSubQuery.setAttributes(new String[]{"max(timestamp)"});
425             root.addEqualTo("timestamp", timestampSubQuery);
426         }
427         
428         Query query = QueryFactory.newQuery(Assignment.class, root);
429         results.addAll(getPersistenceBrokerTemplate().getCollectionByQuery(query));
430         
431         return results;
432     }
433     
434     @Override
435     public Assignment getMaxTimestampAssignment(String principalId) {
436     	Criteria root = new Criteria();
437         Criteria crit = new Criteria();
438         
439         crit.addEqualTo("principalId", principalId);
440         ReportQueryByCriteria timestampSubQuery = QueryFactory.newReportQuery(Assignment.class, crit);
441         timestampSubQuery.setAttributes(new String[]{"max(timestamp)"});
442 
443         root.addEqualTo("principalId", principalId);
444         root.addEqualTo("timestamp", timestampSubQuery);
445 
446         Query query = QueryFactory.newQuery(Assignment.class, root);
447         return (Assignment) this.getPersistenceBrokerTemplate().getObjectByQuery(query);
448     }
449 
450 
451 }