1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16 package org.kuali.kpme.core.block.dao;
17
18 import java.security.Principal;
19 import java.sql.Connection;
20 import java.sql.PreparedStatement;
21 import java.sql.ResultSet;
22 import java.sql.SQLException;
23 import java.sql.Timestamp;
24 import java.util.ArrayList;
25 import java.util.List;
26
27 import org.apache.commons.lang.StringUtils;
28 import org.apache.commons.logging.LogFactory;
29 import org.apache.commons.logging.Log;
30 import org.joda.time.DateTime;
31 import org.joda.time.LocalDate;
32 import org.kuali.kpme.core.api.assignment.Assignment;
33 import org.kuali.kpme.core.block.CalendarBlock;
34 import org.kuali.rice.core.framework.persistence.jdbc.dao.PlatformAwareDaoBaseJdbc;
35 import org.kuali.rice.core.framework.persistence.jdbc.sql.Criteria;
36 import org.kuali.rice.core.framework.persistence.jdbc.sql.SqlBuilder;
37 import org.springframework.jdbc.core.PreparedStatementCreator;
38 import org.springframework.jdbc.core.RowMapper;
39
40 public class CalendarBlockDaoJdbcImpl extends PlatformAwareDaoBaseJdbc implements CalendarBlockDao {
41
42 private static final Log LOG = LogFactory.getLog(CalendarBlockDaoJdbcImpl.class);
43
44 @Override
45 public List<CalendarBlock> getAllCalendarBlocks() {
46 List<CalendarBlock> calendarBlocks = new ArrayList<CalendarBlock>();
47 PreparedStatementCreator psc = new PreparedStatementCreator(){
48
49 @Override
50 public PreparedStatement createPreparedStatement(Connection conn)
51 throws SQLException {
52 String query = "SELECT begin_ts, end_ts, lm_leave_block_id as c_block_id, 'Leave' as c_block_type, document_id, job_number, obj_id, ver_nbr, principal_id, principal_id_modified as user_principal_id, timestamp, task, grp_key_cd, work_area, earn_code, " +
53 "'N' as lunch_deleted, null as overtime_pref, null as hours, leave_amount as amount " +
54 "FROM lm_leave_block_t " +
55 "UNION " +
56 "SELECT begin_ts, end_ts, tk_time_block_id as c_block_id, 'Time' as c_block_type, document_id, job_number, obj_id, ver_nbr, principal_id, user_principal_id, timestamp, task, grp_key_cd, work_area, earn_code, " +
57 "lunch_deleted, ovt_pref as overtime_pref, hours, amount " +
58 "FROM tk_time_block_t;";
59
60 return conn.prepareStatement(query);
61 }
62
63 };
64 calendarBlocks = this.getJdbcTemplate().query(psc, new CalendarBlockRowMapper());
65 return calendarBlocks;
66 }
67
68 @Override
69 public List<CalendarBlock> getActiveCalendarBlocksForDate(LocalDate asOfDate) {
70 PreparedStatementCreator psc = new PreparedStatementCreator(){
71
72
73
74 @Override
75 public PreparedStatement createPreparedStatement(Connection conn)
76 throws SQLException {
77 String query = "SELECT begin_ts, end_ts, tk_time_block_id as c_block_id, 'Time' as c_block_type, document_id, job_number, obj_id, ver_nbr, principal_id, user_principal_id, timestamp, task, grp_key_cd, work_area, earn_code " +
78 "FROM tk_time_block_t";
79
80 return conn.prepareStatement(query);
81 }
82
83 };
84
85 List<CalendarBlock> calendarBlocks = this.getJdbcTemplate().query(psc, new CalendarBlockRowMapper());
86 return calendarBlocks;
87 }
88
89 private class CalendarBlockRowMapper implements RowMapper<CalendarBlock> {
90
91 @Override
92 public CalendarBlock mapRow(ResultSet rs, int rowNum) throws SQLException {
93 CalendarBlock cBlock = new CalendarBlock();
94 cBlock.setBeginTimestamp(rs.getTimestamp("BEGIN_TS"));
95 cBlock.setEndTimestamp(rs.getTimestamp("END_TS"));
96 cBlock.setConcreteBlockId(rs.getString("C_BLOCK_ID"));
97 cBlock.setConcreteBlockType(rs.getString("C_BLOCK_TYPE"));
98 cBlock.setDocumentId(rs.getString("DOCUMENT_ID"));
99 cBlock.setEarnCode(rs.getString("EARN_CODE"));
100 cBlock.setJobNumber(rs.getLong("JOB_NUMBER"));
101 cBlock.setObjectId(rs.getString("OBJ_ID"));
102 cBlock.setVersionNumber(rs.getLong("VER_NBR"));
103 cBlock.setPrincipalId(rs.getString("PRINCIPAL_ID"));
104 cBlock.setUserPrincipalId(rs.getString("USER_PRINCIPAL_ID"));
105 cBlock.setHours(rs.getBigDecimal("HOURS"));
106 cBlock.setAmount(rs.getBigDecimal("AMOUNT"));
107 cBlock.setOvertimePref(rs.getString("OVERTIME_PREF"));
108 cBlock.setLunchDeleted(rs.getBoolean("LUNCH_DELETED"));
109 cBlock.setTask(rs.getLong("TASK"));
110 cBlock.setGroupKeyCode(rs.getString("GRP_KEY_CD"));
111 cBlock.setWorkArea(rs.getLong("WORK_AREA"));
112 cBlock.setTimestamp(rs.getTimestamp("TIMESTAMP"));
113 return cBlock;
114 }
115
116 }
117
118
119 @Override
120 public DateTime getLatestEndTimestampForAssignment(Assignment assignment,String calendarBlockType) {
121
122 PreparedStatementCreator timeBlockPSC = new PreparedStatementCreator() {
123
124 @Override
125 public PreparedStatement createPreparedStatement(Connection conn)
126 throws SQLException {
127 StringBuffer sql = new StringBuffer();
128 sql.append("SELECT max(end_ts) ");
129 sql.append("FROM tk_time_block_t ");
130 sql.append("WHERE principal_id = ? AND job_number=? AND task=? AND work_area=?");
131
132 String query = sql.toString();
133
134 return conn.prepareStatement(query);
135 }
136 };
137
138 PreparedStatementCreator leaveBlockPSC = new PreparedStatementCreator() {
139
140 @Override
141 public PreparedStatement createPreparedStatement(Connection conn)
142 throws SQLException {
143 StringBuffer sql = new StringBuffer();
144 sql.append("SELECT max(end_ts) ");
145 sql.append("FROM lm_leave_block_t ");
146 sql.append("WHERE principal_id = ? AND job_number=? AND task=? AND work_area=?");
147
148 String query = sql.toString();
149
150 return conn.prepareStatement(query);
151 }
152 };
153
154 try {
155 PreparedStatement statement = null;
156 if (StringUtils.equals(calendarBlockType, "Time")) {
157 statement = timeBlockPSC.createPreparedStatement(this.getDataSource().getConnection());
158 } else if (StringUtils.equals(calendarBlockType, "Leave")) {
159 statement = leaveBlockPSC.createPreparedStatement(this.getDataSource().getConnection());
160 }
161 else {
162 throw new IllegalArgumentException("calendarBlockType must be one of 'Time' or 'Leave'");
163 }
164 if(statement != null) {
165 statement.setString(1, assignment.getPrincipalId());
166 statement.setString(2, assignment.getJobNumber().toString());
167 statement.setString(3, assignment.getTask().toString());
168 statement.setString(4, assignment.getWorkArea().toString());
169 }
170
171 ResultSet rs = statement.executeQuery();
172 if(rs != null) {
173 boolean empty = !rs.first();
174 Timestamp maxDate = rs.getTimestamp("max(end_ts)");
175 if(maxDate == null) {
176 return null;
177 }
178 else {
179 return new DateTime(maxDate.getTime());
180 }
181 }
182 } catch (SQLException e) {
183 LOG.warn("error creating or executing sql statement");
184 throw new RuntimeException();
185 }
186 return null;
187 }
188
189 @Override
190 public DateTime getLatestEndTimestampForEarnCode(String earnCode, String calendarBlockType) {
191
192 PreparedStatementCreator timeBlockPSC = new PreparedStatementCreator() {
193
194 @Override
195 public PreparedStatement createPreparedStatement(Connection conn)
196 throws SQLException {
197 StringBuffer sql = new StringBuffer();
198 sql.append("SELECT max(end_ts) ");
199 sql.append("FROM tk_time_block_t ");
200 sql.append("WHERE earn_code = ?");
201
202 String query = sql.toString();
203
204 return conn.prepareStatement(query);
205 }
206 };
207
208 PreparedStatementCreator leaveBlockPSC = new PreparedStatementCreator() {
209
210 @Override
211 public PreparedStatement createPreparedStatement(Connection conn)
212 throws SQLException {
213 StringBuffer sql = new StringBuffer();
214 sql.append("SELECT max(end_ts) ");
215 sql.append("FROM lm_leave_block_t ");
216 sql.append("WHERE earn_code = ?");
217
218 String query = sql.toString();
219
220 return conn.prepareStatement(query);
221 }
222 };
223 try {
224 PreparedStatement statement = null;
225 if (StringUtils.equals(calendarBlockType, "Time")) {
226 statement = timeBlockPSC.createPreparedStatement(this.getDataSource().getConnection());
227 } else if (StringUtils.equals(calendarBlockType, "Leave")) {
228 statement = leaveBlockPSC.createPreparedStatement(this.getDataSource().getConnection());
229 }
230 else {
231 throw new IllegalArgumentException("calendarBlockType must be one of 'Time' or 'Leave'");
232 }
233 if(statement != null) {
234 statement.setString(1, earnCode);
235 }
236
237 ResultSet rs = statement.executeQuery();
238 if(rs != null) {
239 boolean empty = !rs.first();
240 Timestamp maxDate = rs.getTimestamp("max(end_ts)");
241 if(maxDate == null) {
242 return null;
243 }
244 else {
245 return new DateTime(maxDate.getTime());
246 }
247 }
248 } catch (SQLException e) {
249 LOG.warn("error creating or executing sql statement");
250 throw new RuntimeException();
251 }
252 return null;
253 }
254
255 }