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.job.dao;
017
018 import java.util.ArrayList;
019 import java.util.Collection;
020 import java.util.Date;
021 import java.util.LinkedList;
022 import java.util.List;
023
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.job.Job;
031 import org.kuali.hr.time.util.TKUtils;
032 import org.kuali.rice.core.framework.persistence.ojb.dao.PlatformAwareDaoBaseOjb;
033
034 /**
035 * Represents an implementation of {@link JobDao}.
036 */
037 public class JobDaoSpringOjbImpl extends PlatformAwareDaoBaseOjb implements JobDao {
038
039 @SuppressWarnings("unused")
040 private static final Logger LOG = Logger.getLogger(JobDaoSpringOjbImpl.class);
041
042 public void saveOrUpdate(Job job) {
043 this.getPersistenceBrokerTemplate().store(job);
044 }
045
046 public void saveOrUpdate(List<Job> jobList) {
047 if (jobList != null) {
048 for (Job job : jobList) {
049 this.saveOrUpdate(job);
050 }
051 }
052 }
053
054 public Job getPrimaryJob(String principalId, Date payPeriodEndDate) {
055 Criteria root = new Criteria();
056 Criteria effdt = new Criteria();
057 Criteria timestamp = new Criteria();
058
059 effdt.addEqualTo("principalId", principalId);
060 effdt.addEqualToField("jobNumber", Criteria.PARENT_QUERY_PREFIX + "jobNumber");
061 effdt.addLessOrEqualThan("effectiveDate", payPeriodEndDate);
062 ReportQueryByCriteria effdtSubQuery = QueryFactory.newReportQuery(Job.class, effdt);
063 effdtSubQuery.setAttributes(new String[]{"max(effdt)"});
064
065 timestamp.addEqualTo("principalId", principalId);
066 timestamp.addEqualToField("jobNumber", Criteria.PARENT_QUERY_PREFIX + "jobNumber");
067 timestamp.addEqualToField("effectiveDate", Criteria.PARENT_QUERY_PREFIX + "effectiveDate");
068 ReportQueryByCriteria timestampSubQuery = QueryFactory.newReportQuery(Job.class, timestamp);
069 timestampSubQuery.setAttributes(new String[]{"max(timestamp)"});
070
071 root.addEqualTo("principalId", principalId);
072 root.addEqualTo("effectiveDate", effdtSubQuery);
073 root.addEqualTo("timestamp", timestampSubQuery);
074 root.addEqualTo("primaryIndicator", true);
075
076 Criteria activeFilter = new Criteria(); // Inner Join For Activity
077 activeFilter.addEqualTo("active", true);
078 root.addAndCriteria(activeFilter);
079
080 Query query = QueryFactory.newQuery(Job.class, root);
081
082 return (Job) this.getPersistenceBrokerTemplate().getObjectByQuery(query);
083 }
084
085 @SuppressWarnings({"unchecked", "rawtypes"})
086 @Override
087 public List<Job> getJobs(String principalId, Date payPeriodEndDate) {
088 List<Job> jobs = new LinkedList<Job>();
089 Criteria root = new Criteria();
090 Criteria effdt = new Criteria();
091 Criteria timestamp = new Criteria();
092
093 effdt.addEqualTo("principalId", principalId);
094 effdt.addEqualToField("jobNumber", Criteria.PARENT_QUERY_PREFIX + "jobNumber");
095 effdt.addLessOrEqualThan("effectiveDate", payPeriodEndDate);
096 ReportQueryByCriteria effdtSubQuery = QueryFactory.newReportQuery(Job.class, effdt);
097 effdtSubQuery.setAttributes(new String[]{"max(effdt)"});
098
099 timestamp.addEqualTo("principalId", principalId);
100 timestamp.addEqualToField("jobNumber", Criteria.PARENT_QUERY_PREFIX + "jobNumber");
101 timestamp.addEqualToField("effectiveDate", Criteria.PARENT_QUERY_PREFIX + "effectiveDate");
102 ReportQueryByCriteria timestampSubQuery = QueryFactory.newReportQuery(Job.class, timestamp);
103 timestampSubQuery.setAttributes(new String[]{"max(timestamp)"});
104
105 root.addEqualTo("principalId", principalId);
106 root.addEqualTo("effectiveDate", effdtSubQuery);
107 root.addEqualTo("timestamp", timestampSubQuery);
108
109 Criteria activeFilter = new Criteria(); // Inner Join For Activity
110 activeFilter.addEqualTo("active", true);
111 root.addAndCriteria(activeFilter);
112
113
114 Query query = QueryFactory.newQuery(Job.class, root);
115 Collection c = this.getPersistenceBrokerTemplate().getCollectionByQuery(query);
116
117 if (c != null) {
118 jobs.addAll(c);
119 }
120
121
122 return jobs;
123 }
124
125 public Job getJob(String principalId, Long jobNumber, Date asOfDate) {
126 Criteria root = new Criteria();
127 Criteria effdt = new Criteria();
128 Criteria timestamp = new Criteria();
129
130 effdt.addEqualTo("principalId", principalId);
131 effdt.addEqualToField("jobNumber", Criteria.PARENT_QUERY_PREFIX + "jobNumber");
132 effdt.addLessOrEqualThan("effectiveDate", asOfDate);
133 ReportQueryByCriteria effdtSubQuery = QueryFactory.newReportQuery(Job.class, effdt);
134 effdtSubQuery.setAttributes(new String[]{"max(effdt)"});
135
136 timestamp.addEqualTo("principalId", principalId);
137 timestamp.addEqualToField("jobNumber", Criteria.PARENT_QUERY_PREFIX + "jobNumber");
138 timestamp.addEqualToField("effectiveDate", Criteria.PARENT_QUERY_PREFIX + "effectiveDate");
139 ReportQueryByCriteria timestampSubQuery = QueryFactory.newReportQuery(Job.class, timestamp);
140 timestampSubQuery.setAttributes(new String[]{"max(timestamp)"});
141
142 root.addEqualTo("principalId", principalId);
143 root.addEqualTo("jobNumber", jobNumber);
144 root.addEqualTo("effectiveDate", effdtSubQuery);
145 root.addEqualTo("timestamp", timestampSubQuery);
146
147 Criteria activeFilter = new Criteria(); // Inner Join For Activity
148 activeFilter.addEqualTo("active", true);
149 root.addAndCriteria(activeFilter);
150
151 Query query = QueryFactory.newQuery(Job.class, root);
152 Job job = (Job) this.getPersistenceBrokerTemplate().getObjectByQuery(query);
153 return job;
154 }
155
156 @SuppressWarnings("unchecked")
157 public List<Job> getActiveJobsForPosition(String positionNbr, Date asOfDate) {
158 Criteria root = new Criteria();
159 Criteria effdt = new Criteria();
160 Criteria timestamp = new Criteria();
161
162 // OJB's awesome sub query setup part 1
163 effdt.addEqualToField("principalId", Criteria.PARENT_QUERY_PREFIX + "principalId");
164 effdt.addEqualToField("jobNumber", Criteria.PARENT_QUERY_PREFIX + "jobNumber");
165 effdt.addLessOrEqualThan("effectiveDate", asOfDate);
166 ReportQueryByCriteria effdtSubQuery = QueryFactory.newReportQuery(Job.class, effdt);
167 effdtSubQuery.setAttributes(new String[]{"max(effdt)"});
168
169 // OJB's awesome sub query setup part 2
170 timestamp.addEqualToField("principalId", Criteria.PARENT_QUERY_PREFIX + "principalId");
171 timestamp.addEqualToField("jobNumber", Criteria.PARENT_QUERY_PREFIX + "jobNumber");
172 timestamp.addEqualToField("effectiveDate", Criteria.PARENT_QUERY_PREFIX + "effectiveDate");
173 ReportQueryByCriteria timestampSubQuery = QueryFactory.newReportQuery(Job.class, timestamp);
174 timestampSubQuery.setAttributes(new String[]{"max(timestamp)"});
175
176 root.addEqualTo("positionNumber", positionNbr);
177 root.addEqualTo("effectiveDate", effdtSubQuery);
178 root.addEqualTo("timestamp", timestampSubQuery);
179
180 Criteria activeFilter = new Criteria(); // Inner Join For Activity
181 activeFilter.addEqualTo("active", true);
182 root.addAndCriteria(activeFilter);
183
184 Query query = QueryFactory.newQuery(Job.class, root);
185 return (List<Job>) this.getPersistenceBrokerTemplate().getCollectionByQuery(query);
186 }
187
188 @SuppressWarnings("unchecked")
189 public List<Job> getActiveJobsForPayType(String hrPayType, Date asOfDate) {
190 Criteria root = new Criteria();
191 Criteria effdt = new Criteria();
192 Criteria timestamp = new Criteria();
193
194 // OJB's awesome sub query setup part 1
195 effdt.addEqualToField("principalId", Criteria.PARENT_QUERY_PREFIX + "principalId");
196 effdt.addEqualToField("jobNumber", Criteria.PARENT_QUERY_PREFIX + "jobNumber");
197 effdt.addLessOrEqualThan("effectiveDate", asOfDate);
198 ReportQueryByCriteria effdtSubQuery = QueryFactory.newReportQuery(Job.class, effdt);
199 effdtSubQuery.setAttributes(new String[]{"max(effdt)"});
200
201 // OJB's awesome sub query setup part 2
202 timestamp.addEqualToField("principalId", Criteria.PARENT_QUERY_PREFIX + "principalId");
203 timestamp.addEqualToField("jobNumber", Criteria.PARENT_QUERY_PREFIX + "jobNumber");
204 timestamp.addEqualToField("effectiveDate", Criteria.PARENT_QUERY_PREFIX + "effectiveDate");
205 ReportQueryByCriteria timestampSubQuery = QueryFactory.newReportQuery(Job.class, timestamp);
206 timestampSubQuery.setAttributes(new String[]{"max(timestamp)"});
207
208 root.addEqualTo("hrPayType", hrPayType);
209 root.addEqualTo("effectiveDate", effdtSubQuery);
210 root.addEqualTo("timestamp", timestampSubQuery);
211
212 Criteria activeFilter = new Criteria(); // Inner Join For Activity
213 activeFilter.addEqualTo("active", true);
214 root.addAndCriteria(activeFilter);
215
216 Query query = QueryFactory.newQuery(Job.class, root);
217 return (List<Job>) this.getPersistenceBrokerTemplate().getCollectionByQuery(query);
218 }
219
220 @Override
221 public Job getJob(String hrJobId) {
222 Criteria crit = new Criteria();
223 crit.addEqualTo("hrJobId", hrJobId);
224
225 Query query = QueryFactory.newQuery(Job.class, crit);
226 return (Job) this.getPersistenceBrokerTemplate().getObjectByQuery(query);
227 }
228
229 @Override
230 public Job getMaxJob(String principalId) {
231 Criteria root = new Criteria();
232 Criteria crit = new Criteria();
233 crit.addEqualTo("principalId", principalId);
234 ReportQueryByCriteria jobNumberSubQuery = QueryFactory.newReportQuery(Job.class, crit);
235 jobNumberSubQuery.setAttributes(new String[]{"max(jobNumber)"});
236
237 crit.addEqualTo("principalId", principalId);
238 root.addEqualTo("jobNumber", jobNumberSubQuery);
239
240 Query query = QueryFactory.newQuery(Job.class, root);
241 return (Job) this.getPersistenceBrokerTemplate().getObjectByQuery(query);
242 }
243
244 @Override
245 public List<Job> getJobs(String principalId, String jobNumber,
246 String dept, String positionNbr, String payType,
247 java.sql.Date fromEffdt, java.sql.Date toEffdt, String active, String showHistory) {
248
249 List<Job> results = new ArrayList<Job>();
250
251 Criteria crit = new Criteria();
252 Criteria effdtCrit = new Criteria();
253 Criteria timestampCrit = new Criteria();
254
255 if (fromEffdt != null) {
256 crit.addGreaterOrEqualThan("effectiveDate", fromEffdt);
257 }
258
259 if (toEffdt != null) {
260 crit.addLessOrEqualThan("effectiveDate", toEffdt);
261 } else {
262 crit.addLessOrEqualThan("effectiveDate", TKUtils.getCurrentDate());
263 }
264
265 if (StringUtils.isNotEmpty(jobNumber)) {
266 crit.addLike("jobNumber", jobNumber);
267 }
268
269 if (StringUtils.isNotEmpty(principalId)) {
270 crit.addLike("principalId", principalId);
271 }
272
273 if (StringUtils.isNotEmpty(dept)) {
274 crit.addLike("dept", dept);
275 }
276
277 if (StringUtils.isNotEmpty(positionNbr)) {
278 crit.addLike("positionNbr", positionNbr);
279 }
280
281 if (StringUtils.isNotEmpty(payType)) {
282 crit.addLike("payType", payType);
283 }
284
285 if (StringUtils.isEmpty(active) && StringUtils.equals(showHistory, "Y")) {
286 effdtCrit.addEqualToField("principalId", Criteria.PARENT_QUERY_PREFIX + "principalId");
287 effdtCrit.addEqualToField("jobNumber", Criteria.PARENT_QUERY_PREFIX + "jobNumber");
288 ReportQueryByCriteria effdtSubQuery = QueryFactory.newReportQuery(Job.class, effdtCrit);
289 effdtSubQuery.setAttributes(new String[]{"max(effdt)"});
290
291 timestampCrit.addEqualToField("principalId", Criteria.PARENT_QUERY_PREFIX + "principalId");
292 timestampCrit.addEqualToField("jobNumber", Criteria.PARENT_QUERY_PREFIX + "jobNumber");
293 ReportQueryByCriteria timestampSubQuery = QueryFactory.newReportQuery(Job.class, timestampCrit);
294 timestampSubQuery.setAttributes(new String[]{"max(timestamp)"});
295
296 crit.addEqualTo("effectiveDate", effdtSubQuery);
297 crit.addEqualTo("timestamp", timestampSubQuery);
298
299 Query query = QueryFactory.newQuery(Job.class, crit);
300 Collection c = this.getPersistenceBrokerTemplate().getCollectionByQuery(query);
301 results.addAll(c);
302 } else if (StringUtils.isEmpty(active) && StringUtils.equals(showHistory, "N")) {
303 effdtCrit.addEqualToField("principalId", Criteria.PARENT_QUERY_PREFIX + "principalId");
304 effdtCrit.addEqualToField("jobNumber", Criteria.PARENT_QUERY_PREFIX + "jobNumber");
305 ReportQueryByCriteria effdtSubQuery = QueryFactory.newReportQuery(Job.class, effdtCrit);
306 effdtSubQuery.setAttributes(new String[]{"max(effdt)"});
307
308 timestampCrit.addEqualToField("principalId", Criteria.PARENT_QUERY_PREFIX + "principalId");
309 timestampCrit.addEqualToField("jobNumber", Criteria.PARENT_QUERY_PREFIX + "jobNumber");
310 ReportQueryByCriteria timestampSubQuery = QueryFactory.newReportQuery(Job.class, timestampCrit);
311 timestampSubQuery.setAttributes(new String[]{"max(timestamp)"});
312
313 crit.addEqualTo("effectiveDate", effdtSubQuery);
314 crit.addEqualTo("timestamp", timestampSubQuery);
315
316 Query query = QueryFactory.newQuery(Job.class, crit);
317 Collection c = this.getPersistenceBrokerTemplate().getCollectionByQuery(query);
318 results.addAll(c);
319 } else if (StringUtils.equals(active, "Y") && StringUtils.equals("N", showHistory)) {
320 effdtCrit.addEqualToField("principalId", Criteria.PARENT_QUERY_PREFIX + "principalId");
321 effdtCrit.addEqualToField("jobNumber", Criteria.PARENT_QUERY_PREFIX + "jobNumber");
322 ReportQueryByCriteria effdtSubQuery = QueryFactory.newReportQuery(Job.class, effdtCrit);
323 effdtSubQuery.setAttributes(new String[]{"max(effdt)"});
324
325 timestampCrit.addEqualToField("principalId", Criteria.PARENT_QUERY_PREFIX + "principalId");
326 timestampCrit.addEqualToField("jobNumber", Criteria.PARENT_QUERY_PREFIX + "jobNumber");
327 ReportQueryByCriteria timestampSubQuery = QueryFactory.newReportQuery(Job.class, timestampCrit);
328 timestampSubQuery.setAttributes(new String[]{"max(timestamp)"});
329
330 crit.addEqualTo("effectiveDate", effdtSubQuery);
331 crit.addEqualTo("timestamp", timestampSubQuery);
332
333 Criteria activeFilter = new Criteria(); // Inner Join For Activity
334 activeFilter.addEqualTo("active", true);
335 crit.addAndCriteria(activeFilter);
336
337 Query query = QueryFactory.newQuery(Job.class, crit);
338 Collection c = this.getPersistenceBrokerTemplate().getCollectionByQuery(query);
339 results.addAll(c);
340 } //return all active records from the database
341 else if (StringUtils.equals(active, "Y") && StringUtils.equals("Y", showHistory)) {
342 Criteria activeFilter = new Criteria(); // Inner Join For Activity
343 activeFilter.addEqualTo("active", true);
344 crit.addAndCriteria(activeFilter);
345 Query query = QueryFactory.newQuery(Job.class, crit);
346 Collection c = this.getPersistenceBrokerTemplate().getCollectionByQuery(query);
347 results.addAll(c);
348 }
349 //return all inactive records in the database
350 else if (StringUtils.equals(active, "N") && StringUtils.equals(showHistory, "Y")) {
351 effdtCrit.addEqualToField("principalId", Criteria.PARENT_QUERY_PREFIX + "principalId");
352 effdtCrit.addEqualToField("jobNumber", Criteria.PARENT_QUERY_PREFIX + "jobNumber");
353 ReportQueryByCriteria effdtSubQuery = QueryFactory.newReportQuery(Job.class, effdtCrit);
354 effdtSubQuery.setAttributes(new String[]{"max(effdt)"});
355
356 timestampCrit.addEqualToField("principalId", Criteria.PARENT_QUERY_PREFIX + "principalId");
357 timestampCrit.addEqualToField("jobNumber", Criteria.PARENT_QUERY_PREFIX + "jobNumber");
358 ReportQueryByCriteria timestampSubQuery = QueryFactory.newReportQuery(Job.class, timestampCrit);
359 timestampSubQuery.setAttributes(new String[]{"max(timestamp)"});
360
361 crit.addEqualTo("effectiveDate", effdtSubQuery);
362 crit.addEqualTo("timestamp", timestampSubQuery);
363
364 Criteria activeFilter = new Criteria(); // Inner Join For Activity
365 activeFilter.addEqualTo("active", false);
366 crit.addAndCriteria(activeFilter);
367 Query query = QueryFactory.newQuery(Job.class, crit);
368 Collection c = this.getPersistenceBrokerTemplate().getCollectionByQuery(query);
369 results.addAll(c);
370 }
371
372 //return the most effective inactive rows if there are no active rows <= the curr date
373 else if (StringUtils.equals(active, "N") && StringUtils.equals(showHistory, "N")) {
374 effdtCrit.addEqualToField("principalId", Criteria.PARENT_QUERY_PREFIX + "principalId");
375 effdtCrit.addEqualToField("jobNumber", Criteria.PARENT_QUERY_PREFIX + "jobNumber");
376 ReportQueryByCriteria effdtSubQuery = QueryFactory.newReportQuery(Job.class, effdtCrit);
377 effdtSubQuery.setAttributes(new String[]{"max(effdt)"});
378
379 timestampCrit.addEqualToField("principalId", Criteria.PARENT_QUERY_PREFIX + "principalId");
380 timestampCrit.addEqualToField("jobNumber", Criteria.PARENT_QUERY_PREFIX + "jobNumber");
381 ReportQueryByCriteria timestampSubQuery = QueryFactory.newReportQuery(Job.class, timestampCrit);
382 timestampSubQuery.setAttributes(new String[]{"max(timestamp)"});
383
384 Criteria activeFilter = new Criteria(); // Inner Join For Activity
385 activeFilter.addEqualTo("active", false);
386 crit.addAndCriteria(activeFilter);
387 Query query = QueryFactory.newQuery(Job.class, crit);
388 Collection c = this.getPersistenceBrokerTemplate().getCollectionByQuery(query);
389 results.addAll(c);
390 }
391
392 return results;
393 }
394
395 @Override
396 public int getJobCount(String principalId, Long jobNumber, String dept) {
397 Criteria crit = new Criteria();
398 crit.addEqualTo("jobNumber", jobNumber);
399 if(principalId != null) {
400 crit.addEqualTo("principalId", principalId);
401 }
402 if(dept != null) {
403 crit.addEqualTo("dept", dept);
404 }
405 Query query = QueryFactory.newQuery(Job.class, crit);
406 return this.getPersistenceBrokerTemplate().getCount(query);
407 }
408 }