001 /**
002 * Copyright 2004-2012 The Kuali Foundation
003 *
004 * Licensed under the Educational Community License, Version 2.0 (the "License");
005 * you may not use this file except in compliance with the License.
006 * You may obtain a copy of the License at
007 *
008 * http://www.opensource.org/licenses/ecl2.php
009 *
010 * Unless required by applicable law or agreed to in writing, software
011 * distributed under the License is distributed on an "AS IS" BASIS,
012 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
013 * See the License for the specific language governing permissions and
014 * limitations under the License.
015 */
016 package org.kuali.hr.time.assignment.dao;
017
018 import java.sql.Date;
019 import java.util.ArrayList;
020 import java.util.Collection;
021 import java.util.List;
022
023 import org.apache.commons.collections.CollectionUtils;
024 import org.apache.commons.lang.StringUtils;
025 import org.apache.log4j.Logger;
026 import org.apache.ojb.broker.query.Criteria;
027 import org.apache.ojb.broker.query.Query;
028 import org.apache.ojb.broker.query.QueryFactory;
029 import org.apache.ojb.broker.query.ReportQueryByCriteria;
030 import org.kuali.hr.time.assignment.Assignment;
031 import org.kuali.hr.time.service.base.TkServiceLocator;
032 import org.kuali.hr.time.util.TKContext;
033 import org.kuali.hr.time.util.TKUtils;
034 import org.kuali.hr.time.workarea.WorkArea;
035 import org.kuali.rice.core.framework.persistence.ojb.dao.PlatformAwareDaoBaseOjb;
036
037 public class AssignmentDaoSpringOjbImpl extends PlatformAwareDaoBaseOjb implements AssignmentDao {
038
039 private static final Logger LOG = Logger.getLogger(AssignmentDaoSpringOjbImpl.class);
040
041 @Override
042 public void saveOrUpdate(Assignment assignment) {
043 this.getPersistenceBrokerTemplate().store(assignment);
044 }
045
046 @Override
047 public void saveOrUpdate(List<Assignment> assignments) {
048 if (assignments != null) {
049 for (Assignment assign : assignments) {
050 this.getPersistenceBrokerTemplate().store(assign);
051 }
052 }
053 }
054
055 @Override
056 public void delete(Assignment assignment) {
057 if (assignment != null) {
058 LOG.debug("Deleting assignment:" + assignment.getTkAssignmentId());
059 this.getPersistenceBrokerTemplate().delete(assignment);
060 } else {
061 LOG.warn("Attempt to delete null assignment.");
062 }
063 }
064
065 public Assignment getAssignment(String principalId, Long jobNumber, Long workArea, Long task, Date asOfDate) {
066 Criteria root = new Criteria();
067 Criteria effdt = new Criteria();
068 Criteria timestamp = new Criteria();
069
070 effdt.addEqualToField("jobNumber", Criteria.PARENT_QUERY_PREFIX + "jobNumber");
071 effdt.addEqualToField("workArea", Criteria.PARENT_QUERY_PREFIX + "workArea");
072 effdt.addEqualToField("task", Criteria.PARENT_QUERY_PREFIX + "task");
073 effdt.addLessOrEqualThan("effectiveDate", asOfDate);
074 effdt.addEqualToField("principalId", Criteria.PARENT_QUERY_PREFIX + "principalId");
075 ReportQueryByCriteria effdtSubQuery = QueryFactory.newReportQuery(Assignment.class, effdt);
076 effdtSubQuery.setAttributes(new String[]{"max(effdt)"});
077
078 // OJB's awesome sub query setup part 2
079 timestamp.addEqualToField("jobNumber", Criteria.PARENT_QUERY_PREFIX + "jobNumber");
080 timestamp.addEqualToField("workArea", Criteria.PARENT_QUERY_PREFIX + "workArea");
081 timestamp.addEqualToField("task", Criteria.PARENT_QUERY_PREFIX + "task");
082 timestamp.addEqualToField("effectiveDate", Criteria.PARENT_QUERY_PREFIX + "effectiveDate");
083 //timestamp.addEqualTo("active", true);
084 timestamp.addEqualToField("principalId", Criteria.PARENT_QUERY_PREFIX + "principalId");
085 ReportQueryByCriteria timestampSubQuery = QueryFactory.newReportQuery(Assignment.class, timestamp);
086 timestampSubQuery.setAttributes(new String[]{"max(timestamp)"});
087
088 root.addEqualTo("principalId", principalId);
089 root.addEqualTo("jobNumber", jobNumber);
090 root.addEqualTo("workArea", workArea);
091 root.addEqualTo("task", task);
092 root.addEqualTo("effectiveDate", effdtSubQuery);
093 root.addEqualTo("timestamp", timestampSubQuery);
094 //root.addEqualTo("active", true);
095
096 Criteria activeFilter = new Criteria(); // Inner Join For Activity
097 activeFilter.addEqualTo("active", true);
098 root.addAndCriteria(activeFilter);
099
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 }