|
1
8
9
10 package com.quikj.application.communicator.applications.webtalk.model;
11
12 import java.sql.*;
13
14 import java.util.*;
15
16 public class RegUserBean
17 {
18 private Connection connection;
19 private ArrayList results;
20 private String  orderBy;
21 private java.sql.Date startDate;
22 private java.sql.Date endDate;
23
24
25 private String errorMessage;
26
27
28 public RegUserBean(Connection c)
29 {
30 connection = c;
31 }
32
33 public boolean execute()
34 {
35 String temp_table = "temp_reguserreport_tbl";
36
37 String date_constraints = " between ? and ?";
38 String order_by = " order by ?, ?, ?";
39
40 String sessions_query = "create temporary table "
41 + temp_table
42 + " select t1.loginid as loginid, t1.username as name, left(t1.time_stamp,10) as login_date, right(t1.time_stamp,8) as login_time, left(t2.time_stamp,10) as logout_date, right(t2.time_stamp,8) as logout_time from cdr_reg_login_tbl as t1 left join cdr_logout_tbl as t2 on t1.loginid = t2.loginid where t1.loginid is not null and left(t1.time_stamp,10)"
43 + date_constraints
44 + order_by;
45
46 String calls_ans_query = "select count(status), loginid from cdr_session_setup_resp_tbl where status = 233 and left(time_stamp,10)"
47 + date_constraints
48 + " group by loginid";
49
50 String calls_made_query = "select count(*), calling from cdr_session_setup_tbl where left(time_stamp,10)"
51 + date_constraints
52 + " group by calling";
53
54 String no_ans_query = "select count(status), loginid from cdr_session_setup_resp_tbl where status = 431 and left(time_stamp,10)"
55 + date_constraints
56 + " group by loginid";
57
58 String calls_busy_query = "select count(status), loginid from cdr_session_setup_resp_tbl where status = 430 and left(time_stamp,10)"
59 + date_constraints
60 + " group by loginid";
61
62 String cmd = "";
64 try
65 {
66 Statement stmt = connection.createStatement();
67 stmt.executeUpdate("use webtalk");
68
69 cmd = "drop table if exists " + temp_table;
70 stmt.executeUpdate(cmd);
71
72 cmd = sessions_query;
73 PreparedStatement pstmt = connection.prepareStatement(cmd);
74 pstmt.setDate(1, startDate);
75 pstmt.setDate(2, endDate);
76 pstmt.setString(3, orderBy);
77
78 if (orderBy.equals("name") == true)
79 {
80 pstmt.setString(4, "login_date");
81 pstmt.setString(5, "login_time");
82 }
83 else
84 {
85 pstmt.setString(4, "login_time");
86 pstmt.setString(5, "name");
87 }
88
89 int rowcount = pstmt.executeUpdate();
90
91 if (rowcount <= 0)
92 {
93 cmd = "drop table if exists " + temp_table;
94 stmt.executeUpdate(cmd);
95
96 stmt.close();
97 pstmt.close();
98
99 return true; }
101 pstmt.close();
102
103 cmd = "alter table " + temp_table
105 + " add column (calls_made integer, calls_answered integer, calls_no_answer integer, calls_busy integer)";
106 stmt.executeUpdate(cmd);
107
108 cmd = calls_ans_query;
109 pstmt = connection.prepareStatement(cmd);
110 pstmt.setDate(1, startDate);
111 pstmt.setDate(2, endDate);
112 ResultSet temp = pstmt.executeQuery();
113
114 cmd = "update " + temp_table + " set calls_answered = ? where loginid = ?";
115 PreparedStatement pstmt2 = connection.prepareStatement(cmd);
116 while (temp.next())
117 {
118 pstmt2.setInt(1, temp.getInt(1));
119 pstmt2.setString(2, temp.getString(2));
120 pstmt2.executeUpdate();
121 }
122 temp.close();
123 pstmt.close();
124 pstmt2.close();
125
126 cmd = calls_made_query;
127 pstmt = connection.prepareStatement(cmd);
128 pstmt.setDate(1, startDate);
129 pstmt.setDate(2, endDate);
130 temp = pstmt.executeQuery();
131
132 cmd = "update " + temp_table + " set calls_made = ? where loginid = ?";
133 pstmt2 = connection.prepareStatement(cmd);
134 while (temp.next())
135 {
136 pstmt2.setInt(1, temp.getInt(1));
137 pstmt2.setString(2, temp.getString(2));
138 pstmt2.executeUpdate();
139 }
140 temp.close();
141 pstmt.close();
142 pstmt2.close();
143
144 cmd = no_ans_query;
145 pstmt = connection.prepareStatement(cmd);
146 pstmt.setDate(1, startDate);
147 pstmt.setDate(2, endDate);
148 temp = pstmt.executeQuery();
149
150 cmd = "update " + temp_table + " set calls_no_answer = ? where loginid = ?";
151 pstmt2 = connection.prepareStatement(cmd);
152 while (temp.next())
153 {
154 pstmt2.setInt(1, temp.getInt(1));
155 pstmt2.setString(2, temp.getString(2));
156 pstmt2.executeUpdate();
157 }
158 temp.close();
159 pstmt.close();
160 pstmt2.close();
161
162 cmd = calls_busy_query;
163 pstmt = connection.prepareStatement(cmd);
164 pstmt.setDate(1, startDate);
165 pstmt.setDate(2, endDate);
166 temp = pstmt.executeQuery();
167
168 cmd = "update " + temp_table + " set calls_busy = ? where loginid = ?";
169 pstmt2 = connection.prepareStatement(cmd);
170 while (temp.next())
171 {
172 pstmt2.setInt(1, temp.getInt(1));
173 pstmt2.setString(2, temp.getString(2));
174 pstmt2.executeUpdate();
175 }
176 temp.close();
177 pstmt.close();
178 pstmt2.close();
179
180 results = new ArrayList();
182
183 cmd = "select * from " + temp_table;
184 temp = stmt.executeQuery(cmd);
185
186 while (temp.next())
187 {
188 RegUserResultBean reg_user_result = new RegUserResultBean();
189
190 reg_user_result.setUsername(temp.getString(2));
191 reg_user_result.setLoginDate(temp.getDate(3).toString());
192 reg_user_result.setLoginTime(temp.getTime(4).toString());
193
194 if (temp.getDate(5) == null)
195 {
196 reg_user_result.setLogoutDate("Still logged in ");
197 reg_user_result.setLogoutTime("");
198 }
199 else
200 {
201 reg_user_result.setLogoutDate(temp.getDate(5).toString());
202 reg_user_result.setLogoutTime(temp.getTime(6).toString());
203 }
204
205 reg_user_result.setCallsMade(temp.getInt(7));
206 reg_user_result.setCallsAnswered(temp.getInt(8));
207 reg_user_result.setCallsNoAnswer(temp.getInt(9));
208 reg_user_result.setCallsBusy(temp.getInt(10));
209
210 results.add(reg_user_result);
211 }
212 temp.close();
213
214 cmd = "drop table if exists " + temp_table;
216 stmt.executeUpdate(cmd);
217
218 stmt.close();
219
220 return true;
221
222 }
223 catch (SQLException ex)
224 {
225 errorMessage = "SQLException: " + ex.getMessage() + ", SQL command: " + cmd;
226 return false;
227 }
228 }
229
230 public ArrayList getResults()
231 {
232 return results;
233 }
234
235
236
240 public java.lang.String getOrderBy()
241 {
242 return orderBy;
243 }
244
245
249 public void setOrderBy(java.lang.String order)
250 {
251 this.orderBy = order;
252 }
253
254
258 public java.sql.Date getStartDate()
259 {
260 return startDate;
261 }
262
263
267 public void setStartDate(java.sql.Date startDate)
268 {
269
270 this.startDate = startDate;
271 }
272
273
277 public java.sql.Date getEndDate()
278 {
279 return endDate;
280 }
281
282
286 public void setEndDate(java.sql.Date endDate)
287 {
288 this.endDate = endDate;
289
290 }
291
292
296 public String getErrorMessage()
297 {
298 return this.errorMessage;
299 }
300
301
302
303 }
304 |
Popular Tags |
Java API By Example, From Geeks To Geeks.