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.sql.Connection;
19 import java.sql.PreparedStatement;
20 import java.sql.ResultSet;
21 import java.sql.SQLException;
22 import java.sql.Timestamp;
23 import java.util.ArrayList;
24 import java.util.List;
25
26 import org.apache.commons.lang.StringUtils;
27 import org.apache.commons.logging.LogFactory;
28 import org.apache.commons.logging.Log;
29 import org.joda.time.DateTime;
30 import org.joda.time.LocalDate;
31 import org.kuali.kpme.core.block.CalendarBlock;
32 import org.kuali.rice.core.framework.persistence.jdbc.dao.PlatformAwareDaoBaseJdbc;
33 import org.kuali.rice.core.framework.persistence.jdbc.sql.Criteria;
34 import org.kuali.rice.core.framework.persistence.jdbc.sql.SqlBuilder;
35 import org.springframework.jdbc.core.PreparedStatementCreator;
36 import org.springframework.jdbc.core.RowMapper;
37
38 public class CalendarBlockDaoJdbcImpl extends PlatformAwareDaoBaseJdbc implements CalendarBlockDao {
39
40 private static final Log LOG = LogFactory.getLog(CalendarBlockDaoJdbcImpl.class);
41
42 @Override
43 public List<CalendarBlock> getAllCalendarBlocks() {
44 List<CalendarBlock> calendarBlocks = new ArrayList<CalendarBlock>();
45 PreparedStatementCreator psc = new PreparedStatementCreator(){
46
47 @Override
48 public PreparedStatement createPreparedStatement(Connection conn)
49 throws SQLException {
50 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, work_area, earn_code, " +
51 "'N' as lunch_deleted, null as overtime_pref, null as hours, leave_amount as amount " +
52 "FROM lm_leave_block_t " +
53 "UNION " +
54 "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, work_area, earn_code, " +
55 "lunch_deleted, ovt_pref as overtime_pref, hours, amount " +
56 "FROM tk_time_block_t;";
57
58 return conn.prepareStatement(query);
59 }
60
61 };
62 calendarBlocks = this.getJdbcTemplate().query(psc, new CalendarBlockRowMapper());
63 return calendarBlocks;
64 }
65
66 @Override
67 public List<CalendarBlock> getActiveCalendarBlocksForDate(LocalDate asOfDate) {
68 PreparedStatementCreator psc = new PreparedStatementCreator(){
69
70
71
72 @Override
73 public PreparedStatement createPreparedStatement(Connection conn)
74 throws SQLException {
75 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, work_area, earn_code " +
76 "FROM tk_time_block_t";
77
78 return conn.prepareStatement(query);
79 }
80
81 };
82
83 List<CalendarBlock> calendarBlocks = this.getJdbcTemplate().query(psc, new CalendarBlockRowMapper());
84 return calendarBlocks;
85 }
86
87 private class CalendarBlockRowMapper implements RowMapper<CalendarBlock> {
88
89 @Override
90 public CalendarBlock mapRow(ResultSet rs, int rowNum) throws SQLException {
91 CalendarBlock cBlock = new CalendarBlock();
92 cBlock.setBeginTimestamp(rs.getTimestamp("BEGIN_TS"));
93 cBlock.setEndTimestamp(rs.getTimestamp("END_TS"));
94 cBlock.setConcreteBlockId(rs.getString("C_BLOCK_ID"));
95 cBlock.setConcreteBlockType(rs.getString("C_BLOCK_TYPE"));
96 cBlock.setDocumentId(rs.getString("DOCUMENT_ID"));
97 cBlock.setEarnCode(rs.getString("EARN_CODE"));
98 cBlock.setJobNumber(rs.getLong("JOB_NUMBER"));
99 cBlock.setObjectId(rs.getString("OBJ_ID"));
100 cBlock.setVersionNumber(rs.getLong("VER_NBR"));
101 cBlock.setPrincipalId(rs.getString("PRINCIPAL_ID"));
102 cBlock.setUserPrincipalId(rs.getString("USER_PRINCIPAL_ID"));
103 cBlock.setHours(rs.getBigDecimal("HOURS"));
104 cBlock.setAmount(rs.getBigDecimal("AMOUNT"));
105 cBlock.setOvertimePref(rs.getString("OVERTIME_PREF"));
106 cBlock.setLunchDeleted(rs.getBoolean("LUNCH_DELETED"));
107 cBlock.setTask(rs.getLong("TASK"));
108 cBlock.setWorkArea(rs.getLong("WORK_AREA"));
109 cBlock.setTimestamp(rs.getTimestamp("TIMESTAMP"));
110 return cBlock;
111 }
112
113 }
114
115 @Override
116 public DateTime getLatestEndTimestampForEarnCode(String earnCode, String calendarBlockType) {
117
118 PreparedStatementCreator timeBlockPSC = new PreparedStatementCreator() {
119
120 @Override
121 public PreparedStatement createPreparedStatement(Connection conn)
122 throws SQLException {
123 StringBuffer sql = new StringBuffer();
124 sql.append("SELECT max(end_ts) ");
125 sql.append("FROM tk_time_block_t ");
126 sql.append("WHERE earn_code = ?");
127
128 String query = sql.toString();
129
130 return conn.prepareStatement(query);
131 }
132 };
133
134 PreparedStatementCreator leaveBlockPSC = new PreparedStatementCreator() {
135
136 @Override
137 public PreparedStatement createPreparedStatement(Connection conn)
138 throws SQLException {
139 StringBuffer sql = new StringBuffer();
140 sql.append("SELECT max(end_ts) ");
141 sql.append("FROM lm_leave_block_t ");
142 sql.append("WHERE earn_code = ?");
143
144 String query = sql.toString();
145
146 return conn.prepareStatement(query);
147 }
148 };
149 try {
150 PreparedStatement statement = null;
151 if (StringUtils.equals(calendarBlockType, "Time")) {
152 statement = timeBlockPSC.createPreparedStatement(this.getDataSource().getConnection());
153 } else if (StringUtils.equals(calendarBlockType, "Leave")) {
154 statement = leaveBlockPSC.createPreparedStatement(this.getDataSource().getConnection());
155 }
156 else {
157 throw new IllegalArgumentException("calendarBlockType must be one of 'Time' or 'Leave'");
158 }
159 if(statement != null) {
160 statement.setString(1, earnCode);
161 }
162
163 ResultSet rs = statement.executeQuery();
164 if(rs != null) {
165 boolean empty = !rs.first();
166 Timestamp maxDate = rs.getTimestamp("max(end_ts)");
167 if(maxDate == null) {
168 return null;
169 }
170 else {
171 return new DateTime(maxDate.getTime());
172 }
173 }
174 } catch (SQLException e) {
175 LOG.warn("error creating or executing sql statement");
176 throw new RuntimeException();
177 }
178 return null;
179 }
180
181 }