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.earncodesec.dao;
17  
18  import org.apache.commons.lang.StringUtils;
19  import org.apache.log4j.Logger;
20  import org.apache.ojb.broker.query.Criteria;
21  import org.apache.ojb.broker.query.Query;
22  import org.apache.ojb.broker.query.QueryFactory;
23  import org.apache.ojb.broker.query.ReportQueryByCriteria;
24  import org.kuali.hr.earncodesec.EarnCodeSecurity;
25  import org.kuali.hr.time.util.TKUtils;
26  import org.kuali.rice.core.framework.persistence.ojb.dao.PlatformAwareDaoBaseOjb;
27  
28  import java.util.*;
29  
30  public class EarnCodeSecurityDaoSpringOjbImpl extends PlatformAwareDaoBaseOjb implements EarnCodeSecurityDao {
31  
32  	@SuppressWarnings("unused")
33  	private static final Logger LOG = Logger.getLogger(EarnCodeSecurityDaoSpringOjbImpl.class);
34  
35  	public void saveOrUpdate(EarnCodeSecurity earnCodeSec) {
36  		this.getPersistenceBrokerTemplate().store(earnCodeSec);
37  	}
38  
39  	public void saveOrUpdate(List<EarnCodeSecurity> ernCdSecList) {
40  		if (ernCdSecList != null) {
41  			for (EarnCodeSecurity ernCdSec : ernCdSecList) {
42  				this.getPersistenceBrokerTemplate().store(ernCdSec);
43  			}
44  		}
45  	}
46  
47  	@SuppressWarnings({ "unchecked", "deprecation" })
48  	@Override
49  	public List<EarnCodeSecurity> getEarnCodeSecurities(String department, String hrSalGroup, String location, Date asOfDate) {
50  		List<EarnCodeSecurity> decs = new LinkedList<EarnCodeSecurity>();
51  
52  		Criteria root = new Criteria();
53  		Criteria effdt = new Criteria();
54  		Criteria timestamp = new Criteria();
55  		
56  		Criteria deptCrit = new Criteria();
57  		Criteria salGroupCrit = new Criteria();
58  		Criteria locationCrit = new Criteria();
59  		
60  		deptCrit.addEqualTo("dept", "%");
61  		salGroupCrit.addEqualTo("hrSalGroup", "%");
62  		locationCrit.addEqualTo("location", "%");
63  		
64  		Criteria deptCrit2 = new Criteria();
65  		deptCrit2.addEqualTo("dept", department);
66  		deptCrit2.addOrCriteria(deptCrit);
67  		root.addAndCriteria(deptCrit2);
68  		
69  		Criteria salGroupCrit2 = new Criteria();
70  		salGroupCrit2.addEqualTo("hrSalGroup", hrSalGroup);
71  		salGroupCrit2.addOrCriteria(salGroupCrit);
72  		root.addAndCriteria(salGroupCrit2);
73  		
74  		Criteria locationCrit2 = new Criteria();
75  		if ( !location.trim().isEmpty() ){
76  			locationCrit2.addEqualTo("location", location);
77  			locationCrit2.addOrCriteria(locationCrit);
78  			root.addAndCriteria(locationCrit2);
79  		}
80  		
81  		Criteria activeFilter = new Criteria(); // Inner Join For Activity
82  		activeFilter.addEqualTo("active", true);
83  		root.addAndCriteria(activeFilter);
84  		
85  		// OJB's awesome sub query setup part 1
86  		effdt.addEqualToField("dept", Criteria.PARENT_QUERY_PREFIX + "dept");
87  		effdt.addEqualToField("hrSalGroup", Criteria.PARENT_QUERY_PREFIX + "hrSalGroup");
88  		effdt.addEqualToField("earnCode", Criteria.PARENT_QUERY_PREFIX + "earnCode");
89  		effdt.addLessOrEqualThan("effectiveDate", asOfDate);
90  		
91  		if ( !location.trim().isEmpty() ){
92  			effdt.addAndCriteria(locationCrit2);
93  			effdt.addEqualToField("location", Criteria.PARENT_QUERY_PREFIX + "location");
94  		}
95  		
96  		// KPME-856, commented out the following line, when geting max(effdt) for each earnCode, do not need to limit to active entries.
97  		//effdt.addEqualTo("active", true);
98  		ReportQueryByCriteria effdtSubQuery = QueryFactory.newReportQuery(EarnCodeSecurity.class, effdt);
99  		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 }