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.earncodesec.dao;
017
018 import org.apache.commons.lang.StringUtils;
019 import org.apache.log4j.Logger;
020 import org.apache.ojb.broker.query.Criteria;
021 import org.apache.ojb.broker.query.Query;
022 import org.apache.ojb.broker.query.QueryFactory;
023 import org.apache.ojb.broker.query.ReportQueryByCriteria;
024 import org.kuali.hr.earncodesec.EarnCodeSecurity;
025 import org.kuali.hr.time.util.TKUtils;
026 import org.kuali.rice.core.framework.persistence.ojb.dao.PlatformAwareDaoBaseOjb;
027
028 import java.util.*;
029
030 public class EarnCodeSecurityDaoSpringOjbImpl extends PlatformAwareDaoBaseOjb implements EarnCodeSecurityDao {
031
032 @SuppressWarnings("unused")
033 private static final Logger LOG = Logger.getLogger(EarnCodeSecurityDaoSpringOjbImpl.class);
034
035 public void saveOrUpdate(EarnCodeSecurity earnCodeSec) {
036 this.getPersistenceBrokerTemplate().store(earnCodeSec);
037 }
038
039 public void saveOrUpdate(List<EarnCodeSecurity> ernCdSecList) {
040 if (ernCdSecList != null) {
041 for (EarnCodeSecurity ernCdSec : ernCdSecList) {
042 this.getPersistenceBrokerTemplate().store(ernCdSec);
043 }
044 }
045 }
046
047 @SuppressWarnings({ "unchecked", "deprecation" })
048 @Override
049 public List<EarnCodeSecurity> getEarnCodeSecurities(String department, String hrSalGroup, String location, Date asOfDate) {
050 List<EarnCodeSecurity> decs = new LinkedList<EarnCodeSecurity>();
051
052 Criteria root = new Criteria();
053 Criteria effdt = new Criteria();
054 Criteria timestamp = new Criteria();
055
056 Criteria deptCrit = new Criteria();
057 Criteria salGroupCrit = new Criteria();
058 Criteria locationCrit = new Criteria();
059
060 deptCrit.addEqualTo("dept", "%");
061 salGroupCrit.addEqualTo("hrSalGroup", "%");
062 locationCrit.addEqualTo("location", "%");
063
064 Criteria deptCrit2 = new Criteria();
065 deptCrit2.addEqualTo("dept", department);
066 deptCrit2.addOrCriteria(deptCrit);
067 root.addAndCriteria(deptCrit2);
068
069 Criteria salGroupCrit2 = new Criteria();
070 salGroupCrit2.addEqualTo("hrSalGroup", hrSalGroup);
071 salGroupCrit2.addOrCriteria(salGroupCrit);
072 root.addAndCriteria(salGroupCrit2);
073
074 Criteria locationCrit2 = new Criteria();
075 if ( !location.trim().isEmpty() ){
076 locationCrit2.addEqualTo("location", location);
077 locationCrit2.addOrCriteria(locationCrit);
078 root.addAndCriteria(locationCrit2);
079 }
080
081 Criteria activeFilter = new Criteria(); // Inner Join For Activity
082 activeFilter.addEqualTo("active", true);
083 root.addAndCriteria(activeFilter);
084
085 // OJB's awesome sub query setup part 1
086 effdt.addEqualToField("dept", Criteria.PARENT_QUERY_PREFIX + "dept");
087 effdt.addEqualToField("hrSalGroup", Criteria.PARENT_QUERY_PREFIX + "hrSalGroup");
088 effdt.addEqualToField("earnCode", Criteria.PARENT_QUERY_PREFIX + "earnCode");
089 effdt.addLessOrEqualThan("effectiveDate", asOfDate);
090
091 if ( !location.trim().isEmpty() ){
092 effdt.addAndCriteria(locationCrit2);
093 effdt.addEqualToField("location", Criteria.PARENT_QUERY_PREFIX + "location");
094 }
095
096 // KPME-856, commented out the following line, when geting max(effdt) for each earnCode, do not need to limit to active entries.
097 //effdt.addEqualTo("active", true);
098 ReportQueryByCriteria effdtSubQuery = QueryFactory.newReportQuery(EarnCodeSecurity.class, effdt);
099 effdtSubQuery.setAttributes(new String[] { "max(effdt)" });
100
101 // OJB's awesome sub query setup part 2
102 timestamp.addEqualToField("dept", Criteria.PARENT_QUERY_PREFIX + "dept");
103 timestamp.addEqualToField("hrSalGroup", Criteria.PARENT_QUERY_PREFIX + "hrSalGroup");
104 timestamp.addEqualToField("earnCode", Criteria.PARENT_QUERY_PREFIX + "earnCode");
105 if ( !location.trim().isEmpty() ){
106 timestamp.addEqualToField("location", Criteria.PARENT_QUERY_PREFIX + "location");
107 }
108 timestamp.addEqualTo("active", true);
109 timestamp.addEqualToField("effectiveDate", Criteria.PARENT_QUERY_PREFIX + "effectiveDate");
110 ReportQueryByCriteria timestampSubQuery = QueryFactory.newReportQuery(EarnCodeSecurity.class, timestamp);
111 timestampSubQuery.setAttributes(new String[]{ "max(timestamp)" });
112
113 root.addEqualTo("effectiveDate", effdtSubQuery);
114 root.addEqualTo("timestamp", timestampSubQuery);
115
116 root.addOrderBy("earnCode", true);
117 root.addOrderBy("dept",false);
118 root.addOrderBy("hrSalGroup",false);
119
120
121 Query query = QueryFactory.newQuery(EarnCodeSecurity.class, root);
122
123 Collection c = this.getPersistenceBrokerTemplate().getCollectionByQuery(query);
124
125 if (c != null) {
126 decs.addAll(c);
127 }
128
129 //Now we can have duplicates so remove any that match more than once
130 Set<String> aSet = new HashSet<String>();
131 List<EarnCodeSecurity> aList = new ArrayList<EarnCodeSecurity>();
132 for(EarnCodeSecurity dec : decs){
133 if(!aSet.contains(dec.getEarnCode())){
134 aList.add(dec);
135 aSet.add(dec.getEarnCode());
136 }
137 }
138 return aList;
139 }
140
141 @Override
142 public EarnCodeSecurity getEarnCodeSecurity(String hrEarnCodeSecId) {
143 Criteria crit = new Criteria();
144 crit.addEqualTo("hrEarnCodeSecurityId", hrEarnCodeSecId);
145
146 Query query = QueryFactory.newQuery(EarnCodeSecurity.class, crit);
147 return (EarnCodeSecurity)this.getPersistenceBrokerTemplate().getObjectByQuery(query);
148 }
149
150 public List<EarnCodeSecurity> searchEarnCodeSecurities(String dept, String salGroup, String earnCode, String location,
151 java.sql.Date fromEffdt, java.sql.Date toEffdt, String active, String showHistory) {
152 List<EarnCodeSecurity> results = new ArrayList<EarnCodeSecurity>();
153
154 Criteria crit = new Criteria();
155 Criteria effdtCrit = new Criteria();
156 Criteria timestampCrit = new Criteria();
157
158 if (fromEffdt != null) {
159 crit.addGreaterOrEqualThan("effectiveDate", fromEffdt);
160 }
161
162 if (toEffdt != null) {
163 crit.addLessOrEqualThan("effectiveDate", toEffdt);
164 } else {
165 crit.addLessOrEqualThan("effectiveDate", TKUtils.getCurrentDate());
166 }
167
168 if (StringUtils.isNotEmpty(dept)) {
169 crit.addLike("dept", dept);
170 }
171
172 if (StringUtils.isNotEmpty(salGroup)) {
173 crit.addLike("hrSalGroup", salGroup);
174 }
175
176 if (StringUtils.isNotEmpty(earnCode)) {
177 crit.addLike("earnCode", earnCode);
178 }
179
180 if (StringUtils.isNotEmpty(location)) {
181 crit.addLike("location", location);
182 }
183
184 if (StringUtils.isEmpty(active) && StringUtils.equals(showHistory, "Y")) {
185 effdtCrit.addEqualToField("dept", Criteria.PARENT_QUERY_PREFIX + "dept");
186 effdtCrit.addEqualToField("hrSalGroup", Criteria.PARENT_QUERY_PREFIX + "hrSalGroup");
187 effdtCrit.addEqualToField("earnCode", Criteria.PARENT_QUERY_PREFIX + "earnCode");
188 effdtCrit.addEqualToField("location", Criteria.PARENT_QUERY_PREFIX + "location");
189 ReportQueryByCriteria effdtSubQuery = QueryFactory.newReportQuery(EarnCodeSecurity.class, effdtCrit);
190 effdtSubQuery.setAttributes(new String[]{"max(effdt)"});
191 timestampCrit.addEqualToField("dept", Criteria.PARENT_QUERY_PREFIX + "dept");
192 timestampCrit.addEqualToField("hrSalGroup", Criteria.PARENT_QUERY_PREFIX + "hrSalGroup");
193 timestampCrit.addEqualToField("earnCode", Criteria.PARENT_QUERY_PREFIX + "earnCode");
194 timestampCrit.addEqualToField("location", Criteria.PARENT_QUERY_PREFIX + "location");
195
196 ReportQueryByCriteria timestampSubQuery = QueryFactory.newReportQuery(EarnCodeSecurity.class, timestampCrit);
197 timestampSubQuery.setAttributes(new String[]{"max(timestamp)"});
198
199 crit.addEqualTo("effectiveDate", effdtSubQuery);
200 crit.addEqualTo("timestamp", timestampSubQuery);
201
202 Query query = QueryFactory.newQuery(EarnCodeSecurity.class, crit);
203 Collection c = this.getPersistenceBrokerTemplate().getCollectionByQuery(query);
204 results.addAll(c);
205 } else if (StringUtils.isEmpty(active) && StringUtils.equals(showHistory, "N")) {
206 effdtCrit.addEqualToField("dept", Criteria.PARENT_QUERY_PREFIX + "dept");
207 effdtCrit.addEqualToField("hrSalGroup", Criteria.PARENT_QUERY_PREFIX + "hrSalGroup");
208 effdtCrit.addEqualToField("earnCode", Criteria.PARENT_QUERY_PREFIX + "earnCode");
209 effdtCrit.addEqualToField("location", Criteria.PARENT_QUERY_PREFIX + "location");
210 ReportQueryByCriteria effdtSubQuery = QueryFactory.newReportQuery(EarnCodeSecurity.class, effdtCrit);
211 effdtSubQuery.setAttributes(new String[]{"max(effdt)"});
212
213 timestampCrit.addEqualToField("dept", Criteria.PARENT_QUERY_PREFIX + "dept");
214 timestampCrit.addEqualToField("hrSalGroup", Criteria.PARENT_QUERY_PREFIX + "hrSalGroup");
215 timestampCrit.addEqualToField("earnCode", Criteria.PARENT_QUERY_PREFIX + "earnCode");
216 timestampCrit.addEqualToField("location", Criteria.PARENT_QUERY_PREFIX + "location");
217
218 ReportQueryByCriteria timestampSubQuery = QueryFactory.newReportQuery(EarnCodeSecurity.class, timestampCrit);
219 timestampSubQuery.setAttributes(new String[]{"max(timestamp)"});
220
221 crit.addEqualTo("effectiveDate", effdtSubQuery);
222 crit.addEqualTo("timestamp", timestampSubQuery);
223
224 Query query = QueryFactory.newQuery(EarnCodeSecurity.class, crit);
225 Collection c = this.getPersistenceBrokerTemplate().getCollectionByQuery(query);
226 results.addAll(c);
227 } else if (StringUtils.equals(active, "Y") && StringUtils.equals("N", showHistory)) {
228 effdtCrit.addEqualToField("dept", Criteria.PARENT_QUERY_PREFIX + "dept");
229 effdtCrit.addEqualToField("hrSalGroup", Criteria.PARENT_QUERY_PREFIX + "hrSalGroup");
230 effdtCrit.addEqualToField("earnCode", Criteria.PARENT_QUERY_PREFIX + "earnCode");
231 effdtCrit.addEqualToField("location", Criteria.PARENT_QUERY_PREFIX + "location");
232 ReportQueryByCriteria effdtSubQuery = QueryFactory.newReportQuery(EarnCodeSecurity.class, effdtCrit);
233 effdtSubQuery.setAttributes(new String[]{"max(effdt)"});
234
235 timestampCrit.addEqualToField("dept", Criteria.PARENT_QUERY_PREFIX + "dept");
236 timestampCrit.addEqualToField("hrSalGroup", Criteria.PARENT_QUERY_PREFIX + "hrSalGroup");
237 timestampCrit.addEqualToField("earnCode", Criteria.PARENT_QUERY_PREFIX + "earnCode");
238 timestampCrit.addEqualToField("location", Criteria.PARENT_QUERY_PREFIX + "location");
239
240 ReportQueryByCriteria timestampSubQuery = QueryFactory.newReportQuery(EarnCodeSecurity.class, timestampCrit);
241 timestampSubQuery.setAttributes(new String[]{"max(timestamp)"});
242
243 crit.addEqualTo("effectiveDate", effdtSubQuery);
244 crit.addEqualTo("timestamp", timestampSubQuery);
245
246 Criteria activeFilter = new Criteria(); // Inner Join For Activity
247 activeFilter.addEqualTo("active", true);
248 crit.addAndCriteria(activeFilter);
249
250 Query query = QueryFactory.newQuery(EarnCodeSecurity.class, crit);
251 Collection c = this.getPersistenceBrokerTemplate().getCollectionByQuery(query);
252 results.addAll(c);
253 } //return all active records from the database
254 else if (StringUtils.equals(active, "Y") && StringUtils.equals("Y", showHistory)) {
255 Criteria activeFilter = new Criteria(); // Inner Join For Activity
256 activeFilter.addEqualTo("active", true);
257 crit.addAndCriteria(activeFilter);
258 Query query = QueryFactory.newQuery(EarnCodeSecurity.class, crit);
259 Collection c = this.getPersistenceBrokerTemplate().getCollectionByQuery(query);
260 results.addAll(c);
261 }
262 //return all inactive records in the database
263 else if (StringUtils.equals(active, "N") && StringUtils.equals(showHistory, "Y")) {
264 effdtCrit.addEqualToField("dept", Criteria.PARENT_QUERY_PREFIX + "dept");
265 effdtCrit.addEqualToField("hrSalGroup", Criteria.PARENT_QUERY_PREFIX + "hrSalGroup");
266 effdtCrit.addEqualToField("earnCode", Criteria.PARENT_QUERY_PREFIX + "earnCode");
267 effdtCrit.addEqualToField("location", Criteria.PARENT_QUERY_PREFIX + "location");
268 ReportQueryByCriteria effdtSubQuery = QueryFactory.newReportQuery(EarnCodeSecurity.class, effdtCrit);
269 effdtSubQuery.setAttributes(new String[]{"max(effdt)"});
270
271 timestampCrit.addEqualToField("dept", Criteria.PARENT_QUERY_PREFIX + "dept");
272 timestampCrit.addEqualToField("hrSalGroup", Criteria.PARENT_QUERY_PREFIX + "hrSalGroup");
273 timestampCrit.addEqualToField("earnCode", Criteria.PARENT_QUERY_PREFIX + "earnCode");
274 timestampCrit.addEqualToField("location", Criteria.PARENT_QUERY_PREFIX + "location");
275
276 ReportQueryByCriteria timestampSubQuery = QueryFactory.newReportQuery(EarnCodeSecurity.class, timestampCrit);
277 timestampSubQuery.setAttributes(new String[]{"max(timestamp)"});
278
279 crit.addEqualTo("effectiveDate", effdtSubQuery);
280 crit.addEqualTo("timestamp", timestampSubQuery);
281
282 Criteria activeFilter = new Criteria(); // Inner Join For Activity
283 activeFilter.addEqualTo("active", false);
284 crit.addAndCriteria(activeFilter);
285 Query query = QueryFactory.newQuery(EarnCodeSecurity.class, crit);
286 Collection c = this.getPersistenceBrokerTemplate().getCollectionByQuery(query);
287 results.addAll(c);
288 }
289
290 //return the most effective inactive rows if there are no active rows <= the curr date
291 else if (StringUtils.equals(active, "N") && StringUtils.equals(showHistory, "N")) {
292 effdtCrit.addEqualToField("dept", Criteria.PARENT_QUERY_PREFIX + "dept");
293 effdtCrit.addEqualToField("hrSalGroup", Criteria.PARENT_QUERY_PREFIX + "hrSalGroup");
294 effdtCrit.addEqualToField("earnCode", Criteria.PARENT_QUERY_PREFIX + "earnCode");
295 effdtCrit.addEqualToField("location", Criteria.PARENT_QUERY_PREFIX + "location");
296 ReportQueryByCriteria effdtSubQuery = QueryFactory.newReportQuery(EarnCodeSecurity.class, effdtCrit);
297 effdtSubQuery.setAttributes(new String[]{"max(effdt)"});
298
299 timestampCrit.addEqualToField("dept", Criteria.PARENT_QUERY_PREFIX + "dept");
300 timestampCrit.addEqualToField("hrSalGroup", Criteria.PARENT_QUERY_PREFIX + "hrSalGroup");
301 timestampCrit.addEqualToField("earnCode", Criteria.PARENT_QUERY_PREFIX + "earnCode");
302 timestampCrit.addEqualToField("location", Criteria.PARENT_QUERY_PREFIX + "location");
303
304 ReportQueryByCriteria timestampSubQuery = QueryFactory.newReportQuery(EarnCodeSecurity.class, timestampCrit);
305 timestampSubQuery.setAttributes(new String[]{"max(timestamp)"});
306
307 Criteria activeFilter = new Criteria(); // Inner Join For Activity
308 activeFilter.addEqualTo("active", false);
309 crit.addAndCriteria(activeFilter);
310 Query query = QueryFactory.newQuery(EarnCodeSecurity.class, crit);
311 Collection c = this.getPersistenceBrokerTemplate().getCollectionByQuery(query);
312 results.addAll(c);
313 }
314
315 return results;
316
317 }
318
319 @Override
320 public int getEarnCodeSecurityCount(String dept, String salGroup, String earnCode, String employee, String approver, String location,
321 String active, java.sql.Date effdt,String hrDeptEarnCodeId) {
322 Criteria crit = new Criteria();
323 crit.addEqualTo("dept", dept);
324 crit.addEqualTo("hrSalGroup", salGroup);
325 crit.addEqualTo("earnCode", earnCode);
326 crit.addEqualTo("employee", employee);
327 crit.addEqualTo("approver", approver);
328 crit.addEqualTo("location", location);
329 crit.addEqualTo("active", active);
330 crit.addEqualTo("effectiveDate", effdt);
331 if(hrDeptEarnCodeId != null) {
332 crit.addEqualTo("hrEarnCodeSecurityId", hrDeptEarnCodeId);
333 }
334 Query query = QueryFactory.newQuery(EarnCodeSecurity.class, crit);
335 return this.getPersistenceBrokerTemplate().getCount(query);
336 }
337 @Override
338 public int getNewerEarnCodeSecurityCount(String earnCode, Date effdt) {
339 Criteria crit = new Criteria();
340 crit.addEqualTo("earnCode", earnCode);
341 crit.addEqualTo("active", "Y");
342 crit.addGreaterThan("effectiveDate", effdt);
343 Query query = QueryFactory.newQuery(EarnCodeSecurity.class, crit);
344 return this.getPersistenceBrokerTemplate().getCount(query);
345 }
346 }