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.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 @Override
151 @SuppressWarnings("unchecked")
152 public List<EarnCodeSecurity> searchEarnCodeSecurities(String dept, String salGroup, String earnCode, String location, Date fromEffdt, Date toEffdt,
153 String active, String showHistory) {
154
155 List<EarnCodeSecurity> results = new ArrayList<EarnCodeSecurity>();
156
157 Criteria root = new Criteria();
158
159 if (StringUtils.isNotBlank(dept)) {
160 root.addLike("dept", dept);
161 }
162
163 if (StringUtils.isNotBlank(salGroup)) {
164 root.addLike("hrSalGroup", salGroup);
165 }
166
167 if (StringUtils.isNotBlank(earnCode)) {
168 root.addLike("earnCode", earnCode);
169 }
170
171 if (StringUtils.isNotBlank(location)) {
172 root.addLike("location", location);
173 }
174
175 Criteria effectiveDateFilter = new Criteria();
176 if (fromEffdt != null) {
177 effectiveDateFilter.addGreaterOrEqualThan("effectiveDate", fromEffdt);
178 }
179 if (toEffdt != null) {
180 effectiveDateFilter.addLessOrEqualThan("effectiveDate", toEffdt);
181 }
182 if (fromEffdt == null && toEffdt == null) {
183 effectiveDateFilter.addLessOrEqualThan("effectiveDate", TKUtils.getCurrentDate());
184 }
185 root.addAndCriteria(effectiveDateFilter);
186
187 if (StringUtils.isNotBlank(active)) {
188 Criteria activeFilter = new Criteria();
189 if (StringUtils.equals(active, "Y")) {
190 activeFilter.addEqualTo("active", true);
191 } else if (StringUtils.equals(active, "N")) {
192 activeFilter.addEqualTo("active", false);
193 }
194 root.addAndCriteria(activeFilter);
195 }
196
197 if (StringUtils.equals(showHistory, "N")) {
198 Criteria effdt = new Criteria();
199 effdt.addEqualToField("dept", Criteria.PARENT_QUERY_PREFIX + "dept");
200 effdt.addEqualToField("hrSalGroup", Criteria.PARENT_QUERY_PREFIX + "hrSalGroup");
201 effdt.addEqualToField("earnCode", Criteria.PARENT_QUERY_PREFIX + "earnCode");
202 effdt.addEqualToField("location", Criteria.PARENT_QUERY_PREFIX + "location");
203 effdt.addAndCriteria(effectiveDateFilter);
204 ReportQueryByCriteria effdtSubQuery = QueryFactory.newReportQuery(EarnCodeSecurity.class, effdt);
205 effdtSubQuery.setAttributes(new String[]{"max(effectiveDate)"});
206 root.addEqualTo("effectiveDate", effdtSubQuery);
207
208 Criteria timestamp = new Criteria();
209 timestamp.addEqualToField("dept", Criteria.PARENT_QUERY_PREFIX + "dept");
210 timestamp.addEqualToField("hrSalGroup", Criteria.PARENT_QUERY_PREFIX + "hrSalGroup");
211 timestamp.addEqualToField("earnCode", Criteria.PARENT_QUERY_PREFIX + "earnCode");
212 timestamp.addEqualToField("location", Criteria.PARENT_QUERY_PREFIX + "location");
213 timestamp.addAndCriteria(effectiveDateFilter);
214 ReportQueryByCriteria timestampSubQuery = QueryFactory.newReportQuery(EarnCodeSecurity.class, timestamp);
215 timestampSubQuery.setAttributes(new String[]{"max(timestamp)"});
216 root.addEqualTo("timestamp", timestampSubQuery);
217 }
218
219 Query query = QueryFactory.newQuery(EarnCodeSecurity.class, root);
220 results.addAll(getPersistenceBrokerTemplate().getCollectionByQuery(query));
221
222 return results;
223 }
224
225 @Override
226 public int getEarnCodeSecurityCount(String dept, String salGroup, String earnCode, String employee, String approver, String location,
227 String active, java.sql.Date effdt,String hrDeptEarnCodeId) {
228 Criteria crit = new Criteria();
229 crit.addEqualTo("dept", dept);
230 crit.addEqualTo("hrSalGroup", salGroup);
231 crit.addEqualTo("earnCode", earnCode);
232 crit.addEqualTo("employee", employee);
233 crit.addEqualTo("approver", approver);
234 crit.addEqualTo("location", location);
235 crit.addEqualTo("active", active);
236 crit.addEqualTo("effectiveDate", effdt);
237 if(hrDeptEarnCodeId != null) {
238 crit.addEqualTo("hrEarnCodeSecurityId", hrDeptEarnCodeId);
239 }
240 Query query = QueryFactory.newQuery(EarnCodeSecurity.class, crit);
241 return this.getPersistenceBrokerTemplate().getCount(query);
242 }
243 @Override
244 public int getNewerEarnCodeSecurityCount(String earnCode, Date effdt) {
245 Criteria crit = new Criteria();
246 crit.addEqualTo("earnCode", earnCode);
247 crit.addEqualTo("active", "Y");
248 crit.addGreaterThan("effectiveDate", effdt);
249 Query query = QueryFactory.newQuery(EarnCodeSecurity.class, crit);
250 return this.getPersistenceBrokerTemplate().getCount(query);
251 }
252 }