001 /**
002 * Copyright 2004-2013 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 @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 }