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.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     public List<Assignment> searchAssignments(Date fromEffdt, Date toEffdt, String principalId, String jobNumber,
351                                            String dept, String workArea, String active, String showHistory) {
352 
353         Criteria crit = new Criteria();
354         Criteria effdt = new Criteria();
355 
356         List<Assignment> results = new ArrayList<Assignment>();
357 
358         if (fromEffdt != null) {
359             crit.addGreaterOrEqualThan("effectiveDate", fromEffdt);
360         }
361 
362         if (toEffdt != null) {
363             crit.addLessOrEqualThan("effectiveDate", toEffdt);
364         } else {
365             crit.addLessOrEqualThan("effectiveDate", TKUtils.getCurrentDate());
366         }
367 
368         if (StringUtils.isNotEmpty(principalId)) {
369             crit.addLike("principalId", principalId);
370         }
371 
372         if (StringUtils.isNotEmpty(jobNumber)) {
373             crit.addLike("jobNumber", jobNumber);
374         }
375 
376         if (StringUtils.isNotEmpty(dept)) {
377             Criteria workAreaCriteria = new Criteria();
378             Date asOfDate = toEffdt != null ? toEffdt : TKUtils.getCurrentDate();
379             Collection<WorkArea> workAreasForDept = TkServiceLocator.getWorkAreaService().getWorkAreas(dept,asOfDate);
380             if (CollectionUtils.isNotEmpty(workAreasForDept)) {
381                 List<Long> longWorkAreas = new ArrayList<Long>();
382                 for(WorkArea cwa : workAreasForDept){
383                     longWorkAreas.add(cwa.getWorkArea());
384                 }
385                 workAreaCriteria.addIn("workArea", longWorkAreas);
386             }
387             crit.addAndCriteria(workAreaCriteria);
388         }
389 
390         if (StringUtils.isNotEmpty(workArea)) {
391             crit.addLike("workArea", workArea);
392         }
393 
394 
395         if (StringUtils.isEmpty(active) && StringUtils.equals(showHistory, "Y")) {
396             Query query = QueryFactory.newQuery(Assignment.class, crit);
397             Collection c = this.getPersistenceBrokerTemplate().getCollectionByQuery(query);
398             results.addAll(c);
399         } else if (StringUtils.isEmpty(active) && StringUtils.equals(showHistory, "N")) {
400             Query query = QueryFactory.newQuery(Assignment.class, crit);
401             Collection c = this.getPersistenceBrokerTemplate().getCollectionByQuery(query);
402             results.addAll(c);
403         } else if (StringUtils.equals(active, "Y") && StringUtils.equals("N", showHistory)) {
404             Criteria activeFilter = new Criteria(); // Inner Join For Activity
405             activeFilter.addEqualTo("active", true);
406             crit.addAndCriteria(activeFilter);
407             Query query = QueryFactory.newQuery(Assignment.class, crit);
408             Collection c = this.getPersistenceBrokerTemplate().getCollectionByQuery(query);
409             results.addAll(c);
410         } //return all active records from the database
411         else if (StringUtils.equals(active, "Y") && StringUtils.equals("Y", showHistory)) {
412             Criteria activeFilter = new Criteria(); // Inner Join For Activity
413             activeFilter.addEqualTo("active", true);
414             crit.addAndCriteria(activeFilter);
415             Query query = QueryFactory.newQuery(Assignment.class, crit);
416             Collection c = this.getPersistenceBrokerTemplate().getCollectionByQuery(query);
417             results.addAll(c);
418         }
419         //return all inactive records in the database
420         else if (StringUtils.equals(active, "N") && StringUtils.equals(showHistory, "Y")) {
421             Criteria activeFilter = new Criteria(); // Inner Join For Activity
422             activeFilter.addEqualTo("active", false);
423             crit.addAndCriteria(activeFilter);
424             Query query = QueryFactory.newQuery(Assignment.class, crit);
425             Collection c = this.getPersistenceBrokerTemplate().getCollectionByQuery(query);
426             results.addAll(c);
427         }
428 
429         //return the most effective inactive rows if there are no active rows <= the curr date
430         else if (StringUtils.equals(active, "N") && StringUtils.equals(showHistory, "N")) {
431             Criteria activeFilter = new Criteria(); // Inner Join For Activity
432             activeFilter.addEqualTo("active", false);
433             crit.addAndCriteria(activeFilter);
434             Query query = QueryFactory.newQuery(Assignment.class, crit);
435             Collection c = this.getPersistenceBrokerTemplate().getCollectionByQuery(query);
436             results.addAll(c);
437 
438         }
439         return results;
440     }
441     
442     @Override
443     public Assignment getMaxTimestampAssignment(String principalId) {
444     	Criteria root = new Criteria();
445         Criteria crit = new Criteria();
446         
447         crit.addEqualTo("principalId", principalId);
448         ReportQueryByCriteria timestampSubQuery = QueryFactory.newReportQuery(Assignment.class, crit);
449         timestampSubQuery.setAttributes(new String[]{"max(timestamp)"});
450 
451         root.addEqualTo("principalId", principalId);
452         root.addEqualTo("timestamp", timestampSubQuery);
453 
454         Query query = QueryFactory.newQuery(Assignment.class, root);
455         return (Assignment) this.getPersistenceBrokerTemplate().getObjectByQuery(query);
456     }
457 
458 
459 }