1 package sellwin.db; 2 3 import sellwin.domain.*; 4 import sellwin.utils.*; 5 6 import java.sql.*; 7 import java.util.ArrayList ; 8 9 13 14 19 public class ActivityDB extends DBType implements DBInterface { 20 21 private Connection con; 22 23 private final static String selectByOppQuery = 24 "SELECT " + 25 "pk, subject, " + 26 "type, place, message, start_date, duration, " + 27 "group_alarm, group_alarm_ack, " + 28 "alarm, alarm_ack, modified_by, modified_date " + 29 "FROM activity " + 30 "WHERE opp_pk="; 31 32 private final static String selectAlarmsQuery = 33 "SELECT " + 34 "activity.pk, opportunity.pk, activity.subject, " + 35 "activity.type, activity.place, activity.message, " + 36 "activity.start_date, activity.duration," + 37 "activity.group_alarm, activity.group_alarm_ack, " + 38 "activity.alarm, activity.alarm_ack, " + 39 "activity.modified_by, activity.modified_date " + 40 "FROM activity, opportunity, user_group, user_group_member " + 41 "WHERE opportunity.pk = activity.opp_pk AND " + 42 "opportunity.group_name = user_group.name AND " + 43 "user_group.pk = user_group_member.user_group_pk AND " + 44 "activity.alarm = 'Y' AND activity.alarm_ack = 'N' AND "; 45 46 private final static String selectQuery = 47 "SELECT " + 48 "opp_pk, subject, " + 49 "type, place, message, start_date, duration, " + 50 "group_alarm, group_alarm_ack, " + 51 "alarm, alarm_ack, modified_by, modified_date " + 52 "FROM activity " + 53 "WHERE pk="; 54 55 private final static String updateQuery = 56 "UPDATE activity " + 57 "SET " ; 58 59 private final static String insertQuery = 60 "INSERT INTO activity VALUES ( "; 61 62 private final static String deleteQuery = 63 "DELETE FROM activity WHERE pk ="; 64 65 private final static String deleteOppQuery = 66 "DELETE FROM activity where opp_pk = "; 67 68 69 74 public ActivityDB() { 75 } 76 77 81 public ActivityDB(int dbType) { 82 DB_TYPE=dbType; 83 } 84 85 91 public ActivityDB(Connection con) { 92 this.con = con; 93 } 94 95 100 public Connection getConnection() { 101 return this.con; 102 } 103 104 109 public void setConnection(Connection con) 110 throws SQLException { 111 112 this.con = con; 113 } 114 115 123 public final Object selectRow(Object pk) 124 throws SQLException { 125 126 Activity activity = new Activity(""); 127 long pkValue = ((Long )pk).longValue(); 128 activity.setPK(pkValue); 129 Statement stmt = null; 130 ResultSet rs = null; 131 String query = selectQuery + pkValue; 132 133 try { 134 stmt = con.createStatement(); 135 if (Prefs.DEBUG) LogWrite.write(query); 136 rs = stmt.executeQuery(query); 137 138 int i; 139 while (rs.next()) { 140 i=1; 141 activity.setOppPK(rs.getLong(i)); i++; 142 activity.setSubject(rs.getString(i)); i++; 143 activity.setType(rs.getString(i)); i++; 144 activity.setPlace(rs.getString(i)); i++; 145 activity.setMessage(rs.getString(i)); i++; 146 activity.setStartDate(rs.getDate(i)); i++; 147 activity.setDuration(new Integer (rs.getInt(i))); i++; 148 boolean x; 149 if (rs.getString(i).charAt(0)=='Y') x=true; else x=false; 150 activity.setGroupAlarm(x); i++; 151 if (rs.getString(i).charAt(0)=='Y') x=true; else x=false; 152 activity.setGroupAlarmAck(x); i++; 153 if (rs.getString(i).charAt(0)=='Y') x=true; else x=false; 154 activity.setAlarm(new Boolean (x)); i++; 155 if (rs.getString(i).charAt(0)=='Y') x=true; else x=false; 156 activity.setAlarmAck(x); i++; 157 activity.setModifiedBy(rs.getString(i)); i++; 158 activity.setModifiedDate(rs.getDate(i)); 159 } 160 } catch (SQLException e) { 161 throw e; 162 } finally { 163 try { 164 if (rs != null) rs.close(); 165 } catch (SQLException x) { throw x; } 166 try { 167 if (stmt != null) stmt.close(); 168 } catch (SQLException x) { throw x; } 169 } 170 171 return activity; 172 } 173 174 175 184 public void updateRow(Object obj) 185 throws SQLException { 186 187 Activity activity = (Activity)obj; 188 long pk = activity.getPK(); 189 190 Statement stmt = null; 191 StringBuffer query = new StringBuffer (updateQuery); 192 193 try { 194 stmt = con.createStatement(); 195 196 query.append("subject="); 197 query.append(JDBC.quoteMore(activity.getSubject())); 198 query.append("type="); 199 query.append(JDBC.quoteMore(activity.getType())); 200 query.append("place="); 201 query.append(JDBC.quoteMore(activity.getPlace())); 202 query.append("message="); 203 query.append(JDBC.quoteMore(activity.getMessage())); 204 query.append("start_date="); 205 query.append(JDBC.quoteMore(DateUtils.format(DB_TYPE,activity.getStartDate()))); 206 query.append("duration="); 207 query.append(activity.getDuration()).append(","); 208 String x; 209 if (activity.getGroupAlarm()) x="Y"; else x="N"; 210 query.append("group_alarm="); 211 query.append(JDBC.quoteMore(x)); 212 if (activity.getGroupAlarmAck()) x="Y"; else x="N"; 213 query.append("group_alarm_ack="); 214 query.append(JDBC.quoteMore(x)); 215 if (activity.getAlarm().booleanValue()) x="Y"; else x="N"; 216 query.append("alarm="); 217 query.append(JDBC.quoteMore(x)); 218 if (activity.getAlarmAck()) x="Y"; else x="N"; 219 query.append("alarm_ack="); 220 query.append(JDBC.quoteMore(x)); 221 query.append("modified_by="); 222 query.append(JDBC.quoteMore(activity.getModifiedBy())); 223 if (DB_TYPE == Prefs.MYSQL) 224 query.append("modified_date=CURRENT_DATE "); 225 else 226 query.append("modified_date=SYSDATE "); 227 query.append("WHERE pk=").append(activity.getPK()); 228 229 if (Prefs.DEBUG) LogWrite.write(query.toString()); 230 int updatedRows = stmt.executeUpdate(query.toString()); 231 } catch (SQLException e) { 232 throw e; 233 } finally { 234 try { if (stmt != null) stmt.close(); 235 } catch (SQLException x) { } 236 } 237 } 238 239 249 public long insertRow(Object obj, boolean load) 250 throws SQLException { 251 252 Activity activity = (Activity)obj; 253 if (!load) 254 activity.setPK(DBUtils.generatePK()); 255 256 Statement stmt = null; 257 StringBuffer query = new StringBuffer (insertQuery); 258 259 try { 260 stmt = con.createStatement(); 261 262 query.append(activity.getPK()).append(","); 263 query.append(activity.getOppPK()).append(","); 264 query.append(JDBC.quoteMore(activity.getSubject())); 265 query.append(JDBC.quoteMore(activity.getType())); 266 query.append(JDBC.quoteMore(activity.getPlace())); 267 query.append(JDBC.quoteMore(activity.getMessage())); 268 query.append(JDBC.quoteMore(DateUtils.format(DB_TYPE, activity.getStartDate()))); 269 query.append(activity.getDuration()).append(","); 270 String x; 271 if (activity.getGroupAlarm()) x="Y"; else x="N"; 272 query.append(JDBC.quoteMore(x)); 273 if (activity.getGroupAlarmAck()) x="Y"; else x="N"; 274 query.append(JDBC.quoteMore(x)); 275 if (activity.getAlarm().booleanValue()) x = "Y"; else x="N"; 276 query.append(JDBC.quoteMore(x)); 277 if (activity.getAlarmAck()) x = "Y"; else x="N"; 278 query.append(JDBC.quoteMore(x)); 279 query.append(JDBC.quoteMore(activity.getModifiedBy())); 280 if (DB_TYPE == Prefs.MYSQL) 281 query.append("CURRENT_DATE"); 282 else 283 query.append("SYSDATE"); 284 query.append(")"); 285 286 if (Prefs.DEBUG) LogWrite.write(query.toString()); 287 int rc = stmt.executeUpdate(query.toString()); 288 } catch (SQLException e) { 289 throw e; 290 } finally { 291 try { if (stmt != null) stmt.close(); 292 } catch (SQLException x) { } 293 } 294 295 return activity.getPK(); 296 } 297 298 305 public final void deleteRow(Object obj) 306 throws SQLException { 307 308 long pkValue = ((Long )obj).longValue(); 309 String query = deleteQuery + pkValue; 310 311 Statement stmt = null; 312 try { 313 stmt = con.createStatement(); 314 if (Prefs.DEBUG) LogWrite.write(query); 315 stmt.executeUpdate(query); 316 } catch (SQLException e) { 317 throw e; 318 } finally { 319 try { if (stmt != null) stmt.close(); 320 } catch (SQLException x) { } 321 } 322 } 323 324 331 public final void deleteOpportunityRows(Object obj) 332 throws SQLException { 333 334 long opp_pk = ((Long )obj).longValue(); 335 String query = deleteOppQuery + opp_pk; 336 337 Statement stmt = null; 338 try { 339 stmt = con.createStatement(); 340 if (Prefs.DEBUG) LogWrite.write(query); 341 stmt.executeUpdate(query); 342 } catch (SQLException e) { 343 throw e; 344 } finally { 345 try { if (stmt != null) stmt.close(); 346 } catch (SQLException x) { } 347 } 348 } 349 350 358 public final void selectByOppRows(long opp_pk, ArrayList list) 359 throws SQLException { 360 361 Activity activity = null; 362 Statement stmt = null; 363 ResultSet rs = null; 364 String query = selectByOppQuery + opp_pk; 365 366 try { 367 stmt = con.createStatement(); 368 if (Prefs.DEBUG) LogWrite.write(query); 369 rs = stmt.executeQuery(query); 370 371 int i; 372 while (rs.next()) { 373 i=1; 374 activity = new Activity(); 375 activity.setPK(rs.getLong(i)); i++; 376 activity.setOppPK(opp_pk); 377 activity.setSubject(rs.getString(i)); i++; 378 activity.setType(rs.getString(i)); i++; 379 activity.setPlace(rs.getString(i)); i++; 380 activity.setMessage(rs.getString(i)); i++; 381 activity.setStartDate(rs.getDate(i)); i++; 382 activity.setDuration(new Integer (rs.getInt(i))); i++; 383 boolean x; 384 if (rs.getString(i).charAt(0)=='Y') x=true; else x=false; 385 activity.setGroupAlarm(x); i++; 386 if (rs.getString(i).charAt(0)=='Y') x=true; else x=false; 387 activity.setGroupAlarmAck(x); i++; 388 if (rs.getString(i).charAt(0)=='Y') x=true; else x=false; 389 activity.setAlarm(new Boolean (x)); i++; 390 if (rs.getString(i).charAt(0)=='Y') x=true; else x=false; 391 activity.setAlarmAck(x); i++; 392 activity.setModifiedBy(rs.getString(i)); i++; 393 activity.setModifiedDate(rs.getDate(i)); 394 list.add(activity); 395 } 396 } catch (SQLException e) { 397 throw e; 398 } finally { 399 try { 400 if (rs != null) rs.close(); 401 } catch (SQLException x) { throw x; } 402 try { 403 if (stmt != null) stmt.close(); 404 } catch (SQLException x) { throw x; } 405 } 406 } 407 408 416 public final ArrayList selectActiveAlarms(long user_pk) 417 throws SQLException { 418 419 ArrayList acts = new ArrayList (); 420 Activity activity = null; 421 Statement stmt = null; 422 ResultSet rs = null; 423 StringBuffer query = new StringBuffer (selectAlarmsQuery); 424 425 if (DB_TYPE == Prefs.MYSQL) 426 query.append( "activity.start_date < DATE_ADD(CURRENT_DATE, INTERVAL 1 DAY) AND "); 427 else 428 query.append( "activity.start_date < SYSDATE + 1 AND "); 429 430 query.append("user_group_member.user_pk = "); 431 query.append(user_pk); 432 433 try { 434 stmt = con.createStatement(); 435 if (Prefs.DEBUG) LogWrite.write(query.toString()); 436 rs = stmt.executeQuery(query.toString()); 437 438 int i; 439 while (rs.next()) { 440 i=1; 441 activity = new Activity(); 442 activity.setPK(rs.getLong(i)); i++; 443 activity.setOppPK(rs.getLong(i)); i++; 444 activity.setSubject(rs.getString(i)); i++; 445 activity.setType(rs.getString(i)); i++; 446 activity.setPlace(rs.getString(i)); i++; 447 activity.setMessage(rs.getString(i)); i++; 448 activity.setStartDate(rs.getDate(i)); i++; 449 activity.setDuration(new Integer (rs.getInt(i))); i++; 450 boolean x; 451 if (rs.getString(i).charAt(0)=='Y') x=true; else x=false; 452 activity.setGroupAlarm(x); i++; 453 if (rs.getString(i).charAt(0)=='Y') x=true; else x=false; 454 activity.setGroupAlarmAck(x); i++; 455 if (rs.getString(i).charAt(0)=='Y') x=true; else x=false; 456 activity.setAlarm(new Boolean (x)); i++; 457 if (rs.getString(i).charAt(0)=='Y') x=true; else x=false; 458 activity.setAlarmAck(x); i++; 459 activity.setModifiedBy(rs.getString(i)); i++; 460 activity.setModifiedDate(rs.getDate(i)); 461 acts.add(activity); 462 } 463 } catch (SQLException e) { 464 LogWrite.write(e); 465 throw e; 466 } catch (Exception f) { 467 LogWrite.write(f); 468 } finally { 469 try { 470 if (rs != null) rs.close(); 471 } catch (SQLException x) { throw x; } 472 try { 473 if (stmt != null) stmt.close(); 474 } catch (SQLException x) { throw x; } 475 } 476 477 LogWrite.write("ActivityDB.selectActiveAlarms acts="+ acts.size()); 478 return acts; 479 } 480 481 486 public final void truncate() 487 throws SQLException { 488 489 String query = "truncate table activity"; 490 491 Statement stmt = null; 492 try { 493 stmt = con.createStatement(); 494 if (Prefs.DEBUG) LogWrite.write(query); 495 stmt.executeUpdate(query); 496 } catch (SQLException e) { 497 throw e; 498 } finally { 499 try { if (stmt != null) stmt.close(); 500 } catch (SQLException x) { } 501 } 502 } 503 } 504 | Popular Tags |